Import Shapefiles to PostGIS

Last updated on September 25, 2022.

Shapefiles are Esri's ubiquitous file format for describing spatial data. Despite the frustration of shapefiles, having a spatial index, CRS metadata, and attribute specifications make it a well-rounded filetype to work with.

Importing a shapefile into PostGIS normally requires using shp2pgsql, a data loader that comes installed with PostGIS (find shp2pgsql documentation here). However for development environments you often don't have the PostGIS command line tools installed.

You can also upload your shapefile here and download it as an .sql file. This is compatible with PostGIS via psql. Also, you can choose to have Bunting host the table for you.

You must turn your shapefile directory into a .zip file before uploading. Then upload the .zip that contains the .shp, .shx, and .dbf files.

Shapefile Format Support:

  • CRS automatically detected (WGS84 default)
  • Uses GDAL

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)