44

My data is in a PostGIS database. I would like to generate a shapefile from a query. How can I do that?

85

The recommended way to do this is using the pgsql2shp utility, which should be installed with PostGIS. Note that you must include the geometry column in the query.

$ pgsql2shp -f <path to output shapefile> -h <hostname> -u <username> -P <password> databasename "<query>"

Example (creates qds_cnt.shp in current directory):

$ pgsql2shp -f qds_cnt -h localhost -u postgres -P password gisdb "SELECT sp_count, geom FROM grid50_rsa WHERE province = 'Gauteng'"

Initializing... 
Done (postgis major version: 2).
Output shape: Polygon
Dumping: XXXXXXXXXXXXXXXXXXXX [1947 rows].

If you wanted to save a whole table as a shapefile, just use the table name as the query.

You can also use the ogr2ogr utility, but it has more dependencies so should not be the first option. If you are determined, the equivalent command would be:

$ ogr2ogr -f "ESRI Shapefile" qds_cnt.shp PG:"host=localhost user=postgres dbname=gisdb password=password" -sql "SELECT sp_count, geom FROM grid50_rsa WHERE province = 'Gauteng'"

See also

8

I don't have enough reputation points to comment on rudivonstaden's answer but I would add that writing sql commands in uppercase letters matters to pgsql2shp.

For instance, this won't work:

$ pgsql2shp -f qds_cnt -h localhost -u postgres -P password gisdb "Select sp_count, geom from grid50_rsa where province = 'Gauteng'"

whereas this will work:

$ pgsql2shp -f qds_cnt -h localhost -u postgres -P password gisdb "SELECT sp_count, geom FROM grid50_rsa WHERE province = 'Gauteng'"
7

Depending on the piece of data you want to export, another way is to use qgis or a similar product : there you open a connection ot postgis and select the data you're interested in; then you save as shapefile...

If you want to export automatically and/or large parts of data, rudivonstaden gave appropriate solutions!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.