How the ST_DISTANCE_SPHERE() function works in MariaDB?

The ST_DISTANCE_SPHERE() function in MariaDB is used to calculate the minimum distance between two spatial objects on a spherical surface. It is particularly useful when working with geographic data, as it considers the curvature of the Earth and provides accurate distance measurements.

Syntax

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

ST_DISTANCE_SPHERE(g1, g2, radius)

• g1: A geometry value representing the first spatial object.
• g2: A geometry value representing the second spatial object.
• radius: A double value representing the radius of the sphere. If not provided, the default radius of the Earth (6370986 meters) is used.

The function returns the minimum distance between the two spatial objects on the spherical surface, measured in meters.

Examples

Example 1: Distance Between Points

This example demonstrates the calculation of the distance between two points on a spherical surface.

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

SELECT ST_DISTANCE_SPHERE(@point1, @point2);

Output:

6343.848453801355

The output shows the distance between the two points in meters, considering the Earth’s curvature.

Example 2: Distance Between a Point and a LineString

This example calculates the distance between a point and a linestring on a spherical surface.

SET @point = ST_GeomFromText('POINT(45 45)');
SET @line = ST_GeomFromText('LINESTRING(0 0, 90 0, 180 0)');

SELECT ST_DISTANCE_SPHERE(@point, @line);

Output:

5553.492486423244

The output represents the minimum distance between the point and the linestring, measured in meters.

Example 3: Distance Between Polygons

This example demonstrates the calculation of the distance between two polygons on a spherical surface.

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_SPHERE(@poly1, @poly2);

Output:

277.02015419443875

The output shows the minimum distance between the two polygons, considering the Earth’s curvature.

Example 4: Distance with a Custom Radius

This example calculates the distance between two points using a custom radius for the spherical surface.

SET @point1 = ST_GeomFromText('POINT(0 0)');
SET @point2 = ST_GeomFromText('POINT(45 45)');
SET @radius = 3963.1676; -- Miles

SELECT ST_DISTANCE_SPHERE(@point1, @point2, @radius);

Output:

3944.0905698037775

The output represents the distance between the two points in miles, using the provided custom radius.

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(45 45)')),
  (3, ST_GeomFromText('POINT(90 0)'));

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

Output:

2, 6343.848453801355
3, 6370986.0

The output shows the distance between each location and the location with id 1, measured in meters on a spherical surface.

Related Functions

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

• MariaDB ST_DISTANCE() function is used to calculate the Cartesian distance between two spatial objects.
• 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_SPHERE() function in MariaDB is an essential tool for accurately calculating distances between spatial objects on a spherical surface, such as the Earth. By considering the curvature of the Earth, this function provides more precise distance measurements compared to Cartesian distance calculations, making it invaluable for various geographic and spatial applications, including location-based services, transportation planning, and more.