Menu
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!
913
Draw a horizontal line to the right of each point and extend it to infinity. Count the number of times the line intersects with polygon edges. A point is inside the polygon if either count of intersections is odd or point lies on an edge of polygon.
There are basically two ways of conducting PIP in Shapely: using a function called within() that checks if a point is within a polygon. using a function called contains() that checks if a polygon contains a point.
Determines if a spatial object is entirely inside another spatial object without existing only on its boundary. Both arguments must be the same spatial data type. Either specify two GEOMETRY objects or two GEOGRAPHY objects.
The ST_Contains() function takes two geometry objects and returns t (TRUE) if the first object completely contains the second; otherwise, it returns f (FALSE).
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).
141
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
