Takes ST_Geometry objects and produces an output row for each point in the geometry object.
Syntax
[TD_SYSFNLIB.] GeometryToRows (
ON { [ database_name. ] table_name | ( query_expression ) }
[ USING ErrOnUnsupportedGeometry ('value') ]
) [AS] correlation_name [ ( column_list ) ]
- 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
-
SELECT id1, [ id1, ] id1 FROM [ database_name. ] table_name
- value
- The table that contains the geometry type column for which the aggregation is to be computed.
- 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.
- correlation_name
- A name to be assigned to the table returned by the table operator.
- 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
- 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.
Returned Values
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. LineString will have an element_id of 2. |
| 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. |
Valid Data Types
ST_Geometry objects that represent Points, LineStrings, Polygons, MultiPoints, MultiLineStrings, and MultiPolygons.
Usage Notes
- 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.
Example: GeometryToRows
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.