Skip to main content
Geographic Information Systems

Return to Question

deleted 154 characters in body
Source Link
PolyGeo ♦
PolyGeo ♦
  • 65.4k
  • 29
  • 114
  • 345

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.

Any help is appreciated. I'm sure it's mostly my complete lack of understanding of SQL subqueries that is preventing me from doing this successfully.

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.

Any help is appreciated. I'm sure it's mostly my complete lack of understanding of SQL subqueries that is preventing me from doing this successfully.

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.

Source Link
MapNinja
MapNinja
  • 33
  • 2

Spatial Join in CartoDB

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.

Any help is appreciated. I'm sure it's mostly my complete lack of understanding of SQL subqueries that is preventing me from doing this successfully.