(五) 空间查询

Chapter 5 Spatial Queries

译：章节5 空间查询

        The raison d’etre of spatial databases is to perform queries inside the database which would ordinarily require desktop GIS functionality. Using PostGIS effectively requires knowing what spatial functions are available, how to use them in queries, and ensuring that appropriate indexes are in place to provide good performance.

        译：空间数据库存在的理由是在数据库内部执行查询，这通常需要桌面GIS功能。有效地使用PostGIS需要知道哪些空间函数可用，如何在查询中使用它们，并确保适当的索引到位以提供良好的性能。

5.1 Determining Spatial Relationships

译：5.1 确定空间关系

        Spatial relationships indicate how two geometries interact with one another. They are a fundamental capability for querying geometry.

        译：空间关系表示两个几何图形如何相互作用。它们是查询几何图形的基本功能。

5.1.1 Dimensionally Extended 9-Intersection Model

译：5.1.1 尺寸扩展9交模型

        According to the OpenGIS Simple Features Implementation Specification for SQL, "the basic approach to comparing two geometries is to make pair-wise tests of the intersections between the Interiors, Boundaries and Exteriors of the two geometries and to classify the relationship between the two geometries based on the entries in the resulting ’intersection’ matrix."

        译：根据OpenGIS SQL的简单功能实现规范，“比较两种几何图形的基本方法是对两种几何图元的内部、边界和外部之间的相交进行成对测试，并根据生成的‘相交’矩阵中的条目对两种几何形状之间的关系进行分类。”

        In the theory of point-set topology, the points in a geometry embedded in 2-dimensional space are categorized into three sets:

        译：在点集拓扑理论中，嵌入在二维空间中的几何体中的点被分类为三个集合：

Boundary/边界

        The boundary of a geometry is the set of geometries of the next lower dimension. For POINTs, which have a dimension of 0, the boundary is the empty set. The boundary of a LINESTRING is the two endpoints. For POLYGONs, the boundary is the linework of the exterior and interior rings.

        译：几何图形的边界是下一个较低维度的几何图形集。对于维度为0的POINT，边界为空集。LINESTRING的边界是两个端点。对于多边形，边界是外部环和内部环的线条。

Interior/内部

        The interior of a geometry are those points of a geometry that are not in the boundary. For POINTs, the interior is the point itself. The interior of a LINESTRING is the set of points between the endpoints. For POLYGONs, the interior is the areal surface inside the polygon.

        译：几何体的内部是几何体中不在边界中的点。对于point，内部就是点本身。LINESTRING的内部是端点之间的一组点。对于多边形，内部是多边形内部的平面。

Exterior/外部

        The exterior of a geometry is the rest of the space in which the geometry is embedded; in other words, all points not in the interior or on the boundary of the geometry. It is a 2-dimensional non-closed surface.

        译：几何体的外部是几何体嵌入其中的其余空间；换句话说，所有不在几何体内部或边界上的点。它是一个二维非闭合曲面。

        The Dimensionally Extended 9-Intersection Model (DE-9IM) describes the spatial relationship between two geometries by specifying the dimensions of the 9 intersections between the above sets for each geometry. The intersection dimensions can be formally represented in a 3x3 intersection matrix.

        译：尺寸扩展的9交点模型（DE-9IM）通过为每个几何体指定上述集合之间的9个交点的尺寸来描述两个几何体之间的空间关系。交集维度可以用3x3交集矩阵形式化地表示。

        For a geometry g the Interior, Boundary, and Exterior are denoted using the notation I(g), B(g), and E(g). Also, dim(s) denotes the dimension of a set s with the domain of {0,1,2,F}:

        译：对于几何图形g，内部、边界和外部使用符号I（g）、B（g）和E（g）表示。此外，dim(s)表示域为{0,1,2，F}的集合s的维数：

• 0 => point
• 1 => line
• 2 => area
• F => empty set

        Using this notation, the intersection matrix for two geometries a and b is:

        译：使用此符号，两个几何图形a和b的交集矩阵为：

	Interior	Boundary	Exterior
Interior	dim( I(a) ∩ I(b) )	dim( I(a) ∩ B(b) )	dim( I(a) ∩ E(b) )
Boundary	dim( B(a) ∩ I(b) )	dim( B(a) ∩ B(b) )	dim( B(a) ∩ E(b) )
Exterior	dim( E(a) ∩ I(b) )	dim( E(a) ∩ B(b) )	dim( E(a) ∩ E(b) )

        Visually, for two overlapping polygonal geometries, this looks like:

        译：对于两个重叠的多边形几何体，可视化结果如下所示：

		Interior	Boundary	Exterior
	Interior	dim( I(a) ∩ I(b) ) = 2	dim( I(a) ∩ B(b) = 1	dim( I(a) ∩ E(b) ) = 2
	Boundary	dim( B(a) ∩ I(b) ) = 1	dim( B(a) ∩ B(b) ) =0	dim( B(a) ∩ E(b) ) =1
	Exterior	dim( E(a) ∩ I(b) ) = 2	dim( E(a) ∩ B(b) ) =1	dim( E(a) ∩ E(b) = 2

         Reading from left to right and top to bottom, the intersection matrix is represented as the text string ’212101212’.

        译：从左到右、从上到下，交集矩阵表示为文本字符串“212101212”。

        For more information, refer to:

        译：有关更多信息，请参阅：

• OpenGIS Simple Features Implementation Specification for SQL (version 1.1, section 2.1.13.2)

• Wikipedia: Dimensionally Extended Nine-Intersection Model (DE-9IM)

• GeoTools: Point Set Theory and the DE-9IM Matrix

5.1.2 Named Spatial Relationships

译：5.1.2 空间关系命名

        To make it easy to determine common spatial relationships, the OGC SFS defines a set of named spatial relationship predicates. PostGIS provides these as the functions ST_Contains, ST_Crosses, ST_Disjoint, ST_Equals, ST_Intersects, ST_Overlaps, ST_Touches, ST_Within. It also defines the non-standard relationship predicates ST_Covers, ST_CoveredBy, and ST_ContainsProperly.

        译：为了便于确定常见的空间关系，OGCSFS定义了一组命名的空间关系谓词。PostGIS将这些功能提供为ST_Contains、ST_Crosses、ST_Disjoint、ST_Equals、ST_Intercepts、ST_Overlaps、ST_Touches、ST_Wwithin。它还定义了非标准关系谓词ST_Covers、ST_CoveredBy和ST_ContainsProperty。

        Spatial predicates are usually used as conditions in SQL WHERE or JOIN clauses. The named spatial predicates automatically use a spatial index if one is available, so there is no need to use the bounding box operator && as well. For example:

        译：空间谓词通常用作SQL WHERE或JOIN子句中的条件。命名的空间谓词会自动使用空间索引（如果有），因此也不需要使用边界框运算符&&。例如：

SELECT city.name, state.name, city.geom
FROM city JOIN state ON ST_Intersects(city.geom, state.geom);

         For more details and illustrations, see the PostGIS Workshop.

        译：有关更多信息和示例，请参阅PostGIS研讨会。

5.1.3 General Spatial Relationships

译：5.1.3 一般空间关系

        In some cases the named spatial relationships are insufficient to provide a desired spatial filter condition.

        译：在一些情况下，命名的空间关系不足以提供期望的空间过滤条件。

 

        For example, consider a linear dataset representing a road network. It may be required to identify all road segments that cross each other, not at a point, but in a line (perhaps to validate some business rule). In this case ST_Crosses does not provide the necessary spatial filter, since for linear features it returns true only where they cross at a point. A two-step solution would be to first compute the actual intersection (ST_Intersection) of pairs of road lines that spatially intersect (ST_Intersects), and then check if the intersection’s ST_GeometryType is ’LINESTRING’ (properly dealing with cases that return GEOMETRYCOLLECTIONs of [MULTI]POINTs, [MULTI]LINESTRINGs, etc.). Clearly, a simpler and faster solution is desirable.

        译：例如，考虑表示道路网络的线性数据集。可能需要识别所有相互交叉的路段，而不是在一个点上，而是在一条线上（也许是为了验证一些业务规则）。在这种情况下，ST_Crosses不提供必要的空间过滤器，因为对于线性特征，它只在它们在一点交叉的地方返回true。一个分两步的解决方案是首先计算在空间上相交的成对道路线的实际交叉点（ST_intersection），然后检查交叉点的ST_GeometryType是否为“LINESTRING”（正确处理返回[MULTI]POINTs、[MULTI]LINESTRINGs等的几何选择的情况）。显然，需要更简单、更快的解决方案。

 

        A second example is locating wharves that intersect a lake’s boundary on a line and where one end of the wharf is up on shore. In other words, where a wharf is within but not completely contained by a lake, intersects the boundary of a lake on a line, and where exactly one of the wharf’s endpoints is within or on the boundary of the lake. It is possible to use a combination of spatial predicates to find the required features:

        译：第二个例子是定位与湖泊边界相交的码头，码头的一端在岸上。换言之，码头在湖内但不完全被湖所包含，在一条线上与湖的边界相交，码头的确切端点之一在湖的边界内或湖的边界上。可以使用空间谓词的组合来查找所需的功能：

• ST_Contains(lake, wharf) = TRUE • ST_ContainsProperly(lake, wharf) = FALSE

• ST_GeometryType(ST_Intersection(wharf, lake)) = ’LINESTRING’

• ST_NumGeometries(ST_Multi(ST_Intersection(ST_Boundary(wharf), ST_Boundary(lake)))) = 1

... but needless to say, this is quite complicated

        These requirements can be met by computing the full DE-9IM intersection matrix. PostGIS provides the ST_Relate function to do this:

        译： 这些要求可以通过计算完整的DE-9IM交集矩阵来满足。PostGIS提供了ST_Relate函数来执行此操作：

SELECT ST_Relate( 'LINESTRING (1 1, 5 5)',
'POLYGON ((3 3, 3 7, 7 7, 7 3, 3 3))' );
st_relate
-----------
1010F0212

         To test a particular spatial relationship, an intersection matrix pattern is used. This is the matrix representation augmented with the additional symbols {T,*}:

        译：为了测试特定的空间关系，使用了交集矩阵模式。这是用附加符号{T，*}扩充的矩阵表示：

• T => intersection dimension is non-empty; i.e. is in {0,1,2}

• * => don’t care

         Using intersection matrix patterns, specific spatial relationships can be evaluated in a more succinct way. The ST_Relate and the ST_RelateMatch functions can be used to test intersection matrix patterns. For the first example above, the intersection matrix pattern specifying two lines intersecting in a line is ’1*1***1**’:

        译：使用交集矩阵模式，可以以更简洁的方式评估特定的空间关系。Late和ST_RelateMatch函数可用于测试交叉矩阵模式。对于上面的第一个示例，指定在一条线上相交的两条线的相交矩阵模式为“1*1***1**”：

-- Find road segments that intersect in a line
SELECT a.id
FROM roads a, roads b
WHERE a.id != b.id
    AND a.geom && b.geom
    AND ST_Relate(a.geom, b.geom, '1*1***1**');

         For the second example, the intersection matrix pattern specifying a line partly inside and partly outside a polygon is ’102101FF2’:

        译：对于第二个示例，指定部分位于多边形内部和部分位于多边形外部的线的相交矩阵图案为“102101FF2”：

-- Find wharves partly on a lake's shoreline
SELECT a.lake_id, b.wharf_id
FROM lakes a, wharfs b
WHERE a.geom && b.geom
    AND ST_Relate(a.geom, b.geom, '102101FF2');

5.2 Using Spatial Indexes

译：5.2 使用空间索引

        When constructing queries using spatial conditions, for best performance it is important to ensure that a spatial index is used, if one exists (see Section 4.9). To do this, a spatial operator or index-aware function must be used in a WHERE or ON clause of the query.

        译：当使用空间条件构造查询时，为了获得最佳性能，确保使用空间索引（如果存在）是很重要的（请参见第4.9节）。为此，必须在查询的WHERE或ON子句中使用空间运算符或索引感知函数。

        Spatial operators include the bounding box operators (of which the most commonly used is &&; see Section 8.10.1 for the full list) and the distance operators used in nearest-neighbor queries (the most common being ; see Section 8.10.2 for the full list.) Index-aware functions automatically add a bounding box operator to the spatial condition. Index-aware functions include the named spatial relationship predicates ST_Contains, ST_ContainsProperly, ST_CoveredBy, ST_Covers, ST_Crosses, ST_Intersects, ST_Overlaps, ST_Touches, ST_Within, ST_Within, and ST_3DIntersects, and the distance predicates ST_DWithin, ST_DFullyWithin, ST_3DDFullyWithin, and ST_3DDWithin .)

        译：空间运算符包括边界框运算符（其中最常用的是&&；完整列表请参见第8.10.1节）和最近邻查询中使用的距离运算符（最常见的是；完整列表见第8.10.2节）。索引感知功能会自动向空间条件添加边界框运算符。索引感知函数包括命名的空间关系谓词ST_Contains、ST_ContainsPropely、ST_CoveredBy、ST_Covers、ST_Crosses、ST_Intercepts、ST_Overlaps、ST_Touches、ST_Wwithin、ST_WWithin和ST_3DIntersections，以及距离谓词ST_DWithin、ST_DFullyWithin、ST _3DDFullyWith和ST_3DDWithin。）

        Functions such as ST_Distance do not use indexes to optimize their operation. For example, the following query would be quite slow on a large table:

        译：ST_Distance等函数不使用索引来优化其操作。例如，以下查询在一个大表上会非常慢：

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

        This query selects all the geometries in geom_table which are within 100 units of the point (100000, 200000). It will be slow because it is calculating the distance between each point in the table and the specified point, ie. one ST_Distance() calculation is computed for every row in the table.

        译：此查询选择geom_table中距离点100个单位（100000、200000）以内的所有几何图形。它会很慢，因为它正在计算表中每个点和指定点之间的距离，即为表中的每一行计算一个ST_distance()计算。

        The number of rows processed can be reduced substantially by using the index-aware function ST_DWithin:

        译：可以通过使用索引感知函数ST_DWithin来显著减少所处理的行的数量：

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

         This query selects the same geometries, but it does it in a more efficient way. This is enabled by ST_DWithin() using the && operator internally on an expanded bounding box of the query geometry. If there is a spatial index on geom, the query planner will recognize that it can use the index to reduce the number of rows scanned before calculating the distance. The spatial index allows retrieving only records with geometries whose bounding boxes overlap the expanded extent and hence which might be within the required distance. The actual distance is then computed to confirm whether to include the record in the result set.

        译：此查询选择相同的几何图形，但它以更有效的方式进行。这是由ST_DWithin()在查询几何体的扩展边界框上内部使用&&运算符启用的。如果geom上有空间索引，那么查询规划器将认识到，在计算距离之前，它可以使用该索引来减少扫描的行数。空间索引只允许检索具有边界框覆盖扩展范围的几何图形的记录，因此这些几何图形可能在所需距离内。然后计算实际距离，以确认是否将ecord包括在结果集中。

        For more information and examples see the PostGIS Workshop.

        译：有关更多信息和示例，请参阅PostGIS研讨会。

5.3 Examples of Spatial SQL

译：5.3 空间SQL示例

        The examples in this section make use of a table of linear roads, and a table of polygonal municipality boundaries. The definition of the bc_roads table is:

        译：本节中的示例使用了线性道路表和多边形市政边界表。bc_roads表的定义为：

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

        The definition of the bc_municipality table is:

        译：bc_municipality表定义为：

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

        1. What is the total length of all roads, expressed in kilometers?

        译：道路总长度是多少千米？

        You can answer this question with a very simple piece of SQL:

        译：你可以用一条非常简单的SQL来解决：

SELECT sum(ST_Length(geom))/1000 AS km_roads FROM bc_roads;
km_roads
------------------
70842.1243039643

        2.How large is the city of Prince George, in hectares?

        译：Prince George市有多少公顷？

        This query combines an attribute condition (on the municipality name) with a spatial calculation (of the polygon area):

        译：此查询将属性条件（市政名称上）与空间计算（多边形区域）相结合：

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

        3. What is the largest municipality in the province, by area?

        译：按面积来说，该省最大的直辖市是哪个？

        This query uses a spatial measurement as an ordering value. There are several ways of approaching this problem, but the most efficient is below:

        译：该查询使用空间量测作为一个排序字段。有几种方式可以解决这个问题，最有效的是下面这个：

SELECT
    name,
    ST_Area(geom)/10000 AS hectares
FROM bc_municipality
ORDER BY hectares DESC
LIMIT 1;
name           | hectares
---------------+-----------------
TUMBLER RIDGE  | 155020.02556131

        Note that in order to answer this query we have to calculate the area of every polygon. If we were doing this a lot it would make sense to add an area column to the table that could be indexed for performance. By ordering the results in a descending direction, and them using the PostgreSQL "LIMIT" command we can easily select just the largest value without using an aggregate function like MAX().

        译：注意，为了回答这个问题，我们必须计算每个多边形的面积。如果我们经常这样做，那么向表中添加一个可以为性能进行索引的区域列是有意义的。通过按降序排列结果，并使用PostgreSQL“LIMIT”命令，我们可以轻松地选择最大值，而无需使用诸如MAX()之类的聚合函数。

        4. What is the length of roads fully contained within each municipality?

        译：每个市镇内完全包含的道路长度是多少？

        This is an example of a "spatial join", which brings together data from two tables (with a join) using a spatial interaction ("contained") as the join condition (rather than the usual relational approach of joining on a common key):

        译：这是一个“空间联接”的例子，它使用空间交互（“包含”）作为联接条件（而不是通常的在公共键上联接的关系方法）将来自两个表（带有联接）的数据聚集在一起：

SELECT
    m.name,
    sum(ST_Length(r.geom))/1000 as roads_km
FROM bc_roads AS r
JOIN bc_municipality AS m
    ON ST_Contains(m.geom, r.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

        This query takes a while, because every road in the table is summarized into the final result (about 250K roads for the example table). For smaller datsets (several thousand records on several hundred) the response can be very fast.

        译：这个查询需要一段时间，因为表中的每条道路都被汇总为最终结果（示例表中大约有250K条道路）。对于较小的数据集（几百条记录中的几千条），响应可能非常快。

        5. Create a new table with all the roads within the city of Prince George.

        译：创建一个新表存储George省的所有道路。

        This is an example of an "overlay", which takes in two tables and outputs a new table that consists of spatially clipped or cut resultants. Unlike the "spatial join" demonstrated above, this query creates new geometries. An overlay is like a turbo-charged spatial join, and is useful for more exact analysis work:

        译：这是一个“叠加”的例子，它接收两个表，并输出一个由空间裁剪或裁剪的结果组成的新表。与上面演示的“空间连接”不同，该查询创建了新的几何图形。叠加就像涡轮增压的空间连接，对于更精确的分析工作很有用：

CREATE TABLE pg_roads as
SELECT
    ST_Intersection(r.geom, m.geom) AS intersection_geom,
    ST_Length(r.geom) AS rd_orig_length,
    r.*
FROM bc_roads AS r
JOIN bc_municipality AS m
    ON ST_Intersects(r.geom, m.geom)
WHERE
    m.name = 'PRINCE GEORGE';

        6. What is the length in kilometers of "Douglas St" in Victoria?

        译：Victoria的Douglas St多少千米长？

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

        7. What is the largest municipality polygon that has a hole?

        译：有洞的最大市政多边形是什么？

SELECT gid, name, ST_Area(geom) AS area
FROM bc_municipality
WHERE ST_NRings(geom) > 1
ORDER BY area DESC LIMIT 1;
gid  | name         | area
-----+--------------+------------------
12   | SPALLUMCHEEN | 257374619.430216