15.10 - ST_Geometry - Teradata Database

Teradata Database SQL Geospatial Types

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1181-151K

Represents any of the following geometry types: ST_Point, ST_LineString, ST_Polygon, ST_GeomCollection, ST_MultiPoint, ST_MultiLineString, ST_MultiPolygon, and GeoSequence.

 

Syntax element …

Specifies …

SYSUDTLIB.

the name of the database in which all UDTs are created.

attribute

 

 

appropriate data attributes.

An ST_GEOMETRY column supports the following attributes:

  • NULL
  • NOT NULL
  • FORMAT
  • TITLE
  • NAMED
  • DEFAULT NULL
  • For details on using the data attributes, see SQL Data Types and Literals.

    An ST_GEOMETRY column does not support column storage or column constraint attributes.

    Teradata Database implements transform functionality that allows importing and exporting an ST_Geometry type to and from the server as a CLOB. This means that a client application can use a CLOB to insert a value into an ST_Geometry column, provided the CLOB uses the well-known text (WKT) format of one of the geospatial subtypes that ST_Geometry can represent.

    Similarly, for queries that select data from an ST_Geometry column, Teradata Database exports the type to the client application as a CLOB.

    Consider the following definition of a table that defines an ST_Geometry column for representing ST_Point values.

       CREATE TABLE sample_points1 (skey INTEGER, point1 ST_Geometry);

    The following query results in the export of values in the point1 column using the WKT representation of a point:

       SELECT * 
       FROM sample_points1;
     
       SKEY          POINT1
       --------      ------------
       1001          POINT(10 20)
     

    For more information on the WKT format of the ST_Geometry type, see “Well-Known Text Representation” on page 194.

    Teradata Database implements cast functionality that allows data type conversions between the following types:

  • ST_Geometry and VARCHAR(64000)
  • ST_Geometry and CLOB
  • To cast a VARCHAR or CLOB to an ST_Geometry type, the VARCHAR or CLOB data must use the WKT format of any of the ST_Geometry subtypes. For example:

       INSERT INTO sample_shapes 
          VALUES (1001, CAST('LINESTRING(1 1, 2 2, 3 3)' AS ST_Geometry));
     

    For more information on the WKT format of the ST_Geometry type, see “Well-Known Text Representation” on page 194.

    Because Teradata Database requires ordering functionality for UDTs, an ordering definition exists for the ST_Geometry type. In general, however, ordering ST_Geometry types by their ordering definitions is not useful or meaningful.

    To determine spatial relationships between geometries, for example whether one ST_Geometry type is equal to another, the best practice is to use the ST_Geometry spatial methods, for example geom1.ST_Equals(geom2).

    For details on ordering functionality, see CREATE ORDERING in SQL Data Definition Language.

    The maximum size of an ST_Geometry type is approximately 16 MB, allowing for representations of approximately one million points.

    The 16 MB limit applies to the following:

  • Any WKB representation that is passed into the ST_Geometry type (for example, cast, ST_Geometry constructor method)
  • Any WKB representation that is returned from the ST_Geometry type (for example, cast, ST_AsBinary)
  • Any WKT representation that is passed into the ST_Geometry type (for example, ToSQL transform, cast, ST_Geometry constructor method)
  • Any ST_Geometry method (for example, ST_Union, ST_Buffer) that produces a geometry with a WKB that exceeds the 16 MB limit is reported as an error.
  • WKT representations returned from the ST_Geometry type (for example, cast, ST_AsText) can exceed the approximate 16 MB limit. These cases typically occur when the corresponding WKB is large, but less than 16 MB, and the corresponding WKT exceeds 16 MB. Note that if the returned WKT representation exceeds 16 MB, this text representation of the geometry can no longer be input back into the ST_Geometry object, because it exceeds the size limit. In these cases, use the WKB representation instead.

    Because the ST_Geometry type is considered a LOB UDT, any restrictions that apply to a LOB type and to a UDT type also apply to the ST_Geometry type. For example, the maximum number of ST_Geometry columns in a table is the same as the maximum number of LOB columns in a table. Additionally, Teradata load utilities that cannot directly load LOB types similarly cannot load ST_Geometry types.

    The remainder of this chapter describes methods that work with all ST_Geometry types.