Changes between Initial Version and Version 1 of UsersWikiExamplesFindNearby

Timestamp:

Apr 14, 2009, 8:50:10 AM (15 years ago)

Author:

pierre

Comment:

—

UsersWikiExamplesFindNearby

@@ +1 @@
+= 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?"
+
+Taking the schools example as a template, here is a simple query to answer the question:
+
+{{{
+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.
+
+Newer way for version 1.3 and above
+
+{{{
+SELECT the_geom, school_name
+ FROM schools
+ WHERE st_dwithin(the_geom, 'POINT(100312 102312)',5000)
+
+}}}