2

Since I am a beginner to postgis I am facing problem with finding points within polygons from my table.

This is my table structure

   Column    |          Type           |
-------------+-------------------------+
 id          | bigint                  |
 type        | bigint                  |
 geoproperty | geometry(Geometry,4326) |

types are 1=point,2=road,3=polygon

I want to get all the points within a specified polygon.

Can somebody help me to get a working query? I tried using St_within and st_contains but failed to get results.

select id from test_table g where id = 123 and ST_Within(g.geoproperty,g.geoproperty);
Aparichith
  • 247
  • 1
  • 11
  • What does "failed to get results" mean? What results do you get? Those two functions are exactly what you need so what is your actual problem? – MakePeaceGreatAgain Mar 22 '16 at 07:45
  • I was not able to write proper query , failed to put conditions within st_within function i,e "select id from test_table t where st_within(t.geoproperty,t.geoproperty) and t.id = 123 ;" – Aparichith Mar 22 '16 at 07:52

2 Answers2

4
SELECT
    id
FROM test_table t1
JOIN test_table t2 ON (ST_Within(t1.geom, t2.geom))
WHERE t1.type = 1
    AND t2.type = 3;

You need to use a self-join to get the results.

Michal Zimmermann
  • 4,242
  • 22
  • 39
1

With your query you select those geometries that are contained within themself which should always be true. Thus you need to add your query-geometry also:

select id from test_table t where st_within(t.geoproperty, myQueryGeometry) and t.id = 123;

Alternatively use st_containsas follows:

select id from test_table t where st_contains(myQueryGeometry, t.geoproperty) and t.id = 123
MakePeaceGreatAgain
  • 1,491
  • 1
  • 10
  • 26