How the ST_LENGTH() function works in Mariadb?

The ST_LENGTH() function in MariaDB is used to calculate the length or perimeter of a given geometry.

Posted on

The ST_LENGTH() function in MariaDB is used to calculate the length or perimeter of a given geometry. It is particularly useful when working with linestring and polygon geometries, as it allows you to measure the total length or perimeter of these geometries.

Syntax

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

ST_LENGTH(g)
  • g: The geometry value for which the length or perimeter will be calculated.

The function returns the length or perimeter of the given geometry, measured in the same units as the spatial reference system (SRS) of the geometry.

Examples

Example 1: Calculating the length of a linestring

This example demonstrates how to calculate the length of a linestring geometry.

SELECT ST_LENGTH(ST_GEOMFROMTEXT('LINESTRING(0 0, 1 1, 2 0)'));

The following is the output:

2.8284271247461903

The length of the linestring defined by the coordinates (0 0, 1 1, 2 0) is calculated as 2.8284271247461903, which represents the total distance between the vertices.

Example 2: Calculating the perimeter of a polygon

This example shows how to calculate the perimeter of a polygon geometry.

SELECT ST_LENGTH(ST_POLYGONFROMTEXT('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'));

The following is the output:

40.0

The perimeter of the polygon defined by the coordinates (0 0, 10 0, 10 10, 0 10, 0 0) is calculated as 40.0, which represents the total length of the outer ring.

Example 3: Calculating the length of a multilinestring

This example demonstrates how to calculate the length of a multilinestring geometry.

SELECT ST_LENGTH(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1), (2 2, 3 3))'));

The following is the output:

2.8284271247461903

The length of the multilinestring geometry, composed of two linestrings (0 0, 1 1) and (2 2, 3 3), is calculated as 2.8284271247461903, which represents the sum of the lengths of both linestrings.

Example 4: Calculating the length of a geometry with a spatial reference system (SRS)

This example shows how to calculate the length of a geometry with a specific spatial reference system (SRS).

DROP TABLE IF EXISTS lines;
CREATE TABLE lines (id INT PRIMARY KEY, line LINESTRING SRID 4326);
INSERT INTO lines (id, line) VALUES (1, ST_GEOMFROMTEXT('LINESTRING(0 0, 1 1)', 4326));

SELECT ST_LENGTH(line) FROM lines WHERE id = 1;

The following is the output:

156876.49192185052

The length of the linestring defined by the coordinates (0 0, 1 1) with the SRS 4326 (WGS 84) is calculated as 156876.49192185052, which represents the length in meters.

Example 5: Calculating the length of a geometry collection

This example demonstrates how to calculate the length of a geometry collection.

SELECT ST_LENGTH(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(LINESTRING(0 0, 1 1), POLYGON((2 2, 4 2, 4 4, 2 4, 2 2)))'));

The following is the output:

5.656854249492381

The length of the geometry collection, composed of a linestring (0 0, 1 1) and a polygon (2 2, 4 2, 4 4, 2 4, 2 2), is calculated as 5.656854249492381, which represents the sum of the length of the linestring and the perimeter of the polygon.

The following are some functions related to the MariaDB ST_LENGTH() function:

  • MariaDB ST_AREA() function is used to calculate the area of a given geometry.
  • MariaDB ST_DISTANCE() function is used to calculate the distance between two geometries.
  • MariaDB ST_PERIMETER() function is used to calculate the perimeter of a given polygon geometry.
  • MariaDB ST_HAUSDORFFDISTANCE() function is used to calculate the Hausdorff distance between two geometries.
  • MariaDB ST_BUFFER() function is used to create a buffer geometry around a given geometry.

Conclusion

The ST_LENGTH() function in MariaDB is a powerful tool for calculating the length or perimeter of various geometries, including linestrings, polygons, multilinestrings, and even geometry collections. By understanding the syntax and examples provided, you can effectively utilize this function in your spatial queries and analyses, enabling accurate measurements and calculations involving spatial data.