SELECT DISTINCT ON (poly.gid) poly.* FROM PolygonTable As poly INNER JOIN PointTable As Pt ON ST_Within(Pt.the_geom, poly.polygon_geom) ORDER BY poly.gid
The distinct on is just in case 2 points fall in the same polygon, we only want to return the polygon once. If that will never happen, yo can leave that out Leo -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of diplonics Sent: Saturday, April 04, 2009 12:16 PM To: [email protected] Subject: [postgis-users] SQL Loop using ST_Within()H Hi, I have two geometry tabels, one with four points and the other with 70,000 polygons. I want to return all the polygons that contain the four points. I can easily do this with: SELECT * FROM polygonTable WHERE ST_Within((SELECT the_geom FROM PointTable WHERE gid = 3), polygon_geom); Is there a way to perform this query in a loop so that I don't have to change the nested select statement for every point id i need to test. Any help appreciated, and thanks in advance. Diplonics -- View this message in context: http://www.nabble.com/SQL-Loop-using-ST_Within%28%29H-tp22885062p22885062.ht ml Sent from the PostGIS - User mailing list archive at Nabble.com. _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users __________ Information from ESET NOD32 Antivirus, version of virus signature database 3988 (20090404) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
