Converting and Importing Shapefiles for PostGIS
Introduction
Geographical data is most likely produced by shapefiles. A shapefile is a vector data format used for storing data that references geographical objects. These files must be converted into a format that your database can read before it is stored and queried.
A shapefile is commonly downloaded as a single .zip file, that once unzipped, contains three mandatory files with the prefixes .shp, .dbf and .shx. The .shp file contains the geography data, which includes points, lines, and polygons. The .dbf file (or dBase table) contains non-geographic features and attributes that describes the data. And the .shx file contains indices of the record sets in the .shp file for quicker lookups.
Installing gdal
GDAL is used to convert shapefiles to GeoJSON.
If you're using a Mac, you can installed GDAL using Homebrew:
brew install gdal
Getting sample data
You can get a shapefile for the world from Natural Earth Data. We also have a list of shapefiles for the US and Canada.
Converting to a SQL file
The ogr2ogr utility from GDAL can be used to easily read and convert ESRI shapefiles to SQL scripts.
ogr2ogr -nlt PROMOTE_TO_MULTI -f PGDump -t_srs "EPSG:4326" filename.sql filename.shp
If necessary, you will need to set the encoding:
SHAPE_ENCODING="ISO-8859-1" ogr2ogr -nlt PROMOTE_TO_MULTI -f PGDump -t_srs "EPSG:4326" filename.sql filename.shp
The .sql can be imported into PostgresSQL with the psql commandline:
psql < shapefile.sql
If psql fails with the error "ERROR: function addgeometrycolumn(unknown, unknown, unknown, unknown, unknown, integer) does not exist", you likely don't have the PostGIS extension installed. Install it before trying to reimport the .sql file to Postgres:
-- Enable PostGIS (includes raster)
CREATE EXTENSION postgis;
-- Enable Topology
CREATE EXTENSION postgis_topology;
Your first PostGIS geo query
The schema browser in the main editor screen will show a column with the data type geometry. You won't be able to query it as a regular field (it's binary), so you'll have to convert it to text in order to view it in the data browser. The ST_AsText function returns geometry as WKT / Well Known Text:
SELECT ST_AsText(geom) from geotable;
In order to view it on a map, you can use the ST_AsGeoJSON function that returns the geometry as a GeoJSON element:
SELECT ST_AsGeoJSON(geom) from geotable;
Onboarding forward-thinking teams now:
No spam, ever! Unsubscribe any time. Learn more about the product.