Converting and Importing Shapefiles for PostGIS
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 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.
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
Converting to a SQL file
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
.sql can be imported into PostgresSQL with the
psql < shapefile.sql
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;