STV_ShpSource and STV_ShpParser

These two functions work with COPY to parse and load geometries and attributes from a shapefile into a Vertica table, and convert them to the appropriate GEOMETRY data type.

These two functions work with COPY to parse and load geometries and attributes from a shapefile into a Vertica table, and convert them to the appropriate GEOMETRY data type. You must use these two functions together.

The following restrictions apply:

  • An empty multipoint or an invalid multipolygon can not be loaded from a shapefile.

  • If the .dbf component of a shapefile contains a numeric attribute, this field's values might lose precision when the Vertica Place shapefile loader loads it into a table. The target field is a 64-bit FLOAT column, which can only represent about 15 significant digits; in a .dbf file, Numeric fields can be up to 30 digits.

Rejected records are saved to CopyErrorLogs subdirectory, under the Vertica catalog directory.

Behavior type

Immutable

Syntax

COPY table( columnslist )
     WITH SOURCE STV_ShpSource
          ( file = 'filename'[[, SRID=`*`spatial-reference-identifier`*`] [, flatten_2d={true | false }] ] )
     PARSER STV_ShpParser()

Arguments

table
Name of the table in which to load the geometry data.
columnslist
Comma-delimited list of column names in the table that match fields in the external file. Run the CREATE TABLE command that STV_ShpCreateTable creates. When you do so, these columns correspond to the second through the second-to-last columns.
file = 'pathname'
Specifies the fully qualified path of a .dbf, .shp, or .shx file.

You can also load from a shapefile that is stored on a mounted S3 directory where you have read and write permissions. In this case, use the following the syntax:

bucketname/path/filename
SRID=spatial-reference-identifier
Specifies an integer spatial reference identifier (SRID) associated with the shape file.
flatten_2d
Specifies a BOOLEAN argument that excludes 3D or 4D coordinates during COPY commands:

  • true: Excludes geometries with 3D or 4D coordinates before a COPY command.

  • false: Causes the load to fail if a geometry with 3D or 4D coordinate is found.

Default: false

Privileges

  • Source shapefile: Read

  • Shapefile directory: Execute

COPY errors

The COPY command fails under one of the following conditions:

  • The shapefile cannot be located or opened.

  • The number of columns or the data types of the columns that STV_ShpParser creates do not match the columns in the destination table. Use STV_ShpCreateTable to generate the appropriate CREATE TABLE command.

  • One of the mandatory files is missing or cannot be opened. When opening a shapefile, you must have three files: .dbf, .shp, and .shx.

STV_ShpSource file corruption handling

  • If the .shp and .shx files are corrupt, STV_ShpSource returns an error.

  • If the .shp and .shx files are valid, but the .dbf file is corrupt, STV_ShpSource ignores the .dbf file and does not create columns for that data.

Examples


=> COPY tl_2010_us_state10 WITH SOURCE
STV_ShpSource(file='/shapefiles/tl_2010_us_state10.shp', SRID=4269) PARSER STV_ShpParser();

 Rows loaded
-------------
          52