I ran this query successfully, but the column highwayid isn't updated as expected:

update unique_latlongs set highwayid = 
  (SELECT h.osmid FROM rn.highways h WHERE h.geom &&
  ST_Expand(ST_Transform(st_setsrid(st_makepoint(lon, lat),4326), 3414), 50)
  ORDER BY ST_Distance(h.geom, 
  ST_Transform(st_setsrid(st_makepoint(lon, lat),4326), 3414)) limit 1);

What do you think could be wrong in this query? In unique_latlongs, lat and lon are columns.

EDIT: Perhaps I should explain what I was trying to do: I have lat and long values in the unique_latlongs table. I also have the column highwayid, which I want to update to the id the the closest highway to each respective lat long in unique_latlongs. The highways are store in the rn.highways table, with the h.geom being the geometry value of the highway.

  • If you run the select query by itself what do you get?
    – Nathan W
    Dec 28, 2012 at 9:52
  • @NathanW: If i replace 'lat' and 'lon' by respective values of lat and lon of a given row, it returns a single value row (which I expected would be the value of highway_id after the update query is executed). Dec 28, 2012 at 17:55

1 Answer 1


Although the sub-query may return a single row in some instances, it might return zero rows in other instances (where there are no overlaps). If you have PostGIS 2.0, you can quickly find the nearest geometry (provided you have a GiST index on geom), and get the osmid for highwayid. Try this:

UPDATE unique_latlongs SET highwayid =
    (SELECT h.osmid
     FROM highways h
     ORDER BY h.geom <-> ST_Transform(st_SetSRID(ST_MakePoint(lon, lat), 4326), 3414)
     LIMIT 1);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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