我通过遵循SQLite R*Tree tutorial实现了一个基于边界框的简单空间索引模式,它给了我以下模式:
CREATE VIRTUAL TABLE events_region USING rtree(
id, -- primary key
xmin, xmax, -- minimum and maximum X coordinate
ymin, ymax, -- minimum and maximum Y coordinate
);
字符串
我现在想将这个模式移植到Postgres(但不使用PostGIS,以减少依赖性)。我知道使用GIST索引应该可以做到这一点(因为R树已经被淘汰,现在GIST正在填补它们的角色),但我正在努力解决如何实现这一点。
1条答案
按热度按时间r7xajy2e1#
下面是一个使用点而不是框的示例:
字符串
这将创建一条由10 m个点组成的线,并查询左下角(x= 1 m,y= 1 m)和右上角(x= 9 m,y= 2 m)框内的点计数:
型
索引创建在我的SSD上花了100秒,这有点可悲。但是好吧。查询是0.2,但是这是通过迭代一堆数据,在空区域上使用
limit 100
的查询是0.035s,这很好,它肯定是被加速的。point
是一个自身的类型,如下所示:https://www.postgresql.org/docs/current/datatype-geometric.html。我不知道如何在预先存在的整数列上创建空间索引,这是一个耻辱。奇怪的运算符
<@
表示“包含在”。https://www.postgresql.org/docs/15/functions-geometry.html记录了其他一些奇怪的几何运算符,https://www.postgresql.org/docs/15/gist-builtin-opclasses.html列出了哪些几何类型支持哪些几何类型。为了更紧密地匹配SQLite模式,您可能希望使用
box
类型而不是point
类型来设置数据库,然后确定正确的怪异运算符选择,可能是<@
或&&
用于交集。这是一个很好的来源,关于这个晦涩的PostgreSQL功能:https://medium.com/postgres-professional/indexes-in-postgresql-5-gist-86e19781b5db的例子是非常罕见的!
在Ubuntu 23.04、PostgreSQL 15.3、Lenovo ThinkPad P51、SSD上测试:三星MZVLB 512 HAJQ-000 L7 512 GB固态硬盘,3 GB/s标称速度。