In my previous post, a long 24-hours ago, I proposed some automatic modification of line for cartographic reasons. I had some flaws in my code. The points were over-rotated by 45 degrees. Can you spot why? Tip: it’s a basic trigonometric mistake. Here’s the corrected code (though there may be a better way):

CREATE TABLE movedRoad AS
SELECT
    ST_Translate(b.the_geom,
        0.5 * ST_Distance(b.the_geom, ST_Union(a.the_geom)) * (cos(ST_Azimuth(b.the_geom, ST_ClosestPoint(ST_Union(a.the_geom), b.the_geom))) - 3.14159/4),
        0.5 * ST_Distance(b.the_geom, ST_Union(a.the_geom)) * (sin(ST_Azimuth(b.the_geom, ST_ClosestPoint(ST_Union(a.the_geom), b.the_geom))) - 3.14159/4) )
            AS the_geom
    FROM road2_points b, road1 a
        GROUP BY a.id, b.the_geom
    ;

An alternate approach is to only move those points that are too close, ala:

CREATE TABLE movedRoadAgain AS
	SELECT
        ST_Translate(b.the_geom,
	        (100 - ST_Distance(b.the_geom, ST_Union(a.the_geom))) * (cos(ST_Azimuth(b.the_geom, ST_ClosestPoint(ST_Union(a.the_geom), b.the_geom))) - 3.14159/4) ,
	        (100 - ST_Distance(b.the_geom, ST_Union(a.the_geom)))* (sin(ST_Azimuth(b.the_geom, ST_ClosestPoint(ST_Union(a.the_geom), b.the_geom))) - 3.14159/4) ) AS the_geom
	    FROM road2_points b, road1 a
	        GROUP BY a.id, b.the_geom
		HAVING ST_Distance(b.the_geom, ST_Union(a.the_geom)) < 100

	UNION ALL

	SELECT
		b.the_geom AS the_geom
	    FROM bridle_points b, apt a
	        GROUP BY a.id, b.the_geom
		HAVING ST_Distance(b.the_geom, ST_Union(a.the_geom)) >= 100
	    ;