Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
    
If you run the select query by itself what do you get? –  Nathan W Dec 28 '12 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). –  Thi Duong Nguyen Dec 28 '12 at 17:55
add comment

1 Answer

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);
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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