在SQL中查询经纬度矩形的最有效方法是什么？

Question

我目前正在进行一组土地的查询，该土地在给定的纬度、经度矩形内。这些坐标存储为单个双精度值。我创建了一个包含两列的单一索引，因此当前查询15240个网格需要在我的本地机器上花费0.10秒。

目前，表中有2300万行数据，但在表完成后，预计将有约8亿行数据，因此预计查询时间会变得更慢。

以下是我正在运行的查询，附带示例值：

SELECT * FROM territories
WHERE nwlat < 47.606977 and nwlat > 47.506977
and   nwlng < -122.232991 and nwlng > -122.338991;

有更高效的方法吗？我对大型数据库还比较新，所以非常需要帮助。顺便提一下，我正在使用PostgreSQL。

- DonutGaz

---

你尝试过使用两个不同的索引（一个用于纬度，一个用于经度）吗？ - Mark Adelsberger

是的，那实际上是我最初索引的内容。当我切换到经纬度索引时，时间减半了。 - DonutGaz

一个单一的聚集索引应该比多个非聚集索引更快。 - Edward

Answer 1

如果使用GiST或SP-GiST索引和“box-contains-points”查询，效率会更高...

GiST索引

该索引是在一个面积为零的框上建立的，该框由相同的点(point(nwlat, nwlng))两次构建而成。

在CREATE INDEX手册中有相关的代码示例。

CREATE INDEX territories_box_gist_idx ON territories
USING gist (box(point(nwlat, nwlng), point(nwlat, nwlng)));

使用"重叠"操作符&&进行查询：

SELECT *
FROM   territories
WHERE  box(point(nwlat, nwlng), point(nwlat, nwlng))
    && '(47.606977, -122.232991), (47.506977, -122.338991)'::box;

SP-GiST索引

仅针对点的较小索引：

CREATE INDEX territories_box_spgist_idx ON territories
USING spgist (point(nwlat, nwlng));

使用包含运算符@>的查询：

SELECT *
FROM   point
WHERE  '(47.606977, -122.232991), (47.506977, -122.338991)'::box
    @> point(nwlat, nwlng);

在Postgres 9.6.1上，我通过对100万行的简单测试获得了SP-GiST索引最快的结果。

对于更复杂的需求，请考虑使用PostGIS扩展。

- Erwin Brandstetter

---

哇，我使用了SP-GiST索引，查询执行时间减半了。这非常有帮助，非常感谢！ - DonutGaz