拓扑关系函数:确定几何对象之间的拓扑关系
目录
ST_Contains
几何A contains 几何B 等价于以下两个条件:
- B 中不存在位于 A 外部的点
- B 中至少有一个点位于 A 内部
当以上条件满足时,函数返回 TRUE
该定义的一个细节在于:A 不 contains 它的边界,但 A contains 它自身。
Geometry A contains Geometry B if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A.
An important subtlety of this definition is that A does not contain its boundary, but A does contain itself.
函数类型:boolean
ST_Contains(geometry geomA, geometry geomB);
举例
SELECT
ST_Contains(smallc, bigc) As smallcontainsbig,
ST_Contains(bigc,smallc) As bigcontainssmall,
ST_Contains(bigc, ST_Union(smallc, bigc)) as bigcontainsunion,
ST_Equals(bigc, ST_Union(smallc, bigc)) as bigisunion,
ST_Covers(bigc, ST_ExteriorRing(bigc)) As bigcoversexterior,
ST_Contains(bigc, ST_ExteriorRing(bigc)) As bigcontainsexterior
FROM (SELECT
ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) As smallc,
ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) As bigc) As foo;
结果:
smallcontainsbig | bigcontainssmall | bigcontainsunion | bigisunion | bigcoversexterior | bigcontainsexterior
------------------+------------------+------------------+------------+-------------------+---------------------
f | t | t | t | t | f
select st_contains(
(select st_collect(geom)
from cities_polygon
where "name" = '武汉'),
(select geom
from cities_point
where "name" = '武汉')
)
# 判断点状城市数据是否位于对应面状城市数据内
select ST_Contains(
(select st_makepolygon((select ST_LineFromMultiPoint(
'MULTIPOINT(1 1, 3 1, 3 3, 1 3, 1 1)')))
),
(select st_makepolygon((select ST_LineFromMultiPoint(
'MULTIPOINT(1 1, 3 1, 3 3, 1 3, 1 1)')))
)
)
# 判断多边形是否 contains 自身:结果返回TRUE
传入参数
- 两个 geometry A, B (判断前者 A 是否包含后者 B)
注意
- 只有在 B 完全位于 A 内部时,才返回 TRUE 值
- 只有当 A,B 的坐标投影、SRID值均一致时,该函数才有意义
- 本函数 ST_Contains 是函数 ST_Within 的逆。正常情况下,ST_Contains(A,B) 成立隐含着 ST_Within(B,A) 成立
ST_ContainsProperly
几何A contains properly 几何B 等价于以下两个条件:
- B 与 A 的内部相交
- B 不与 A 的边界 或 A 的外部相交
当以上条件满足时,函数返回 TRUE
A 不 contains properly 它自身,但 A contains 它自身
函数类型:boolean
ST_ContainsProperly(geometry geomA, geometry geomB);
举例
SELECT
ST_ContainsProperly(smallc, bigc) As smallcontainspropbig,
ST_ContainsProperly(bigc,smallc) As bigcontainspropsmall,
ST_ContainsProperly(bigc, ST_Union(smallc, bigc)) as bigcontainspropunion,
ST_Equals(bigc, ST_Union(smallc, bigc)) as bigisunion,
ST_Covers(bigc, ST_ExteriorRing(bigc)) As bigcoversexterior,
ST_ContainsProperly(bigc, ST_ExteriorRing(bigc)) As bigcontainsexterior
FROM (SELECT
ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) As smallc,
ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) As bigc) As foo;
结果:
smallcontainspropbig | bigcontainspropsmall | bigcontainspropunion | bigisunion | bigcoversexterior | bigcontainsexterior
------------------+------------------+------------------+------------+-------------------+---------------------
f | t | f | t | t | f
select ST_ContainsProperly(
(select ST_LineFromMultiPoint('MULTIPOINT(1 1, 3 1, 3 3, 1 3, 1 1)')
),
(select st_makepolygon((select ST_LineFromMultiPoint(
'MULTIPOINT(1 1, 3 1, 3 3, 1 3, 1 1)')))
)
)
# 判断多边形是否 contains properly 其边界:结果返回FALSE
select ST_ContainsProperly(
(select st_makepolygon((select ST_LineFromMultiPoint(
'MULTIPOINT(1 1, 3 1, 3 3, 1 3, 1 1)')))
),
(select st_makepolygon((select ST_LineFromMultiPoint(
'MULTIPOINT(1 1, 3 1, 3 3, 1 3, 1 1)')))
)
)
# 判断多边形是否 contains properly 自身:结果返回FALSE
select ST_ContainsProperly(
(select geom
from counties
where name = '正定县'
),
(select geom
from counties
where name = '正定县'
)
)
# 判断多边形是否 contains properly 自身(有实际意义多边形,意思同上)
传入参数
- 两个 geometry A, B (判断前者 A 是否包含后者 B)
Contains 与 ContainsProperly
- 两者不同之处在于:多边形是否包含自身。
Contains 认为包含 自身,而 ContainsProperly 认为不包含 自身。 - 两者都认为:多边形不包含 其边界。
ST_Covers
A covers B 等价于以下条件:
- B 中不存在 A 外部的点
函数类型:boolean
ST_Covers(geometry geomA, geometry geomB);
ST_Covers(geography geogpolyA, geography geogpointB);
举例
SELECT
ST_Covers(smallc,smallc) As smallinsmall,
ST_Covers(smallc, bigc) As smallcoversbig,
ST_Covers(bigc, ST_ExteriorRing(bigc)) As bigcoversexterior,
ST_Contains(bigc, ST_ExteriorRing(bigc)) As bigcontainsexterior
FROM (SELECT
ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) As smallc,
ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) As bigc) As foo;
结果:
smallinsmall | smallcoversbig | bigcoversexterior | bigcontainsexterior
--------------+----------------+-------------------+---------------------
t | f | t | f
select ST_Covers(
(select geom
from cities_polygon
where name = '武汉'
),
(select geom
from lake
where name = '太湖'
)
)
# 判断武汉是否覆盖太湖
传入参数
- 两个 geometry A,B 或两个 geography A,B(判断前者 A 是否覆盖后者 B)
ST_CoveredBy
几何A covered by 几何B 等价于以下条件:
- A 中不存在 B 外部的点
当以上条件满足时,函数返回 TRUE
函数类型:boolean
ST_CoveredBy(geometry geomA, geometry geomB);
ST_CoveredBy(geography geogA, geography geogB);
举例
SELECT
ST_CoveredBy(smallc,smallc) As smallinsmall,
ST_CoveredBy(smallc, bigc) As smallcoveredbybig,
ST_CoveredBy(ST_ExteriorRing(bigc), bigc) As exteriorcoveredbybig,
ST_Within(ST_ExteriorRing(bigc),bigc) As exeriorwithinbig
FROM (SELECT
ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) As smallc,
ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) As bigc) As foo;
结果:
smallinsmall | smallcoveredbybig | exteriorcoveredbybig | exeriorwithinbig
--------------+-------------------+----------------------+------------------
t | t | t | f
传入参数
- 两个 geometry A,B 或两个 geography A,B(判断前者 A 是否覆盖后者 B)
ST_Crosses
几何A crosses 几何B 等价于以下条件:
- A 与 B 在空间上交叉(即:A 与 B 有共同的内点,但内点不完全相同)
当以上条件满足时,函数返回 TRUE
函数类型:boolean
ST_Crosses(geometry g1, geometry g2);
举例
select rivers.name, cities_polygon.name
from rivers , cities_polygon
where
st_crosses(rivers.geom, cities_polygon.geom)
# 找到和城市相交的河流
传入参数
- 两个 geometry A, B (判断前者 A 是否包含后者 B)
ST_LineCrossingDirection
表示两个 LineString 的穿越情况。取值范围:-3到3的整数。具体条件如下:
- 0: LINE NO CROSS 不交叉
- -1: LINE CROSS LEFT
- 1: LINE CROSS RIGHT
- -2: LINE MULTICROSS END LEFT
- 2: LINE MULTICROSS END RIGHT
- -3: LINE MULTICROSS END SAME FIRST LEFT
- 3: LINE MULTICROSS END SAME FIRST RIGHT
暂时没看懂,参考 ST_LineCrossingDirection
该网站第一个示例,我复制原语句运行出的结果是 -3,-3 而并非原网站所给出的结果 3,-3
函数类型:integer
ST_LineCrossingDirection(geometry linestringA, geometry linestringB);
传入参数
- 两个 geometry A, B (必须均为 LineString 类型)
ST_Disjoint
几何A disjoint 几何B 等价于以下条件:
- A 与 B 不存在任何 Overlaps, Touches, Within 关系
当以上条件满足时,函数返回 TRUE
函数类型:boolean
ST_Disjoint( geometry A , geometry B );
举例
SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry);
结果:
TRUE
select st_disjoint(
(select geom
from cities_polygon
where name = '北京'
),
(select geom
from rivers
where name = '黄河'
)
)
# 判断黄河是否不经过北京
传入参数
- 两个 geometry A, B
ST_Equals
几何A equals 几何B 等价于以下两个条件:
- ST_Within(A,B) = true
- ST_Within(B,A) = true
当以上条件满足时,函数返回 TRUE
注意:点的顺序可以不一致,只要表示相同的几何结构即可。如果想确认点序一致,可以使用 ST_OrderingEquals 函数
函数类型:boolean
ST_Equals(geometry A, geometry B);
举例
SELECT ST_Equals(
ST_GeomFromText('LINESTRING(0 0, 10 10)'),
ST_GeomFromText('LINESTRING(0 0, 5 5, 10 10)'));
结果:
TRUE
传入参数
- 两个 geometry A, B
ST_Intersects
判断两个 geometry 或两个 geography 是否有任何在空间上共享的部分,即是否相交。对 geography 而言,容差为 0.00001 米(比容差更接近的点被视为相交)。
几何A intersects 几何B 等价于以下条件:
- ST_Overlaps, ST_Touches, ST_Within 任何一个为真(即:ST_Disjoint 为假)
当以上条件满足时,函数返回 TRUE
函数类型:boolean
ST_Intersects( geometry geomA , geometry geomB );
ST_Intersects( geography geogA , geography geogB );
举例
SELECT ST_Intersects(
'POINT(0 0)'::geometry,
'LINESTRING ( 2 0, 0 2 )'::geometry);
结果:
FALSE
SELECT ST_Intersects(
'POINT(0 0)'::geometry,
'LINESTRING ( 0 0, 0 2 )'::geometry);
结果:
TRUE
select cities.name
from cities , railway
where
st_intersects(cities.geom, railway.geom) = 'TRUE' and
railway."name" ='京九线'
# 找到京九线途径的地方
传入参数
- 两个 geometry A,B 或两个 geography A,B
ST_OrderingEquals
几何A ordering equals 几何B 等价于以下两个条件:
- ST_Equals( A, B) = true
- A, B 的坐标顺序严格相同
当以上条件满足时,函数返回 TRUE
函数类型:boolean
ST_OrderingEquals(geometry A, geometry B);
举例
SELECT ST_OrderingEquals(
ST_GeomFromText('LINESTRING(0 0, 10 10)'),
ST_GeomFromText('LINESTRING(0 0, 5 5, 10 10)'));
结果:
FALSE
SELECT ST_OrderingEquals(
ST_GeomFromText('LINESTRING(0 0, 10 10)'),
ST_GeomFromText('LINESTRING(0 0, 0 0, 10 10)'));
结果:
FALSE
SELECT ST_OrderingEquals(
ST_GeomFromText('LINESTRING(0 0, 10 10)'),
ST_GeomFromText('LINESTRING(0 0, 10 10)'));
结果:
TRUE
传入参数
- 两个 geometry A, B
ST_Overlaps
几何A overlaps 几何B 等价于以下两个条件:
- A 与 B 相交(intersect)
- A 与 B 彼此均不完全包含(contain)
当以上条件满足时,函数返回 TRUE
只有维数相同才可能 overlap
函数类型:boolean
ST_Overlaps(geometry A, geometry B);
举例
SELECT
ST_Overlaps(a,b) As a_overlap_b,
ST_Crosses(a,b) As a_crosses_b,
ST_Intersects(a, b) As a_intersects_b,
ST_Contains(b,a) As b_contains_a
FROM (SELECT
ST_GeomFromText('POINT(1 0.5)') As a,
ST_GeomFromText('LINESTRING(1 0, 1 1, 3 5)') As b)
As foo
结果:
a_overlap_b | a_crosses_b | a_intersects_b | b_contains_a
------------+-------------+----------------+--------------
f | f | t | t
# 线上一点被线包含,但并不与线重叠(overlaps),也不与线交叉(crosses)
SELECT
ST_Overlaps(a,b) As a_overlap_b,
ST_Crosses(a,b) As a_crosses_b,
ST_Intersects(a, b) As a_intersects_b,
ST_Contains(a,b) As a_contains_b
FROM (SELECT ST_Buffer(
ST_GeomFromText('POINT(1 0.5)'), 3) As a,
ST_GeomFromText('LINESTRING(1 0, 1 1, 3 5)') As b)
As foo;
结果:
a_overlap_b | a_crosses_b | a_intersects_b | a_contains_b
-------------+-------------+----------------+--------------
f | t | t | f
# 维数不同不能重叠
select ST_Overlaps(
(select st_collect(geom)
from counties
where name = '星子县'),
(select st_collect(geom)
from lakes
where name = '鄱阳湖')
)
# 鄱阳湖部分位于星子县;鄱阳湖并非全部位于星子县
# 星子县部分坐落于鄱阳湖,星子县并非全境均为鄱阳湖
传入参数
- 两个 geometry A, B
ST_PointInsideCircle
几何A point inside circle 某圆 等价于以下两个条件:
- A 是一个点
- A 在这个圆内
当以上条件满足时,函数返回 TRUE
函数类型:boolean
ST_PointInsideCircle(geometry a_point, float center_x, float center_y, float radius);
举例
SELECT ST_PointInsideCircle(ST_Point(1,2), 0.5, 2, 3);
结果:
TRUE
select ST_PointInsideCircle(
(select geom
from cities_point
where name = '酒泉'),
(select centroid_x
from cities_polygon
where name = '敦化'),
(select centroid_y
from cities_polygon
where name = '敦化'),
30.0
);
传入参数
- 一个POINT类型 geometry A
- 圆的中心点坐标及半径
ST_Relate
获取两个几何的关系
函数类型:boolean / text (见下代码注释)
ST_Relate(geometry geomA, geometry geomB,
text intersectionMatrixPattern);
# 类型一:boolean
# 判断两个几何是否满足 DE-9IM 模式矩阵字符串指定的条件,即输入的几何关系是否成立
ST_Relate(geometry geomA, geometry geomB);
# 类型二:text
# 返回九交模型的值
ST_Relate(geometry geomA, geometry geomB, integer BoundaryNodeRule);
# 类型三:text
# 同类型二,但允许指定边界节点规则
九交模型简介
几何图形的部分 | 英文缩写 |
---|---|
内部 | I |
边界 | B |
外部 | E |
相交部分 | 维度数字 |
---|---|
点 | 0 |
线 | 1 |
面 | 2 |
不相交 | -1 |
举例
SELECT ST_Relate(
ST_GeometryFromText('POINT(1 2)'),
ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2));
# 类型二
结果:
0FFFFF212
SELECT ST_Relate(
ST_GeometryFromText('LINESTRING(1 2, 3 4)'),
ST_GeometryFromText('LINESTRING(5 6, 7 8)'));
# 类型二
结果:
FF1FF0102
SELECT ST_Relate(
ST_GeometryFromText('POINT(1 2)'),
ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2),
'0FFFFF212');
# 类型一
结果:
TRUE
SELECT ST_Relate(
ST_GeometryFromText('POINT(1 2)'),
ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2),
'*FF*FF212');
# 类型一
结果:
TRUE
select ST_Relate(
(select geom
from cities
where name = '武汉'),
(select geom
from rivers
where name = '长江')
)
# 获取武汉和长江的九交模型值
# 结果:212101212 即武汉和长江的关系和上面的示例图一样
传入参数
- 两个 geometry A, B
- (可选)text 类型的 intersectionMatrixPattern
- (可选)integer 类型的 BoundaryNodeRule
ST_RelateMatch
判断给定的 intersectionMatrix 是否满足给定的 intersectionMatrixPattern,若满足则返回 TRUE
函数类型:boolean
ST_RelateMatch(text intersectionMatrix, text intersectionMatrixPattern);
举例
SELECT ST_RelateMatch('101202FFF', 'TTTTTTFFF') ;
结果:
TRUE
SELECT mat.name, pat.name, ST_RelateMatch(mat.val, pat.val) As satisfied
FROM
(VALUES ('Equality', 'T1FF1FFF1'),
('Overlaps', 'T*T***T**'),
('Within', 'T*F**F***'),
('Disjoint', 'FF*FF****'))
As pat(name,val)
CROSS JOIN
(VALUES ('Self intersections (invalid)', '111111111'),
('IE2_BI1_BB0_BE1_EI1_EE2', 'FF2101102'),
('IB1_IE1_BB0_BE0_EI2_EI1_EE2', 'F11F00212'))
As mat(name,val);
结果:
name |name |satisfied|
----------------------------+--------+---------+
Self intersections (invalid)|Equality|false |
IE2_BI1_BB0_BE1_EI1_EE2 |Equality|false |
IB1_IE1_BB0_BE0_EI2_EI1_EE2 |Equality|false |
Self intersections (invalid)|Overlaps|true |
IE2_BI1_BB0_BE1_EI1_EE2 |Overlaps|false |
IB1_IE1_BB0_BE0_EI2_EI1_EE2 |Overlaps|false |
Self intersections (invalid)|Within |false |
IE2_BI1_BB0_BE1_EI1_EE2 |Within |false |
IB1_IE1_BB0_BE0_EI2_EI1_EE2 |Within |false |
Self intersections (invalid)|Disjoint|false |
IE2_BI1_BB0_BE1_EI1_EE2 |Disjoint|false |
IB1_IE1_BB0_BE0_EI2_EI1_EE2 |Disjoint|false |
ST_Touches
几何A touches 几何B 等价于以下条件:
- A 与 B 的共同点在 A 与 B 的 boundaries 并集上
当以上条件满足时,函数返回 TRUE
函数类型:boolean
ST_Touches(geometry A, geometry B);
举例
SELECT ST_Touches(
'LINESTRING(0 0, 1 1, 0 2)'::geometry,
'POINT(1 1)'::geometry);
结果:
FALSE
SELECT ST_Touches(
'LINESTRING(0 0, 1 1, 0 2)'::geometry,
'POINT(0 2)'::geometry);
结果:
TRUE
select ST_TOUCHES(
(select geom
from cities
where name = '深圳'),
(select geom
from cities
where name = '香港')
)
# 判断是否接壤
# 结果:TRUE
传入参数
- 两个 geometry A, B (判断 A 是否与 B 边缘接触)
注意
输入几何对必须是以下几种之一:
- Area/Area
- Line/Line
- Line/Area
- Point/Area
- Point/Line
即不接受 Point/Point 对。
ST_Within
几何A within 几何B 等价于以下条件:
- A 完全在 B 的内部
当以上条件满足时,函数返回 TRUE
函数类型:boolean
ST_Within(geometry A, geometry B);
举例
SELECT
ST_Within(smallc,smallc) As smallinsmall,
ST_Within(smallc, bigc) As smallinbig,
ST_Within(bigc,smallc) As biginsmall,
ST_Within(ST_Union(smallc, bigc), bigc) as unioninbig,
ST_Within(bigc, ST_Union(smallc, bigc)) as biginunion,
ST_Equals(bigc, ST_Union(smallc, bigc)) as bigisunion
FROM(SELECT
ST_Buffer(ST_GeomFromText('POINT(50 50)'), 20) As smallc,
ST_Buffer(ST_GeomFromText('POINT(50 50)'), 40) As bigc) As foo;
结果:
smallinsmall|smallinbig|biginsmall|unioninbig|biginunion|bigisunion|
------------+----------+----------+----------+----------+----------+
true |true |false |true |true |true |
select st_within(
(select geom
from cities_point
where name = '武汉'),
(select geom
from cities_polygon
where name = '武汉')
)
# 结果:TRUE
传入参数
- 两个 geometry A, B (判断 A 完全在 B 内部)