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?
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
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.