I have one PostGIS table of polygons like this:

polygon_id | polygon_geom

And another of points like this:

point_id | polygon_id | point_geom

I want to find all the points from the points table that are within a specific polygon from the polygons table. I tried this query:

SELECT points.point_id FROM points, polygons WHERE St_contains((SELECT polygons.polygon_geom FROM polygons WHERE polygons.polygon_id = 2905), points.point_geom) = 't';

I expect 21 point_id's, but instead it gives me the entire polygon table with each line repeated 21 times. How can I get just the 21 rows from the points table? And furthermore, how can I then assign the polygon_id that these points are within to the points.polygon_id column for those 21 rows in the points table?

EDIT: If I use this query to check if one polygon is in another from the polygon table, this query works perfectly. So I feel like I'm close but fundamentally misunderstanding something.

EDIT 2: changed polygon.id column to polygon.polygon_id


Your problem is the Where clause. The FROM points, polygons syntax gives you a Cartesian join between both tables, ie, it is just syntactic sugar for a full outer join. So, you can use ST_Contains to only return those points that are inside a polygon. The array_agg function will give you all polygons that a point is inside, in the case that there might be more than one.

   pts.point_id, array_agg(poly.polygon_id)
    points pts, polygons poly
 WHERE ST_Contains(poly_geom, point_geom)
 GROUP BY pts.point_id;
  • Thanks! This looks like the answer. My table is pretty big so in order to test this quickly, can I plug in a specific polygon_id to just test one polygon first? Where can I plug that in? – sunday_funday Jan 4 at 15:26
  • ` WHERE ST_Contains(poly_geom, point_geom) AND polygon_id = 1`. Make sure you have a spatial index on the polygon table. – John Powell Jan 4 at 15:38

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.