在PostGIS中计算线、面数据集是否自相交（Calculate whether Line or Polygon datasets self intersect in PostGIS）

最近在开发关于空间数据拓扑的检查工作，检查空间线或面数据是否自相交。数据存储在PostGIS中。
通过PostGIS中的ST_IsValidDetail函数检查出来的结果有一种是Self-intersection，但是对于线数据集并不能判断出自相交的情况（项目中的要求是线对象的线段之间有相交的情况）。
所以对于面对象，采用了ST_IsValidDetail进行检查，对于线对象，则采用ST_Node函数进行计算，如果非自相交的线，则比有geom = ST_Node(geom),否则认为是自相交。
同时将检查结果写入记录表中。
目前支持的几何类型为ST_Polygon和ST_LineString,其他类型未做验证。

Recently, I have been working on checking the topology of spatial data, checking whether spatial line or polygon data intersects with each other. The data is stored in PostGIS.
One of the results obtained through the ‘ST-IsValidDetail’ function in PostGIS is self intersection, but for line datasets, it is not possible to determine self intersection (the requirement in the project is that there is intersection between line segments of line objects).
So for face objects, ‘ST-IsValidDetail’ is used for checking, and for line objects, the ‘ST-Node’ function is used for calculation. If there are non self intersecting lines, for example, if there is’ geo m=ST-Node (geo m) ', otherwise it is considered self intersecting.
At the same time, write the inspection results into the record table.
The currently supported geometric types are ST-Polygon and ST-LineString, and other types have not been validated.
实现的数据库函数如下，其中函数入参如下：

targetTb:待检查的目标表名称,
resultTb:检查结果写入的表名称,
keyColumnName:待检查表的唯一标示列名称
geomColumnName:待检查表的空间字段名称

返回值为:正常检查完成返回结果表名称，否则返回"-1";

create or replace function public.sp_check_self_intersecting(targetTb varchar,resultTb varchar,
	keyColumnName varchar default 'id',geomColumnName varchar default 'geom')
RETURNS character varying
LANGUAGE 'plpgsql'
AS $BODY$
	--DECLARE _RESULT VARCHAR;
	declare _GEOTYPE character varying;
	declare _VALIDGEOTYPE BOOLEAN default false;
begin
	-- ID、要素ID、相交位置Point、是否已修正
	EXECUTE 'CREATE TABLE IF NOT EXISTS ' || resultTb ||' (
		id varchar DEFAULT (((''ID_''::text || to_char(now(), ''YYYYMMDDHH24MISS''::text)) || ''_''::text) || "substring"(md5(random()::text), 1, 10)) NOT NULL,
		fid varchar(128),
		geom geometry,
		corrected Boolean
	)';
	execute 'DELETE FROM ' || resultTb;
	EXECUTE 'select st_geometrytype('||geomColumnName||') from ' || targetTb || ' LIMIT 1 ' into _GEOTYPE;

	-- 如果是多边形类型的，采用ST_IsValidDetail获取reason为Self-intersection的
	-- ST_IsValidDetail: valid,reason,location
	if _GEOTYPE = 'ST_Polygon' then 
		_VALIDGEOTYPE := true;
		EXECUTE 'insert into '||resultTb||' (fid,geom)
			select id AS FID,location as geom from 
				(select '||keyColumnName||' as id,(ST_IsValidDetail('||geomColumnName||')).* from '||public.mark_line||') t 
				where t.valid = false AND t.reason = ''Self-intersection'' ';
	end if;

	-- 如果是线类型的，则采用ST_Node对几何对象进行处理，如果ST_Node和原对象不一致，就认为是自相交了
	if _GEOTYPE = 'ST_LineString' then
		_VALIDGEOTYPE := true;
		EXECUTE 'insert into '||resultTb||' (fid,geom) 
			SELECT ID AS FID,GEOM FROM
			(
				SELECT '||keyColumnName||' as id, '||geomColumnName||',ST_ASTEXT(ST_Node('||geomColumnName||')) AS GEOM1,ST_ASTEXT('||geomColumnName||') AS GEOM2 FROM MARK_line
			) T WHERE GEOM1 <> GEOM2';
	end if;

	if _VALIDGEOTYPE = false then
		return '-1';
	end if;
	RETURN resultTb;
end;
$BODY$;