Database Reference
In-Depth Information
shp2pgsql -s 3734 -d -i -I -W LATIN1 -g
the_geom CUY_ADDRESS_POINTS_subset
chp04.knn_addresses | psql -U me -d
postgis_cookbook
Wespecifiedthe
-I
flaginordertorequestaspatialindexbecreatedupontheim-
port of this data.
Let us start by seeing how many records we are dealing with:
SELECT COUNT(*) FROM chp04.knn_addresses;
--484958
Wehave,inthisaddresstable,almosthalfamillionaddressrecords,whichisnotan
insubstantial number against which to perform a query.
How to do it...
KNNisanapproachtosearchingforanarbitrarynumberofpointsclosesttoagiven
point.Withouttherighttools,thiscanbeaveryslowprocessthatrequirestestingthe
distance between the point of interest and all the possible neighbors. The problem
withthisapproachisthatthesearchbecomesexponentiallyslowerasthenumberof
points increases. Let's start with this naïve approach and then improve upon it.
Supposewewereinterestedinfinding10recordsclosesttothegeographiclocation,
-81.738624, 41.396679. The naïve approach would be to transform this value into
ourlocalcoordinatesystemandcomparethedistancetoeachpointinthedatabase
fromthesearchpoint,orderthosevaluesbydistance,andlimitthesearchtothefirst
10closestrecords(itisnotrecommendedthatyourunthefollowingquery—itcould
run indefinitely)
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,
