STV_ShpCreateTable

Returns a CREATE TABLE statement with the columns and types of the attributes found in the specified shapefile.

Returns a CREATE TABLE statement with the columns and types of the attributes found in the specified shapefile.

The column types are sized according to the shapefile metadata. The size of the column is based on the largest geometry found in the shapefile. The first column in the table is gid, which is an IDENTITY primary key column. The cache value is set to 64 by default. The last column is a GEOMETRY data type for storing the actual geometry data.

Behavior type

Immutable

Syntax

STV_ShpCreateTable (USING PARAMETERS file='filename') OVER()

Arguments

file = 'filename'
Fully qualified path of the .dbf, .shp, or .shx file (file extension optional).

You can also create a table using a shapefile stored on a mounted S3 directory where you have read and write permissions. Use the following syntax:

bucketname/path/filename

Returns

CREATE TABLE statement that matches the specified shapefile

Usage tips

  • STV_ShpCreateTable returns a CREATE TABLE statement; but it does not create the table. Modify the CREATE TABLE statement as needed, and then create the table before loading the shapefile into the table.

  • To create a table with characters other than alphanumeric and underscore (_) characters, you must specify the table name enclosed in double quotes, such as "counties%NY".

  • The name of the table is the same as the name of the shapefile, without the directory name or extension.

  • The shapefile must be accessible from the initiator node.

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

  • All the mandatory files (.dbf, .shp, .shx) must be in the same directory. If not, STV_ShpCreateTable returns an error.

  • If the .dbf component of a shapefile contains a Numeric attribute, this field's values may lose precision when the Vertica 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.

    Vertica records all instances of shapefile values that are too long in the vertica.log file.

Examples

The following example shows how to use STV_ShpCreateTable.

Returns a CREATE TABLE statement:


=> SELECT STV_ShpCreateTable
      (USING PARAMETERS file='/shapefiles/tl_2010_us_state10.shp')
      OVER() as create_table_states;
      create_table_states
 ----------------------------------
CREATE TABLE tl_2010_us_state10(
   gid IDENTITY(64) PRIMARY KEY,
   REGION10 VARCHAR(2),
   DIVISION10 VARCHAR(2),
   STATEFP10 VARCHAR(2),
   STATENS10 VARCHAR(8),
   GEOID10 VARCHAR(2),
   STUSPS10 VARCHAR(2),
   NAME10 VARCHAR(100),
   LSAD10 VARCHAR(2),
   MTFCC10 VARCHAR(5),
   FUNCSTAT10 VARCHAR(1),
   ALAND10 INT8,
   AWATER10 INT8,
   INTPTLAT10 VARCHAR(11),
   INTPTLON10 VARCHAR(12),
   geom GEOMETRY(940845)
);
(18 rows)

See also