# Finding points where another point layer does NOT intersect a polygon layer

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 Jul 11 '18 at 17:36
• yes it is. Im working with a parcel polygons. – Daykray Jul 11 '18 at 18:44
• post the SQL you have tried already Kray Dizzle – ziggy Jul 11 '18 at 19:57

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_b`s points, and join that result set with `point_a`:

``````SELECT a.<id>,
a.<geom>
FROM point_a AS a
JOIN (
SELECT p.<geom>
FROM polygon AS p
WHERE NOT EXISTS (
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.