STV_Export2Shapefile

Exports GEOGRAPHY or GEOMETRY data from a database table or a subquery to a shapefile.

Exports GEOGRAPHY or GEOMETRY data from a database table or a subquery to a shapefile. Output is written to the directory specified using STV_SetExportShapefileDirectory.

Behavior type

Immutable

Syntax

STV_Export2Shapefile( columns USING PARAMETERS shapefile = 'shapefile-name'
   [, overwrite = boolean ]
   [, shape = 'spatial-class'] )
OVER()

Arguments

columns
The columns to export to the shapefile.

A value of asterisk (*) is the equivalent to listing all columns of the FROM clause.

Parameters

shapefile
Prefix of the component names of the shapefile. The following requirements apply:

  • 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 shapefile-name—for example, visualizations/city-data.shp.

You can also export a shapefile to a mounted S3 directory where you have read and write permissions. Use the syntax bucketname/path/filename.

overwrite

Boolean, specifies whether to overwrite the index, if an index exists. This parameter cannot be NULL.

Default: False

shape
One of the following spatial classes:

  • Point

  • Polygon

  • Linestring

  • Multipoint

  • Multipolygon

  • Multilinestring

Polygons and multipolygons always have a clockwise orientation.

Default: Polygon

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.

  • Shapefiles cannot exceed 4GB in size. If your shapefile is too large, try splitting the data and exporting to multiple shapefiles.

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)