6

Is it possible to perform a query like this:

SELECT * FROM foo 
    JOIN bar
        ON ST_Dwithin(foo.geom, bar.geom, > 10);
        -- the > 10 means that I want to select everything that is further away then 10

I want to revert the ST_DWithin() function. I want to get all the geometries, that are farther away than 10 from foo.geom.

Is that possible? If not with the ST_DWithin() function, is there another way?

8

Use a correlated [NOT] EXISTS filter:

SELECT *
FROM   foo
WHERE  NOT EXISTS (
  SELECT 1
  FROM   bar
  WHERE  ST_DWithin(bar.geom, foo.geom, <dist>)
);
2
  • Could you Explain the WHERE NOT EXISTS ... SELECT 1?
    – Stophface
    Aug 3 at 12:13
  • I added the link to the docs - they do a good job at explaining the mechanics, including the common SELECT 1 convention.
    – geozelot
    Aug 3 at 13:24
4

Have you tried

SELECT * FROM foo 
    JOIN bar
        on ST_Dwithin(foo.geom, bar.geom, 10)=FALSE;
3
  • This results in a (costly) cartesian product.
    – geozelot
    Aug 3 at 8:28
  • Doesn't the original SQL do the same? The title of the question was about inverting ST_Dwithin. Another option is to use where ST_Distance(foo.geom, bar.geom) > 10. But your answer really solves the problem instead of just answering the question.
    – user30184
    Aug 3 at 8:47
  • 1
    Yeah I can't really just be literal in my answers if there's a misconception on the horizon. This is literally the inverse (though a NOT would be more canonical), you are right. The issue with the result set here is the cartesian product of non-intersecting geometries - I described that better in one of OPs other questions so OP will likely know what is meant.
    – geozelot
    Aug 3 at 8:57
3

It's possible with a right joint

SELECT bar.* 
FROM foo 
RIGHT JOIN bar
    on ST_Dwithin(foo.geom, bar.geom, 10)
WHERE bar.geom IS NULL;

Or with a set operator :

SELECT *
FROM bar
EXCEPT
SELECT bar.* 
FROM foo 
JOIN bar
    on ST_Dwithin(foo.geom, bar.geom, 10);

(I couldn't test this requests)

4
  • Your first query needs to be a LEFT JOIN.
    – geozelot
    Aug 3 at 8:29
  • 1
    ST_Dwithin takes just one fixed distance and > 10 gives a syntax error.
    – user30184
    Aug 3 at 8:44
  • @user30184 Indeed, the correct syntax is ST_Dwithin(foo.geom, bar.geom, 10);. I have update the answer.
    – Atm
    Aug 3 at 9:11
  • @geozelot As I understand the question, the objective is to select individuals from the bar table not from the foo table, hence the use of the RIGHT JOIN and the bar.* after the SELECT.
    – Atm
    Aug 3 at 9:22

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.