I need to update a table of points with the FIP code of the nearest county polygon from a separate table. I've managed to update the column for those points that Intersect a county polygon with this:

update points p set fips = c.fips 
from counties c where ST_Intersects(c.the_geom, p.the_geom)

but I've got about 60 coastal points that, because of generalization in the county polygons, don't overlap a polygon feature. I need to update the fips of these with the NEAREST county polygon's fips code, and I've been futzing around with the <-> Indexed NN, but I just can't get it to work for me.

So, where p.fips IS NULL I need to set p.fips = c.fips of the nearest county feature.


Just do a distance sort by nearest and a limit of 1. Because <-> is an in index approach, it is pretty quick too.

  points p 
  fips = (
    SELECT fips FROM counties ORDER BY the_geom <-> p.the_geom LIMIT 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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