Reverse Geocoding in PostGIS using TIGER Data

Published

Recently I was trying to attach location information (city, county, and state) to some geotagged photos. I came across the PostGIS TIGER Geocoder, but despite finding some helpful guides, I had a difficult time getting the extension up and running.

I took a simpler approach: loading TIGER data into PostGIS and querying the tables to match any shapes that intersected by photo’s GPS coordinates. From there I could pull the names of the matching shapes, which contained the city/county/state information.

Downloading TIGER Shapefiles

TIGER data is available for public download on the US Census website. I used the urban areas (a.k.a. city), county, and state datasets, and used the following commands to download and extract the shapefile archives:

wget https://www2.census.gov/geo/tiger/TIGER2019/UAC/tl_2019_us_uac10.zip
wget https://www2.census.gov/geo/tiger/TIGER2019/COUNTY/tl_2019_us_county.zip
wget https://www2.census.gov/geo/tiger/TIGER2019/STATE/tl_2019_us_state.zip

unzip tl_2019_us_uac10.zip
unzip tl_2019_us_county.zip
unzip tl_2019_us_state.zip
US State boundaries captured in the TIGER state dataset

Loading TIGER Data into PostGIS

Once I had the shapefiles available, I used the shp2pgsql utility to load the data into PostGIS. I used the commands below to create tables inside my existing photo database and with the corresponding shape data:

shp2pgsql -I -s 4326 -W "latin1" tl_2019_us_uac10.shp urbanareas | psql -d photo -U postgres
shp2pgsql -I -s 4326 -W "latin1" tl_2019_us_county.shp counties | psql -d photo -U postgres
shp2pgsql -I -s 4326 -W "latin1" tl_2019_us_state.shp states | psql -d photo -U postgres

The -s parameter is declaring a projection of 4326 on the geometry columns in PostGIS. Make sure this projection matches the SRID/projection of any source geometries you’re querying against.

Querying for Location

Next it was time to take the GPS coordinates on my photos and query the TIGER data tables to find the city, county, and state. Here are a few example queries using the TIGER data:

-- returns urban area: Chicago, IL--IN
SELECT name10 FROM urbanareas WHERE ST_Contains(geom, ST_GeomFromText('POINT(-87.74181388888 41.789933333333)', 4326))

-- returns county: Cook
SELECT name FROM counties WHERE ST_Contains(geom, ST_GeomFromText('POINT(-87.74181388888 41.789933333333)', 4326))

-- returns state: Illinois
SELECT name FROM states WHERE ST_Contains(geom, ST_GeomFromText('POINT(-87.74181388888 41.789933333333)', 4326))

After confirming these queries returned the location data I was looking for, I needed to join them with my existing photo table so my photo’s GPS coordinate (saved in the meta_geom column) could be used as the location source:

SELECT states.name 
FROM states, photo 
WHERE ST_Contains(states.geom, photo.meta_geom)

When I originally ran this query, PostGIS complained that the SRID on my photo.meta_geom column did not match the SRID on the TIGER data table. To fix this issue, I wrapped my meta_geom column with the ST_SetSRID() function documented here.

Saving Location Information

There are several options for saving this new location information back into PostGIS including:

  • Creating a new column on the existing table and scripting a batch update for existing rows
  • Creating a view on top of the existing table with extra columns that populate the location dynamically using queries like those mentioned above
  • Creating a materialized view on top of the existing table so that location information can be persisted, and does not need to be calculated with each query

What’s Next

This solution works well with geocoded data that falls within the United States., however it would be great to also cover other countries and large urban areas.

OpenStreetMaps provides a wealth of GIS information covering the entire planet, however extracting country or city shapes is difficult because OSM data is generally packaged by region (otherwise parsing the global dataset north of 80 gigabytes can be quite a challenge).

So far I have not been able to find country/city exports of OSM data, but believe me, I’ll be keeping an eye out 😉

Subscribe by Email

Enter your email address below to be notified about updates and new posts.


Comments

Loading comments..

No responses yet

Leave a Reply

Your email address will not be published. Required fields are marked *