Database Reference
In-Depth Information
When used in an
ORDER BY
clause, the distance operator uses the index:
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
ORDER BY addr.the_geom <->
searchpoint.the_geom
LIMIT 10;
Thisapproachrequiresno
a priori
knowledgeofhowfarthenearestneighborsmight
be.Italsoscalesverywell,returningthousandsofrecordsinnotmorethanthetime
ittakestoreturnafewrecords.Itissometimesslowerthanusing
ST_DWithin
,de-
pendingonhowsmalloursearchdistanceisandhowlargethedatasetwearedeal-
ing with. But the tradeoff is that we don't need to make a guess as to our correct
searchdistanceandforlargequeries,itcanbemuchfasterthanthenaïveapproach.
How it works...
WhatmakesthismagicpossibleisthatPostGISusesanR-Treeindex.Thismeans
thattheindexitselfissortedhierarchicallybasedonspatialinformation.Asdemon-
strated,wecanleveragethestructureoftheindexinsortingdistancesfromagiven
arbitrarylocationand,thus,usetheindextodirectlyreturnthesortedrecords.This
meansthatthestructureofthespatialindexitselfhelpsusanswersuchfundamental
questions quickly and inexpensively.
Tip
More information about KNN and R-tree can be found at
ht-
and
