MariaDB 中不存在 FUNCTION ST_Distance_Sphere [英] FUNCTION ST_Distance_Sphere does not exist in MariaDB

问题描述

我想获取我所在位置周围的所有位置，但函数 ST_Distance_Sphere 不起作用.

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

我的查询:

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

错误:

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)

推荐答案

MariaDB中还需要函数的，可以根据公式创建函数

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

这篇关于MariaDB 中不存在 FUNCTION ST_Distance_Sphere的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！