在PostGIS中计算没有在线上的点（Calculate points that do not on the line in PostGIS）

概述

在项目业务需求中需要计算出一些点没有落在线上，比如电网项目中的电杆点位必须落在点线传输路线上。
电杆的数据存储在专门的一张PostGIS空间表中，传输线路以矢量线段的方式存储在另一张线空间表。需要检查计算出那些没有落在传输线路上的电杆。

基本参数如下

targetTb varchar,--待检查的点要素表名称
resultTb varchar,--检查结果写入的表名称
lineTb varchar default 'public.pipeline',--检查基于的线要素表名称
keyColumnName varchar default 'id',--待检查点要素表的唯一标示列
geomColumnName varchar default 'geom',--待检查点要素表的空间字段列
GeomColumnNameOfLine varchar default 'geom' - Check spatial field columns based on line features

In the project business requirements, it is necessary to calculate some points that are not on the line, such as the pole positions in the power grid project that must be on the point line transmission route.
The data of the poles is stored in a dedicated PostGIS spatial table, while the transmission lines are stored in another line spatial table in the form of vector segments. It is necessary to check and calculate the poles that have not fallen on the transmission line.
The basic parameters are as follows:

TargetTb varchar, -- Name of the point element table to be checked
ResultTb varchar, -- the name of the table where the check result is written
LineTb varchar default 'public. pipeline', -- Check based on line feature table names
KeyColumnName varchar default 'id', -- the unique identifier column of the feature table to be checked
GeomColumnName varchar default 'geom', -- the spatial field column of the feature table to be checked
GeomColumnNameOfLine varchar default 'geom' - Check spatial field columns based on line features

最终检查函数

create or replace function public.point_not_on_line(targetTb varchar,resultTb varchar,lineTb varchar default 'public.pipeline_section',
	keyColumnName varchar default 'id',geomColumnName varchar default 'geom',geomColumnNameOfLine varchar default 'geom')
RETURNS character varying
LANGUAGE 'plpgsql'
AS $BODY$
	--DECLARE _RESULT VARCHAR;
	--DECLARE row record;
	DECLARE _EPSG Integer;
	--如果大于该阈值，则不会计算出推荐的修正位置
    declare _TOLERANCE FLOAT8 default 1;
   	declare _SQLTMP text;
begin	
	if EXISTS(SELECT POSITION('Point' in ST_GEOMETRYTYPE(GEOM)) = false FROM public.mark_point LIMIT 1) then 
		return -1;
	end if;
	--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_SRID('||geomColumnNameOfLine||') from '||lineTb||' limit 1 ' into _EPSG;

	EXECUTE 'insert into '||resultTb||' (fid)
			SELECT t.'||keyColumnName||' as fid from '||targetTb||' t where id not in (
				select mp.'||keyColumnName||' from '||targetTb||' mp 
				join '||lineTb||' ps 
				on st_intersects(ST_Transform(mp.'||geomColumnName||','||_EPSG||'),ps.'||geomColumnNameOfLine||')
			)';
	-- 计算更新最近的点；如果未计算出来，则geo字段为空
	_SQLTMP := 'UPDATE RESULT_TABLE T6 SET GEOM = T5.CLOSESTPOINT FROM 
	(
		with srid as (select st_srid(geom) as num from public.pipeline_section limit 1),
		t3 as 
		(
			select T1.ID,T1.FID,T1.GEOM,ST_Transform(t2.geom,srid.num) as pointGeom 
			FROM RESULT_TABLE T1 
			LEFT JOIN TARGET_TABLE T2 ON T1.FID = T2.ID_CLOUMN::varchar 
			LEFT JOIN srid ON 1=1
		)
		select DISTINCT ON (t3.id) t3.pointGeom,t3.fid,t4.geom as lineGeom,ST_ClosestPoint(t4.geom,t3.pointGeom) AS CLOSESTPOINT
			from t3, LINE_TABLE t4 order by t3.id, (t3.pointGeom <-> t4.geom)
	) T5 WHERE T5.FID = T6.FID AND ST_DISTANCE(ST_Transform(T5.pointGeom,3857),ST_Transform(T5.CLOSESTPOINT,3857)) < TOLERANCE_VALUE';
	_SQLTMP:= REPLACE(_SQLTMP,'RESULT_TABLE',resultTb);
	_SQLTMP:= REPLACE(_SQLTMP,'TARGET_TABLE',targetTb);
	_SQLTMP:= REPLACE(_SQLTMP,'ID_CLOUMN',keyColumnName);
	_SQLTMP:= REPLACE(_SQLTMP,'LINE_TABLE',lineTb);
	_SQLTMP:= REPLACE(_SQLTMP,'TOLERANCE_VALUE',_TOLERANCE::VARCHAR);

	raise notice '_SQLTMP : %',_SQLTMP;
	execute _SQLTMP;
	RETURN resultTb;
end;
$BODY$;

调用：

select point_not_on_line('pointTb','resultTb');