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 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.
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. |