STV_Export2Shapefile
Exports GEOGRAPHY or GEOMETRY data from a database table or a subquery to a shapefile. Writes the output to the directory specified using STV_SetExportShapefileDirectory.
Behavior Type
Syntax
STV_Export2Shapefile( columns USING PARAMETERS shapefile = 'name_of_shapefile' [, overwrite = { TRUE | FALSE } ] [, shape = ' { Point | Polygon | Linestring | Multipoint | Multipolygon | Multilinestring } ' ] ) OVER()
Parameters
shapefile = 'name_of_shapefile' |
Prefix of the component names of the shapefile, type VARCHAR. Must end with the file extension .shp. Limited to 128 octets in length. For example, city-data.shp. If you want to save the shapefile to a sub-directory you can do so by concatenating the sub-directory to |
overwrite = { TRUE | FALSE } |
(Optional) BOOLEAN value that specifies whether to overwrite the index, if an index exists. This parameter cannot be NULL. Default: False Overwriting may corrupt the existing files. |
shape = ' { Point | Polygon | Linestring | Multipoint | Multipolygon | Multilinestring } ' |
Must be one of the following spatial classes: Point, Polygon, Linestring, Multipoint, Multipolygon, Multilinestring. Polygons and multipolygons always have a clockwise orientation. Default: Polygon |
Arguments
columns |
The columns to export to the shapefile. A value of asterisk (*) is the equivalent to listing all columns of the FROM clause. |
Returns
Three files in the shapefile export directory with the extensions .shp, .shx, and .dbf.
Limitations
-
If a multipolygon, multilinestring, or multipoint contains only one element, then it is written as a polygon, line, or point, respectively.
-
Column names longer than 10 characters are truncated.
-
Empty POINTS cannot be exported.
-
All rows with NULL geometry or geography data are skipped.
-
Unsupported or invalid dates are replaced with NULLs.
- Numeric values may lose precision when they are exported. This loss occurs because the target field in the .dbf file is a 64-bit FLOAT column, which can only represent about 15 significant digits.
Examples
The following example shows how you can use STV_Export2Shapefile to export all columns from the table geo_data to a shapefile named city-data.shp:
=> SELECT STV_Export2Shapefile(* USING PARAMETERS shapefile = 'visualizations/city-data.shp', overwrite = true, shape = 'Point') OVER() FROM geo_data WHERE REVENUE > 25000; Rows Exported | File Path ---------------+-------------------------------------------------------------- 6442892 | v_geo-db_node0001: /home/geo/temp/visualizations/city-data.shp (1 row)