ST_Geometry Type Syntax | Geospatial Data Types | Teradata Vantage - 17.05 - Syntax - Teradata Database

Teradata Vantageā„¢ - Geospatial Data Types

Advanced SQL Engine
Teradata Database
June 2020
Programming Reference
  [ ( maxlength ) ]
  [ INLINE LENGTH integer ]
  [ attribute [...] ]
The name of the database in which all UDTs are created.
A positive integer value followed by an optional multiplier.
maxlength specifies the maximum length of the data type in bytes. You can define a maximum length on a per instance basis. When specified, the data type is used in a manner analogous to the VARBYTE or BLOB data types.
The length specified only covers the actual data length. The actual storage sizes include additional header information.
The default value is approximately 16 MB.
You can find the actual size of ST_Geometry objects by using the DataSize system function.
A positive integer value which specifies the inline storage size. Data that is smaller than or equal to the inline storage size is stored inside the base row; otherwise, it is stored in a LOB subtable.
The inline length cannot be larger than maxlength.
The default value is approximately 10,000 bytes.
Appropriate data attributes.
An ST_GEOMETRY column supports the following attributes:
  • NULL
For more information on using the data attributes, see Teradata Vantageā„¢ - Data Types and Literals, B035-1143.

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

Usage Notes

You can use the optional INLINE LENGTH specification to specify the inline storage size. When the data is smaller than or equal to the inline storage size, it is stored inside the base row. Otherwise, the data is stored as a LOB (large object).

If the inline length is equal to the maximum length specified for the data type, the data type is treated as a non-LOB type. In this case, the performance may be better because there is no LOB overhead. You may see some performance improvement especially when the data type is used with UDFs.

You can use the maxlength together with the INLINE LENGTH options to improve space management for geospatial data by forcing smaller geometries to be stored inline as non-LOB values. This stores the geospatial data within the table row itself, rather than in a separate LOB subtable. Furthermore, by specifying a small inline length for small geometries, Teradata Database will reserve less space in the row, so more columns can be added to the table. Additionally, non-LOB geometries work with some load utilities that do not support LOBs.

Examples: Specifying an ST_Geometry column using the maxlength and INLINE LENGTH options

The following examples show different column data type specifications for ST_Geometry columns and describe how the geometry data is stored.

Because there is neither a maxlength nor and INLINE LENGTH specified, maxlength defaults to approximately 16 MB and INLINE LENGTH defaults to approximately 10000 bytes. Any geometry that is less than or equal to 10,000 bytes will be stored directly in the row, otherwise it will be stored as a LOB.
The maximum length of the ST_Geometry is 250,000 bytes. Since no INLINE LENGTH was specified, it defaults to 10,000 bytes. If this ST_Geometry is larger than 10,000 bytes, it will be stored as a LOB
ST_Geometry(250000) INLINE LENGTH 4000
The maximum length of the ST_Geometry is 250,000 bytes. Because there is also an INLINE LENGTH specified, if the geometry data is less than or equal to 4,000 bytes it will be stored inline, within a row, otherwise it will be stored as a LOB.
Because the maximum length is specified without an INLINE LENGTH, and the maximum is less than the Teradata threshold for storing a column value as a LOB, the ST_Geometry values are always stored inline.
ST_Geometry(8000) INLINE LENGTH 2000
The maximum length of the ST_Geometry is 8,000, however, if it exceeds 2,000 bytes it will be stored as a LOB.
ST_Geometry INLINE LENGTH 2000
ST_Geometry values larger than 2,000 bytes will be stored as LOB values. The maxlength will be the maximum length possible, approximately 16 MB.