CODEWITHSUNDEEP
mysqlgeometrymariadbgeospatial
adam78
adam78

Reputation: 10068

MariaDB no equivalent function for ST_Distance_Sphere

In MySQL 5.7 there is a function called ST_Distance_Sphere which can be used to find points within a radius e.g.:

CREATE TABLE `places` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL,
  `coordinates` point DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `places` ( `name`, `coordinates`)
VALUES ("Eiffel Tower", POINT(48.858271, 2.293795));

INSERT INTO `places` ( `name`, `coordinates`)
VALUES ("Pere Lachaise", POINT(48.861131, 2.394683));

INSERT INTO `places` ( `name`, `coordinates`)
VALUES ("Brooklyn", POINT(40.711089, -73.948391));

If we want to see all points that are less than 10km from the Louvre which is in the center of Paris, we can do:

SELECT name FROM places
WHERE ST_Distance_Sphere(coordinates, POINT(48.861105, 2.335337)) < 10000

However no such function is supported in MariaDB 10.1+

How can I recreate the same function or recreate the same query in MariaDB. The following post shows one method of recreating the same function FUNCTION ST_Distance_Sphere does not exist in MariaDB?

Maybe something like this?

SELECT name FROM places
WHERE ST_Within(
   coordinates, ST_Buffer(POINT(48.861105, 2.335337), 10000)
);

Any help appreciated.

Upvotes: 1

Views: 1892

Answers (3)

KOFFI HARRIS KOUASSI
KOFFI HARRIS KOUASSI

Reputation: 336

I just had the same problem. Use ST_DISTANCE link : https://mariadb.com/kb/en/st_distance/ Simple example:

SELECT ST_DISTANCE(POINT(x y),POINT(x1 y1));

Returns the unit of your coordinates.

Upvotes: 0

Jacopo Pace
Jacopo Pace

Reputation: 492

Based on the suggestions of @adam78 and @Rick James I solved this way:

CREATE FUNCTION st_distance_sphere(pt1 POINT, pt2 POINT)
RETURNS double(10,2)

RETURN 6371000 * 2 * ASIN(
    SQRT(
        POWER(SIN((ST_Y(pt2) - ST_Y(pt1)) * pi()/180 / 2), 2) +
        COS(ST_Y(pt1) * pi()/180 ) *
        COS(ST_Y(pt2) * pi()/180) *
        POWER(SIN((ST_X(pt2) - ST_X(pt1)) * pi()/180 / 2), 2)
    )
)

Upvotes: 2

Rick James
Rick James

Reputation: 142258

I provide a workaround in http://mysql.rjweb.org/doc.php/find_nearest_in_mysql -- However, it works with a pair of numeric columns of latitude and longitude instead of POINTs. (Those can be extracted from a POINT().)

The main purpose of that link is your 'next' question -- how to speed up the filtering by distance. It discusses 5 approaches. (Your code is still at the least performant one.)

Upvotes: 0

Related Questions