postgresql ST_Intersects对于非相交地理返回true

7ajki6be 于 2023-06-22 发布在 PostgreSQL

关注(0)|答案(1)|浏览(237)

以下是我的地理数据：

select 'SRID=4326;MULTIPOLYGON (((105.67151103718783 26.882512088629863, 115.99865947468783 31.74650295647645, 116.37219463093783 43.48451247459262, 147.02954326375033 62.362161196046245, -177.2690102030465 66.81825017074695, -168.77863849894493 66.26641372873476, -169.78045429484337 60.98223428128946, 165.38220439656283 44.47269646435982, 172.99572978718783 23.382219202927942, 168.86487041218783 -11.63112025581975, 140.82776103718783 -11.372743681342062, 130.19299541218783 -9.644484126039586, 124.04065166218783 -12.662180464457489, 103.91369853718783 -12.23305845779793, 90.37854228718783 6.9914492544889555, 88.79651103718783 23.945718564044213, 105.67151103718783 26.882512088629863)))'::geography
union all
select 'SRID=4326;POINT (0 80)'::geography

在DBeaver中看起来像这样：

请注意，由于反子午线的关系，照片上的多面体被扭曲了，但它基本上位于亚洲-印度尼西亚地区。
正如你所看到的，点和多边形在几何上和地理上都不相交。
但以下查询仍返回true：

select st_intersects(
'SRID=4326;MULTIPOLYGON (((105.67151103718783 26.882512088629863, 115.99865947468783 31.74650295647645, 116.37219463093783 43.48451247459262, 147.02954326375033 62.362161196046245, -177.2690102030465 66.81825017074695, -168.77863849894493 66.26641372873476, -169.78045429484337 60.98223428128946, 165.38220439656283 44.47269646435982, 172.99572978718783 23.382219202927942, 168.86487041218783 -11.63112025581975, 140.82776103718783 -11.372743681342062, 130.19299541218783 -9.644484126039586, 124.04065166218783 -12.662180464457489, 103.91369853718783 -12.23305845779793, 90.37854228718783 6.9914492544889555, 88.79651103718783 23.945718564044213, 105.67151103718783 26.882512088629863)))'::geography,
'SRID=4326;POINT (0 80)'::geography
)

为什么会这样？

postgresql

来源：https://stackoverflow.com/questions/76507171/st-intersects-returns-true-for-non-intersecting-geography

1条答案

按热度按时间

oxalkeyp1#

那是因为你的多重面是无效的，检查

select
st_isvalid('SRID=4326;MULTIPOLYGON (((105.67151103718783 26.882512088629863, 115.99865947468783 31.74650295647645, 116.37219463093783 43.48451247459262, 
147.02954326375033 62.362161196046245, -177.2690102030465 66.81825017074695, -168.77863849894493 66.26641372873476, -169.78045429484337 60.98223428128946, 
165.38220439656283 44.47269646435982, 172.99572978718783 23.382219202927942, 168.86487041218783 -11.63112025581975, 140.82776103718783 -11.372743681342062, 
130.19299541218783 -9.644484126039586, 124.04065166218783 -12.662180464457489, 103.91369853718783 -12.23305845779793, 90.37854228718783 6.9914492544889555, 
88.79651103718783 23.945718564044213, 105.67151103718783 26.882512088629863)))'::geometry);

虽然地理上没有一个有效的标准，但我们可以考几何。它在输出日志中返回

Self-intersection at or near point 121.48832421332854 46.634833731220766

但是如果你让多边形有效，它会返回false

select 
st_intersects(
st_makevalid('POLYGON ((105.67151103718783 26.882512088629863, 115.99865947468783 31.74650295647645, 116.37219463093783 43.48451247459262, 147.02954326375033 62.362161196046245
, -177.2690102030465 66.81825017074695, -168.77863849894493 66.26641372873476, -169.78045429484337 60.98223428128946, 165.38220439656283 44.47269646435982
, 172.99572978718783 23.382219202927942, 168.86487041218783 -11.63112025581975, 140.82776103718783 -11.372743681342062, 130.19299541218783 -9.644484126039586, 
124.04065166218783 -12.662180464457489, 103.91369853718783 -12.23305845779793, 90.37854228718783 6.9914492544889555, 88.79651103718783 23.945718564044213, 105.67151103718783 26.882512088629863))'::geometry),
'SRID=4326;POINT (0 80)'::geography);

并非所有PostGIS功能都适用于几何和地理，但也可用于测试目的（而非测量）Working sample

赞(0）回复(0）举报 2023-06-22

我来回答

postgresql ST_Intersects对于非相交地理返回true

1条答案

相关问题

热门标签

最新问答