Geocoding US Counties with PostGIS

us_counties_geocoding.md

County/State level geocoding in postgis

If your geocoding task requires only US county and state level information, you can avoid a third party web service dependency using open data and postgis.

Data source

Natural Earth counties, US only.

wget https://www.naturalearthdata.com/http//www.naturalearthdata.com/download/10m/cultural/ne_10m_admin_2_counties.zip

Ingest

Use OGR directly to import the zipped shapefile into a us_counties table.

ogr2ogr -f "PostgreSQL" \
    PG:"host=localhost user=postgres dbname=local-test-postgis password=postgres" \
    /vsizip/ne_10m_admin_2_counties.zip \
    -nln us_counties \
    -nlt PROMOTE_TO_MULTI

Find the county and state associated with your point features

For polygons, presumably the point_features.point would be derived from the centroid of the original geometry.

-- Augment the source table
-- with county and state columns
-- based on spatial intersection
with point_features as (
  select st_setsrid(st_point(-120, 40), 4326) as point,
  'test point' as name
)
select p.*, c.name as county_name, c.region as state
from point_features as p
join us_counties as c
     on st_intersects(p.point, c.wkb_geometry);