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.