How the ST_GeometryFromText() function works in MariaDB?

The ST_GeometryFromText() function in MariaDB is used to construct a geometry value from a Well-Known Text (WKT) 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_GeometryFromText() function is as follows:

ST_GeometryFromText(wkt[, srid])

• wkt: A Well-Known Text 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 WKT representation of a geometry. If the input is not a valid WKT representation of a geometry, it returns NULL.

Examples

Example 1: Creating a Point geometry

This example demonstrates how to create a Point geometry.

SELECT ST_GeometryFromText('POINT(1 1)');

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.

SELECT ST_GeometryFromText('LINESTRING(0 0, 1 1, 2 2)');

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.

SELECT ST_GeometryFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 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_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.

SELECT ST_GeometryFromText('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))');

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_GeometryFromText() function:

• MariaDB ST_GeometryFromWKB() function is used to construct a geometry value from a Well-Known Binary (WKB) 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_GeometryFromText() function in MariaDB is a crucial tool for creating geometry values from Well-Known Text (WKT) 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.