Examples Anti Near

Anti Near Neighbor Search - Aka the Non Join Join

Problem: Find all locations who are more than 3000 meters away from their closest hospital.

Solution: This example uses the trick that the primary key or non-nullable field of the right table in a left join will return NULL only in the situation where there is no match to the left table. So here we first ask the question what hospitals are within 3000 meters from each location and then we throw out all the records that have hospitals within that distance.

SELECT l.location_name
FROM locations As l
LEFT JOIN hospitals h ON ST_DWithin(l.the_geom, h.the_geom,3000)
WHERE h.hospital_id IS NULL;

Another way of solving the same problem:

SELECT l.location_name
FROM locations As l
INNER JOIN hospitals h ON not ST_DWithin(l.the_geom, h.the_geom,3000);

We just switch the result of the boolean question ST_DWithin and get the ones not matching.

or maybe:

SELECT l.location_name
FROM locations As l, hospitals h
WHERE not ST_DWithin(l.the_geom, h.the_geom,3000);