有多少个“实体”？都在这个层次结构之下。嵌套集/邻接（英格兰境内有多少个 POI）

Question

开发语言和数据库：PHP/MySQL

我有一个表geo_places，其中包含大约 800 万个地理位置。

这些地方都是分层的，我使用

• parent_id（邻接）、
• lft/rgt（嵌套集）
• 和ancestry（枚举）。

现在，我有一个名为 entities 的“兴趣点”表，它被分配给一个地理位置，并且我针对每个实体记录：

• lft 中位置的值code>geo_places
• 和地理位置的实际 ID。

现在我需要一种方法来提供一个目录列表，其中包含某个位置下所有位置的有效计数（但无论如何我都会缓存它）。

例如，如果我选择欧洲，那么我应该看到所有具有欧洲 Parent_id 的地方，以及其下方的实体数量。请记住，一个地方不会直接分配给欧洲，但可能会分配给意大利的一个小村庄（这是欧洲的孩子）。

您知道它是欧洲的孩子，因为：

• 意大利小村庄的 lft 值介于 lft 和 rgt 值之间位置
• 或者因为祖先映射到该地方。

例如，欧洲的祖先路径为 /1，ID 为 5。（1 表示“世界”）。然后意大利的小村庄会有 /1/5/234/28924/124128

其中 1 = 世界 5 = 欧洲 234 = 意大利 28924 = 贝加莫等等...

无论如何，这就是我的方式已经构建了数据集，并且我已经使用了分层结构的混合，以便使我的查询更加高效（对于那些想知道为什么我支持嵌套集、邻接集的人来说）并枚举..这是因为我通过这种方式获得了所有结构中最好的）。

这是我正在尝试做的一个例子。

我很灵活，可以根据需要更改管理位置的方式。但是，这也是一个多租户应用程序，因此我想尝试避免保存针对 geo_places 的计数（如果可以避免的话）。

简单地说： 选择一个位置...然后显示所有已分配给该位置或该位置的子位置的兴趣点的位置。

有什么建议吗？

Answer 1

这是一种解决方案：

select p.woeid, p.name, e.id, e.woeid, e.lft, count(e.lft) from
geo_places as p
 join  entities as e on e.lft >= p.lft and e.lft < p.rgt
where p.parent_woeid = 1
group by p.woeid

您可以将 1 替换为您想要查找其后代的位置。

使用 entities 中的 10 万个实体和 geo_places 中的 800 万行进行了测试。
关于 lft 和 rgt 以及 woeid 的索引。