Geocode CSV data into PostGIS

Last updated on August 17, 2022.

Spatial CSVs come in all sorts of formats that can be a pain to parse and upload into your database. You're in luck if your data provider blessed you with latitude and longitude columns. Often, rows will be associated with ZIP codes, and you'll need to write a script to join that with some TIGER Shapefiles. Worse, if addresses or street corners are provided, you'll need to use a geocoding API that charges by the row.

Some savvy developers even write their own scripts to geocode addresses, but that process can take weeks. We've automated the entire process and support most CSV formats you'll see in the wild.

Here, you can upload your CSV file and download it as a spatial .sql file. We'll automatically geocode it. You can directly upload this .sql file to your PostGIS instance. Or, you can choose to have us host it for you.

CSV Format Support:

  • Geocodes addresses
  • Identifies FIPS and GEOIDs
  • Join with ZIP codes and state names

Once the file is converted to PostGIS, you can either download the file as .sql and upload to your database with psql, or host the file with us for free. This file fits on our free plan, which is under 128MB of storage and shared compute time. Larger datasets can be stored for $2.50/GB/month.

Uploading to PostGIS (self-host)

To load your .psql file into your PostGIS instance (local, Amazon RDS, or other), you can use the psql command line tool.

psql -U postgres -h localhost -d postgres -f downloaded_file.sql

Database Connection (managed)

If you choose Bunting as your host, connect to the PostGIS database with your username and password:

psql -W -U username --dbname data_gis \
 -h pgexample.gis.buntinglabs.com
Password:
password
Done!

Connection can also be made through GeoDjango or GeoPandas, like so:

from sqlalchemy import create_engine
db_connection_url = "postgresql://username:[email protected]:5432/data_gis"
con = create_engine(db_connection_url)  
sql = "SELECT geom, highway FROM target_table"
gdf = geopandas.GeoDataFrame.from_postgis(sql, con)