Saturday, 11 July 2015

point in polygon - How to find all features in one table that intersect features in another table in PostGIS?


I have two tables, boundary polygon table and location point table. I need to find all boundary polygons that contain at least 1 location point. What is the proper way to do this in PostGIS?


I tried:



SELECT *
FROM boundary, location
WHERE ST_Intersects(boundary.geom, location.geom)

But this returns each combination of boundary and location that intersect, when all I want is the boundary returned once. For example, I got 76 results, but only have 10 boundary entries. Do you know a better way to do this?



Answer



Would be more efficient with EXISTS I think since it can stop after first positive. Though Martin's answer should work as well


SELECT boundary.*
FROM boundary
WHERE EXISTS (SELECT location.geom

FROM location
WHERE ST_Intersects(boundary.geom,location.geom) );

No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...