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.