Database Reference
In-Depth Information
SELECT ST_Distance(searchpoint.the_geom,
addr.the_geom) AS dist, * FROM
chp04.knn_addresses addr,
(SELECT
ST_Transform(ST_SetSRID(ST_MakePoint(-81.738624,
41.396679), 4326), 3734) AS the_geom)
searchpoint
WHERE ST_DWithin(searchpoint.the_geom,
addr.the_geom, 200)
ORDER BY ST_Distance(searchpoint.the_geom,
addr.the_geom)
LIMIT 10;
This approach performs well so long as our search window,
ST_DWithin
, is the
rightsizeforthedata.Theproblemwiththisapproachisthat,inordertooptimizeit,
weneedtoknowhowtosetasearchwindowthatisabouttherightsize.Anylarger
thantherightsizeandthequerywillrunmoreslowlythanwe'dlike.Anysmallerthan
therightsizeandwemightnotgetallthepointsbackthatweneed.Inherently,we
don't know this ahead of time, so we can only hope for the best guess.
Inthissamedataset,ifweapplythesamequeryinanotherlocation,theoutputwill
return no points because the 10 closest points are further than 200 feet away. We
can see this in the following diagram:
