Make Lines from Points to nearest road

lineToStreet

CREATE OR REPLACE FUNCTION line_to_street (geometry) RETURNS geometry AS $$
 
WITH index_query as
                (SELECT ST_Distance($1,road.the_geom_webmercator) as dist,
                                ST_MakeLine(ST_ClosestPoint(road.the_geom_webmercator, $1), $1) as the_geom_webmercator
                FROM cuy_streets As road
                ORDER BY $1 <#> road.the_geom_webmercator limit 10)

SELECT the_geom_webmercator
                FROM index_query
                ORDER BY dist
LIMIT 1;
 
$$ LANGUAGE SQL;



SELECT cartodb_id,
	line_to_street(addr.the_geom_webmercator)
	AS the_geom_webmercator
        FROM
      address_points_4326 addr