在PostGIS中计算一个面要素表中的重叠部分（Find overlaps among polygons in PostGIS）

场景

在PostGIS中有一张面要素表，需要检查该表中的哪些地方有重叠。
其中重叠定义为这些多边形的面状交集。
There is a surface feature table in PostGIS, and it is necessary to check which areas in the table have overlaps.
Overlap is defined as the planar intersection of these polygons.
数据分布如下：

可以直观判断出如下两个区域存在问题：

我们现在来计算出这些区域。

第一步，查找重叠区域

SELECT T1.ID::varchar AS ID1,
				T2.ID::varchar AS ID2,
				CASE WHEN T1.ID > T2.ID THEN ST_INTERSECTION(T1.GEOM_COLUMN,T2.GEOM_COLUMN)
				ELSE ST_INTERSECTION(T2.GEOM_COLUMN,T1.GEOM_COLUMN)
				END AS GEOM
			FROM TARGET_TABLE T1 LEFT JOIN TARGET_TABLE T2 ON 
			ST_INTERSECTS(T1.GEOM_COLUMN,T2.GEOM_COLUMN) AND POSITION(''Polygon'' in ST_GEOMETRYTYPE(ST_INTERSECTION(T1.GEOM_COLUMN,T2.GEOM_COLUMN))) > 0
			AND T1.ID <> T2.ID

第二步，对查找结果去重,并忽略一部分重叠过小的结果

SELECT DISTINCT ON(GEOM) * FROM (
...
T WHERE GEOM IS NOT NULL AND ST_AREA(GEOM) > TOLERANCE_VALUE
			ORDER BY GEOM,ID2

第三步，查询结果

select public.sp_check_overlap('TARGET_TABLE','RESULT_TABLE');

第四步，最终结果

完整存储过程

create or replace function public.sp_check_overlap(targetTb varchar,resultTb varchar,
	keyColumnName varchar default 'id',geomColumnName varchar default 'geom', curUserName varchar default '')
RETURNS character varying
LANGUAGE 'plpgsql'
AS $BODY$
	--DECLARE _RESULT VARCHAR;
	--DECLARE row record;
    declare _TOLERANCE FLOAT8 default 0.000000000001;
   	declare _SQLTMP text;
    declare _ISPOINTTYPE BOOLEAN;
begin
	
	execute 'SELECT EXISTS (SELECT POSITION(''Polygon'' in ST_GEOMETRYTYPE(GEOM)) FROM ' ||targetTb|| ' LIMIT 1)' into _ISPOINTTYPE;
	-- 如果类型不是面，则不检查
	if _ISPOINTTYPE = false then 
		return -1;
	end if;

	--ID、要素1ID,要素2ID，重叠区域、是否已修正
	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,
		id1 varchar,
		id2 varchar,
		geom Geometry,
		corrected Boolean
	)';
	execute 'DELETE FROM ' || resultTb;
	-- 获取容差，如果重叠面积大于该容差，才认为重叠
	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;
		raise notice '--3--';
		if _TOLERANCE is null then 
			execute 'SELECT ITEMVAL FROM PUBLIC.sp_ck_settings WHERE ITEMNAME = ''single_line_tolerance_4_autofix'' AND USERNAME = ''system'' ' into _TOLERANCE;
		end IF;
		raise notice '--4--';
		if _TOLERANCE is null then 
			_TOLERANCE = 1;
		end if;
	end IF;

	_SQLTMP :='
			insert into RESULT_TABLE (ID1,ID2,GEOM)
			SELECT DISTINCT ON(GEOM) * FROM (
			SELECT T1.ID::varchar AS ID1,
				T2.ID::varchar AS ID2,
				CASE WHEN T1.ID > T2.ID THEN ST_INTERSECTION(T1.GEOM_COLUMN,T2.GEOM_COLUMN)
				ELSE ST_INTERSECTION(T2.GEOM_COLUMN,T1.GEOM_COLUMN)
				END AS GEOM
			FROM TARGET_TABLE T1 LEFT JOIN TARGET_TABLE T2 ON 
			ST_INTERSECTS(T1.GEOM_COLUMN,T2.GEOM_COLUMN) AND POSITION(''Polygon'' in ST_GEOMETRYTYPE(ST_INTERSECTION(T1.GEOM_COLUMN,T2.GEOM_COLUMN))) > 0
			AND T1.ID <> T2.ID
			) T WHERE GEOM IS NOT NULL AND ST_AREA(GEOM) > TOLERANCE_VALUE
			ORDER BY GEOM,ID2; 
			';
	
	_SQLTMP:= REPLACE(_SQLTMP,'RESULT_TABLE',resultTb);
	_SQLTMP:= REPLACE(_SQLTMP,'TARGET_TABLE',targetTb);
	_SQLTMP:= REPLACE(_SQLTMP,'GEOM_COLUMN',geomColumnName);
	_SQLTMP:= REPLACE(_SQLTMP,'TOLERANCE_VALUE',_TOLERANCE::VARCHAR);
	raise notice '_SQLTMP : %',_SQLTMP;
	execute _SQLTMP;
		
	RETURN resultTb;
end;
$BODY$;