Export shapefile from postgreSQL – pgsql2shp – Command line or Php

Is your GIS data stored in postgreSQL Database? Are you looking to export that postgreSQL data in shapefile and further use in different way. It is directly possible with Pgsql2shp command which is directly available with postgreSQL system. In this article you will directly understand the way to export Shapefile from postgreSQL with pgsql2shp tool.

Pgsql2shp (postgis to shapefile dumper) is command that provides shapefile and takes query as input. The geometry column should be present when running the postgis query.
You need to download zip file to run pgsql2shp. You can download pgsql2shp tool from any authentic website for mac, ubuntu or linux system or windows.

Similarly you can import the shapefile in postgresql using the command line tool shp2pgsql. 

Export shapefile from postgreSQL – pgsql2shp

Here we will check pgsql2shp tool that can be used either with command line or with php language. Similarly you may connect to postgresql with server side language and use pgsql2shp.

With Command line – Export shapefile from postgreSQL

After downloading the tool file, navigate from command line to that particular folder. You may type this command to get the help description and options of the tool —

pgsql2shp

The general syntax for the command pgsql2shp is:

pgsql2shp [options] database [schema.]table
pgsql2shp [options] database query

To export a full table from postgis to shapefile:

pgsql2shp  -u <username> -h <hostname> -P <password> -p 5434 -u postgres -f <file path to save shape file> <table_name>

To export a query data from postgis to shapefile

pgsql2shp -f <file path to save shape file> -u <username> -h <hostname> -P <password> <database Name> “query to be executed”

There are many option,which are listed below-
OPTIONS
The command line options are:

-f <filename>
Write the output to a particular filename.

-h <host>
The database host to connect to.

-p <port>
The port to connect to on the database host.

-P <password>
The password to use when connecting to the database.

-u <user>
The username to use when connecting to the database.

-g <geometry column>
In the case of tables with multiple geometry columns, the
geometry column to use when writing the shape file.

-b Use a binary cursor. When used on pre-1.0.0 PostGIS versions
this will reduce the likelihood of coordinate drift due to
conversion to and from WKT format. Coordinate drifts will not
occur with PostGIS 1.0.0 and newer versions. It will be slightly
faster, but might fail if any NON-geometry column lacks a cast
to text.

-r Raw mode. Do not drop the gid field, or escape column names.

-d For backward compatibility: write a 3-dimensional shape file
when dumping from old (pre-1.0.0) postgis databases (the default
is to write a 2-dimensional shape file in that case). Starting
from postgis-1.0.0+, dimensions are fully encoded.

-k Keep identifiers case (don’t uppercase field names).

-m <filename>
Specify a file containing a set of mappings of (long) column
names to 10 character DBF column names. The content of the file
is one or more lines of two names separated by white space and
no trailing or leading space:

-? Display version and usage information.

With programming language – Export shapefile from postgreSQL

We have used codeigniter to execute the query and export the postgis data to shapefile. The scenario is very simple. We have a Road shapefile of a country, where we want to find the distance between two cities or points and render the same on map. For this, we have used pgr_dijkstra() alogrithm and find the shortest path. The shortest path output is collected as shapefile and rerendered on map. There are many ways to render the shortest path, just to understand pgsql2shp we used this one.

Here queries are written in PHP, where $start and $end  are the variable here. We have written query to take starting and ending point for finding shortest path. 

This command is normally executed in terminal as written above, but we can execute it in code shell_exec() method from PHP.

$start = $this->db->query(“SELECT source FROM network1 ORDER BY geom <-> ST_SetSRID(ST_Point ($dlong1,$dlat1),4326) LIMIT 1”)->result();
$end= $this->db->query(“SELECT source FROM network1 ORDER BY geom <-> ST_SetSRID(ST_Point ($dlong2,$dlat2),4326) LIMIT 1″)->result();
$start1= $start[0]->source;
$end1= $end[0]->source;
$query=”‘Select gid as id, source, target, st_length(geom::geography)/1000 as cost from network1′”;

$command= ‘pgsql2shp -f “/var/www/html/PHP_pgrouting/public/shpfolder/try_new.shp” -h localhost -u user -P “pass1” postgres “select seq,id1,id2,cost,geom from pgr_dijkstra(‘.$query.’,’.$start1.’,’.$end1.’,false, false) as di JOIN network1 pt ON (di.id2 = pt.gid)”‘;

echo shell_exec($command);

This will produce a shapefile, where we have rendered the same as you can see in the output.

I hope this article might have helped you in convert and export shapefile from postgreSQL with pgsql2shp tool. If you find any problem in implementing the steps, let me know via comment, so that we can solve the same.