15.10 - AggGeomUnion - Teradata Database

Teradata Database SQL Geospatial Types

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

Note: This function is deprecated. Teradata recommends using instead the AggGeom table operator, which provides better performance an no size restriction.

Aggregate function that returns the union of all spatial objects in the aggregation group.

 

Argument …

Specifies …

ageometry

a constant or column expression for which the union is to be computed.

The data type of ageometry is ST_Geometry.

Returns an ST_Geometry value.

NULL arguments are not included in the aggregation. If all values in the aggregation are null, the return value is NULL.

Note: Because the order in which the rows are processed and per-AMP results are returned for aggregation can vary from run to run, the ordering of the values in the results can vary between different instances of identical queries. Although the order of the individual values in the returned results can vary, the overall results from the query will be the same when an identical query is run on the same data.

The input geometry cannot be larger than 64000 bytes.

If the WKB representation of the resulting geometry becomes larger than 64000 bytes during the aggregation, Teradata Database reports an error.

This function does not support geometry collections.

During the final phase of aggregation, each AMP sends a portion of the aggregated results to be combined into a final aggregation. The order in which this happens can vary from run to run, so actual results can vary per run.

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

This example uses the AggGeomUnion function to get the spatial union of the shape column in rows of the sample_shapes table:

CREATE TABLE sample_shapes(skey INTEGER, shape ST_Geometry);
 
INSERT INTO sample_shapes(1001,'MultiPoint((10 20 30),(40 50 60))');
INSERT INTO sample_shapes(1002,'Point(10 20 30)');
INSERT INTO sample_shapes(1003,
 'Polygon((100 100 100, 100 500 500, 500 500 500, 500 100 500, 100 100 100))');
INSERT INTO sample_shapes(1004, 'Linestring (100 500 500, 500 100 500)');
INSERT INTO sample_shapes(1005,
 'Polygon((500 500 500, 500 1000 1000, 1000 1000 1000, 1000 500 1000, 500 500 500))');
INSERT INTO sample_shapes(1006, 'Polygon((0 0 0, 0 50 50, 50 50 50, 50 0 50, 0 0 0))');
INSERT INTO sample_shapes(1007, 
 'Polygon((50 50 50, 50 100 100, 100 100 100, 100 50 100, 50 50 50))');
 
select AggGeomUnion(shape) from sample_shapes where skey in (1001, 1002);

AggGeomUnion(shape)
----------------------------------------------------------------------------------------
MULTIPOINT (10 20 30,40 50 60)
select AggGeomUnion(shape) from sample_shapes where skey in (1003, 1004);

AggGeomUnion(shape)
----------------------------------------------------------------------------------------POLYGON ((100 100 100,100 500 500,500 500 500,500 100 500,100 100 100))
select AggGeomUnion(shape) from sample_shapes where skey in (1003,1005,1006,1007);

AggGeomUnion(shape)
----------------------------------------------------------------------------------------MULTIPOLYGON (((50 50 50,50 100 100,100 100 100,100 50 100,50 50 50)),((500 500 500,500 1000 1000,1000 1000 1000,1000 500 1000,500 500 500)),((0 0 0,0 50 50,50 50 50,50 0 50,0 0 0)),((100 100 100,100 500 500,500 500 500,500 100 500,100 100 100)))