How the DIMENSION() function works in Mariadb?

The DIMENSION() function is a built-in function in Mariadb that returns the dimension of a geometry value.

Posted on

The DIMENSION() function is a built-in function in Mariadb that returns the dimension of a geometry value. The dimension is the topological dimension of the geometry, which is 0 for points, 1 for lines, and 2 for polygons. The DIMENSION() function is a synonym for the ST_DIMENSION() function, which follows the standard SQL/MM specification.

Syntax

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

DIMENSION(g)

The parameter g is a geometry value. The function returns an integer value that represents the dimension of the geometry. If g is NULL or not a valid geometry value, the function returns NULL.

Examples

Here are some examples of using the DIMENSION() function with different types of geometries.

Example 1: Point

A point is a zero-dimensional geometry, so the DIMENSION() function returns 0 for a point.

SELECT DIMENSION(ST_GeomFromText('POINT (1 2)'));

The output is:

+-------------------------------------------+
| DIMENSION(ST_GeomFromText('POINT (1 2)')) |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+

Example 2: LineString

A line string is a one-dimensional geometry, so the DIMENSION() function returns 1 for a line string.

SELECT DIMENSION(ST_GeomFromText('LineString (1 2,2 3,3 4,4 5)'));

The output is:

+------------------------------------------------------------+
| DIMENSION(ST_GeomFromText('LineString (1 2,2 3,3 4,4 5)')) |
+------------------------------------------------------------+
|                                                          1 |
+------------------------------------------------------------+

Example 3: Polygon

A polygon is a two-dimensional geometry, so the DIMENSION() function returns 2 for a polygon.

SET @poly = 'POLYGON ( (0 0,5 0,5 5,0 5,0 0), (1 1,2 1,2 2,1 2,1 1))';
SELECT DIMENSION(ST_GeomFromText(@poly));

The output is:

+-----------------------------------+
| DIMENSION(ST_GeomFromText(@poly)) |
+-----------------------------------+
|                                 2 |
+-----------------------------------+

There are some other functions that are related to the DIMENSION() function in Mariadb. Here are some of them:

  • ST_DIMENSION(): This function is equivalent to the DIMENSION() function, but follows the standard SQL/MM specification. It can be used interchangeably with the DIMENSION() function.
  • GEOMETRYTYPE(): This function returns the name of the geometry type of a geometry value, such as POINT, LINESTRING, or POLYGON.
  • ST_GEOMETRYTYPE(): This function is similar to the GEOMETRYTYPE() function, but follows the standard SQL/MM specification. It returns the name of the geometry type with a ST_ prefix, such as ST_POINT, ST_LINESTRING, or ST_POLYGON.
  • SRID(): This function returns the spatial reference system identifier (SRID) of a geometry value. The SRID is a numeric value that defines the coordinate system and projection of the geometry.
  • ST_SRID(): This function is similar to the SRID() function, but follows the standard SQL/MM specification. It returns the same value as the SRID() function.

For example, the following query shows the dimension, geometry type, and SRID of a point geometry.

SELECT DIMENSION(g), GEOMETRYTYPE(g), SRID(g) FROM (SELECT ST_GeomFromText('POINT (1 2)', 4326) AS g) AS t;

The output is:

+--------------+-----------------+----------+
| DIMENSION(g) | GEOMETRYTYPE(g) | SRID(g)  |
+--------------+-----------------+----------+
| 0            | POINT           | 4326     |
+--------------+-----------------+----------+

Conclusion

The DIMENSION() function is a useful function in Mariadb that returns the dimension of a geometry value. The dimension is the topological dimension of the geometry, which is 0 for points, 1 for lines, and 2 for polygons. The DIMENSION() function is a synonym for the ST_DIMENSION() function, which follows the standard SQL/MM specification. There are some other functions that are related to the DIMENSION() function, such as GEOMETRYTYPE(), SRID(), and their standard counterparts. These functions can help us to get more information about the geometry values in Mariadb.