Database Reference
In-Depth Information
Improving proximity filtering with KNN
Thebasicquestionthatweseektoanswerinthisrecipeisthefundamentaldistance
question,"Whicharetheclosest(namewhatyouaresearchingfor)tome?",forex-
ample,"Whicharethefivecoffeeshopsclosesttome?"Itturnsoutthatwhileitisa
fundamentalquestion,it'snotalwayseasytoanswer,thoughwewillmakethispos-
sibleinthisrecipe.Wewillapproachthiswithtwoapproaches.Thefirstwayinwhich
we'll approach this is in a simple heuristic, which will allow us to come to a solution
quickly. Then, we'll take advantage of the deeper PostGIS functionality to make the
solution faster and more general with a
K-Nearest Neighbor
(
KNN
) approach.
A concept that we need to understand from the outset is that of a spatial index. A
spatialindex,likeotherdatabaseindexes,functionslikeabookindex.Itisaspecial
constructtomakelookingforthingsinsideourtableeasier,muchinthewayabook
indexhelpsusfindcontentinabookfaster.Inthecaseofaspatialindex,ithelpsus
findfasterwherethingsareinspace.Therefore,byusingaspatialindexinourgeo-
graphic searches, we can speed up our searches by many orders of magnitude.
Note
To learn more about spatial indexes, see
http://en.wikipedia.org/wiki/Spa-
Getting ready
We will start by loading our data. Our data are the address records from Cuyahoga
County, Ohio, USA.
shp2pgsql -s 3734 -d -i -I -W LATIN1 -g the_geom
CUY_ADDRESS_POINTS chp04.knn_addresses | psql -U
me -d postgis_cookbook
As this dataset may take a while to load, you can alternatively load a subset.
