How the ST_GEOMETRYTYPE() function works in MariaDB?

The ST_GEOMETRYTYPE() function in MariaDB is used to retrieve the type of a geometry value.

Posted on

The ST_GEOMETRYTYPE() function in MariaDB is used to retrieve the type of a geometry value. It returns a string representing the geometry type, such as POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, or GEOMETRYCOLLECTION.

Syntax

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

ST_GEOMETRYTYPE(geom)
  • geom: A geometry value.

The function returns a string representing the type of the input geometry. If the input is NULL, it returns NULL.

Examples

Example 1: Getting the type of a Point geometry

This example demonstrates how to get the type of a Point geometry.

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

The output is as follows:

POINT

This returns the string POINT because the input geometry is a Point.

Example 2: Getting the type of a LineString geometry

This example demonstrates how to get the type of a LineString geometry.

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

The output is as follows:

LINESTRING

This returns the string LINESTRING because the input geometry is a LineString.

Example 3: Getting the type of a Polygon geometry

This example demonstrates how to get the type of a Polygon geometry.

SELECT ST_GEOMETRYTYPE(ST_GeometryFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'));

The output is as follows:

POLYGON

This returns the string POLYGON because the input geometry is a Polygon.

Example 4: Getting the type of a MultiPoint geometry

This example demonstrates how to get the type of a MultiPoint geometry.

SELECT ST_GEOMETRYTYPE(ST_GeometryFromText('MULTIPOINT(1 1, 2 2, 3 3)'));

The output is as follows:

MULTIPOINT

This returns the string MULTIPOINT because the input geometry is a MultiPoint.

Example 5: Getting the type of a GeometryCollection

This example demonstrates how to get the type of a GeometryCollection.

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

The output is as follows:

GEOMETRYCOLLECTION

This returns the string GEOMETRYCOLLECTION because the input geometry is a GeometryCollection.

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

  • MariaDB ST_GeometryFromText() function is used to construct a geometry value from a Well-Known Text (WKT) representation of a geometry.
  • MariaDB ST_GeometryCollectionFromText() function is used to construct a GeometryCollection value from a Well-Known Text (WKT) representation of a geometry collection.
  • MariaDB ST_AsText() function is used to convert a geometry value to its Well-Known Text (WKT) representation.
  • MariaDB ST_SRID() function is used to retrieve the Spatial Reference System Identifier (SRID) of a geometry value.

Conclusion

The ST_GEOMETRYTYPE() function in MariaDB is a simple yet essential tool for working with spatial data. It allows you to determine the type of a geometry value, which can be useful in various scenarios, such as conditional processing based on geometry type, validating input data, or performing type-specific operations. The provided examples demonstrate how to use this function with different geometry types, including Point, LineString, Polygon, MultiPoint, and GeometryCollection. Overall, ST_GEOMETRYTYPE() is a valuable addition to the spatial data handling capabilities in MariaDB.