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:

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?


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

Try something like this

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

[paul ramsey made an improvement, but forgot the I in ID]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.