FUNCTION ST_Distance_Sphere does not exist in MariaDB

Question

I want get all locations around my location but the function ST_Distance_Sphere does not work.

My query:

select *, astext(location) as location from `locations`
where ST_Distance_Sphere(location, POINT(35.905069591297, 49.765869174153)) < 1000

Error :

SQLSTATE[42000]: Syntax error or access violation:
1305 FUNCTION app.ST_Distance_Sphere does not exist (SQL:
select *, astext(location) as location from `locations`
where ST_Distance_Sphere(location, POINT(35.905069591297, 49.765869174153)) < 1000)

The formula with lots of trig calls works MySQL or MariaDB. MySQL only recently (5.7.6) implemented that ST function; MariaDB does not seem to have picked it up yet. — Rick James, Jun 08 '17 at 05:43
i see this link but ST_Distance_Sphere function is not Similar ! [MariaDb Docs](https://mariadb.com/kb/en/mariadb/mysqlmariadb-spatial-support-matrix/) — webafra, Jun 08 '17 at 05:49
The formulas need latitudes and longitudes. But that is what your `POINT` is composed of, correct? I am suggesting a somewhat messy workaround, not a simple drop-in replacement. — Rick James, Jun 08 '17 at 19:13
I angry From MariaDB :| i change DataBase to PostgreSQL ! :| — webafra, Jun 09 '17 at 06:12

score 15 · Answer 1 · answered Jun 19 '19 at 13:38

15

For those who still need the function in MariaDB, you can create the function based on the formula

    CREATE FUNCTION `st_distance_sphere`(`pt1` POINT, `pt2` POINT) RETURNS 
    decimal(10,2)
    BEGIN
    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) ));
    END

answered Jun 19 '19 at 13:38

Carlos Poma

199
2
7

I had to delete "BEGIN". Also, I guess a spatial index does not work with this function. Correct? – moritz.vieli Aug 13 '20 at 06:14
@moritz.vieli .True, BEGIN is not required for single line routine. As long as indexes are used on base tables, the functions can be used in Views, Stored Procedures, or Triggers. Or what do you mean? – Carlos Poma Aug 14 '20 at 05:06
Let's assume, I have a SELECT statement where I sort by st_distance_sphere. Does MariaDB use the spatial index I added on a column, which is used as a parameter for the function? – moritz.vieli Aug 14 '20 at 08:53
well, obviously not with this self-implemented function, rendering the spatial index in mariadb completely useless. – user3700562 Jul 25 '21 at 11:12

Evan Carroll · Answer 2 · 2021-09-19T22:58:55.607

9

10.2+

This issue has been fixed and backported with MDEV-13467. It's available 10.2.38, 10.3.29, 10.4.19, 10.5.10

Find their support matrix here.

edited Sep 19 '21 at 22:58

answered Jan 21 '19 at 08:26

Evan Carroll

78,363
46
261
468

Update - ST_DISTANCE_SPHERE is finally available in MariaDB after 10.2! Thank you for posting the link to the issue, where the issue is now "closed" – flexponsive Sep 19 '21 at 22:56
@flexponsive updated, thanks – Evan Carroll Sep 19 '21 at 22:59
1

I'm using 10.3.22-MariaDB on a Debian 10 server and still got "st_distance_sphere does not exist" – cnmuc Apr 16 '22 at 13:35

score 1 · Answer 3 · answered Apr 24 '20 at 04:21

1

http://mysql.rjweb.org/doc.php/find_nearest_in_mysql#gcdistdeg

That blog discusses multiple ways of "finding nearest" on the globe in MySQL/MariaDB. As part of that discussion, I developed that Stored Function.

answered Apr 24 '20 at 04:21

Rick James

135,179
13
127
222

score -3 · Answer 4 · answered Feb 07 '19 at 18:56

-3

I think that's it ST_DISTANCE https://mariadb.com/kb/en/library/st_distance/

answered Feb 07 '19 at 18:56

Максим Нюхин

1

7

That's for flat geometry, not for sphere one. – Hex Aug 12 '19 at 08:10

FUNCTION ST_Distance_Sphere does not exist in MariaDB

4 Answers4

10.2+

Linked