Changes between Version 1 and Version 2 of UsersWikiExamplesAntiNear


Ignore:
Timestamp:
04/14/09 08:52:57 (16 years ago)
Author:
pierre
Comment:

Legend:

Unmodified
Added
Removed
Modified
  • UsersWikiExamplesAntiNear

    v1 v2  
    88Solution: 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.
    99
     10{{{
    1011SELECT l.location_name
    1112FROM locations As l
     
    1314WHERE h.hospital_id IS NULL;
    1415
     16}}}
    1517
    1618Another way of solving the same problem:
    1719
     20
     21{{{
    1822SELECT l.location_name
    1923FROM locations As l
    2024INNER JOIN hospitals h ON not ST_DWithin(l.the_geom, h.the_geom,3000);
    2125
     26}}}
     27
     28
    2229We just switch the result of the boolean question ST_DWithin and get the ones not matching.
    2330
    24 or maybe :
     31or maybe:
    2532
     33{{{
    2634SELECT l.location_name
    2735FROM locations As l, hospitals h
    2836WHERE not ST_DWithin(l.the_geom, h.the_geom,3000);
     37
     38}}}