I am using PostGreSQL to deal with 3 layers. I have two point layers(points A and points B) and a polygon layer. Some of the points in both A & B fall within the same polygon. I want to find all of the points in points A where points B do not intersect with the polygon layer. How can I intersect B and the polygons while only needing the points from point layer A?

  • so is this a multipolygon?
    – Matt
    Commented Jul 11, 2018 at 17:36
  • yes it is. Im working with a parcel polygons.
    – Krizzle
    Commented Jul 11, 2018 at 18:44
  • post the SQL you have tried already Kray Dizzle
    – ziggy
    Commented Jul 11, 2018 at 19:57

1 Answer 1


That has always been a little tricky; simply joining two tables by non-intersection results in their conditioned cartesian product.

The key here is to sieve the polygons by exclusion first and then join the third table by intersection. I'll use a NOT EXISTS construct to get only those polygons that do not intersect with point_bs points, and join that result set with point_a:

SELECT a.<id>,
FROM point_a AS a
  SELECT p.<geom>
  FROM polygon AS p
    SELECT 1
    FROM point_b AS b
    WHERE ST_Intersects(b.<geom>, p.<geom>)
) AS excl
ON ST_Intersects(a.<geom>, excl.<geom>);

I found this to be most efficient in terms of speed. There are other ways, e.g. this (old) post has some more, including the above and one doing it nicely wrong.

Note: For larger tables you might want to ST_Dump your MultiPolygons into a new (indexed) table. In general, MultiGeometries are not best suited for spatial relation analysis.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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