PostGIS常用SQL

聚合:

SELECT array_to_string(array_agg(distinct caliber order by caliber asc),'/') as ccc from ys_line

方法:

create or replace function computeJZWAreaForPSH()
returns void as $$

declare
 psh_jz_area NUMERIC;
 psh_zd_area NUMERIC;
 psh_info RECORD;
 jzw_info RECORD;

BEGIN
--遍历排水户
FOR psh_info IN (SELECT gid,geom FROM psh) LOOP
 --建筑面积求和统计临时变量
 psh_jz_area = 0;
 --建筑占地面积求和统计临时变量
 psh_zd_area = 0;
 --遍历建筑物
 FOR jzw_info IN (SELECT fwjzmj,fwjzzdmj,geom FROM jzw) LOOP
  --如果该建筑物面在排水户面内
  IF st_within(jzw_info.geom, psh_info.geom) THEN
   --累加计算
   psh_jz_area = psh_jz_area + jzw_info.fwjzmj;
   psh_zd_area = psh_zd_area + jzw_info.fwjzzdmj;
  ELSE
  END IF;
 END LOOP;
 UPDATE psh SET jzmj = psh_jz_area, jzzdmj = psh_zd_area WHERE gid = psh_info.gid;
END LOOP;
END;

$$ language plpgsql;
CREATE 
	OR REPLACE FUNCTION computeLineLength ( ) 
	RETURNS void AS $$ 
	DECLARE
	ys_len_info RECORD;
	ws_len_info RECORD;
	hs_len_info RECORD;
BEGIN
	--雨水 
	--找到每条管线的起始点坐标集合,并计算长度 
	FOR ys_len_info IN (
SELECT
	t.gid,|/ (
	( t.startx - t.endx ) ^ 2+ ( t.starty - t.endy ) ^ 2+ ( t.startz - t.endz ) ^ 2 
	) as len 
FROM
	(
SELECT
	l.gid,
	l.st_num,
	to_number ( px.x, '999999.99999' ) startx,
	to_number ( px.y, '999999.99999' ) starty,
	( to_number ( px.surfh, '999999.99999' ) - COALESCE ( l.st_depth, 0.0 ) ) startz,
	l.ed_num,
	to_number ( py.x, '999999.99999' ) endx,
	to_number ( py.y, '999999.99999' ) endy,
	( to_number ( py.surfh, '999999.99999' ) - COALESCE ( l.st_depth, 0.0 ) ) endz 
FROM
	ys_line l
	LEFT JOIN ys_point px ON l.st_num = px.mapno
	LEFT JOIN ys_point py ON l.ed_num = py.mapno 
	) AS t ) LOOP
	UPDATE ys_line 
	SET shape_leng = ys_len_info.len 
WHERE
	gid = ys_len_info.gid;
END LOOP;

--污水 
FOR ws_len_info IN (
	SELECT
		t1.gid,|/(
			( t1.startx - t1.endx ) ^ 2+ ( t1.starty - t1.endy ) ^ 2+ ( t1.startz - t1.endz ) ^ 2 
		) as len 
	FROM
		(
		SELECT
			l.gid,
			l.st_num,
			to_number ( px.x, '999999.99999' ) startx,
			to_number ( px.y, '999999.99999' ) starty,
			( to_number ( px.surfh, '999999.99999' ) - COALESCE ( l.st_depth, 0.0 ) ) startz,
			l.ed_num,
			to_number ( py.x, '999999.99999' ) endx,
			to_number ( py.y, '999999.99999' ) endy,
			( to_number ( py.surfh, '999999.99999' ) - COALESCE ( l.st_depth, 0.0 ) ) endz 
		FROM
			ws_line l
			LEFT JOIN ws_point px ON l.st_num = px.mapno
			LEFT JOIN ws_point py ON l.ed_num = py.mapno 
		) AS t1 ) LOOP
		UPDATE ws_line 
		SET shape_leng = ws_len_info.len 
	WHERE
		gid = ws_len_info.gid;
END LOOP;

--合流 
FOR hs_len_info IN (
	SELECT
		gid,|/ ( ( startx - endx ) ^ 2+ ( starty - endy ) ^ 2+ ( startz - endz ) ^ 2 ) as len 
	FROM
		(
		SELECT
			l.gid,
			l.st_num,
			to_number ( px.x, '999999.99999' ) startx,
			to_number ( px.y, '999999.99999' ) starty,
			( to_number ( px.surfh, '999999.99999' ) - COALESCE ( l.st_depth, 0.0 ) ) startz,
			l.ed_num,
			to_number ( py.x, '999999.99999' ) endx,
			to_number ( py.y, '999999.99999' ) endy,
			( to_number ( py.surfh, '999999.99999' ) - COALESCE ( l.st_depth, 0.0 ) ) endz 
		FROM
			hs_line l
			LEFT JOIN hs_point px ON l.st_num = px.mapno
			LEFT JOIN hs_point py ON l.ed_num = py.mapno 
		) as t 
	)
LOOP
		UPDATE hs_line 
		SET shape_leng = hs_len_info.len 
	WHERE
		gid = hs_len_info.gid;
	
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- 传入一段直线文本,获取该直线相交的管线、路中线,获取到道路名称、管线的管径、相交点的埋深、相交点与路中排序间距
-- 例子:输入'LINESTRING(108.83220317733155 34.1684491918418,108.83246816948501 34.16844649472065)'
-- 返回:[]
drop type AnalysisData;
-- 创建一种新的返回类型
CREATE TYPE AnalysisData AS    
(
		layerType VARCHAR,
		roadName VARCHAR,
		caliber VARCHAR,
		depth NUMERIC,
		x NUMERIC,
		y NUMERIC
);

-- 插值算法伪代码
-- let start_end_distance = 可求算;// 起点与终点距离
-- let end_want_distance = 可求算;
-- let L1; // 起点到与零平面相交点的水平距离
-- let L2; // 终点到与零平面相交点的水平距离
-- // 根据三角函数
-- startDepth/L1==endDepth/L2;
-- if(startDepth > endDepth){
--   L1==L2+start_end_distance;
--   // 换算
--   startDepth/(L2+start_end_distance)==endDepth/L2
--   startDepth==endDepth+(endDepth*start_end_distance)/L2
--   L2==(endDepth* start_end_distance)/(startDepth - endDepth)
--   // 再根据三角函数
--   endDepth/L2 == wantDepth/(L2+end_want_distance)
--   wantDepth==endDepth/L2*(L2+end_want_distance)
--   wantDepth==endDepth+(endDepth*end_want_distance)/L2
--   wantDepth==endDepth+(endDepth*end_want_distance)/(endDepth* start_end_distance)*(startDepth - endDepth)
-- } else if(startDepth < endDepth){
--   L1==L2-start_end_distance
--   // 换算
--   startDepth/(L2-start_end_distance)==endDepth/L2
--   startDepth==endDepth-(endDepth*start_end_distance)/L2
--   L2==(endDepth*start_end_distance)/(endDepth-startDepth)
--   // 再根据三角函数
--   endDepth/L2 == wantDepth/(L2-end_want_distance)
--   wantDepth==(endDepth/L2)*(L2-end_want_distance)
--   wantDepth==endDepth-(endDepth*end_want_distance)/L2
--   wantDepth==endDepth-(endDepth*end_want_distance)/(endDepth*start_end_distance)*(endDepth-startDepth)
-- } else {
--   wantDepth == startDepth == endDepth
-- }
DROP FUNCTION crosssectionanalysis(character varying);
CREATE or REPLACE FUNCTION crossSectionAnalysis(linetext character varying)
	RETURNS SETOF AnalysisData AS $$ 
	DECLARE
	ys_record RECORD;
	ws_record RECORD;
	hs_record RECORD;
	lz_record RECORD;
	rec geometry;
	result AnalysisData % rowtype;
	singleRow AnalysisData;
	end_want_distance NUMERIC;
	start_end_distance NUMERIC;
	startDepth NUMERIC;
	endDepth NUMERIC;

BEGIN

-- 遍历雨水
FOR ys_record IN (select l.st_depth startDepth,l.ed_depth endDepth,p1.geom startGeom,p2.geom endGeom,l.road_name,l.caliber,l.geom from ys_line l left join ys_point p1 on l.st_num=p1.mapno left join ys_point p2 on l.ed_num=p2.mapno) LOOP
IF (SELECT st_intersects(st_geomfromtext(linetext, '4326'), ys_record.geom)) THEN
	-- 如果相交,拿到该管线id 获取交点
	rec = (SELECT st_intersection(st_geomfromtext(linetext, '4326'), ys_record.geom));
	singleRow.layerType = '雨水';
	singleRow.roadName = ys_record.road_name;
	singleRow.caliber = ys_record.caliber;
	-- 插值运算
	-- wantDepth==endDepth-(endDepth*end_want_distance)/(endDepth*start_end_distance)*(endDepth-startDepth)
	end_want_distance = (SELECT st_distance(st_transform(rec,3857),st_transform(ys_record.endGeom,3857)));
	start_end_distance = (SELECT st_distance(st_transform(ys_record.startGeom,3857),st_transform(ys_record.endGeom,3857)));
-- 	startDepth = to_number(ys_record.startDepth, '999999.99999');
-- 	endDepth = to_number(ys_record.endDepth, '999999.99999');
	startDepth = ys_record.startDepth;
	endDepth = ys_record.endDepth;
	singleRow.depth = endDepth - (endDepth * end_want_distance) / (endDepth * start_end_distance) * (endDepth - startDepth);
	singleRow.x = st_x(rec);
	singleRow.y = st_y(rec);
	return next singleRow;
END IF;
END LOOP;

-- 遍历污水
FOR ws_record IN (select l.st_depth startDepth,l.ed_depth endDepth,p1.geom startGeom,p2.geom endGeom,l.road_name,l.caliber,l.geom from ws_line l left join ws_point p1 on l.st_num=p1.mapno left join ws_point p2 on l.ed_num=p2.mapno) LOOP
IF (SELECT st_intersects(st_geomfromtext(linetext, '4326'), ws_record.geom)) THEN
	-- 如果相交,拿到该管线id 获取交点
	rec = (SELECT st_intersection(st_geomfromtext(linetext, '4326'), ws_record.geom));
	singleRow.layerType = '污水';
	singleRow.roadName = ws_record.road_name;
	singleRow.caliber = ws_record.caliber;
	-- 插值运算
	-- wantDepth==endDepth-(endDepth*end_want_distance)/(endDepth*start_end_distance)*(endDepth-startDepth)
	end_want_distance = (SELECT st_distance(st_transform(rec,3857),st_transform(ws_record.endGeom,3857)));
	start_end_distance = (SELECT st_distance(st_transform(ws_record.startGeom,3857),st_transform(ws_record.endGeom,3857)));
-- 	startDepth = to_number(ys_record.startDepth, '999999.99999');
-- 	endDepth = to_number(ys_record.endDepth, '999999.99999');
	startDepth = ws_record.startDepth;
	endDepth = ws_record.endDepth;
	singleRow.depth = endDepth - (endDepth * end_want_distance) / (endDepth * start_end_distance) * (endDepth - startDepth);
	singleRow.x = st_x(rec);
	singleRow.y = st_y(rec);
	return next singleRow;
END IF;
END LOOP;

-- 遍历合流水
FOR hs_record IN (select l.st_depth startDepth,l.ed_depth endDepth,p1.geom startGeom,p2.geom endGeom,l.road_name,l.caliber,l.geom from hs_line l left join hs_point p1 on l.st_num=p1.mapno left join hs_point p2 on l.ed_num=p2.mapno) LOOP
IF (SELECT st_intersects(st_geomfromtext(linetext, '4326'), hs_record.geom)) THEN
	-- 如果相交,拿到该管线id 获取交点
	rec = (SELECT st_intersection(st_geomfromtext(linetext, '4326'), hs_record.geom));
	singleRow.layerType = '合流';
	singleRow.roadName = hs_record.road_name;
	singleRow.caliber = hs_record.caliber;
	-- 插值运算
	-- wantDepth==endDepth-(endDepth*end_want_distance)/(endDepth*start_end_distance)*(endDepth-startDepth)
	end_want_distance = (SELECT st_distance(st_transform(rec,3857),st_transform(hs_record.endGeom,3857)));
	start_end_distance = (SELECT st_distance(st_transform(hs_record.startGeom,3857),st_transform(hs_record.endGeom,3857)));
-- 	startDepth = to_number(ys_record.startDepth, '999999.99999');
-- 	endDepth = to_number(ys_record.endDepth, '999999.99999');
	startDepth = hs_record.startDepth;
	endDepth = hs_record.endDepth;
	singleRow.depth = endDepth - (endDepth * end_want_distance) / (endDepth * start_end_distance) * (endDepth - startDepth);
	singleRow.x = st_x(rec);
	singleRow.y = st_y(rec);
	return next singleRow;
END IF;
END LOOP;

-- 遍历路中
FOR lz_record IN (select geom from xzlz) LOOP
IF (SELECT st_intersects(st_geomfromtext(linetext, '4326'), lz_record.geom)) THEN
	-- 如果相交,拿到该管线id 获取交点
	rec = (SELECT st_intersection(st_geomfromtext(linetext, '4326'), lz_record.geom));
	singleRow.layerType = '路中';
	singleRow.roadName = '';
	singleRow.caliber = 0;
	singleRow.depth = 0;
	singleRow.x = st_x(rec);
	singleRow.y = st_y(rec);
	return next singleRow;
END IF;
END LOOP;

END;
$$ LANGUAGE plpgsql;

SELECT * FROM crossSectionAnalysis('LINESTRING(108.83220317733155 34.1684491918418,108.83246816948501 34.16844649472065)');
-- 为雨、污、合增加统计字段,即方便统计使用的字段
create or replace function countCaliber()
returns void as $$

declare
 rec record;
 greatestValue integer;
 leastValue integer;

BEGIN
--添加字段
ALTER TABLE ys_line
ADD COLUMN greatest_caliber NUMERIC;
ALTER TABLE ys_line
ADD COLUMN least_caliber NUMERIC;
--遍历雨水管线管径
For rec in (SELECT gid,caliber from ys_line) loop
	IF position('*' in rec.caliber)>0 THEN
		greatestValue = GREATEST(split_part(rec.caliber, '*', 1), split_part(rec.caliber, '*', 2));
		leastValue = LEAST(split_part(rec.caliber, '*', 1), split_part(rec.caliber, '*', 2));
	ELSEIF position('X' in rec.caliber)>0 THEN
		greatestValue = GREATEST(split_part(rec.caliber, 'X', 1), split_part(rec.caliber, 'X', 2));
		leastValue = LEAST(split_part(rec.caliber, 'X', 1), split_part(rec.caliber, 'X', 2));
	ELSEIF position('×' in rec.caliber)>0 THEN
		greatestValue = GREATEST(split_part(rec.caliber, '×', 1), split_part(rec.caliber, '×', 2));
		leastValue = LEAST(split_part(rec.caliber, '×', 1), split_part(rec.caliber, '×', 2));
	ELSEIF rec.caliber='管径不详' or rec.caliber='不详' THEN
		greatestValue = 0;
		leastValue = 0;
	ELSE
		greatestValue = rec.caliber;
		leastValue = rec.caliber;
	END IF;
	UPDATE ys_line SET greatest_caliber=greatestValue,least_caliber=leastValue WHERE gid = rec.gid;
END LOOP;

--添加字段
ALTER TABLE ws_line
ADD COLUMN greatest_caliber NUMERIC;
ALTER TABLE ws_line
ADD COLUMN least_caliber NUMERIC;
--遍历雨水管线管径
For rec in (SELECT gid,caliber from ws_line) loop
	IF position('*' in rec.caliber)>0 THEN
		greatestValue = GREATEST(split_part(rec.caliber, '*', 1), split_part(rec.caliber, '*', 2));
		leastValue = LEAST(split_part(rec.caliber, '*', 1), split_part(rec.caliber, '*', 2));
	ELSEIF position('X' in rec.caliber)>0 THEN
		greatestValue = GREATEST(split_part(rec.caliber, 'X', 1), split_part(rec.caliber, 'X', 2));
		leastValue = LEAST(split_part(rec.caliber, 'X', 1), split_part(rec.caliber, 'X', 2));
	ELSEIF position('×' in rec.caliber)>0 THEN
		greatestValue = GREATEST(split_part(rec.caliber, '×', 1), split_part(rec.caliber, '×', 2));
		leastValue = LEAST(split_part(rec.caliber, '×', 1), split_part(rec.caliber, '×', 2));
	ELSEIF rec.caliber='管径不详' or rec.caliber='不详' THEN
		greatestValue = 0;
		leastValue = 0;
	ELSE
		greatestValue = rec.caliber;
		leastValue = rec.caliber;
	END IF;
	UPDATE ws_line SET greatest_caliber=greatestValue,least_caliber=leastValue WHERE gid = rec.gid;
END LOOP;

--添加字段
ALTER TABLE hs_line
ADD COLUMN greatest_caliber NUMERIC;
ALTER TABLE hs_line
ADD COLUMN least_caliber NUMERIC;
--遍历雨水管线管径
For rec in (SELECT gid,caliber from hs_line) loop
	IF position('*' in rec.caliber)>0 THEN
		greatestValue = GREATEST(split_part(rec.caliber, '*', 1), split_part(rec.caliber, '*', 2));
		leastValue = LEAST(split_part(rec.caliber, '*', 1), split_part(rec.caliber, '*', 2));
	ELSEIF position('X' in rec.caliber)>0 THEN
		greatestValue = GREATEST(split_part(rec.caliber, 'X', 1), split_part(rec.caliber, 'X', 2));
		leastValue = LEAST(split_part(rec.caliber, 'X', 1), split_part(rec.caliber, 'X', 2));
	ELSEIF position('×' in rec.caliber)>0 THEN
		greatestValue = GREATEST(split_part(rec.caliber, '×', 1), split_part(rec.caliber, '×', 2));
		leastValue = LEAST(split_part(rec.caliber, '×', 1), split_part(rec.caliber, '×', 2));
	ELSEIF rec.caliber='管径不详' or rec.caliber='不详' THEN
		greatestValue = 0;
		leastValue = 0;
	ELSE
		greatestValue = rec.caliber;
		leastValue = rec.caliber;
	END IF;
	UPDATE hs_line SET greatest_caliber=greatestValue,least_caliber=leastValue WHERE gid = rec.gid;
END LOOP;
END;

$$ language plpgsql;

 

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页