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
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 😉
Comments
No responses yet