PostGis中将MULTILINESTRING拆分为LINESTRING

测试账号-001

已于 2023-08-02 11:27:45 修改

阅读量2.6k

点赞数

分类专栏： PostGis postgresql 文章标签： postgresql

于 2022-09-15 17:51:25 首次发布

本文链接：https://blog.csdn.net/uid_ao/article/details/126875569

版权

postgresql 同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

PostGis

1 篇文章 0 订阅

订阅专栏

在postgis中使用ST_LineSubstring()函数计算线上两点间距离会遇到数据类型出错的问题，通过查阅官方文档可以发现原因是此函数仅适用于线，要在连续的多线上使用，首先将它们与ST_LineMerge()连接起来。
所以我们使用ST_LineMerge()函数将MULTILINESTRING转为LINESTRING

使用ST_LineMerge拆分

例子

SELECT ST_AsText(ST_LineMerge(
'MULTILINESTRING((106.47501712427169 29.570577878985887,106.47497253836764 29.570525808740683,106.47474799533029 29.570208205623373,106.47458594753044 29.570091790301376,106.47447087680541 29.570119104828336,106.474387699222 29.570204063566166,106.4742403046186 29.570202368559194,106.47411079606542 29.57010064601485,106.47408213461318 29.569828251623733,106.47393818939582 29.56959748968012,106.47341994330696 29.569204910438827))'
		));
--------------------------------------------------------------------------------------------------
输出：LINESTRING(106.47501712427169 29.570577878985887,106.47497253836764 29.570525808740683,106.47474799533029 29.570208205623373,106.47458594753044 29.570091790301376,106.47447087680541 29.570119104828336,106.474387699222 29.570204063566166,106.4742403046186 29.570202368559194,106.47411079606542 29.57010064601485,106.47408213461318 29.569828251623733,106.47393818939582 29.56959748968012,106.47341994330696 29.569204910438827)

但如果有多个多线可能出现每个多线之间相互连接导致线段重合
这时需要先使用ST_Union()将多个多线合并为一个多线，再使用ST_LineMerge()合并多线之中的线段。
示例如下

SELECT 
ST_AsText(ST_LineMerge( ST_SetSRID ( (SELECT ST_Union(geom) as geom 
FROM
	(
	SELECT ST_LineMerge(
'MULTILINESTRING((106.47501712427169 29.570577878985887,106.47497253836764 29.570525808740683,106.47474799533029 29.570208205623373,106.47458594753044 29.570091790301376,106.47447087680541 29.570119104828336,106.474387699222 29.570204063566166,106.4742403046186 29.570202368559194),(106.4742403046186 29.570202368559194,106.47411079606542 29.57010064601485,106.47408213461318 29.569828251623733,106.47393818939582 29.56959748968012,106.47341994330696 29.569204910438827))'
		) geom
	) t
), 4326 ))) geom

示例结果

如果由于不接触线而无法合并，则返回原始的多线字符串，以下为官方示例：

SELECT ST_AsText(ST_LineMerge(
'MULTILINESTRING((-29 -27,-30 -29.7,-36 -31,-45 -33),(-45.2 -33.2,-46 -32))'
));
----------------
输出：MULTILINESTRING((-45.2 -33.2,-46 -32),(-29 -27,-30 -29.7,-36 -31,-45 -33))

或者使用ST_Dump()拆分MULTILINESTRING

SELECT ST_AsText((ST_Dump(geom)).geom)

计算线上两点间的距离

转换之后再使用ST_LineSubstring()计算线上两点的距离，第一个参数必须是线，第二个和第三个参数是 [0， 1] 范围内的值，表示起点和终点位置作为行长度的比例。

总长 [0， 1]：


SELECT trunc(ST_Length (
		ST_GeographyFromText (
			ST_AsText (
				ST_LineSubstring (  ST_LineMerge(
'MULTILINESTRING((106.47501712427169 29.570577878985887,106.47497253836764 29.570525808740683,106.47474799533029 29.570208205623373,106.47458594753044 29.570091790301376,106.47447087680541 29.570119104828336,106.474387699222 29.570204063566166,106.4742403046186 29.570202368559194,106.47411079606542 29.57010064601485,106.47408213461318 29.569828251623733,106.47393818939582 29.56959748968012,106.47341994330696 29.569204910438827))'
		), 0, 1) 
			) 
		) 
	)) AS distance;
	----------------
	输出：249

如两点不在线上，通过ST_ClosestPoint()查出最近的点，再通过ST_LineLocatePoint()函数计算该点在线上的
比例(浮点值)。

with points as(
	SELECT 
		line,
		ST_LineLocatePoint ( line, ST_ClosestPoint ( line, ST_PointFromText ( 'POINT(106.47511712427169 29.570527878985887)', 4326)) ) p1,
		ST_LineLocatePoint ( line, ST_ClosestPoint ( line, ST_PointFromText ( 'POINT(106.47441994330696 29.569204910438827)', 4326)) ) p2
	from (
		select 
			ST_SetSRID ( ST_LineMerge(
		'MULTILINESTRING((106.47501712427169 29.570577878985887,106.47497253836764 29.570525808740683,106.47474799533029 29.570208205623373,106.47458594753044 29.570091790301376,106.47447087680541 29.570119104828336,106.474387699222 29.570204063566166,106.4742403046186 29.570202368559194,106.47411079606542 29.57010064601485,106.47408213461318 29.569828251623733,106.47393818939582 29.56959748968012,106.47341994330696 29.569204910438827))'), 4326 ) line
	)  t
)
SELECT trunc(ST_Length (
		ST_GeographyFromText (
			ST_AsText (
				ST_LineSubstring (  line, p1, p2) 
			) 
		) 
	)) AS distance
	FROM points