How the ST_GeomCollFromWKB() function works in MariaDB?

The ST_GeomCollFromWKB() function in MariaDB is used to construct a geometry collection object from a Well-Known Binary (WKB) representation. It takes the WKB value as input and returns a geometry collection object.

Syntax

The syntax of the MariaDB ST_GeomCollFromWKB() function is as follows:

ST_GeomCollFromWKB(wkb_value, srid)

• wkb_value: A binary value that represents the Well-Known Binary (WKB) format of a geometry collection.
• srid (optional): The Spatial Reference System Identifier (SRID) for the geometry collection.

The function returns a GEOMETRYCOLLECTION object if the input is valid, or NULL if the input is invalid.

Examples

Example 1: Creating a Geometry Collection from WKB

This example demonstrates how to create a geometry collection from a WKB value.

SET @wkb = x'010700000002000000010200000003000000000000000000000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000F03F0000000000000000010300000002000000000000000000000000000000000000F03F000000000000F03F

SELECT ST_GeomCollFromWKB(@wkb);

The following is the output:

GEOMETRYCOLLECTION(LINESTRING(0 0, 1 1, 1 1), POINT(1 1))

This example creates a geometry collection containing a LINESTRING and a POINT from the provided WKB value.

Example 2: Creating a Geometry Collection with SRID

This example demonstrates how to create a geometry collection with a specified SRID.

SET @wkb = x'010700000002000000010200000003000000000000000000000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000000000010300000002000000000000000000000000000000000000F03F000000000000F03F';

SELECT ST_GeomCollFromWKB(@wkb, 4326);

The following is the output:

GEOMETRYCOLLECTION(LINESTRING(0 0, 1 1, 1 1), POINT(1 1)) SRID=4326

This example creates a geometry collection with an SRID of 4326 (WGS 84) from the provided WKB value.

Example 3: Handling Invalid WKB Input

This example demonstrates how the function handles invalid WKB input.

SET @invalid_wkb = x'0107000000020000000102000000030000000000000000000000000000000000000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000000000010300000002000000000000000000000000000000000000F03F000000000000F03F';

SELECT ST_GeomCollFromWKB(@invalid_wkb);

The following is the output:

NULL

Since the input WKB value is invalid, the function returns NULL.

Example 4: Creating a Geometry Collection from a Table

This example demonstrates how to create a geometry collection from a table.

DROP TABLE IF EXISTS geometries;
CREATE TABLE geometries (
    id INT PRIMARY KEY,
    geom GEOMETRY
);

INSERT INTO geometries (id, geom) VALUES
    (1, ST_GeomFromText('LINESTRING(0 0, 1 1, 2 2)')),
    (2, ST_GeomFromText('POINT(3 3)'));

SELECT ST_GeomCollFromWKB(ST_AsBinary(ST_GeomCollFromText('GEOMETRYCOLLECTION(' || GROUP_CONCAT(ST_AsText(geom)) || ')')), 4326) AS geom_coll
FROM geometries;

The following is the output:

GEOMETRYCOLLECTION(LINESTRING(0 0, 1 1, 2 2), POINT(3 3)) SRID=4326

This example creates a geometry collection by combining the geometries from the geometries table.

Example 5: Creating a Geometry Collection with Nested Collections

This example demonstrates how to create a geometry collection with nested collections.

SET @wkb = x'010700000003000000010200000003000000000000000000000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000000000010300000002000000000000000000000000000000000000F03F000000000000F03F010700000002000000010200000002000000000000000000F03F000000000000F03F000000000000000000000000000000000000000000010300000002000000000000000000F03F000000000000F03F';

SELECT ST_GeomCollFromWKB(@wkb);

The following is the output:

GEOMETRYCOLLECTION(LINESTRING(0 0, 1 1, 1 1), POINT(1 1), GEOMETRYCOLLECTION(LINESTRING(1 1, 0 0), POINT(1 1)))

This example creates a geometry collection with nested geometry collections, containing a LINESTRING, a POINT, and another geometry collection with a LINESTRING and a POINT.

Related Functions

The following are some functions related to the MariaDB ST_GeomCollFromWKB() function:

• MariaDB ST_GeomFromWKB() function is used to construct a single geometry object from a Well-Known Binary (WKB) representation.
• MariaDB ST_AsWKB() function is used to return the Well-Known Binary (WKB) representation of a geometry object.
• MariaDB ST_GeomCollFromText() function is used to construct a geometry collection object from a Well-Known Text (WKT) representation.
• MariaDB ST_AsText() function is used to return the Well-Known Text (WKT) representation of a geometry object.

Conclusion

The ST_GeomCollFromWKB() function in MariaDB is a powerful tool for constructing geometry collection objects from Well-Known Binary (WKB) representations. It provides a convenient way to work with complex spatial data structures and supports various geometries, including points, lines, and polygons. By understanding its syntax, parameters, and usage examples, you can effectively utilize this function in your spatial data processing and analysis tasks.