Join Zip Codes Table to Nearest City from Second Table

Question

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.

Answer 1

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