ST_Geometry Type Transforms

Teradata Vantageā„¢ Geospatial Data Types

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

Default ST_Geometry Transform

By default, Teradata Database imports and exports geometry data to and from the server as CLOB data in the well-known text (WKT) standard format for geospatial data. Client applications can insert CLOB values in WKT format into an ST_Geometry column for the geospatial subtypes that ST_Geometry can represent. Similarly, for queries that select data from an ST_Geometry column and export it to a client application, Teradata Database exports the data to the client application as CLOB values in WKT format.

Additional Transform Groups for ST_Geometry Data

To accommodate importing and exporting geospatial data from and to other types and formats, Teradata Database provides other transform groups for the ST_Geometry type.
  • To override the default CLOB/WKT transform for geospatial data for the current session, use the SET TRANSFORM GROUP FOR TYPE statement.
  • To override the default CLOB/WKT transform for geospatial data for a particular user or profile, specify the transform to use in the CREATE/MODIFY USER and CREATE/MODIFY PROFILE statements.
For more information about these SQL statements, see Teradata Vantageā„¢ SQL Data Definition Language Syntax and Examples, B035-1144.

The following table lists the transform groups that are available for the ST_Geometry type.

Transform Group Import/Export Type Default Format
ST_WellKnownText CLOB

(Latin character set)

Yes WKT

This corresponds to the ST_GEOMETRY_IO transform group in previous Teradata Database releases.

ST_WellKnownBinary BLOB No Well-known binary (WKB)
TD_GEO_VARCHAR VARCHAR(64000)

(Latin character set)

No WKT
TD_GEO_VARBYTE VARBYTE(64000) No WKB
ST_WellKnownTextSRID CLOB

(Latin character set)

No Extended WKT (WKT and SRID)
ST_WellKnownBinarySRID BLOB No Extended WKB (WKB and SRID)
TD_GEO_VARCHAR_SRID VARCHAR(64000)

(Latin character set)

No Extended WKT
TD_GEO_VARBYTE_SRID VARBYTE(64000) No Extended WKB

For more information on the WKT, WKB, and extended formats, see Geospatial Data Formats.

Example: Exporting Geometry Data as CLOB in WKT Format

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);

By default, the following query issued through a client application 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 Format.

Transform Group Macros

You can use the following macros to find the transform group for a UDT (or CDT), or the transform group settings for a user, profile, or current session.

Macro Description
SYSUDTLIB.HelpCurrentUserTransforms Lists the transform group settings of the current logon user.
SYSUDTLIB.HelpCurrentSessionTransforms Lists the transform group settings of the current session.
SYSUDTLIB.HelpUserTransforms(User) Lists the transform group settings for a specific user.
SYSUDTLIB.HelpCurrentUDTTransform(UDT)

Lists the transform group settings of the current session for the specified UDT.

SYSUDTLIB.HelpUDTTransform(User,UDT) Lists the transform group for a UDT for a user.
SYSUDTLIB.HelpProfileTransforms(Profile) Lists the transform group settings for a specific profile.

SYSUDTLIB.HelpProfileTransform(Profile,UDT)

Lists the transform group for a UDT for a profile.