ST_UnionAggr

The ST_UnionAggr function is a union aggregate function that works as a scalar function. This function returns a result for each row.

The result is the union of the geometry on that row and all of the geometries of the previous rows. The result of the final row is the union of all the geometries of that column.

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

Syntax

Parameters

geometries: A column in a table that has a type of one of the seven distinct spatial data types. The geometries of the column are combined into a union.

Return type

db2gse.ST_Geometry

Examples

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

Example 1: This example illustrates how you can use a union aggregate function to combine a set of points into multipoints. Several points are added to the SAMPLE_POINTS table. The ST_UnionAggr function is used to construct the union of the points for each row.


SET CURRENT FUNCTION PATH = CURRENT FUNCTION PATH, db2gse
CREATE TABLE SYSADM.SAMPLE_POINTS (id INTEGER, geometry ST_Point)

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

SELECT CAST (ST_AsText( ST_UNIONAGGR(geometry) )  
       AS VARCHAR(160) ) POINT_AGGREGATE
  FROM SYSADM.SAMPLE_POINTS

Results:


POINT_AGGREGATE
----------------------------------------------------------
POINT      (2.000000 3.000000)
MULTIPOINT (2.000000 3.000000, 4.000000 5.000000) 
MULTIPOINT (2.000000 3.000000, 4.000000 5.000000, 13.000000 15.000000)
MULTIPOINT (2.000000 3.000000, 4.000000 5.000000, 12.000000 5.000000, 
           13.000000 15.000000)
MULTIPOINT (2.000000 3.000000, 4.000000 5.000000, 12.000000 5.000000, 
           13.000000 15.000000, 23.000000 2.000000)
MULTIPOINT (2.000000 3.000000, 4.000000 5.000000, 11.000000 4.000000, 
           12.000000 5.000000, 13.000000 15.000000, 23.000000 2.000000)

Example 2: The following examples shows how you can return the last row of the result set from the ST_UnionAggr function.

   EXEC SQL BEGIN DECLARE SECTION;

/* User Defined Variables                  */

    struct
         {  short   len;
            char    data(256);
 
    } HOSTVAR;
    short I_HOSTVAR;
   EXEC SQL END DECLARE SECTION;
      


/* Declare static scroll cursor*/
     EXEC SQL DECLARE C1 SENSITIVE STATIC SCROLL
     CURSOR  FOR   SELECT
     CAST(DB2GSE.ST_ASTEXT(DB2GSE.ST_UNIONAGGR(GEOMETRY) ) AS VARCHAR(256) )
                    FROM SYSADM.SYSADM.SAMPLE_POINTS;
.
.
.
/* open cursor*/
     EXEC SQL OPEN C1;

/* Fetch the last row to HOSTVAR */
     EXEC SQL
     FETCH  ABSOLUTE -1   C1
     INTO :HOSTVAR:I_HOSTVAR;

/* Close the cursor*/
    EXEC SQL
         CLOSE C1 ;