15.10 - GeometryToRows - Teradata Database

Teradata Database SQL Geospatial Types

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

Takes ST_Geometry objects and produces an output row for each point in the geometry object.

 

Argument …

Specifies …

database_name.
table_name

a table that contains the ST_Geometry types, the component points of which are returned as individual rows in the table returned by the table operator. The table must be defined to have two or three columns, as described for the query_expression subquery: one or two ID columns and an ST_Geometry type column.

query_expression

a SELECT subquery the provides input data for the table operator.

value

whether errors are reported for unsupported geometries that are passed to the table function. value can be either Yes or No. The default is No. The value must be delimited by apostrophe (single-quote) characters.

When value is No, or this argument is omitted, unsupported geometries are ignored.

id1

a numeric or character type column that uniquely identifies each geometry object in the table passed to the operator. id1 can be any valid Teradata Database numeric or character data type except CLOB. If id1 is NULL for a particular input geometry object, the rows returned by the table operator for this geometry object show corresponding NULLs in their out_geom_id1 column.

id2

a numeric or character type column that, combined with id1, uniquely identifies each geometry object in the table passed to the operator. id2 can be any valid Teradata Database numeric or character data type except CLOB. If id2 is NULL for a particular input geometry object, the rows returned by the table operator for this geometry object show corresponding NULLs in their out_geom_id2 column.

If an id2 column is not passed to the table operator, an out_geom_id2 column is not returned.

ageometry

an ST_Geometry type column that contains geometry objects. For each component point of each object, the table operator returns a separate row that uniquely identifies the object, the geometric type, and the x, y, and z coordinates that locate the point in space.

correlation_name

A name to be assigned to the table returned by the table operator.

column_list

Optional list of custom names for the columns returned by the table operator. These names override the column names that would be otherwise generated by the operator. If you specify any column names, you must provide names for all columns returned. If more than one column is returned by the operator, separate the names with commas.

The GeometryToRows table operator returns an eight or nine column table with the following columns.

 

Column Name

Description

Geometry object identifier column

The value of the input id1 argument, which is passed directly to the output of the GeometryToRows operator. Identifies the points that are returned for a particular input geometry. The type of this column matches the type of id1 in the input table.

If the SELECT statement that uses the table operator does not specify a column list in the AS clause, the name of this column matches the name of the id1 argument to the table operator.

Optional second geometry object identifier column

If the input table included an id2 column to further identify the input geometry objects, that value is passed through the table operator and appears in a second column in the returned table. The this column matches the type of id2 in the input table.

If the input table does not have an id2 column, there is no corresponding column in the table returned by the GeometryToRows table operator.

element_id

An INTEGER value that identifies the particular component element of a Multi type geometry to which the point represented by this row belongs. Individual elements in a Multi geometry are numbered starting from 1.

For example, if a MutliLineString contains two LineStrings, the points for the first LineString will have an element_id of 1 and the points from the second LineString will have an element_id of 2.

Non-Multi type geometries have element_id values of 1.

ring_id

An INTEGER value that identifies the particular polygon ring of a Polygon or MultiPolygon to which the point represented by this row belongs. Individual rings for a polygon are numbered starting from1, with the exterior ring being number 1, and the interior rings numbered starting from 2.

If the geometry object is not a Polygon or MultiPolygon, the ring_id will be set to NULL.

point_id

An INTEGER value that reflects the ordering of the point within the geometry. point_id numbering starts with 1. The points for each geometry are numbered in the order in which they occur within the geometry.

geomType

The geometry type of the input geometry. The possible values are ST_Point, ST_MultiPoint, ST_LineString, ST_MultiLineString, ST_Polygon, or ST_MultiPolygon. If the input geometry is NULL, this value will be NULL. The data type of this value is VARCHAR(30) CHARACTER SET LATIN.

x

The x coordinate value for the point represented by the row. This is a DOUBLE PRECISION value.

If the input geometry is an empty set, this value is NULL.

y

The y coordinate value for the point represented by the row. This is a DOUBLE PRECISION value.

If the input geometry is an empty set, this value is NULL.

z

The z coordinate value for the point represented by the row. This is a DOUBLE PRECISION value.

If the geometry is a 2D geometry, or if the input geometry is an empty set, this value is NULL.

ST_Geometry objects that represent Points, LineStrings, Polygons, MultiPoints, MultiLineStrings, and MultiPolygons

  • The first and last points in a polygon will be the same point.
  • A polygon consists of one exterior ring and zero or more interior rings. The interior rings must lie completely within the exterior ring. A MultiPolygon is a group of non-overlapping polygons.
  • The element ID of each point identifies which element the point belongs to in a Multi-type geometry. For example, for a MultiLineString that contains two LineStrings, the first LineString will be element 1 and the second LineString will be element 2.
  • For non-Multi geometries, the element ID is always 1.

  • If an input geometry value is NULL, the corresponding output row that is returned will have NULLs in the geomType, x, y, and z columns.
  • Due to the hierarchical nature of the ID columns, you can use an ORDER BY clause to keep the rows associated with each passed-in geometry together, and grouped according to the sub-geometries of Multi types. See the example below.
  • CREATE TABLE geo_table(pkey integer, geom_id VARCHAR(20), geom ST_Geometry);
    INSERT INTO geo_table VALUES(0, 'S101', new ST_Geometry('Point(10 20 5)'));
    INSERT INTO geo_table VALUES(1, 'S102', new ST_Geometry('LineString(10 10, 20 20)'));
    INSERT INTO geo_table VALUES(2, 'S103', new ST_Geometry(
       'Polygon((0 0, 0 10, 10 10, 10 0, 0 0))'));
    INSERT INTO geo_table VALUES(3, 'S104', new ST_Geometry('LineString(EMPTY)'));
    INSERT INTO geo_table VALUES(4, 'S105', NULL);
    INSERT INTO geo_table VALUES(5, 'S106', new ST_Geometry(
       'MultiLineString((10 10, 20 20),(30 30, 40 40, 50 50))'));

    INSERT INTO geo_table VALUES(6, 'S107', new ST_Geometry(
       'Polygon((0 0, 0 10, 10 10, 10 0, 0 0), (2 2, 2 8, 8 8, 8 2, 2 2))'));
    SELECT PointsTable.geom_id1 (FORMAT 'X(8)'),
           PointsTable.element_id (FORMAT 'Z'),
           PointsTable.ring_id (FORMAT 'Z'),
           PointsTable.point_id (FORMAT 'Z'),
           PointsTable.geomType (FORMAT 'X(15)'),
           PointsTable.x (FORMAT '99.99'),
           PointsTable.y (FORMAT '99.99'),
           PointsTable.z (FORMAT '99.99')
    FROM  GeometryToRows(ON (SELECT geom_id, geom FROM geo_table) ) 
    AS PointsTable (geom_id1, element_id, ring_id, point_id, geomType, x, y, z) 
    ORDER BY 1, 2, 3, 4;

    The output would look like the following.

    geom_id1  element_id  ring_id  point_id  geomType             x      y      z
    --------  ----------  -------  --------  ---------------  -----  -----  -----
    S101               1        ?         1  POINT            10.00  20.00  05.00
    S102               1        ?         1  LINESTRING       10.00  10.00      ?
    S102               1        ?         2  LINESTRING       20.00  20.00      ?
    S103               1        1         1  POLYGON          00.00  00.00      ?
    S103               1        1         2  POLYGON          00.00  10.00      ?
    S103               1        1         3  POLYGON          10.00  10.00      ?
    S103               1        1         4  POLYGON          10.00  00.00      ?
    S103               1        1         5  POLYGON          00.00  00.00      ?
    S104               1        ?         1  LINESTRING           ?      ?      ?
    S105               1        ?         1  ?                    ?      ?      ?
    S106               1        ?         1  MULTILINESTRING  10.00  10.00      ?
    S106               1        ?         2  MULTILINESTRING  20.00  20.00      ?
    S106               2        ?         1  MULTILINESTRING  30.00  30.00      ?
    S106               2        ?         2  MULTILINESTRING  40.00  40.00      ?
    S106               2        ?         3  MULTILINESTRING  50.00  50.00      ?
    S107               1        1         1  POLYGON          00.00  00.00      ?
    S107               1        1         2  POLYGON          00.00  10.00      ?
    S107               1        1         3  POLYGON          10.00  10.00      ?
    S107               1        1         4  POLYGON          10.00  00.00      ?
    S107               1        1         5  POLYGON          00.00  00.00      ?
    S107               1        2         1  POLYGON          02.00  02.00      ?
    S107               1        2         2  POLYGON          02.00  08.00      ?
    S107               1        2         3  POLYGON          08.00  08.00      ?
    S107               1        2         4  POLYGON          08.00  02.00      ?
    S107               1        2         5  POLYGON          02.00  02.00      ?

    Notice how the ORDER BY clause causes the rows to be arranged by ID values, keeping the rows for each geometry and sub-geometry (for Multi types) together and organized hierarchically by geometries and their sub-geometries. Otherwise, the rows would be returned in a random order, and the relationships would be more difficult to discern.