使用SQL查找彼此最接近的坐标对

CREATE TABLE coordinates (
    id INT PRIMARY KEY,
    latitude DECIMAL(9,6),
    longitude DECIMAL(9,6)
);

INSERT INTO coordinates (id, latitude, longitude)
VALUES
    (1, 39.9042, 116.4074), -- 北京
    (2, 31.2304, 121.4737), -- 上海
    (3, 23.1291, 113.2644), -- 广州
    (4, 22.5431, 114.0579); -- 深圳

SELECT c1.id AS coordinate1_id, c2.id AS coordinate2_id,
       c1.latitude AS coordinate1_latitude, c1.longitude AS coordinate1_longitude,
       c2.latitude AS coordinate2_latitude, c2.longitude AS coordinate2_longitude,
       ST_DISTANCE_SPHERE(point(c1.longitude, c1.latitude), point(c2.longitude, c2.latitude)) AS distance
FROM coordinates c1
JOIN coordinates c2 ON c1.id <> c2.id
ORDER BY distance ASC
LIMIT 1;