2

I have just one table of street segments of Berlin. I have a query that outputs all segments of category > 1, that are within a distance of 2000m of those segments which are of category 1.

This works fine:

SELECT DISTINCT s.id, s.geom
    FROM table s
    INNER JOIN table m 
    ON ST_DWithin(s.geom, m.geom, 2000)
    WHERE m.category = 1
    AND s.category > 1;

But now i want to exclude from the result set those segments of s that intersect with m. So i just appended the following expression:

...
AND NOT ST_Intersects(s.geom, m.geom);

With this additional line the result set is the same as without, though i know there are segments that meet this condition. If i omit the 'NOT', asking for segments that DO intersect, i get about 400 segments. I also tried to use Disjoint, with the same problem.

Any idea?

5

You have a set of things in M. You have a set of things in S. You are thinking setwise when you apply "not intersects". However, the condition will be applied pairwise to every pair of features in M and S. So you could have M1,S1 that intersect, so they fail the condition, but M2,S1 to not intersect, so S1 can still be allowed into the final result.

The fix is kind of ugly. You take your dwithin join and add to it a left intersects join, so you can pick off just the parts that fail the join condition, like this:

WITH dwithin AS (
  SELECT DISTINCT s.id, s.geom
    FROM table s
    INNER JOIN table m 
    ON ST_DWithin(s.geom, m.geom, 2000)
    WHERE m.category = 1
    AND s.category > 1
)
SELECT dwithin.id, dwithin.geom
FROM dwithin
LEFT JOIN table m
ON ST_Intersects(m.geom, dwithin.geom)
WHERE m.id IS NULL
  • Thank you. In my case it works with a small modification: As penultimate line add: AND m.category = 1 – dijea Sep 29 '16 at 13:51

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.