How the ST_GeomCollFromText() function works in MariaDB?

The ST_GeomCollFromText() function in MariaDB is used to create a geometry collection from a Well-Known Text (WKT) representation of a geometry. It takes a WKT string as input and returns a geometry collection object.

Syntax

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

ST_GeomCollFromText(wkt, srid)

• wkt: A string representing the Well-Known Text (WKT) representation of the geometry collection.
• srid (optional): An integer representing the Spatial Reference System Identifier (SRID) for the geometry collection.

The function returns a geometry collection object based on the provided WKT string. If the WKT string is invalid or cannot be parsed, it returns NULL.

Examples

Example 1: Creating a Geometry Collection

This example demonstrates how to create a geometry collection using ST_GeomCollFromText().

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

Output:

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

The output shows the geometry collection created from the provided WKT string.

Example 2: Creating a Geometry Collection with SRID

This example creates a geometry collection with a specified SRID (Spatial Reference System Identifier).

SELECT ST_AsText(ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))', 4326));

Output:

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

The output shows the geometry collection with the SRID set to 4326 (WGS 84 coordinate system).

Example 3: Creating an Empty Geometry Collection

This example demonstrates the creation of an empty geometry collection.

SELECT ST_AsText(ST_GeomCollFromText('GEOMETRYCOLLECTION EMPTY'));

Output:

GEOMETRYCOLLECTION EMPTY

The output shows an empty geometry collection.

Example 4: Creating a Geometry Collection from a Table

This example retrieves 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_GeomCollFromText('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))')),
  (2, ST_GeomCollFromText('GEOMETRYCOLLECTION(POLYGON((0 0, 0 2, 2 2, 2 0, 0 0)))'));

SELECT id, ST_AsText(geom) AS geometry_collection
FROM geometries;

Output:

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

The output shows the geometry collections stored in the table.

Example 5: Creating a Geometry Collection with Invalid WKT

This example demonstrates the behavior of ST_GeomCollFromText() when provided with an invalid WKT string.

SELECT ST_AsText(ST_GeomCollFromText('INVALID_WKT_STRING'));

Output:

NULL

The output is NULL because the provided WKT string is invalid and cannot be parsed.

Related Functions

The following are some functions related to MariaDB ST_GeomCollFromText():

• MariaDB ST_GeomFromText() function is used to create a geometry object from a Well-Known Text (WKT) representation.
• MariaDB ST_AsText() function is used to convert a geometry object to its Well-Known Text (WKT) representation.
• MariaDB ST_GeometryType() function is used to retrieve the type of a geometry object.
• MariaDB ST_IsEmpty() function is used to determine whether a geometry object is empty or not.

Conclusion

The ST_GeomCollFromText() function in MariaDB is a valuable tool for working with geometry collections. It allows you to create geometry collection objects from their Well-Known Text (WKT) representation, which can be useful in various spatial data processing and analysis tasks. By combining this function with other spatial functions, you can perform complex operations and calculations on your spatial data, enabling a wide range of applications in areas such as geographic information systems (GIS), location-based services, and spatial data management.