How the GeomCollFromText() function works in Mariadb?

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

Posted on

The GeomCollFromText() function is a spatial function that creates a geometry collection from a well-known text (WKT) representation. It can be useful for storing or manipulating multiple geometries of different types as a single object.

Syntax

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

GeomCollFromText(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 GeomCollFromText() function to create a geometry collection from a WKT string.

SELECT AsText(GeomCollFromText('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 GeomCollFromText() function with the SRID parameter.

SELECT AsText(GeomCollFromText('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 GeomCollFromText() function with an invalid WKT string.

SELECT GeomCollFromText('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.

Example 4: Empty geometry collection

The following example shows how to use the GeomCollFromText() function to create an empty geometry collection.

SELECT AsText(GeomCollFromText('GEOMETRYCOLLECTION EMPTY')) AS geom;

The output is:

+--------------------------+
| geom                     |
+--------------------------+
| GEOMETRYCOLLECTION EMPTY |
+--------------------------+

The function returns an empty geometry collection object, which is represented by a binary string that contains only the type and the SRID.

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

  • GeomCollFromWKB(): This function creates a geometry collection from a well-known binary (WKB) representation. It is similar to the GeomCollFromText() function, except that it takes a binary string instead of a text string as the input.
  • AsText(): This function converts a geometry object to a well-known text representation. It is the inverse of the GeomCollFromText() function. For example, AsText(GeomCollFromText('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3), POLYGON((4 4, 5 4, 5 5, 4 5, 4 4)))')) returns ‘GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3), POLYGON((4 4, 5 4, 5 5, 4 5, 4 4)))’.
  • 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.

Conclusion

The GeomCollFromText() 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.