Changes between Initial Version and Version 1 of UsersWikiExamplesAntiNear

Timestamp:

Apr 14, 2009, 8:52:26 AM (15 years ago)

Author:

pierre

Comment:

—

UsersWikiExamplesAntiNear

@@ +1 @@
+= 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);