I have a roads table that has been transformed into nodes for use with pgrouting.
I also have a table of geocoded addresses which contains lat, lon and geom. I am trying to write a query that updates the addresses table with the GID of the nearest node.
update addresses set nearest_node_id=(SELECT gid FROM roads ORDER BY geom <-> ST_GeometryFromText('POINT(addresses.lat addresses.lon)',4326) LIMIT 1 );
The Select gid ... portion of this works fine with string values subbed for addresses.lat & lon. But I can't figure out how to get the whole thing working.