How the ST_GeometryCollectionFromWKB() function works in MariaDB?

The ST_GeometryCollectionFromWKB() function in MariaDB is used to construct a GeometryCollection value from a Well-Known Binary (WKB) representation of a geometry collection. A GeometryCollection is a collection of one or more geometries of any type.

Syntax

The syntax for the MariaDB ST_GeometryCollectionFromWKB() function is as follows:

ST_GeometryCollectionFromWKB(wkb[, srid])

• wkb: A Well-Known Binary representation of a geometry collection.
• srid: The Spatial Reference System Identifier (SRID) for the geometry collection. If not specified, the default SRID of 0 is used, which represents a non-spatial Cartesian coordinate system.

The function returns a GEOMETRYCOLLECTION value if the input is a valid WKB representation of a geometry collection. If the input is not a valid WKB representation of a geometry collection, it returns NULL.

Examples

Example 1: Creating a GeometryCollection with Point and LineString

This example demonstrates how to create a GeometryCollection containing a Point and a LineString geometry.

SET @wkb = ST_GeometryCollectionFromText('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))');
SELECT ST_GeometryCollectionFromWKB(ST_AsWKB(@wkb));

The output is as follows:

GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))

This creates a GeometryCollection with a Point at (1, 1) and a LineString from (2, 2) to (3, 3).

Example 2: Creating a GeometryCollection with Polygon and MultiPoint

This example demonstrates how to create a GeometryCollection containing a Polygon and a MultiPoint geometry.

SET @wkb = ST_GeometryCollectionFromText('GEOMETRYCOLLECTION(POLYGON((0 0, 0 1, 1 1, 1 0, 0 0)), MULTIPOINT(2 2, 3 3))');
SELECT ST_GeometryCollectionFromWKB(ST_AsWKB(@wkb));

The output is as follows:

GEOMETRYCOLLECTION(POLYGON((0 0,0 1,1 1,1 0,0 0)),MULTIPOINT(2 2,3 3))

This creates a GeometryCollection with a Polygon and a MultiPoint containing two points at (2, 2) and (3, 3).

Example 3: Creating a GeometryCollection with a specified SRID

This example demonstrates how to create a GeometryCollection with a specified SRID.

SET @wkb = ST_GeometryCollectionFromText('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))', 4326);
SELECT ST_GeometryCollectionFromWKB(ST_AsWKB(@wkb), 4326);

The output is as follows:

GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))

This creates a GeometryCollection with a Point and a LineString, using the SRID 4326, which represents the World Geodetic System (WGS) 84 spatial reference system.

Example 4: Creating a GeometryCollection from a table

This example demonstrates how to create a GeometryCollection from a table containing geometry data.

DROP TABLE IF EXISTS geometries;
CREATE TABLE geometries (id INT, geom GEOMETRY);
INSERT INTO geometries VALUES
    (1, ST_GeometryFromWKB(ST_AsWKB(ST_GeometryFromText('POINT(1 1)')))),
    (2, ST_GeometryFromWKB(ST_AsWKB(ST_GeometryFromText('LINESTRING(2 2, 3 3)'))));

SELECT ST_GeometryCollectionFromWKB(ST_AsWKB(
    ST_GeometryCollectionFromText(
        GROUP_CONCAT(ST_AsText(geom) SEPARATOR ','),
        4326
    )
), 4326) AS geom_collection
FROM geometries;

The output is as follows:

GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))

This creates a GeometryCollection from the geometries stored in the geometries table, using the SRID 4326.

Example 5: Creating an empty GeometryCollection

This example demonstrates how to create an empty GeometryCollection.

SELECT ST_GeometryCollectionFromWKB(ST_AsWKB(ST_GeometryCollectionFromText('GEOMETRYCOLLECTION()')));

The output is as follows:

GEOMETRYCOLLECTION()

This creates an empty GeometryCollection.

Related Functions

The following are a few functions related to the MariaDB ST_GeometryCollectionFromWKB() function:

• MariaDB ST_GeometryFromWKB() function is used to construct a geometry value from a Well-Known Binary (WKB) representation of a geometry.
• MariaDB ST_GeometryCollectionFromText() function is used to construct a GeometryCollection value from a Well-Known Text (WKT) representation of a geometry collection.
• MariaDB ST_AsText() function is used to convert a geometry value to its Well-Known Text (WKT) representation.
• MariaDB ST_AsWKB() function is used to convert a geometry value to its Well-Known Binary (WKB) representation.

Conclusion

The ST_GeometryCollectionFromWKB() function in MariaDB is a useful tool for creating GeometryCollection values from Well-Known Binary (WKB) representations of geometry collections. It can be used to construct complex geometries by combining multiple geometries of different types. The provided examples demonstrate various use cases and scenarios for this function, including creating GeometryCollections with different geometry types, specifying spatial reference systems, and constructing GeometryCollections from table data.