How to do it...

Insofar as KNN is a nuanced approach to these problems, forcing KNN to run on all the records in a dataset takes what I like to call a venerable and age-old approach. In other words, it requires a bit of a hack.

More on the general solution to using KNN within a function can be found in Alexandre Neto's post on the PostGIS users list at the following link:
http://lists.osgeo.org/pipermail/postgis-users/2012-May/034017.html

In SQL, the typical way to loop is to use a SELECT statement. For our case, we don't have a function that does KNN looping through the records in a table to use; we simply have an operator 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 to loop through the records for us. The cost is the creation and deletion of 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) 
 
SELECT azimuth FROM index_query ORDER BY dist 
LIMIT 1; 
 
$$ LANGUAGE SQL;

Now, we can use this function quite easily:

CREATE TABLE chp04.knn_address_points_rot AS SELECT addr.*, chp04.angle_to_street(addr.the_geom) FROM chp04.knn_addresses  addr; 

If you have loaded the whole address dataset, this will take a while.

If we choose to, we can optionally drop the function so that extra functions are not left in our database:

DROP FUNCTION chp04.angle_to_street (geometry); 

In the next recipe, Rotating geometries, the calculated angle will be used to build new geometries.