Difference between revisions of "PostGIS function pgsql2shp"

From IMSMA Wiki
Jump to: navigation, search
Line 25: Line 25:
 
:: SQL view or table 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 all rows then you refer to the SQL view/table by the name of it.<br/>
 
If you would like to export only some rows then you need to add more SQL statements.
 
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. <br/>Example: <nowiki>pgsql2shp -f land_polygon -h localhost -u imsma -P password staging " select * from haz_gis_poly where \"Status\" = 'Closed' " </nowiki>}}
  
 
[[Image:Pgsql2shp.png|600px|center]]<br/>
 
[[Image:Pgsql2shp.png|600px|center]]<br/>
Line 34: Line 34:
 
'' The number of rows exported will be shown when the export is done ''
 
'' The number of rows exported will be shown when the export is done ''
 
</div>
 
</div>
Example: pgsql2shp -f land_polygon -h localhost -u imsma -P password staging " select * from haz_gis_poly where \"Status\" = 'Closed' "
 
  
 
{{Note | If the Output shape type is '''''Unknown''''' or '''''Null'''''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''''' or '''''Null'''''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:NAA]]
 
[[Category:NAA]]

Revision as of 13:17, 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.
Example: pgsql2shp -f land_polygon -h localhost -u imsma -P password staging " select * from haz_gis_poly where \"Status\" = 'Closed' "
Pgsql2shp.png

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

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