1

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.

  • 1
    What error messages, if any, are you seeing when trying to execute that? – greenlaw Aug 15 '14 at 20:36
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 );
  • Right, that makes sense. Works perfectly now. – user2658742 Aug 15 '14 at 21:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.