In PostGIS, we are trying to select the rows in a table of points (point_table) that are within a polygon, which is stored in a separate relation (poly_table). Having dug around for hours, we can't work out how to get our desired result, all the points within each polygon.

The closest we've gotten (i.e. no errors) is below, which returns 0 rows:

SELECT * FROM point_table as p 
WHERE ST_Within(p.point::geometry, 
(SELECT polygon FROM poly_table WHERE objectid=1)::geometry);

We did get a result of all FALSE boolean values from the query below, which we think is why the query above returns 0 rows...

SELECT ST_Within(p.point::geometry, 
(SELECT polygon FROM poly_table WHERE objectid=1)::geometry) 
FROM point_table as p;

We added these explicit type casts because any alternative threw an error, even though both the point and poly attributes are geography datatypes.

  • 2
    paste the result of \d point_table, and \d poly_table, and then confirm the geometry type of point and polygon. SELECT ST_GeometeryType(point) FROM point_table and SELECT ST_GeometeryType(polygon) FROM poly_table – Evan Carroll Dec 1 '16 at 0:22
  • 2
    Are both tables in the same CRS? – Liam G Dec 1 '16 at 2:31

Do it as a join.

SELECT pt.*, py.*
FROM point_table pt
JOIN poly_table py
ON ST_Intersects(py.polygon, pt.point)
WHERE py.objectid = 1;
| improve this answer | |
  • Alright, super helpful - we've figured it out! Our mistake - the polygon data was actually ST_Linestring (thanks @Evan). We added a new column and loaded the data, converting it with the ST_MakePolygon function. After that, @Paul's query worked like a charm. (And @Freight_Train - both tables had a SRID of 4326.) Thanks! – OEFOBP Dec 1 '16 at 16:46

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