16.20 - Well-Known Text Format - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ Geospatial Data Types

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1181-162K

Purpose

Defines the format of geometry data when using character data to construct new ST_Geometry instances or transfer ST_Geometry values to and from client applications.

Format

















EMPTY
The empty set.
x
A numeric value that represents the x coordinate of a point.
y
A numeric value that represents the y coordinate of a point.
z
A numeric value that represents the z coordinate of a point in 3D space.
ts
A timestamp value with the following format:
   yyyy-mm-dd hh:mi:ss.ms
The first timestamp value is associated with the first point, the second timestamp value is associated with the second point, and so forth.
You must specify n timestamp values, where n is the number of points in the geosequence.
linkID
A NUMERIC(18,0) value for the ID of the link on the road network for a point in the geosequence.
This value is reserved for a future release.
The first link ID value is associated with the first point, the second link ID value is associated with the second point, and so forth.
You must specify n link ID values, where n is the number of points in the geosequence.
count
An integer value that represents the number of uf elements for each point in the geosequence.
A value of 0 indicates that no uf elements appear after count in the character string.
uf
A user field that represents a FLOAT value to associate with a point. For example, certain tracking systems may associate velocity, direction, and acceleration values with each point.
You must specify count groups of n user field values (where n is the number of points in the geosequence). The first group provides the first user field values for each point, the second group provides the second user field values for each point, and so forth.

Usage Notes

Teradata Database implements transform functionality that, by default, allows importing and exporting an ST_Geometry type to and from the server as a CLOB in WKT format. This means that a client application can use a CLOB to insert a value into an ST_Geometry column, provided the CLOB uses the WKT format of one of the geospatial subtypes that ST_Geometry can represent. Similarly, when a client application submits a query that selects data from an ST_Geometry column, by default, Teradata Database exports the type as a CLOB using the WKT format of the geometry that the ST_Geometry column represents.

Teradata also provides other transforms for the ST_Geometry type that allow for the import and export of geospatial data as other types and formats. For more information, see ST_Geometry Type Transforms

Examples: WKT Format for Geospatial Data

Consider the following table:

   CREATE TABLE sample_shapes (skey INTEGER, shape ST_Geometry);

Here are some examples that show how to insert geometry values using WKT representation:

   INSERT INTO sample_shapes
      VALUES (1001, 'POINT(10 20)');

   INSERT INTO sample_shapes
      VALUES (1002, 'POINT EMPTY');

   INSERT INTO sample_shapes
      VALUES (1003, 'LINESTRING(1 1, 2 2, 3 3, 4 4)');

   INSERT INTO sample_shapes
      VALUES (1004, 'LINESTRING EMPTY');

   INSERT INTO sample_shapes
      VALUES (1005, 'POLYGON((0 0, 0 20, 20 20, 20 0, 0 0),
                             (5 5, 5 10, 10 10, 10 5, 5 5))');

   INSERT INTO sample_shapes
      VALUES (1006, 'MULTIPOINT((1 1), (1 3), (6 3), (10 5), (20 1))');

   INSERT INTO sample_shapes
      VALUES (1007, 'MULTILINESTRING((1 1, 1 3, 6 3),
                                     (10 5, 20 1))');

   INSERT INTO sample_shapes
      VALUES (1008, 'MULTIPOLYGON(((1 1, 1 3, 6 3, 6 0, 1 1)),
                                  ((10 5, 10 10, 20 10, 20 5, 10 5)))');

   INSERT INTO sample_shapes
      VALUES (1009, 'GEOMETRYCOLLECTION( POINT(10 10),
                                         POINT(30 30),
                                         LINESTRING(15 15, 20 20))');

   INSERT INTO sample_shapes
      VALUES (1010, 'GEOSEQUENCE( (10 20, 30 40, 50 60),
                                  (2007-08-22 12:05:23.56,
                                   2007-08-22 12:08:25.14,
                                   2007-08-22 12:11:41.52),
                                  (1, 2, 3),
                                  (2, 10, 12, 11, 18, 21, 19) )' );

   INSERT INTO sample_shapes
      VALUES (1011, 'GEOSEQUENCE( (10 20, 30 40, 50 60),
                                  (2008-03-17 10:34:03.53,
                                   2008-03-17 10:38:25.21,
                                   2008-03-17 10:41:41.48),
                                  (1, 2, 3),
                                  (0))');

SELECT * FROM sample_shapes ORDER BY skey;