Spatial Join in CartoDB

Question

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.

Answer 1

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

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