Database Reference
In-Depth Information
How to do it...
InsofarasKNNisanuancedapproachtotheseproblems,forcingKNNtorunonall
therecordsinadatasettakeswhatIliketocallavenerableandage-oldapproach.
In other words, it requires a bit of a hack.
Tip
MoreonthegeneralsolutiontousingKNNwithinafunctioncanbefoundinAl-
exandre Neto's post on the PostGIS users list at the following link:
In SQL, of course, the typical way to loop is to use a
SELECT
statement. For our
case, we don't have a function that does KNN looping; we simply have an operat-
or that allows us to efficiently order our returning records by distance from a given
record. The workaround is to write a temporary function and, thus, be able to use
SELECT
toloopthroughtherecordsforus.Thecostisthecreationanddeletionof
the function, plus the work done by the query, and the combination of costs is well
worth the "hackiness" of the approach.
First, consider the following function:
CREATE OR REPLACE FUNCTION
chp04.angle_to_street (geometry) RETURNS double
precision AS $$
WITH index_query as
(SELECT
ST_Distance($1,road.the_geom) as dist,
degrees(ST_Azimuth($1,
ST_ClosestPoint(road.the_geom, $1))) as azimuth
FROM chp04.knn_streets As road
ORDER BY $1 <#> road.the_geom
limit 5)
