How the ST_GeometryFromWKB() function works in MariaDB?

The ST_GeometryFromWKB() function in MariaDB is used to construct a geometry value from a Well-Known Binary (WKB) representation of a geometry. It supports various geometry types, including Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection.

Syntax

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

ST_GeometryFromWKB(wkb[, srid])

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

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

Examples

Example 1: Creating a Point geometry

This example demonstrates how to create a Point geometry.

SET @wkb = ST_GeometryFromText('POINT(1 1)');
SELECT ST_GeometryFromWKB(ST_AsWKB(@wkb));

The output is as follows:

POINT(1 1)

This creates a Point geometry at coordinates (1, 1).

Example 2: Creating a LineString geometry

This example demonstrates how to create a LineString geometry.

SET @wkb = ST_GeometryFromText('LINESTRING(0 0, 1 1, 2 2)');
SELECT ST_GeometryFromWKB(ST_AsWKB(@wkb));

The output is as follows:

LINESTRING(0 0,1 1,2 2)

This creates a LineString geometry with three vertices at (0, 0), (1, 1), and (2, 2).

Example 3: Creating a Polygon geometry with a specified SRID

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

SET @wkb = ST_GeometryFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 4326);
SELECT ST_GeometryFromWKB(ST_AsWKB(@wkb), 4326);

The output is as follows:

POLYGON((0 0,0 1,1 1,1 0,0 0))

This creates a Polygon geometry with four vertices, using the SRID 4326, which represents the World Geodetic System (WGS) 84 spatial reference system.

Example 4: Creating a MultiPoint geometry from a table

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

DROP TABLE IF EXISTS points;
CREATE TABLE points (id INT, x DOUBLE, y DOUBLE);
INSERT INTO points VALUES
    (1, 1.0, 1.0),
    (2, 2.0, 2.0),
    (3, 3.0, 3.0);

SELECT ST_GeometryFromWKB(
    ST_AsWKB(
        ST_GeometryFromText(
            CONCAT('MULTIPOINT(', GROUP_CONCAT(CONCAT(x, ' ', y) SEPARATOR ','), ')')
        )
    )
) AS multipoint
FROM points;

The output is as follows:

MULTIPOINT(1 1,2 2,3 3)

This creates a MultiPoint geometry from the points stored in the points table.

Example 5: Creating a GeometryCollection

This example demonstrates how to create a GeometryCollection.

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

The output is as follows:

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

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

Related Functions

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

• MariaDB ST_GeometryFromText() function is used to construct a geometry value from a Well-Known Text (WKT) representation of a geometry.
• 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.
• MariaDB ST_GeometryType() function is used to retrieve the type of a geometry value.

Conclusion

The ST_GeometryFromWKB() function in MariaDB is a crucial tool for creating geometry values from Well-Known Binary (WKB) representations. It supports various geometry types and allows for specifying spatial reference systems. The provided examples demonstrate different use cases for this function, including creating Point, LineString, Polygon, MultiPoint, and GeometryCollection geometries, as well as constructing geometries from table data. The function is an essential part of the spatial data handling capabilities in MariaDB, enabling developers and users to work with and manipulate geometric data effectively.