安装 earthdistance 模块

首先，我们需要在 PostgreSQL 中安装 earthdistance 模块，以便进行地理位置的计算。可以通过以下命令进行安装：

CREATE EXTENSION earthdistance;

-- CREATE EXTENSION IF NOT EXISTS cube;
-- CREATE EXTENSION IF NOT EXISTS earthdistance;

创建记录表

安装完成后，我们可以创建一个包含城市名称、经度和纬度信息的表，可以使用以下命令：

CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    location POINT
);

或者

CREATE TABLE cities (
  id SERIAL PRIMARY KEY,
  name TEXT,
  location POINT
);

添加测试数据

接下来，我们可以向表中插入一些数据，例如：

INSERT INTO cities (name, location) VALUES
    ('北京', POINT(116.4074, 39.9042)),
    ('上海', POINT(121.4737, 31.2304)),
    ('广州', POINT(113.2644, 23.1291)),
    ('深圳', POINT(114.0579, 22.5431)),
    ('成都', POINT(104.0665, 30.5723)),
    ('杭州', POINT(120.1551, 30.2741)),
    ('武汉', POINT(114.3054, 30.5931)),
    ('重庆', POINT(106.5507, 29.5647)),
    ('西安', POINT(108.9402, 34.3416)),
    ('南京', POINT(118.7969, 32.0603));

INSERT INTO cities (name, location) VALUES
  ('New York', '(40.7128, -74.0060)'),
  ('Los Angeles', '(34.0522, -118.2437)'),
  ('Chicago', '(41.8781, -87.6298)'),
  ('Houston', '(29.7604, -95.3698)'),
  ('Phoenix', '(33.4484, -112.0740)'),
  ('Philadelphia', '(39.9526, -75.1652)'),
  ('Dallas', '(32.7767, -96.7970)'),
  ('San Jose', '(37.3382, -121.8863)');

计算北京和上海之间的距离

接下来，我们可以计算任意两个城市之间的距离，并按照距离远近进行排序。例如，如果我们想要计算北京和上海之间的距离，可以使用以下命令：

SELECT earth_distance(ll_to_earth(39.9042, 116.4074), ll_to_earth(31.2304, 121.4737)) AS distance;

按照距离远近对城市进行排序

这将返回一个浮点数，表示两个城市之间的距离（单位为米）。如果我们想要按照距离远近对城市进行排序，可以使用以下命令：

SELECT name, earth_distance(location, POINT(116.4074, 39.9042)) AS distance
FROM cities
ORDER BY distance;

计算任意两个城市之间的距离，并按照距离远近对城市进行排序

可以计算任意两个城市之间的距离，并按照距离远近对城市进行排序

SELECT
  c1.name AS city1,
  c2.name AS city2,
  earth_distance(ll_to_earth(c1.location[1], c1.location[0]), ll_to_earth(c2.location[1], c2.location[0])) / 1000 AS distance_km
FROM
  cities c1
  CROSS JOIN cities c2
WHERE
  c1.id < c2.id
ORDER BY
  distance_km;

这将返回一个包含城市名称和距离的结果集，按照距离从近到远进行排序。

添加 GiST 索引

最后，我们可以为表中的 location 列添加 GiST 索引，以加速查询。可以使用以下命令：

CREATE INDEX idx_cities_location ON cities USING gist (location);

这将为 location 列创建一个 GiST 索引，从而加速距离计算和排序。

现在，我们可以使用索引来加速距离计算和排序操作：

SELECT
  c1.name AS city1,
  c2.name AS city2,
  earth_distance(ll_to_earth(c1.location[1], c1.location[0]), ll_to_earth(c2.location[1], c2.location[0])) / 1000 AS distance_km
FROM
  cities c1
  CROSS JOIN cities c2
WHERE
  c1.id < c2.id
ORDER BY
  c1.location <-> c2.location;

注意，这里我们使用了 <-> 运算符来计算两个点之间的距离，它利用了 GiST 索引的支持，可以更快地计算距离并排序。