export

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.

Export shapefile from postgreSQL - pgsql2shp — Export shapefile from postgreSQL – pgsql2shp

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.

Edit – Export – download data from OpenStreetMap in QGIS

Edit – Export – download data from OpenStreetMap (OSM). Open Street Map is a free editable map of the world, and is very easy to handle. Here in this post we will see how to edit and create geometries on open street map. Also how to export, download and convert the .osm file to any other file.

Steps to Edit – Export – Download data from OpenStreetMap:

1.) Sign in – Before starting editing you need to have one Account in OpenStreetMap to your, so create the one if you don’t have any and login with your account.

2.) Click the Edit dropdown menu button, placed at the upper left of the map, where you will find following three options:

Edit - Export - download data from OpenStreetMap — Edit – Export – download data from OpenStreetMap

Where In-Browser editor are used to edit online and JOSM is used for offline editing.

3.) Lets try with iD In-Browser editor for demonstrating editing here. So click Edit with iD option.

4.) Screen will shows up as follows:

In right hand side you have various option which you can explore one by one and is very easy to understand. Bing Ariel imagery is preferred in background which is by default is in background however you can select any background you want.

5.) Now we can start editing. You can select either Point, Line or Polygon from your screen.

6.) Click on the screen on the location where you want to edit. If you selected a point then just click on the location and press enter and then fill the entries on the left hand side and give necessary information.

7.) Click save and your edits will be saved, which you may see it again by opening the osm in another session. Likewise you can digitize lines and polygons.

8.) You can export the map from osm and it will be downloaded to your computer with extension .osm.

Edit – Export – download data from OpenStreetMap

The exported .osm file can be opened in QGIS or many other GIS tools.

Here I am showing you how to open .osm file in QGIS. Select Add Vector Layer icon as shown below, and browse to the location where you saved the downloaded file and click open.

A pop-up will open. Select layers as shown in figure.

Now select OK. The downloaded map will be imported on the map canvas. In the layers space, you can see four layers which was exported as an example which are .osm layers and can be easily converted to any other GIS vector file like shapefiles.

Download OpenStreetMap OSM data directly from QGIS:

1.) We can also download osm map of our desired location to our computer using QGIS.

2.) In QGIS click vector and click openstreetmap and click download data.

3.) Enter the coordinates. Click … and give the name of the output file and click download.

4.) Downloading will take some time depending on the size of the data.

QGIS tutorial – A simple Guide

After downloading map you can convert back the osm data to any other spatial data, preserving database attributes too. You can also use some advance plugin given in repository of QGIS. I hope this post would have helped you to export Spatial data with the help of OpenStreetMap. If you are getting any problem in performing the steps do comment below also, if have any other tricks to export spatial data from openstreetmap or any other source do provide us the reference by commenting below.

Create save digitize and download kml or kmz from Google Earth

Create save digitize and download kml or kmz from Google Earth. Google earth can be named a virtual globe or map of earth, which provide high resolution view of any area in recent years or any period of time from last decades. Always its not possible to go for field survey and prepare the layers like roads, places, river, water-body , settlements etc for any GIS application as it need resources and manual work which increases cost and project duration. Why do not one use a efficient way for the creation of layers like digitize kmz from the Google earth and convert it to shape-file or use directly. You can also see for how to save, create and export kml file from Google map. To digitize and save kmz from Google earth following steps need to be followed.

Download Google Earth Pro is now free – Official

Steps to Create save digitize and download kml or kmz from Google Earth

1) Start with running the application of Google earth on desktop or laptop, wait till the globe appears in the Google earth application window.

2) Add a folder to the My places contained by Places window of Google earth as shown in fig below.

3) Give name which you want to give to kmz in the tab opened clicking on the My place>add>folder button.

4) To plot a kmz of line type click on the Add Path button on the toolbar placed on the top of the window.

5) Start digitizing with the left click of the mouse and scrolling the window up, down, left and right by pressing the scroll wheel and zoom in and zoom out by rotating the scroll wheel inward and outward. To undo plotted points adjoining line press right click.

6) Save every line feature in the same folder named for kmz. Name of the feature can be edited by right click on the feature in the place window and going to properties of the feature.

7) Now after digitizing all feature save the kmz to the desired destination by right clicking on the folder in the My places.

8) Lastly the question may arise how to digitize kmz with polygon and point feature. For polygon feature click on Add Polygon on the tool bar and for point feature click on Add Place marks on the toolbar.

Note: Google provides Google Earth and this kind of service for free. But i suppose, if you are going to extract and create KML file with your program in bulk, then Google may notify you for the same.

You may also look on some other tutorial:

Thus Kmz is digitized and saved from Google earth. One may convert the kmz to shp in any GIS software like ArcGIS, QGIS, Global Mapper. Do comment below if you are getting any issue in performing above steps. Also do let us know in what way and in which application you are using KML file for performing GIS operation, by putting the information in the below provided space.

Create save Export and download KML file from Google map

Create save Export and download KML file from Google map. Google map or Google Earth makes use of KML or keyhole markup language, to display features, which actually stores geographic features in Vector format. You can create KML file from Google map for mapping and displaying the same by again uploading the same KML file on Google map or created KML file converted from Shapefile. Before creating KML file on Google map, you should have an Google account and should be logged in wtih that. So that once KML file created, you would be able to see it in My Places of Google map after wards. You can also look link for how to create KML file from Google earth.

Create save Export and download KML file from Google map

Note: Google provides Google map and this kind of service for free. But i suppose, if you are going to extract and create KML file with your program in bulk, then Google may notify you for the same.

Create save Export and download KML file from Google map

Steps to Create save Export and download KML file from Google map

1.) Open Google map and sign in with all in one Google account.

2.) Click the setting button which appears at the bottom right corner of map as shown below (New Google map), and select My places. Or if you are using old map theme, then choose my places from top right corner.

Update: If you are not getting my places option from here, then you click on left side Menu icon (three lines) and select your places and tap on Maps.

Once you clicked your places select the Maps.

3.) Now click on Create Map (in the last with blue font) which will open new link in new tab and select Create Map again.

4.) Perform and create a map, with the help of tool bars as shown below, Which has following options i.e

Undo
Redo
Select item
Add a Marker
Draw a line
Add direction
Measure distance and Area.

Create save Export and download KML file from Google map

5.)Name your map, by clicking on untitled map. You can now Add layers too, i.e for Adding Markers, which will be treated as point feature, make different layer while for Adding direction or line
you can have different layer.

Note: You cannot have more than 3 layers for the same map project for Free. You need to go for Google map pro.

Here is the look, i had created 3 layers, i.e 1st one is Point layer, 2nd is Direction layer and 3rd is Line layer, as shown above.

6.) Once you are done with creating map, click the folder icon, shown just below your title of map project and Select Export as KML. Now you can export the entire map or either of the layer by selecting the options. Done.

You can now share your map you created map and can also embed it in your website. What are your plannings with the newly created KML file? Do you want to convert kml file to shapefile. Do comment below, how you use KML file so that me as well as our visitors will come to know about new technology and uses. Your valuable suggestion and questions are always welcomed by commenting below in the section provided.