postgis create geom from lat long

Question

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.

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);