PostGreSQL（六）PostGIS-空间连接和空间索引

空间连接

空间连接（spatial joins）是空间数据库的主要组成部分，它们允许你使用空间关系作为连接键（join key）来连接来自不同数据表的信息，如：

SELECT
  subways.name AS subway_name,
  neighborhoods.name AS neighborhood_name,
  neighborhoods.boroname AS borough
FROM nyc_neighborhoods AS neighborhoods
JOIN nyc_subway_stations AS subways
ON ST_Contains(neighborhoods.geom, subways.geom)
WHERE subways.name = 'Broad St';

任何在两个表之间提供true/false关系的函数都可以用来驱动空间连接，但最常用的函数是：

ST_Intersects、ST_Contains和ST_DWithin

默认情况下，数据库使用的是INNER JOIN连接类型，还可以用 LEFT OUTER JOIN、RIGHT OUTER JOIN

空间索引

创建和使用索引

如下创建一个空间索引：

CREATE INDEX nyc_census_blocks_geom_idx
ON nyc_census_blocks
USING GIST (geom)

USING GIST子句告诉PostgreSQL在构建索引时使用generic index structure（GIST-通用索引结构）

PostGIS使用"R-Tree"空间索引结构。R-Tree将数据分解为矩形（rectangle）、子矩形（sub-rectangle）和子-子矩形（sub-sub rectangle）等。它是一种可自动处理可变数据的密度和对象大小的自调优（self-tuning）索引结构。

对于一个大的数据表来说，先计算出近似结果，然后进行精确测试的"两遍"机制可以从根本上减少计算量。（这种思想就是粗调和精调的思想，就像显微镜一样有粗粒度的调整和细粒度的调整。很多事物都涉及到这个思想，它的作用就是减少了耗费的代价）

使用索引：

• 纯索引查询：使用"&&"运算符。对于几何图形，&&运算符表示"边界框重叠或接触"（纯索引查询），就像对于数字，"="运算符表示"值相同"。

SELECT Sum(popn_total)
FROM nyc_neighborhoods neighborhoods
JOIN nyc_census_blocks blocks
ON neighborhoods.geom && blocks.geom
WHERE neighborhoods.name = 'West Village';

• PostGIS中最常用的函数（ST_Contains、ST_Intersects、ST_DWithin等）都包含自动索引过滤器

• 有些函数（如ST_Relate）不包括索引过滤器

分析（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;