公司网站开发费能记研发费用哪个科目,手机系统优化,网上购物app,wordpress批量修改文章标题PostGIS学习教程十#xff1a;空间索引
回想一下#xff0c;空间索引是空间数据库的三个关键特性之一。空间索引使得使用空间数据库存储大型数据集成为可能。在没有空间索引的情况下#xff0c;对要素的任何搜索都需要对数据库中的每条记录进行顺序扫描。索引通…PostGIS学习教程十空间索引
回想一下空间索引是空间数据库的三个关键特性之一。空间索引使得使用空间数据库存储大型数据集成为可能。在没有空间索引的情况下对要素的任何搜索都需要对数据库中的每条记录进行顺序扫描。索引通过将数据组织到搜索树中来加快搜索速度搜索树可以快速遍历以查找特定记录。
空间索引是PostGIS的最大价值之一。在前面的示例中构建空间连接需要对整个表进行相互比较。这样做的代价很高连接两个各包含10000条记录的表每个表都没有索引将需要进行100000000次比较如果使用空间索引则比较次数可能低至20000次。 加载nyc_census_blocks表时pgShapeLoader会自动创建名为nyc_census_blocks_geom_idx的空间索引。
为了演示空间索引对性能有多重要让我们在没有空间索引的情况下搜索nyc_census_blocks表。
我们的第一步是删除索引
DROP INDEX nyc_census_blocks_geom_idx;注意DROP INDEX语句从数据库系统中删除现有索引。有关更多信息请参见PostgreSQL文档。
现在查看pgAdmin查询窗口右下角的计时表并运行以下命令。我们的查询将搜索每个单独的人口普查块census block以查找宽街Broad Street那个记录。
SELECT blocks.blkid
FROM nyc_census_blocks blocks
JOIN nyc_subway_stations subways
ON ST_Contains(blocks.geom, subways.geom)
WHERE subways.name Broad St;nyc_census_blocks表非常小只有几千条记录因此即时没有索引查询也非常快。
现在重新添加空间索引并再次进行查询
CREATE INDEX nyc_census_blocks_geom_idx
ON nyc_census_blocks
USING GIST (geom);注意USING GIST子句告诉PostgreSQL在构建索引时使用generic index structureGIST-通用索引结构。创建索引时如果收到类似错误ERROR:index row requires 11340 bytesmaximum size is 8911则可能是因为没有添加USING GIST子句。 在我的测试计算机上时间下降到11毫秒。表越大索引查询的相对速度提高就越大。 文章目录 PostGIS学习教程十空间索引一、空间索引是怎样工作的二、纯索引查询 三、分析ANALYZE四、清理VACUUM五、相关函数 一、空间索引是怎样工作的
标准数据库索引基于某个列的值创建层次结构树。空间索引略有不同-它们不能索引几何要素本身而是索引几何要素的边界框。 在上图中与黄星相交的线串数是一条即红线。但是与黄色框相交的要素的边界框是两个红框和蓝框。
空间数据库回答哪些直线与黄星相交这一问题使用的方法是首先使用空间索引速度非常快判断哪些框与黄色框相交然后仅对第一次返回的几何要素进行哪些直线与黄星相交的精确计算。
对于一个大的数据表来说这种先计算出近似结果然后进行精确测试的两遍机制可以从根本上减少计算量。这种思想就是粗调和精调的思想就像显微镜一样有粗粒度的调整和细粒度的调整。很多事物都涉及到这个思想它的作用就是减少了耗费的代价
PostGIS和Oracle Spatial都具有相同的R-Tree空间索引结构。R-Tree将数据分解为矩形rectangle、子矩形sub-rectangle和子-子矩形sub-sub rectangle等。它是一种可自动处理可变数据的密度和对象大小的自调优self-tuning索引结构。
二、纯索引查询
PostGIS中最常用的函数ST_Contains、ST_Intersects、ST_DWithin等都包含自动索引过滤器。但有些函数如ST_Relate不包括索引过滤器。
要使用索引执行边界框搜索即纯索引查询-Index only Query-没有过滤器需要使用“运算符。对于几何图形运算符表示边界框重叠或接触”纯索引查询就像对于数字“运算符表示值相同”。
让我们将对West Village社区人口的纯空间索引查询与更精确的查询进行比较。使用操作符的纯索引查询如下所示
SELECT Sum(popn_total)
FROM nyc_neighborhoods neighborhoods
JOIN nyc_census_blocks blocks
ON neighborhoods.geom blocks.geom
WHERE neighborhoods.name West Village;现在让我们使用更精确的ST_Intersects函数执行相同的查询
SELECT Sum(popn_total)
FROM nyc_neighborhoods neighborhoods
JOIN nyc_census_blocks blocks
ON ST_Intersects(neighborhoods.geom, blocks.geom)
WHERE neighborhoods.name West Village;结果数量低得多第一个查询汇总与社区neighborhood关于边界框相交的每个人口统计块census block第二个查询仅汇总了与该社区几何图形本身严格相交的人口统计块。
三、分析ANALYZE
PostgreSQL查询规划器query planner智能地选择何时使用或不使用空间索引来计算查询。与直觉相反执行空间索引搜索并不总是更快如果搜索将返回表中的每条记录则遍历索引树以获取每条记录实际上比从一开始线性读取整个表要慢注意这句话。
为了弄清楚要处理的数据的大概内容读取表的一小部分信息而不是读取表的大部分信息PostgreSQL保存每个索引列中数据分布的统计信息。默认情况下PostgreSQL定期收集统计信息。但是如果你在短时间内更改了表的构成则统计数据将不会是最新的。
为确保统计信息与表内容匹配明智的做法是在表中加载和删除大容量数据后手动运行ANALYZE命令。这将强制统计系统收集所有索引列的统计信息。
ANALYZE命令要求PostgreSQL遍历该表并更新用于查询操作而估算的内部统计信息。
ANALYZE nyc_census_blocks;四、清理VACUUM
值得强调的是仅仅创建空间索引不足以让PostgreSQL有效地使用它。每当创建新索引或对表大量更新、插入或删除后都必须执行清理VACUUMing。VACUUM命令要求PostgreSQL回收表页面中因记录的更新或删除而留下的任何未使用的空间。
清理对于数据库的高效运行非常关键因此PostgreSQL提供了一个“自动清理autovacuum选项。
默认情况下自动清理机制会根据活动级别确定的合理时间间隔自动清理恢复空间和分析更新统计信息。虽然这对于高度事务性的数据库是必不可少的功能但在添加索引或大容量数据之后等待自动清理运行是不明智的如果执行大批量更新则应该手动运行VACUUM命令。
根据需要可以单独执行清理和分析。发出VACUUM命令不会更新数据库统计信息同样执行ANALYZE命令也不会清理未使用的表空间。这两个命令都可以针对整个数据库、单个表或单个列运行。
VACUUM ANALYZE nyc_census_blocks;五、相关函数
