Mysql 高德转天地图经纬度sql ,高德地图GCJ-02火星坐标系与GPS的WGS-84坐标系转换

-- mysql高德转天地图
SELECT 
 -- 经度
xlongitude * 2 -(xlongitude +
 (((300.0 + (xlongitude - 105.0) + 2.0 * (ylatitude - 35.0) + 0.1 * (xlongitude - 105.0) * (xlongitude - 105.0) + 
 0.1 * (xlongitude - 105.0) * (ylatitude - 35.0) + 0.1 * sqrt(abs((xlongitude - 105.0))) + 
 (20.0 * sin(6.0 * (xlongitude - 105.0) * 3.1415926535897932384626) + 20.0 * sin(2.0 * (xlongitude - 105.0) * 3.1415926535897932384626)) * 2.0 / 3.0 + 
 (20.0 * sin((xlongitude - 105.0) * 3.1415926535897932384626) + 40.0 * sin((xlongitude - 105.0) / 3.0 * 3.1415926535897932384626)) * 2.0 / 3.0 + 
 (150.0 * sin((xlongitude - 105.0) / 12.0 * 3.1415926535897932384626) + 300.0 * sin((xlongitude - 105.0) / 30.0 * 3.1415926535897932384626)) * 2.0 / 3.0) * 180.0) / (6378245.0 / (SQRT(1 - 0.00669342162296594323 * sin((ylatitude) / 180.0 * 3.1415926535897932384626) * sin((ylatitude) /180.0 * 3.1415926535897932384626))) * COS((ylatitude) / 180.0 * 3.1415926535897932384626) * 3.1415926535897932384626))) AS lon,
-- 纬度
 ylatitude * 2 - (ylatitude +
((-100.0 + 2.0 * (xlongitude - 105.0) + 3.0 * (ylatitude - 35.0) + 0.2 * (ylatitude - 35.0) * (ylatitude - 35.0) + 
0.1 * (xlongitude - 105.0) * (ylatitude - 35.0) + 0.2 * sqrt(abs(xlongitude - 105.0)) +
(20.0 * sin(6.0 * (xlongitude - 105.0) * 3.1415926535897932384626) + 20.0 * sin(2.0 * (xlongitude - 105.0) * 3.1415926535897932384626)) * 2.0 /3.0 + 
(20.0 * sin((ylatitude - 35.0) * 3.1415926535897932384626) + 40.0 * sin((ylatitude - 35.0) / 3.0 * 3.1415926535897932384626)) * 2.0 / 3.0 + 
(160.0 * sin((ylatitude - 35.0) / 12.0 * 3.1415926535897932384626) + 320 * sin((ylatitude - 35.0) * 3.1415926535897932384626 / 30.0)) * 2.0 / 3.0 ) * 180) / ((6378245.0 * (1 - 0.00669342162296594323)) / ((1 - 0.00669342162296594323 * sin((ylatitude) /180.0 * 3.1415926535897932384626) * sin((ylatitude) /180.0 * 3.1415926535897932384626)) * (SQRT(1 - 0.00669342162296594323 * sin((ylatitude) /180.0 * 3.1415926535897932384626) * sin((ylatitude) /180.0 * 3.1415926535897932384626)))) * 3.1415926535897932384626)) AS lat
 FROM `t_zuobiao` WHERE   mapType = '高德地图'

百度坐标转高德

-- 百度坐标转高德坐标
SELECT ROUND((SQRT((xlongitude - 0.0065) * (xlongitude - 0.0065) + (ylatitude - 0.006) * (ylatitude - 0.006)) + 0.00002 * SIN((ylatitude - 0.006) * 3.14159265358979324 * 3000.0 / 180.0) )
* COS(ATAN2((ylatitude - 0.006), (xlongitude - 0.0065)) + 0.000003 * COS((xlongitude - 0.0065) * 3.14159265358979324 * 3000.0 / 180.0)),6)  AS gaode_lon,
ROUND((SQRT((xlongitude - 0.0065) * (xlongitude - 0.0065) + (ylatitude - 0.006) * (ylatitude - 0.006)) + 0.00002 * SIN((ylatitude - 0.006) * 3.14159265358979324 * 3000.0 / 180.0) )
* SIN(ATAN2((ylatitude - 0.006), (xlongitude - 0.0065)) + 0.000003 * COS((xlongitude - 0.0065) * 3.14159265358979324 * 3000.0 / 180.0)),6)  AS gaode_lat
 FROM `t_zuobiao` WHERE   mapType = '百度地图'

创建测试表：

CREATE TABLE `t_zuobiao` (
  `xlongitude` varchar(50) DEFAULT NULL COMMENT '经度',
  `ylatitude` varchar(50) DEFAULT NULL COMMENT '纬度',
  `mapType` varchar(50) DEFAULT NULL COMMENT '坐标类型'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

添加测试数据：