2

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
2

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.

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.