ST_BuildUnionAggr and ST_GetAggrResult functions

A union aggregate is the combination of the ST_BuildUnionAggr and ST_GetAggrResult functions. Use this combination to aggregate a column of geometries in a table to single geometry by constructing the union.

If all of the geometries to be combined in the union are null , then null is returned. If each of the geometries to be combined in the union are either null or are empty, then an empty geometry of type ST_Point is returned.

The ST_BuildUnionAggr function can also be called as a method.

Syntax

Read syntax diagramSkip visual syntax diagramdb2gse.ST_GetAggrResult(MAX( db2sge.ST_BuildUnionAggr(geometries) ))

Parameters

geometries
A column in a table that has a type of ST_Geometry or one of its subtypes and represents all the geometries that are to be combined into a union.

Return type

db2gse.ST_Geometry

Restrictions

You cannot construct the union aggregate of a spatial column in a table in any of the following situations:
  • In partitioned database environments
  • If a GROUP BY clause is used in the select
  • If you use a function other than the aggregate function MAX

Example

In the following example, the lines of results have been reformatted for readability. The spacing in your results will vary according to your online display.

This example illustrates how a union aggregate can be used to combine a set of points into multipoints. Several points are added to the SAMPLE_POINTS table. The ST_GetAggrResult and BuildUnionAggr functions are used to construct the union of the points. 

SET CURRENT FUNCTION PATH = CURRENT FUNCTION PATH, db2gse
CREATE TABLE sample_points (id INTEGER, geometry ST_Point)

INSERT INTO sample_points
  VALUES (1, ST_Point (2, 3, 1) )
INSERT INTO sample_points
  VALUES (2, ST_Point (4, 5, 1) )
INSERT INTO sample_points
  VALUES (3, ST_Point (13, 15, 1) )
INSERT INTO sample_points
  VALUES (4, ST_Point (12, 5, 1) )
INSERT INTO sample_points
  VALUES (5, ST_Point (23, 2, 1) )
INSERT INTO sample_points
  VALUES (6, ST_Point (11, 4, 1) )

SELECT CAST (ST_AsText(
          ST_GetAggrResult( MAX( ST_BuildUnionAggr (geometry) ) ))
       AS VARCHAR(160)) POINT_AGGREGATE
  FROM sample_points
Results: 

POINT_AGGREGATE
----------------------------------------------------------
MULTIPOINT ( 2.00000000 3.00000000, 4.00000000 5.00000000,
            11.00000000 4.00000000, 12.00000000 5.00000000,
            13.00000000 15.00000000, 23.00000000 2.00000000)