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);
2
  • 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? Mar 22, 2016 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, 2016 at 7:52

2 Answers 2

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.

0
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 
4
  • Do I need to pass polygon value in place of myQueryGeometry ?
    – Aparichith
    Mar 22, 2016 at 8:07
  • Yeap, it is your search-polygon. Mar 22, 2016 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, 2016 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. Mar 22, 2016 at 9:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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