公告
财富商城
积分规则
提问
发文
2019-10-09 13:57发布
三岁会撩人
mysql中如何将地理坐标拆成经度和纬度两份? 如下图所示,start_loc和end_loc是起止坐标,我的目的是求两坐标的距离。
已解决 分割经纬度: SELECT f1, start_loc, end_loc, SUBSTR(start_loc, 2, 17) AS 'start_weidu', SUBSTR(start_loc, -19, 18) AS 'start_jingdu', SUBSTR(end_loc, 2, 17) AS 'end_weidu', SUBSTRING(end_loc, -19, 18) AS 'end_jingdu' FROM processed_data; 距离计算 SELECT start_jingdu, start_weidu, end_jingdu, end_weidu, ROUND( 6378.138 * 2 * ASIN( SQRT( POW( SIN( ( start_weidu * PI() / 180 - end_weidu * PI() / 180 ) / 2 ), 2 ) + COS(start_weidu * PI() / 180) * COS(end_weidu * PI() / 180) * POW( SIN( ( start_jingdu * PI() / 180 - end_jingdu * PI() / 180 ) / 2 ), 2 ) ) ) * 1000 ) AS distance FROM jingweidu
最多设置5个标签!
已解决
分割经纬度:
SELECT f1, start_loc, end_loc,
SUBSTR(start_loc, 2, 17) AS 'start_weidu',
SUBSTR(start_loc, -19, 18) AS 'start_jingdu',
SUBSTR(end_loc, 2, 17) AS 'end_weidu',
SUBSTRING(end_loc, -19, 18) AS 'end_jingdu'
FROM processed_data;
距离计算
SELECT
start_jingdu, start_weidu, end_jingdu, end_weidu, ROUND(
6378.138 * 2 * ASIN(
SQRT(
POW(
SIN(
(
start_weidu * PI() / 180 - end_weidu * PI() / 180
) / 2
),
2
) + COS(start_weidu * PI() / 180) * COS(end_weidu * PI() / 180) * POW(
SIN(
(
start_jingdu * PI() / 180 - end_jingdu * PI() / 180
) / 2
),
2
)
)
) * 1000
) AS distance
FROM jingweidu