我的数据库中有一个表，它存储了googleMap上的位置以及它们返回的坐标。以前，我将lat和lng存储为小数点，在我将mysql版本升级到8之后，发现有预定义的数据类型和函数来处理坐标和距离计算。我创建了如下表，并从googlemap中插入了一些随机值。

CREATE TABLE `Location` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lat` varchar(50) NOT NULL,
  `lng` varchar(50) NOT NULL,
  `coordinates` point NOT NULL,
  `name` varchar(500) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_lat_lng` (`lat`,`lng`),
  SPATIAL KEY `sk_coordinates` (`coordinates`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入的样本数据

INSERT INTO Location (name, lat, lng, coordinates)
SELECT 'Thayyeni Koomban', '12.33984592973732', '75.41285991668701', POINT(12.33984592973732, 75.41285991668701) UNION
SELECT 'Tejaswini river rafting', '12.29208143873455', '75.4130744934082', POINT(12.29208143873455, 75.4130744934082) UNION
SELECT 'Mossy Forest, Cameron Highlands', '4.5242225', '101.38192709999998', POINT(4.5242225, 101.38192709999998) UNION
SELECT 'Dhanushkodi Point', '9.152226599999999', '79.44291569999996', POINT(9.152226599999999, 79.44291569999996) UNION
SELECT 'KL Sentral', '3.13333', '101.68667000000005', POINT(3.13333, 101.68667000000005) UNION
SELECT 'Kozhippara Waterfalls', '11.3537295', '76.10803290000001', POINT(11.3537295, 76.10803290000001) UNION
SELECT 'Laguna Honda Hospital and Rehabilitation Center', '37.7492806', '-122.45702240000003', POINT(37.7492806, -122.45702240000003) UNION
SELECT 'Singapore Zoo', '1.4043485', '103.79302299999995', POINT(1.4043485, 103.79302299999995) UNION
SELECT 'Taj Mahal', '27.1750151', '78.04215520000002', POINT(27.1750151, 78.04215520000002) UNION
SELECT 'Sea View Point', '11.2643567', '75.76153939999995', POINT(11.2643567, 75.76153939999995) UNION
SELECT 'Club Mahindra Ashtamudi', '8.965749299999997', '76.57136119999996', POINT(8.965749299999997, 76.57136119999996) UNION
SELECT 'Kollam Beach', '8.8756778', '76.58891629999994', POINT(8.8756778, 76.58891629999994) UNION
SELECT 'Tropical Islands', '52.03892399999999', '13.748616999999967', POINT(52.03892399999999, 13.748616999999967) UNION
SELECT 'Taroko National Park', '24.15870679999999', '121.62162969999997', POINT(24.15870679999999, 121.62162969999997) UNION
SELECT 'Lake Tyrrell', '-35.2553505', '142.8419824', POINT(-35.2553505, 142.8419824) UNION
SELECT 'Karthika Regency', '10.0192948', '76.30539509999994', POINT(10.0192948, 76.30539509999994) UNION
SELECT 'Sentosa', '1.2494041', '103.83032090000006', POINT(1.2494041, 103.83032090000006) UNION
SELECT 'Kovalam Beach', '12.7902597', '80.25390390000007', POINT(12.7902597, 80.25390390000007) UNION
SELECT 'Torres del Paine National Park', '-50.9423262', '-73.40678789999998', POINT(-50.9423262, -73.40678789999998) UNION
SELECT 'Niagara Falls', '43.0828162', '-79.07416289999998', POINT(43.0828162, -79.07416289999998);

了解到google coordinates使用的srid为3857，因此使用lat和lng的不同组合创建了以下查询来生成点。但所有的距离似乎都不准确。

SELECT 
    name, 
    lat, 
    lng, 
    ST_Distance(ST_SRID(coordinates, 3857), ST_GeomFromText('POINT(10.0120262 76.3586236)', 3857)) AS distance,
    ST_Distance(ST_SRID(POINT(lat, lng), 3857), ST_GeomFromText('POINT(10.0120262 76.3586236)', 3857)) AS lat_lng,
    ST_Distance(ST_SRID(POINT(lng, lat), 3857), ST_GeomFromText('POINT(10.0120262 76.3586236)', 3857)) AS lng_lat_first,
    ST_Distance(ST_SRID(POINT(lng, lat), 3857), ST_GeomFromText('POINT(76.3586236 10.0120262)', 3857)) AS lng_lat_all,
    ST_Distance(ST_SRID(POINT(lng, lat), 3857), ST_SRID(POINT(76.3586236, 10.0120262), 3857)) AS lng_lat_all_x,
    ST_Distance(ST_SRID(POINT(lng, lat), 3857), ST_SRID(POINT(10.0120262, 76.3586236), 3857)) AS lng_lat_all_y, 
    ST_Distance(ST_SRID(POINT(lat, lng), 3857), ST_SRID(POINT(10.0120262, 76.3586236), 3857)) AS lng_lat_all_z   
from test.Location;

我这样做是正确的还是遗漏了什么？提前谢谢。