Returns a union or intersection of a group of ST_Geometry objects.
Syntax
[TD_SYSFNLIB.] AggGeom ( ON ( SELECT ageometry [, part_column_list ] FROM [ database_name. ] table_name ) [ PARTITION BY part_column_list ] [ USING OPERATION ('value') ] ) [AS] correlation_name [ ( column_list ) ]
- ageometry
- A constant or column expression for which the aggregation is to be computed. The data type of ageometry is ST_Geometry.
- part_column_list
- The column name set by which rows are to be partitioned before being passed to the operator. If a PARTITION BY clause is used, the list of columns must match the part_column_list that was passed to the table operator.
- database_name
- table_name
- The table that contains the geometry type column for which the aggregation is to be computed.
- value
- The type of aggregation operation desired, either Union or Intersection. Union is the default. The value must be delimited by apostrophe (single-quote) characters.
- 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.
Returned Values
The kind of geometry objects in the ST_Geometry type column returned by the table operator matches the type of geometry objects that were aggregated.
For Intersection operations, "GEOMETRYCOLLECTION EMPTY" is returned if there is no intersection between the objects.
If the Well-Known Binary (WKB) representation of the resulting geometry becomes larger than the maximum size geometry (~16MB), Teradata Database reports an error.
Non-Empty geometry collections are not supported, so any union or intersection that results in a collection, as either an intermediate or final result generates an error. involving one will result in an error.
Valid Data Types
ST_Geometry objects that represent Points, LineStrings, Polygons, MultiPoints, MultiLineStrings, MultiPolygons, and GeometryCollections.
Usage Notes
- The first input column in the ON clause must have data type ST_GEOMETRY and contain the objects to be aggregated. After that, any number of partitioning columns may be included in the ON clause and they will be written as is to the output. The PARTITION BY clause must specify all columns other than the first or the table operator will return an error.
- NULL arguments are not included in aggregation operations. If all values in the aggregation are NULL, no rows are returned.
- If the PARTITION BY clause is omitted, each AMP performs a local aggregation operation on its rows. One output row is produced per AMP.
- Whenever input rows are redistributed to other AMPs, as caused by a PARTITION BY clause, the order of the redistribution can vary from run to run. Consequently, the geometries produced by the aggregation can vary per run. Nevertheless, the result geometries will be equivalent, but there may be differences in the ordering of points within geometries. For example, they might display different starting and ending point for a polygon.
Similarly, the structure of the geometries can vary, for example, a single LineString can represents two LineStrings or a MultiLineString. Because the union or intersection steps can occur in a different order from run to run, precision errors that can normally happen in the computation of these operations can vary slightly.
Examples: AggGeom
AggGeom can be used to aggregate by a partition value (or list). This example performs an aggregate union of geometries by zip code. If you invoke AggGeom with the PARTITION BY clause on the zip code column, all of the rows are redistributed by zip code, returning one output row per zip code.
CREATE TABLE geom_table(pkey integer, zipcode CHAR(5), geom
ST_Geometry);
INSERT INTO geom_table VALUES(
0, '92127', new ST_Geometry('LineString(10 10, 20 20)'));
INSERT INTO geom_table VALUES(
1, '92127', new ST_Geometry('LineString(20 20, 30 30, 40 5)'));
INSERT INTO geom_table VALUES(
2, '90338', new ST_Geometry('LineString(40 5, 50 50)'));
SELECT zipcode, geom
FROM AggGeom( ON (
SELECT geom, zipcode
FROM geom_table)
PARTITION BY zipcode
USING Operation('Union') ) L;
zipcode geom
------- -------------------------------------------------------------
90338 LINESTRING (40 5,50 50)
92127 MULTILINESTRING ((20 20,30 30,40 5),(10 10,20 20))
This second example aggregates all geometry values using a local aggregation and a final global aggregation.
The AggGeom operator can be invoked twice within a statement to perform a single aggregation of all input geometry values. If you invoke AggGeom once from SQL without the PARTITION BY clause, it will perform a local aggregation on each AMP, and one row will be returned from each amp.
To do a final union from all AMPs, nest a second call to AggGeom. The inner call to AggGeom performs the local aggregation on each AMP, and the outer call will do a final aggregation of the local aggregations and produce a single result row that represents the union of all geometries.
Use the Partition BY clause with a constant value to perform the final aggregation on a single amp. By specifying a constant value, like the number 1, the locally aggregated rows from each AMP will be in the same partition, so will all be redistributed to the same AMP for the final aggregation. A single output row is returned.
You could also aggregate all values with a single call to AggGeom by partitioning by a constant value, but that would re-distribute all rows to a single AMP to perform the aggregation. This would not take advantage of the parallel processing capability of Teradata Database. By using two calls to AggGeom, the AMPs all perform local aggregations in parallel, and only the final aggregation is performed on a single AMP.
CREATE TABLE geom_table(pkey integer,
zipcode CHAR(5),
geom ST_Geometry);
INSERT INTO geom_table VALUES(
0, '92127', new ST_Geometry('LineString(10 10, 20 20)'));
INSERT INTO geom_table VALUES(
1, '92127', new ST_Geometry('LineString(20 20, 30 30, 40 5)'));
INSERT INTO geom_table VALUES(
2, '90338', new ST_Geometry('LineString(40 5, 50 50)'));
SELECT *
FROM AggGeom( ON (
SELECT L.*, 1 as p from AggGeom( ON (
SELECT geom from geom_table)
USING Operation('Union') ) L)
PARTITION BY p
) G;
geom
----------------------------------------------------------------
MULTILINESTRING ((40 5,50 50),(20 20,30 30,40 5),(10 10,20 20))