How the ST_DISTANCE() function works in MariaDB?

The ST_DISTANCE() function in MariaDB is used to calculate the Cartesian distance between two spatial objects. It returns the minimum distance between the objects, measured in the same unit as the spatial reference system of the geometries.

Syntax

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

ST_DISTANCE(g1, g2)

• g1: A geometry value representing the first spatial object.
• g2: A geometry value representing the second spatial object.

The function returns the minimum Cartesian distance between the two spatial objects. If the geometries are disjoint, it returns the minimum distance between them. If the geometries intersect or touch, it returns 0.

Examples

Example 1: Distance Between Points

This example demonstrates the calculation of the distance between two points.

SET @point1 = ST_GeomFromText('POINT(0 0)');
SET @point2 = ST_GeomFromText('POINT(3 4)');

SELECT ST_DISTANCE(@point1, @point2);

Output:

5

The output shows the Cartesian distance between the two points, which is 5 units.

Example 2: Distance Between a Point and a LineString

This example calculates the distance between a point and a linestring.

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

SELECT ST_DISTANCE(@point, @line);

Output:

2

The output represents the minimum Cartesian distance between the point and the linestring, which is 2 units.

Example 3: Distance Between Intersecting Geometries

This example demonstrates the distance between intersecting geometries.

SET @poly1 = ST_GeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))');
SET @poly2 = ST_GeomFromText('POLYGON((1 1, 1 3, 3 3, 3 1, 1 1))');

SELECT ST_DISTANCE(@poly1, @poly2);

Output:

0

The output shows that the distance between the intersecting polygons is 0 units.

Example 4: Distance Between Disjoint Geometries

This example calculates the distance between disjoint geometries.

SET @poly1 = ST_GeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))');
SET @poly2 = ST_GeomFromText('POLYGON((3 3, 3 5, 5 5, 5 3, 3 3))');

SELECT ST_DISTANCE(@poly1, @poly2);

Output:

1.4142135623730951

The output represents the minimum Cartesian distance between the two disjoint polygons, which is approximately 1.41 units.

Example 5: Distance from a Table

This example retrieves the distance between geometries stored in a table.

DROP TABLE IF EXISTS locations;
CREATE TABLE locations (id INT PRIMARY KEY, location POINT);

INSERT INTO locations (id, location) VALUES
  (1, ST_GeomFromText('POINT(0 0)')),
  (2, ST_GeomFromText('POINT(3 4)')),
  (3, ST_GeomFromText('POINT(6 8)'));

SELECT id, ST_DISTANCE(location, (SELECT location FROM locations WHERE id = 1)) AS distance
FROM locations
WHERE id <> 1;

Output:

2, 5
3, 10

The output shows the Cartesian distance between each location and the location with id 1.

Related Functions

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

• MariaDB ST_DISTANCE_SPHERE() function is used to calculate the distance between two spatial objects on a spherical surface.
• MariaDB ST_DISTANCE_SPHEROID() function is used to calculate the distance between two spatial objects on a spheroidal surface.
• MariaDB ST_BUFFER() function is used to create a buffer around a spatial object at a specified distance.
• MariaDB ST_WITHIN() function is used to determine whether a spatial object is entirely within another spatial object.

Conclusion

The ST_DISTANCE() function in MariaDB is a powerful tool for calculating the Cartesian distance between spatial objects. It provides an accurate measurement of the minimum distance between geometries, which can be useful in various spatial analyses and applications, such as proximity calculations, spatial queries, and more. By understanding the Cartesian distance between spatial objects, you can make informed decisions and perform operations based on their relative positions and distances.