postgresql - 为复杂的地理查询选择数据结构/存储解决方案

Question

我有一个实体数据集及其类型和纬度/经度。像这样:

Name    Type Lat Long
House1  Big   1   2
House11  Bigger   2   2
House12  Biggest   3   2
House13  Small   4   2
House14  Medium 5   2

这些是带有类型和位置的房屋。现在我需要回答类似这样的问题:“找到在其 10 公里半径内有小型和中型房屋的所有大型房屋”

什么样的数据结构/存储解决方案就在这里？我看了Elasticsearch和 Redis但看起来我需要遍历给定类型的所有房屋(对于上面的示例查询来说很大)才能回答这个问题。

Answer 1

使用 PostGIS 直接从 PostgreSQL 完全可行。

考虑你的表结构......

CREATE TEMPORARY TABLE t (name TEXT, type TEXT, geom GEOGRAPHY);

...和您的测试数据...

INSERT INTO t VALUES ('House1','Big', ST_MakePoint(1,2));
INSERT INTO t VALUES ('House11','Bigger', ST_MakePoint(2,2));
INSERT INTO t VALUES ('House12','Biggest', ST_MakePoint(3,2));
INSERT INTO t VALUES ('House13','Small', ST_MakePoint(4,2));
INSERT INTO t VALUES ('House14','Medium', ST_MakePoint(5,2));

(注意:此处将纬度、经度拆分为不同的列没有意义。PostGIS 可以将两者存储在单个 GEOGRAPHY 或 GEOMETRY 列中。有关详细信息，请参阅 ST_MakePoint。)

"Find all house of type Big which have a Small and a Medium house in its 10km radius"

使用 ST_Distance 尝试这样的事情:

WITH j AS (SELECT * FROM t WHERE type = 'Big')
SELECT 
  j.name,j.type, 
  ST_Distance(j.geom,t.geom) AS distance, 
  t.name, t.type
FROM j,t 
WHERE 
  ST_Distance(j.geom,t.geom) > 10000 AND
  t.type IN ('Small','Medium');


  name  | type |    distance     |  name   |  type  
--------+------+-----------------+---------+--------
 House1 | Big  |  333756.3481116 | House13 | Small
 House1 | Big  | 445008.41595616 | House14 | Medium
(2 Zeilen)

(此查询返回距离 Big 类型房屋超过 10k 米的记录。只需根据您的需要调整第一个 where 语句)

编辑:根据评论查询。

WITH j AS (SELECT *, ARRAY(SELECT DISTINCT t2.type 
               FROM t t2
                   WHERE t2.type IN ('Small','Medium') AND 
               ST_Distance(t2.geom,t1.geom) < 100000
                   ) AS nearHouseType 
       FROM t t1 WHERE type = 'Big')
SELECT *
FROM j
WHERE j.nearHouseType @> '{Medium, Small}'::TEXT[]