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!

Answer 1

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).

Answer 2

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

Answer 3

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

友情链接