Snap To Closest

index.sql

 with nearest as (
 SELECT DISTINCT ON(b.cartodb_id) b.cartodb_id as id_point, a.cartodb_id as id_road, a.the_geom
 FROM stuartlynn.primaryandsecondaryroads a INNER JOIN stuartlynn.taret b
 ON ST_DWithin(CDB_LATLNG(b.dest_lat, b.dest_lng), a.the_geom, 1000)
 ORDER BY b.cartodb_id, ST_Distance(a.the_geom, b.the_geom)
 ),

snap as (
 select ST_SNAP(CDB_LATLNG(b.origin_lat, b.origin_lng),  a.the_geom ,1.01 ) as the_geom,
id_point as cartodb_id
  
   from nearest,
   stuartlynn.target b,
   stuartlynn.primaryandsecondaryroads a
 
   where
   b.cartodb_id = id_point and
   a.cartodb_id  = id_road
)
update stuartlynn.target

set dest_lat_snap = ST_Y(snap.the_geom),dest_lng_snap = ST_X(snap.the_geom)

from snap
where snap.cartodb_id = stuartlynn.target.cartodb_id