ST_GrandMBR aggregation function

Use ST_GrandMBR spatial aggregation function to aggregate a set of geometries from a column into a single geometry. Z and M coordinates are discarded when the aggregate is computed.

The function returns the minimum bounding rectangle for the combined geometries as a geometry of type ST_Polygon. If the set contains a single geometry, then the function returns the same geometry as is.

If all of the selected geometries are null, then null is returned.

If all of the selected geometries are either null or empty, then an empty geometry is returned.

If the combined geometries result in a single point, then this point is returned as an ST_Point value.

If the combined geometries result in a horizontal or vertical linestring, then this linestring is returned as an ST_LineString value.

Syntax

Parameter

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

Return type

ST_Geometry

Restrictions

For the ST_GrandMBR aggregation function, all the geometries for which the minimum bounding rectangle is to be computed must have the same spatial reference system, otherwise an error will be reported.

Examples

Example 1:

This example shows how to use the ST_GrandMBR function to obtain the minimum 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 function then determines the minimum bounding rectangle of all of the points put together:


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 SUBSTR(ST_ASTEXT(ST_GRANDMBR(geometry)),1,200) AS "Aggregate_of_Points" FROM sample_points

Result:


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

 1 record(s) selected.

Example 2:

The following example shows the ST_GrandMBR function used in a group-by query to compute the minimum bounding rectangles of all the geometries in respective groups:


CREATE TABLE sample_geometries(id int, region int, geometry st_geometry)

INSERT INTO sample_geometries values (1, 1, st_point(1,1))   

INSERT INTO sample_geometries values (2, 1, st_point(2,2))

INSERT INTO sample_geometries values (3, 2, st_point(5,5))

INSERT INTO sample_geometries values (4, 2, st_point(2,5))

INSERT INTO sample_geometries values (5, 3, st_point(5,5))

INSERT INTO sample_geometries values (6, 1, st_point(2,5))

SELECT region ,SUBSTR(ST_ASTEXT(ST_GRANDMBR(geometry)),1,150) AS "MBR_of_Points" FROM sample_geometries WHERE id < 5 GROUP BY region

Result:


 REGION    MBR_of_Points
 --------- ------------------------------------------------------------------------------------------------------------------------------------------------------
         1 POLYGON ((1.000000000 1.000000000, 2.000000000 1.000000000, 2.000000000 2.000000000, 1.000000000 2.000000000, 1.000000000 1.000000000))             
         2 LINESTRING (2.000000000 5.000000000, 5.000000000 5.000000000) 

  2 record(s) selected.