场景

在PostGIS中有一张线要素表，需要检查该表中的孤线，并且进行自动纠正的计算。
其中孤线定义为两端端点都不在任何其他线的顶点上。
本文介绍在PostGIS中的线要素点，通过函数计算指定线要素表中的孤线，并计算最接近的纠偏位置。
In PostGIS, there is a table of line features, and it is necessary to check the orphan lines in this table and perform the calculation for automatic correction. Among them, an orphan line is defined as a line whose both end points are not on the vertices of any other lines. This article introduces the line feature points in PostGIS, and through functions, calculates the orphan lines in the specified line feature table and calculates the closest corrected position. The test data situation is as follows. There are six lines, ABCDEM, distributed as follows:

测试数据情况如下，存在ABCDEM六条线，分布如下：

其中A和M的分布关系如下：

ABC在相交处的关系如下：

CDE的关系如下：

我们可以直观看出B和D属于孤线。

第一步线进行孤线查找

判断待检查的要素表如果不是线类型的，则直接返回：

execute SELECT EXISTS (SELECT POSITION(''Line'' in ST_GEOMETRYTYPE(GEOM)) FROM ' ||targetTb|| ' LIMIT 1) into _ISPOINTTYPE;-- 如果类型不是点，则不检查if _ISPOINTTYPE = false then return -1;end if;

获取自动修复的容差，如果偏差大于该阈值，则不做自动修复的计算

if EXISTS (SELECT ID FROM PUBLIC.sp_ck_settings WHERE ITEMNAME = 'single_line_tolerance_4_autofix') then execute 'SELECT ITEMVAL FROM PUBLIC.sp_ck_settings WHERE ITEMNAME = ''single_line_tolerance_4_autofix'' AND USERNAME = '  ||  curUserName into _TOLERANCE;if _TOLERANCE is null then execute 'SELECT ITEMVAL FROM PUBLIC.sp_ck_settings WHERE ITEMNAME = ''single_line_tolerance_4_autofix'' AND USERNAME = ''system'' '