How the GeometryCollectionFromText() function works in Mariadb?

The GeometryCollectionFromText() function is a spatial function that creates a geometry collection from a well-known text (WKT) representation.

Posted on

The GeometryCollectionFromText() function is a spatial function that creates a geometry collection from a well-known text (WKT) representation. It is an alias for the GeomCollFromText() function, which means they have the same functionality and syntax. It can be useful for storing or manipulating multiple geometries of different types as a single object.

Syntax

The syntax of the GeometryCollectionFromText() function is as follows:

GeometryCollectionFromText(wkt, [srid])

The wkt parameter is a string that contains the well-known text representation of the geometry collection. The function returns a geometry collection object that contains the geometries specified by the WKT string.

The optional srid parameter is an integer that specifies the spatial reference system identifier (SRID) of the geometry collection. The function assigns the SRID to the geometry collection object if the parameter is provided. Otherwise, the SRID is set to 0 by default.

Examples

Example 1: Basic usage

The following example shows how to use the GeometryCollectionFromText() function to create a geometry collection from a WKT string.

SELECT AsText(GeometryCollectionFromText('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3), POLYGON((4 4, 5 4, 5 5, 4 5, 4 4)))')) AS geom;

The output is:

+-----------------------------------------------------------------------------------+
| geom                                                                              |
+-----------------------------------------------------------------------------------+
| GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3),POLYGON((4 4,5 4,5 5,4 5,4 4))) |
+-----------------------------------------------------------------------------------+

The function returns a geometry collection object that contains a point, a linestring, and a polygon. The object is stored as a binary string in the internal format of Mariadb.

Example 2: SRID parameter

The following example shows how to use the GeometryCollectionFromText() function with the SRID parameter.

SELECT AsText(GeometryCollectionFromText('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3), POLYGON((4 4, 5 4, 5 5, 4 5, 4 4)))', 4326)) AS geom;

The output is:

+-----------------------------------------------------------------------------------+
| geom                                                                              |
+-----------------------------------------------------------------------------------+
| GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3),POLYGON((4 4,5 4,5 5,4 5,4 4))) |
+-----------------------------------------------------------------------------------+

The function returns a geometry collection object that has the SRID of 4326, which is the standard code for the WGS 84 coordinate system. The SRID is stored as the first four bytes of the binary string.

Example 3: Invalid WKT string

The following example shows how to use the GeometryCollectionFromText() function with an invalid WKT string.

SELECT GeometryCollectionFromText('Invalid WKT string') AS geom;

The output is:

+------+
| geom |
+------+
| NULL |
+------+

The function returns NULL when the WKT string is invalid or does not represent a geometry collection.

Some of the functions that are related to the GeometryCollectionFromText() function are:

  • GeomCollFromText(): This function creates a geometry collection from a well-known text (WKT) representation. It is the same as the GeometryCollectionFromText() function, which means they have the same functionality and syntax. For example, GeomCollFromText('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3), POLYGON((4 4, 5 4, 5 5, 4 5, 4 4)))') returns the same geometry collection object as the example 1.
  • GeomCollFromWKB(): This function creates a geometry collection from a well-known binary (WKB) representation. It is similar to the GeometryCollectionFromText() function, except that it takes a binary string instead of a text string as the input. For example, GeomCollFromWKB(0x0000000007000000030101000000000000000000F03F000000000000F03F01020000000200000000000000000000400000000000004040000000000000404003000000000500000000000000000010400000000000001040000000000000104000000000000014400000000000001440000000000000144000000000000010400000000000001040000000000000104000000000000001040) returns the same geometry collection object as the example 1.
  • NumGeometries(): This function returns the number of geometries in a geometry collection. It can be used to iterate over the elements of a geometry collection. For example, NumGeometries(GeometryCollectionFromText('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3), POLYGON((4 4, 5 4, 5 5, 4 5, 4 4)))')) returns 3.

Conclusion

The GeometryCollectionFromText() function is a useful function for creating a geometry collection from a well-known text representation. It can be used for storing or manipulating multiple geometries of different types as a single object. It can also be combined with other spatial functions to achieve various spatial operations.