PostGIS Cookbook - Second Edition

How to do it...

Recall our function from the Improving proximity filtering with KNN – advanced recipe:

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) 
 
SELECT azimuth FROM index_query ORDER BY dist 
LIMIT 1; 
 
$$ LANGUAGE SQL; 

This function will calculate the geometry's angle to the nearest road line. Now, to construct geometries using this calculation, run the following function:

CREATE TABLE chp04.tsr_building AS SELECT ST_Rotate(ST_Envelope(ST_Buffer(the_geom, 20)), radians(90 - chp04.angle_to_street(addr.the_geom)), ...