ST_BuildMBRAggr and ST_GetAggrResult functions

Use the ST_BuildMBRAggr and ST_GetAggrResult functions combined to aggregate a set of geometries from a column into a single geometry. The combination constructs a rectangle that represents the minimum bounding rectangle that encloses all the geometries in the column. Z and M coordinates are discarded when the aggregate is computed.

The following expression is an example that uses the MAX function with the db2gse.ST_BuildMBRAggr spatial function to compute the MBR of the geometries in the columnName column and the db2gse.ST_GetAggrResult spatial function to return the resulting geometry that was computed for the MBR:
db2gse.ST_Get_AggrResult(MAX(db2gse.ST_BuildMBRAggr(columnName)))

If all of the geometries to be combined are null, then null is returned. If all of the geometries are either null or empty, then an empty geometry is returned. If the minimum bounding rectangle of all the geometries to be combined results in a point, then this point is returned as an ST_Point value. If the minimum bounding rectangle of all the geometries to be combined results in a horizontal or vertical linestring, then this linestring is returned as an ST_LineString value. Otherwise, the minimum bounding rectangle is returned as an ST_Polygon value.

Syntax

Read syntax diagramSkip visual syntax diagramdb2gse.ST_GetAggrResult(MAX( db2gse.ST_BuildMBRAggr ( geometries)))

Parameter

geometries
A selected column that has a type of ST_Geometry or one of its subtypes and represents all the geometries for which the minimum bounding rectangle is to be computed.

Return type

db2gse.ST_Geometry

Restrictions

You cannot construct the union aggregate of a spatial column in a full-select in any of the following situations:
  • In a partitioned database environment
  • If GROUP BY clause is used in the full-select.
  • If you use a function other than the Db2® 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 shows how to use the ST_BuildMBRAggr function to obtain the maximum bounding rectangle of all of the geometries within a column. In this example, several points are added to the GEOMETRY column in the SAMPLE_POINTS table. The SQL code then determines the maximum bounding rectangle of all of the points put together. 

SET CURRENT FUNCTION PATH = CURRENT FUNCTION PATH, db2gse

CREATE TABLE sample_points (id integer, geometry ST_Point)

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


SELECT cast(ST_GetAggrResult(MAX(ST_BuildMBRAggr
    (geometry)))..ST_AsText AS varchar(160)) 
    AS ";Aggregate_of_Points";
FROM sample_points
Results: 

Aggregate_of_Points
----------------------------------------------------------
POLYGON (( 2.00000000 2.00000000, 23.00000000 2.00000000, 
23.00000000 15.00000000, 2.00000000 15.00000000, 2.00000000 
2.00000000))