按距離排序

如果我有查詢返回附近的咖啡館：

SELECT * FROM cafes c WHERE (
  ST_DWithin(
  ST_GeographyFromText(
    'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')'
  ),
  ST_GeographyFromText('SRID=4326;POINT(-76.000000 39.000000)'),
    2000
  )
)

如何選擇距離，以及按距離排序？

有沒有比這個更有效的方法：

SELECT id, 
ST_Distance(ST_GeographyFromText('SRID=4326;POINT(-76.000000 39.000000)'),
            ST_GeographyFromText(
            'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')')      
            ) as distance 
FROM cafes c
  WHERE (
  ST_DWithin(
    ST_GeographyFromText(
    'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')'
  ),
   ST_GeographyFromText('SRID=4326;POINT(-76.000000 39.000000)'),
  2000
)
) order by distance

一、使用

ST_SetSRID(ST_MakePoint(c.longitude, c.latitude),4326)::geography

代替

ST_GeographyFromText('SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')')

手冊：

ST_MakePoint雖然不符合 OGC 標準，但通常比ST_GeomFromText和更快、更精確ST_PointFromText。如果你有原始座標而不是 WKT，它也更容易使用。

接下來，要使查詢更短並且只輸入一次搜尋參數（對性能沒有太大影響），請使用子查詢（或 CTE）：

SELECT id
    , ST_Distance(**t.x**
                , ST_SetSRID(ST_MakePoint(c.longitude, c.latitude),4326)::geography) AS dist
FROM   cafes c
   **, (SELECT ST_GeographyFromText('SRID=4326;POINT(-76.000000 39.000000)')) AS t(x)**
WHERE  ST_DWithin(**t.x**
               , ST_SetSRID(ST_MakePoint(c.longitude, c.latitude),4326)::geography, 2000)
ORDER  BY dist;

最後，您需要一個GiST 索引來使大表的速度更快。手冊ST_DWithin()：

此函式呼叫將自動包含一個邊界框比較，該比較將利用幾何上可用的任何索引。

您可以在答案開頭使用表達式上的功能索引來使用它。但我會儲存一個geography類型列以開始（讓我們命名它thegeog）並創建一個普通的 GiST 索引，如：

CREATE INDEX cafes_thegeog_gist ON cafes USING gist(thegeog);

得到這個更簡單、更快的查詢：

SELECT id, ST_Distance(t.x, thegeog) AS distance 
FROM   cafes c
   , (SELECT ST_GeographyFromText('SRID=4326;POINT(-76.000000 39.000000)')) AS t(x)
WHERE  ST_DWithin(t.x, thegeog, 2000)
ORDER  BY distance;

正如@LR1234567 在評論中指出的那樣，更新為與 匹配geography。geography作為替代方案，您可以使用geometry. 此處使用的所有函式都適用於兩者（除了ST_MakePoint，因此附加了演員表）。有什麼不同？看：

• 為什麼我在 Postgres 13 中的空間查詢比在 Postgres 11 中慢？

如果您想獲取半徑範圍內的n 個最近的咖啡館，請考慮“最近鄰居”搜尋。往往更方便。

引用自：https://dba.stackexchange.com/questions/60619