PostGIS数据库中的几种复杂查询举例

本文包含以下内容：

1. 利用索引
2. 空间查询SQL示例

---

有效地使用PostGIS需要知道哪些空间功能可用，并确保适当的索引可用以提供良好的性能。这些示例中使用的SRID 312仅用于演示。您应该使用spatial_ref_sys表中列出的REAL SRID，并使用与数据投影相匹配的REAL SRID。如果数据未指定空间参考系统，则应仔细考虑为什么没有这样做，也许应该这样做。

如果是因为要建模的对象没有定义地理空间参考系统，例如分子的内部结构或尚未建造的游乐园的平面图，那很好。但是，如果已经计划了游乐园的位置，那么，只要确保游乐部分不会侵入已经存在的结构，就应该对该位置使用合适的平面坐标系。

即使计划在发生核大*的情况下进行火星探险以运送人类，并且您想要绘制火星行星以进行人类居住的情况，也可以使用非地球坐标系，例如火星2000并将其插入spatial_ref_sys表中。尽管此火星坐标系是非平面的（以球面度为单位），但您可以将其与地理类型一起使用，以长度和接近度单位以米（而非度）为单位。

1. 利用索引

构造查询时，重要的是要记住，只有基于边界框的运算符（如&&）才能利用GiST空间索引。 诸如ST_Distance（）之类的函数无法使用索引来优化其操作。 例如，在大型表上，以下查询将非常慢：

SELECT the_geom
FROM geom_table
WHERE ST_Distance(the_geom, 'SRID=312;POINT(100000 200000)') < 100

该查询正在选择geom_table中所有在点（100000，200000）的100个单位内的几何。 这会很慢，因为它正在计算表中每个点与我们指定点（即）之间的距离。 表中的每一行都进行一次ST_Distance（）计算。 我们可以通过使用单步索引加速函数ST_DWithin减少所需的距离计算次数来避免这种情况：

SELECT the_geom
FROM geom_table
WHERE ST_DWithin(the_geom, 'SRID=312;POINT(100000 200000)', 100)

该查询选择相同的几何形状，但是它以更有效的方式执行。 假设the_geom上有一个GiST索引，查询计划者将认识到可以在计算ST_Distance（）函数的结果之前使用该索引减少行数。 注意，在&&操作中使用的ST_MakeEnvelope几何是一个以原始点为中心的200单位的方形框-这是我们的“查询框”。 &&运算符使用索引将结果集快速缩减为仅具有边界框与“查询框”重叠的那些几何。 假设我们的查询框比整个几何表的范围小得多，这将大大减少需要完成的距离计算的数量。

2. 空间查询SQL示例

本节中的示例将使用两个表，一个线性道路表和一个多边形市政边界表。 bc_roads表的表定义为：

Column      | Type              | Description
------------+-------------------+-------------------
gid         | integer           | Unique ID
name        | character varying | Road Name
the_geom    | geometry          | Location Geometry (Linestring)

边界数据bc_municipality表的表定义为：

Column     | Type              | Description
-----------+-------------------+-------------------
gid        | integer           | Unique ID
code       | integer           | Unique ID
name       | character varying | City / Town Name
the_geom   | geometry          | Location Geometry (Polygon)

所有道路的总长度是多少（以公里为单位）？

SELECT sum(ST_Length(the_geom))/1000 AS km_roads FROM bc_roads;

km_roads
------------------
70842.1243039643
(1 row)

乔治王子城有多少公顷？

SELECT
  ST_Area(the_geom)/10000 AS hectares
FROM bc_municipality
WHERE name = 'PRINCE GEORGE';

hectares
------------------
32657.9103824927
(1 row)

按地区划分，全省最大的直辖市是哪个？

SELECT
  name,
  ST_Area(the_geom)/10000 AS hectares
FROM
  bc_municipality
ORDER BY hectares DESC
LIMIT 1;

name           | hectares
---------------+-----------------
TUMBLER RIDGE  | 155020.02556131
(1 row)
--注意，为了回答这个查询，我们必须计算每个多边形的面积。
--如果我们经常这样做，则可以在表中添加一个Area列，这样我们就可以单独索引以提高性能。 
--通过将结果按降序排列，并使用PostgreSQL的“ LIMIT”命令，我们可以轻松选择最大值，而无需使用诸如max（）之类的聚合函数。

每个自治市完全包含的道路长度是多少？

--这是“空间联接”的一个示例，因为我们将来自两个表的数据（进行联接）组合在一起
--但使用空间交互条件（“包含”）作为联接条件，而不是通常的关系方法来联接：
SELECT
  m.name,
  sum(ST_Length(r.the_geom))/1000 as roads_km
FROM
  bc_roads AS r,
  bc_municipality AS m
WHERE
  ST_Contains(m.the_geom, r.the_geom)
GROUP BY m.name
ORDER BY roads_km;

name                        | roads_km
----------------------------+------------------
SURREY                      | 1539.47553551242
VANCOUVER                   | 1450.33093486576
LANGLEY DISTRICT            | 833.793392535662
BURNABY                     | 773.769091404338
PRINCE GEORGE               | 694.37554369147
...
--该查询需要一些时间，因为表中的每条道路都汇总为最终结果
--（对于我们的特定示例表，大约为25万条道路）。 
--对于较小的覆盖图（几百个记录中的数千个记录），响应速度可能非常快。

创建一张包含乔治王子市内所有道路的新表格。

--这是“叠加”的一个示例，它包含两个表并输出一个新表，该表由空间裁剪或剪切的结果组成。
---与上面展示的“空间连接”不同，此查询实际上创建了新的几何。 
---覆盖层就像涡轮增压的空间连接，对于更精确的分析工作很有用：
CREATE TABLE pg_roads as
SELECT
  ST_Intersection(r.the_geom, m.the_geom) AS intersection_geom,
  ST_Length(r.the_geom) AS rd_orig_length,
  r.*
FROM
  bc_roads AS r,
  bc_municipality AS m
WHERE
  m.name = 'PRINCE GEORGE'
	AND ST_Intersects(r.the_geom, m.the_geom);

维多利亚州“道格拉斯街”的公里长度是多少？

SELECT
  sum(ST_Length(r.the_geom))/1000 AS kilometers
FROM
  bc_roads r,
  bc_municipality m
WHERE
	r.name = 'Douglas St'
	AND m.name = 'VICTORIA'
	AND ST_Intersects(m.the_geom, r.the_geom);

kilometers
------------------
4.89151904172838
(1 row)

有洞的最大的自治市多边形是多少？

SELECT gid, name, ST_Area(the_geom) AS area
FROM bc_municipality
WHERE ST_NRings(the_geom) > 1
ORDER BY area DESC LIMIT 1;

gid  | name         | area
-----+--------------+------------------
12   | SPALLUMCHEEN | 257374619.430216
(1 row)