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 have two tables, siteareas and ziplookup. The former contains large cities in the US; the latter all US zips with lat/lngs. I have a foreign key into the former table in the latter: sitearea_id. I have made the join for all cities in the zip table that area present in the siteareas table. I wish to do so for the remaining zip codes by finding the nearest city from siteareas and updating ziplookup with that city's sitearea_id.

Here's what I have so far:

update ziplookup set sitearea_id = siteareas.id 
FROM siteareas 
     inner join ziplookup 
     on ST_DWithin(siteareas.lnglat, ziplookup.lnglat, 0.1) 
where ziplookup.sitearea_id is null 
order by siteareas.lnglat <-> ziplookup.lnglat 
limit 1 ;

This is obviously not working, but I need a way to order by the closest result and limit to one result. This is probably more of a SQL question, but any help is appreciated.

share|improve this question
add comment

1 Answer

Something like this :

update ziplookup set sitearea_id = siteareas.id 
 FROM siteareas as s  
 WHERE ST_Distance(ziplookup.the_geom, s.the_geom > 10 ORDER BY DESC LIMIT 1

where > 10 always return something

the_geom is geometry from lon/lat coordinates

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.