Examples Find Nearby

One of the most common spatial SQL queries answers the question: "What are all the features within distance X of this other feature?" In concrete terms: "How many cell towers are within 1km of my current location?" or "What are the schools within 5km of this house?"

For versions 1.3 and higher, the answer is very simple:

SELECT the_geom, school_name
 FROM schools
 WHERE ST_DWithin(the_geom, 'POINT(100312 102312)',5000)

For older versions, you will have to hand-roll the query:

SELECT the_geom, school_name
FROM schools
WHERE Distance(the_geom, 'POINT(100312 102312)') < 5000

Note that we have translated "5km" into 5000 meters, and "this house" into a discrete POINT. The example above has a major defect, it does not use a spatial index. As a result, the performance will get much worse as the number of records in the schools table goes up.

(Note also that distance function is related to the format your map data is in….it is not always kilometers! This will trick up beginners).

Here is an example of the same query that uses a spatial index:

SELECT the_geom, school_name
FROM schools
WHERE the_geom && Expand('POINT(100312 102312)',5000)
AND Distance(the_geom, 'POINT(100312 102312)') < 5000

Note the new logical clause before the AND. The && operator is the index operator, and represents a bounding box filter between the feature in the_geom and the comparison feature. The Expand() function is a utility function that takes an existing feature and returns a new feature with a larger bounding box. The result of this new query is to first use the index to find all features where the bounding boxes interact within a tolerance of 5km, then use the exact Distance() function to strip the final result set out of the intermediate bounding box result set.