Examples: AggGeom - Advanced SQL Engine - Teradata Database

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 Vantage. 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))