Difference between revisions of "PostGIS function pgsql2shp"

From IMSMA Wiki
Jump to: navigation, search
Line 1: Line 1:
One of the geotables need to be included in the table/view that you are exporting and the mandatory column is called ''shape''.
+
{{Note | The example below is using the ''staging'' area database.}}
 +
 
 +
One of the geotables need to be included in the table/view that you are exporting:
 +
* xx_point
 +
* xx_polygon
 +
* xx_polyline.
 +
 
 +
[[Image:Pg sql2.png|500px|center]]
 +
<div align="center">
 +
The mandatory column to include in the export is called ''shape''
 +
</div>
  
 
# Create a SQL view or use one of the existing GIS views
 
# Create a SQL view or use one of the existing GIS views
Line 6: Line 16:
 
# Run the command ''pgsql2shp'' with the following parameters
 
# Run the command ''pgsql2shp'' with the following parameters
 
:: -f the name of the resulting shape file
 
:: -f the name of the resulting shape file
:: -h host name If the Staging area database is on your computer than use ''localhost''
+
:: -h host name  
:: -u user Use ''imsma''
+
::: if the Staging area database is on your computer than use ''localhost''
:: -P password Use ''password
+
:: -u user  
 +
::: use ''imsma''
 +
:: -P password  
 +
::: use ''password
 
:: database name
 
:: database name
 
:: SQL view or table name  
 
:: SQL view or table name  
  
You may refer to the SQL view/table by the name of it you would like to export all rows OR specify the SQL statement.
+
If you would like to export all rows then you refer to the SQL view/table by the name of it.
 +
If you would like to export only some rows then you need to add more SQL statements.
  
 
{{Note | Any " inside the SQL statement needs to be escaped and therefore it is easier to refer to a SQL view that contains the columns and rows you would like to export.}}
 
{{Note | Any " inside the SQL statement needs to be escaped and therefore it is easier to refer to a SQL view that contains the columns and rows you would like to export.}}
Line 24: Line 38:
 
{{Note | If the Output shape type is '''''Unknown''''' then there is something wrong with the SQL view/table. Most likely the mandatory column ''shape'' is '''not''' included.}}
 
{{Note | If the Output shape type is '''''Unknown''''' then there is something wrong with the SQL view/table. Most likely the mandatory column ''shape'' is '''not''' included.}}
 
{{NavBox AGOL Extension}}
 
{{NavBox AGOL Extension}}
[[Category:PEW]]
+
[[Category:NAA]]

Revision as of 10:25, 11 September 2018

Note.jpg The example below is using the staging area database.

One of the geotables need to be included in the table/view that you are exporting:

  • xx_point
  • xx_polygon
  • xx_polyline.
Pg sql2.png

The mandatory column to include in the export is called shape

  1. Create a SQL view or use one of the existing GIS views
  2. Open the command / DOS prompt
  3. Go to C:\IMSMAng\pgsql\bin
  4. Run the command pgsql2shp with the following parameters
-f the name of the resulting shape file
-h host name
if the Staging area database is on your computer than use localhost
-u user
use imsma
-P password
use password
database name
SQL view or table name

If you would like to export all rows then you refer to the SQL view/table by the name of it. If you would like to export only some rows then you need to add more SQL statements.

Note.jpg Any " inside the SQL statement needs to be escaped and therefore it is easier to refer to a SQL view that contains the columns and rows you would like to export.
Pgsql2shp.png

The number of rows exported witll be shown when the export is done

Example: pgsql2shp -f land_polygon -h localhost -u imsma -P password staging " select * from haz_gis_poly where \"Status\" = 'Closed' "

Note.jpg If the Output shape type is Unknown then there is something wrong with the SQL view/table. Most likely the mandatory column shape is not included.