Difference between revisions of "PostGIS function pgsql2shp"
From IMSMA Wiki
| (6 intermediate revisions by the same user not shown) | |||
| Line 5: | Line 5: | ||
* xx_polygon | * xx_polygon | ||
* xx_polyline. | * xx_polyline. | ||
| + | |||
| + | You do not need to include longitude and latitude columns for points in the SQL view since it is the column '''''shape''''' that will be used when creating the shape file. | ||
| + | |||
[[Image:Pg sql2.png|500px|center]] | [[Image:Pg sql2.png|500px|center]] | ||
| Line 10: | Line 13: | ||
The mandatory column to include in the export is called ''shape'' | The mandatory column to include in the export is called ''shape'' | ||
</div> | </div> | ||
| − | + | <ol> | |
| − | + | <li> Create a SQL view or use one of the existing GIS views | |
| − | + | <li> Open the command / DOS prompt | |
| − | + | <li> Go to ''C:\IMSMAng\pgsql\bin'' | |
| − | + | <li> 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 | :: -h host name | ||
| Line 24: | Line 27: | ||
:: database name | :: database name | ||
:: SQL view or table name | :: SQL view or table name | ||
| + | <li> The export files are created in ''C:\IMSMAng\pgsql\bin'' (dbf, prj, shp and shx) | ||
| + | </ol> | ||
| + | If you would like to export all rows then you only need to refer to the SQL view/table by the name of it.<br/> | ||
| + | If you would like to export only some of the rows then you need to add SQL statement. | ||
| − | + | {{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>}} | |
| − | |||
| − | |||
| − | {{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.}} | ||
| − | [[Image:Pgsql2shp.png| | + | [[Image:Pgsql2shp.png|600px|center]] |
<div align="center"> | <div align="center"> | ||
| − | '' The number of rows exported | + | '' The number of rows exported will be shown when the export is done '' |
</div> | </div> | ||
| − | |||
| − | {{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''''' 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]] | ||
Latest revision as of 16:13, 11 September 2018
 |
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.
You do not need to include longitude and latitude columns for points in the SQL view since it is the column shape that will be used when creating the shape file.
The mandatory column to include in the export is called shape
- Create a SQL view or use one of the existing GIS views
- Open the command / DOS prompt
- Go to C:\IMSMAng\pgsql\bin
- 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
- The export files are created in C:\IMSMAng\pgsql\bin (dbf, prj, shp and shx)
If you would like to export all rows then you only need to refer to the SQL view/table by the name of it.
If you would like to export only some of the rows then you need to add SQL statement.
| |
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' " |
The number of rows exported will be shown when the export is done
| |
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. |
| |||||||||||
