Updating table with nearest node from another table for pgRouting?

Question

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.

Something like:

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.

Answer 1

The problem appears to be that you're attempting to insert values from your SELECT into a string, but instead are specifying the columns as part of the string literal, rather than constructing the string from the selected values.

Try this:

UPDATE addresses SET nearest_node_id=(SELECT gid FROM roads ORDER BY geom <-> ST_GeometryFromText('POINT(' || addresses.lat || ' ' || addresses.lon || ')',4326) LIMIT 1 );