15.10 - GeoSequenceToRows - Teradata Database

Teradata Database SQL Geospatial Types

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

Table function that takes a GeoSequence input argument, represented as an ST_Geometry type, and returns table rows where each row is a point in the GeoSequence object. Output rows are fixed in size and allow up to ten user fields.

 

Argument …

Specifies …

id

an ID that can be used to associate the rows in the table with a single GeoSequence object.

The data type of id is DECIMAL(18,0).

ageometry

an ST_Geometry value that represents the GeoSequence object.

For details on the rules for argument data types, see the introduction to this chapter.

Returns the following columns:

 

Column Name

Data Type

Description

out_key

DECIMAL(18,0)

A key that is passed back. This can be used to associate all points in the table with this key to a single GeoSequence type.

point_index

INTEGER

The index of this point in the sequence, where the index of the first point is 1.

x

FLOAT

X coordinate of the point.

y

FLOAT

Y coordinate of the point.

ts

TIMESTAMP

Timestamp of the point.

Link_id

DECIMAL(18,0)

Link ID of the point.

UserFld1

FLOAT

User fields of data for this point.

UserFld2

UserFld3

UserFld4

UserFld5

UserFld6

UserFld7

UserFld8

UserFld9

UserFld10

Inset Owner: Doris Darre
Inset Change Log: 9/18/2012: Initial version.
This is a Teradata extension to the ANSI SQL:2011 standard.

CREATE TABLE sample_shapes(skey INTEGER, shape ST_Geometry);

INSERT INTO sample_shapes
(    100,
     'GeoSequence( (10 10, 15 15, -2 0),
                   (2007-03-14 01:35:00, 2007-03-14 01:35:05, 2007-03-14 01:35:08),
                   (1222, 1223, 1224),
                   (2, 12.1, 3.14159, 2.78128, -10, -11, 100.1))' );

CREATE TABLE sample_trips(pkey FLOAT, point_index INTEGER, x FLOAT, y FLOAT, ts TIMESTAMP, Link_id DECIMAL(18,0), UserFld1 FLOAT, UserFld2 FLOAT, UserFld3 FLOAT);

   INSERT INTO sample_trips
   SELECT out_key, point_index, x, y, ts, Link_id, UserFld1, UserFld2, UserFld3
   FROM TABLE (GeoSequenceToRows(9601., sample_shapes.shape)) AS ts;