In PostGIS, how do I find all points within a polygon?

Question

I am using PostgreSQL with the GIS extension to store map data, together with OpenLayers, GeoServer etc. Given a polygon, e.g. of a neighborhood, I need to find all LAT/LONG points stored in some table (e.g. traffic lights, restaurants) that are within the polygon. Alternatively given a set of polygons I would like to find the set of points within each polygon (like a GROUP BY query, rather then iterating over each polygon).

Are these functions something I need to program, or is the functionality available (as extended SQL)? Please elaborate.

Also for the simple 2D data I have do I actually need the GIS extension (GPL license is a limitation) or will PostgreSQL suffice?

Thanks!

score 14 · Answer 1 · edited Jan 23 '13 at 01:14

In PostGIS you can use the bounding box operator to find candidates, which is very efficient as it uses GiST indexes. Then, if strict matches are required, use the contains operator.

Something like

SELECT 
     points,neighborhood_name from points_table,neighborhood 
WHERE 
     neighborhood_poly && points /* Uses GiST index with the polygon's bounding box */
AND 
    ST_Contains(neighborhood_poly,points); /* Uses exact matching */

About if this is needed, depends on your requirements. For the above to work you certainly need PostGIS and GEOS installed. But, if bounding box match is enough, you can code it simply in SQL not needing PostGIS.

If exact matches are required, contains algorithms are publicly available, but to implement them efficiently requires some effort implementing it in a library which would then be called from SQL (just like GEOS).

The `neighborhood_poly && points` check is now redundant. [`ST_Contains`](http://postgis.net/docs/ST_Contains.html) uses the index automatically now. I'm not sure exactly what version that changed, but I think it was as of [1.3.0](https://postgis.net/docs/release_notes.html#idm43912) ("Added inline index support for relational functions (except ST_Disjoint)"). — jpmc26, Jan 18 '19 at 19:36

score 4 · Answer 2 · answered Jun 02 '10 at 23:24

4

I believe ST_Contains automatically rewrites the query to use the the GiST-indexed bounding box, as it notes:

"This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries. To avoid index use, use the function _ST_Contains."

http://postgis.refractions.net/docs/ST_Contains.html

answered Jun 02 '10 at 23:24

David

291
2
4

This is more a comment than an answer. – RickyA Jul 11 '13 at 09:28
1

It's an answer as per the SO FAQ. – IamIC Apr 13 '16 at 10:45
It doesn't "rewrite." `ST_Contains` were redefined to be plain SQL functions that invoke the SQL `geomA && geomB AND _ST_Contains(geomA, geomB)`, with `_ST_Contains` mapping to the compiled binary function that does the actual contains check. Most other relational functions were also redefined similarly. – jpmc26 Jan 18 '19 at 19:38

score 2 · Answer 3 · answered Jan 13 '14 at 08:01

2

ST_Contains would solve your problem.

SELECT  
polygon.gid, points.x, points.y
FROM
  polygons LEFT JOIN points
ON st_contains(polygon.geom, points.geom)
WHERE
  polygon.gid=your_id

answered Jan 13 '14 at 08:01

Jackie

307
2
14

In PostGIS, how do I find all points within a polygon?

3 Answers3