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);
  • 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? – HimBromBeere Mar 22 '16 at 7: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 7:52
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.

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 
  • Do I need to pass polygon value in place of myQueryGeometry ? – Aparichith Mar 22 '16 at 8:07
  • Yeap, it is your search-polygon. – HimBromBeere Mar 22 '16 at 8:08
  • select id from test_table t where st_within(t.geoproperty, (select geoproperty from t where id = 123)); failing by saying type cast error. – Aparichith Mar 22 '16 at 8:18
  • And what exactly is the error message? What cannot be cast to anything else? Please be a bit more specific on your errors. – HimBromBeere Mar 22 '16 at 9:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.