Database Reference
In-Depth Information
How it works...
Ourfunctionissimple,KNNmagicaside.Asaninputtothefunction,weallowgeo-
metry as shown in the following code:
CREATE OR REPLACE FUNCTION
chp04.angle_to_street (geometry) RETURNS double
precision AS $$
The preceding function returns a floating-point value.
We then use a
WITH
statement to create a temporary table that returns the five
closestlinestoourpointofinterest.Remember,astheindexusesboundingboxes,
wedon'treallyknowwhichlineistheveryclosest,sowegatherafewextrapoints
and then filter them based on distance. This idea is implemented in the following
query:
WITH index_query as
(SELECT
ST_Distance($1,road.geom) as dist,
degrees(ST_Azimuth($1,
ST_ClosestPoint(road.geom, $1))) as azimuth
FROM street_centerlines As road
ORDER BY $1 <#> road.geom LIMIT
5)
Notethatweareactuallyreturningtwocolumns.Thefirstcolumnis
dist
inwhich
we calculate the distance to the nearest five road lines. Note that this operation
is performed after the
ORDER BY
and
LIMIT
functions have been used as filters,
so this does not take much computation. Then, we use
ST_Azimuth
to calculate
the angle from our point to the closest points (
ST_ClosestPoint
) on each of our
nearestfivelines.Insummary,whatreturnswithourtemporary
index_query
table
is the distance to the nearest five lines and the respective rotation angles to the
nearest five lines.
