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?
3
-
so is this a multipolygon?– MattCommented Jul 11, 2018 at 17:36
-
yes it is. Im working with a parcel polygons.– KrizzleCommented Jul 11, 2018 at 18:44
-
post the SQL you have tried already Kray Dizzle– ziggyCommented Jul 11, 2018 at 19:57
Add a comment
|
1 Answer
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>,
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.
