Load Excel files into PostGIS
Last updated on August 28, 2022.
Excel spreadsheets are a common source for geographic data but are a pain to parse because they're not spatial-native (like a shapefile). Usually you need to convert the
.xlsx file into a
.csv, then create a schema in your spatial database to fit that format. Only after that can you upload the Excel file into the database, normally by using the
COPY PostgreSQL command.
While most Excel spreadsheets come with latitude and longitude columns, they sometimes are instead given ZIP codes, GEOIDs, or FIPS 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.
While there are many blog articles describing the conversion process, it's onerous and annoying. Here, you can drag-and-drop Excel spreadsheets and we'll convert it into a PostGIS table we manage, or allow you to download it as a spatial .sql file.
We'll automatically geocode the spreadsheet if needed. You can directly upload this .sql file to your PostGIS instance. Or, you can choose to have us host it for you.
Excel Format Support:
- Loads one Excel sheet
- Geocodes addresses
- Identifies FIPS and GEOIDs
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 \
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)