欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-06-04 10:58:53
...

本文包含以下内容:

  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)