How the ST_DIMENSION() function works in MariaDB?

The ST_DIMENSION() function in MariaDB is used to retrieve the dimension value of a spatial object, allowing for efficient analysis and manipulation of spatial data.

Posted on

The ST_DIMENSION() function in MariaDB is used to retrieve the dimension value of a spatial object. The dimension value represents the inherent complexity of the geometry, with 0 being the lowest and 2 being the highest.

Syntax

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

ST_DIMENSION(g)
  • g: A geometry value representing a spatial object.

The function returns an integer value indicating the dimension of the spatial object:

  • 0 for scalar values (points)
  • 1 for linear values (linestrings, multilinestrings)
  • 2 for areal values (polygons, multipolygons)
  • NULL if the input geometry is NULL

Examples

Example 1: Point Dimension

This example demonstrates the dimension of a point.

SET @point = ST_GeomFromText('POINT(1 1)');

SELECT ST_DIMENSION(@point);

Output:

0

The output 0 indicates that the dimension of a point is 0.

Example 2: LineString Dimension

This example shows the dimension of a linestring.

SET @line = ST_GeomFromText('LINESTRING(0 0, 2 2, 4 4)');

SELECT ST_DIMENSION(@line);

Output:

1

The output 1 indicates that the dimension of a linestring is 1.

Example 3: Polygon Dimension

This example demonstrates the dimension of a polygon.

SET @poly = ST_GeomFromText('POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))');

SELECT ST_DIMENSION(@poly);

Output:

2

The output 2 indicates that the dimension of a polygon is 2.

Example 4: Geometry Collection Dimension

This example shows the dimension of a geometry collection.

SET @geom = ST_GeomFromText('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(0 0, 2 2), POLYGON((0 0, 4 0, 4 4, 0 4, 0 0)))');

SELECT ST_DIMENSION(@geom);

Output:

2

The output 2 indicates that the dimension of the geometry collection is determined by the maximum dimension of its components, which in this case is 2 (the polygon).

Example 5: Dimension from a Table

This example retrieves the dimension of geometries stored in a table.

DROP TABLE IF EXISTS geometries;
CREATE TABLE geometries (id INT PRIMARY KEY, geom GEOMETRY);

INSERT INTO geometries (id, geom) VALUES
  (1, ST_GeomFromText('POINT(1 1)')),
  (2, ST_GeomFromText('LINESTRING(0 0, 2 2)')),
  (3, ST_GeomFromText('POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))'));

SELECT id, ST_DIMENSION(geom) AS dimension
FROM geometries;

Output:

1, 0
2, 1
3, 2

The output shows the dimension of each geometry stored in the table.

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

  • MariaDB ST_GeometryType() function is used to retrieve the type of a spatial object.
  • MariaDB ST_IsEmpty() function is used to determine whether a spatial object is empty or not.
  • MariaDB ST_IsSimple() function is used to determine whether a spatial object is simple or not.
  • MariaDB ST_IsValid() function is used to determine whether a spatial object is valid or not.

Conclusion

The ST_DIMENSION() function in MariaDB provides a convenient way to retrieve the dimension value of a spatial object, which can be useful in various spatial analyses and operations. By understanding the dimension of a geometry, you can better understand its complexity and handle it accordingly in your applications.