PostGIS常用SQL

最新推荐文章于 2023-11-15 09:12:55 发布

牛老大

最新推荐文章于 2023-11-15 09:12:55 发布

阅读量430

点赞数

文章标签： 1024程序员节

本文链接：https://blog.csdn.net/niujing1987/article/details/108671024

版权

聚合：

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.88888888 34.8888888,108.888888, 34.888888888)'
-- 返回：[]
drop type AnalysisData;
-- 创建一种新的返回类型
CREATE TYPE AnalysisData AS    
(
		layerType VARCHAR,
		roadName VARCHAR,
		caliber VARCHAR,
		depth NUMERIC,
		roadCenterDistance 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;
	road_rec geometry;
	rec geometry;
	result AnalysisData % rowtype;
	singleRow AnalysisData;
	end_want_distance NUMERIC;
	start_end_distance NUMERIC;
	startDepth NUMERIC;
	endDepth NUMERIC;

BEGIN

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

-- 遍历雨水
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.roadCenterDistance = (SELECT st_distance(st_transform(ys_record.geom,3857),st_transform(road_rec,3857)));
	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.roadCenterDistance = (SELECT st_distance(st_transform(ws_record.geom,3857),st_transform(road_rec,3857)));
	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.roadCenterDistance = (SELECT st_distance(st_transform(hs_record.geom,3857),st_transform(road_rec,3857)));
	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.88888888 34.8888888,108.888888, 34.888888888)');

-- 为雨、污、合增加统计字段，即方便统计使用的字段
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;

drop type AnalysisDataForLongitudinal;
-- 创建一种新的返回类型
CREATE TYPE AnalysisDataForLongitudinal AS    
(
		startnum VARCHAR,
		startsurfh NUMERIC,
		startx NUMERIC,
		starty NUMERIC,
		upstreamdepth VARCHAR,
		startdepth NUMERIC,
		endnum VARCHAR,
		endsurfh NUMERIC,
		endx NUMERIC,
		endy NUMERIC,
		downstreamdepth VARCHAR,
		enddepth NUMERIC,
		pipelength NUMERIC,
		roadname VARCHAR,
		caliber VARCHAR,
		material VARCHAR
);

DROP FUNCTION longitudinalsectionAnalysis(out character varying, in character varying);
CREATE or REPLACE FUNCTION longitudinalsectionAnalysis(pointnums character varying)
	RETURNS SETOF AnalysisDataForLongitudinal AS $$ 
	DECLARE
	-- 纵断面结果集
	return_record AnalysisDataForLongitudinal;
	-- 存储传入的参数
	char_array VARCHAR[];
	-- 存储查询sql
	query_sql VARCHAR;
	-- 线表
	line_table VARCHAR;
	-- 点表
	point_table VARCHAR;
	-- 查询结果集
	query_record RECORD;
	-- 循环
	i INTEGER;
	-- 查询结果临时变量
	query_value1 VARCHAR;
	query_value2 VARCHAR;

 
BEGIN
 
-- 遍历传入的数据组
  char_array = (SELECT string_to_array(pointnums,','));
	IF (SELECT char_array[1] ~ 'WS') THEN
		line_table = 'ws_line';
		point_table = 'ws_point';
	ELSEIF (SELECT char_array[1] ~ 'YS') THEN
		line_table = 'ys_line';
		point_table = 'ys_point';
	END IF;
 
	i = 1;
	WHILE i<array_length(char_array,1) LOOP
		IF char_array[i+2]='0' THEN
			raise notice '%',i;
			query_sql = 'select l.st_num startnum,p1.surfh startsurfh,p1.x startx,p1.y starty,l.st_depth startdepth,l.ed_num endnum,p2.surfh endsurfh,p2.x endx,p2.y endy,l.ed_depth enddepth,l.shape_leng pipelength,l.road_name roadname,l.caliber,l.material from (select st_num,st_depth,ed_num,ed_depth,shape_leng,road_name,caliber,material from ' || line_table || ' where st_num=''' || char_array[i] || ''' and ed_num=''' || char_array[i+1] || ''') l left join (SELECT mapno,surfh,x,y FROM ' || point_table || ' where mapno=''' || char_array[i] || ''') p1 on p1.mapno=l.st_num left join (SELECT mapno,surfh,x,y FROM ' ||point_table || ' where mapno=''' || char_array[i+1] || ''') p2 on p2.mapno=l.ed_num';
		EXECUTE query_sql into query_record;
			return_record.startnum = query_record.startnum;
			return_record.startsurfh = query_record.startsurfh;
			return_record.startx = query_record.startx;
			return_record.starty = query_record.starty;
			return_record.startdepth = query_record.startdepth;
			return_record.endnum = query_record.endnum;
			return_record.endsurfh = query_record.endsurfh;
			return_record.endx = query_record.endx;
			return_record.endy = query_record.endy;
			return_record.enddepth = query_record.enddepth;
			return_record.pipelength = query_record.pipelength;
			return_record.roadname = query_record.roadname;
			return_record.caliber = query_record.caliber;
			return_record.material = query_record.material;
		ELSE
			raise notice '--%',i;
			query_sql = 'select l.st_num startnum,p1.surfh startsurfh,p1.x startx,p1.y starty,l.st_depth startdepth,l.ed_num endnum,p2.surfh endsurfh,p2.x endx,p2.y endy,l.ed_depth enddepth,l.shape_leng pipelength,l.road_name roadname,l.caliber,l.material from (select st_num,st_depth,ed_num,ed_depth,shape_leng,road_name,caliber,material from ' ||line_table || ' where st_num=''' || char_array[i] || ''' and ed_num=''' || char_array[i+1] || ''') l left join (SELECT mapno,surfh,x,y FROM ' || point_table || ' where mapno=''' || char_array[i] || ''') p1 on p1.mapno=l.st_num left join (SELECT mapno,surfh,x,y FROM ' || point_table || ' where mapno=''' || char_array[i+1] || ''') p2 on p2.mapno=l.ed_num';
			EXECUTE query_sql into query_record;
			return_record.startnum = query_record.endnum;
			return_record.startsurfh = query_record.endsurfh;
			return_record.startx = query_record.endx;
			return_record.starty = query_record.endy;
			return_record.startdepth = query_record.enddepth;
			return_record.endnum = query_record.startnum;
			return_record.endsurfh = query_record.startsurfh;
			return_record.endx = query_record.startx;
			return_record.endy = query_record.starty;
			return_record.enddepth = query_record.startdepth;
			return_record.pipelength = query_record.pipelength;
			return_record.roadname = query_record.roadname;
			return_record.caliber = query_record.caliber;
			return_record.material = query_record.material;
		END IF;
		
			-- 查询上游埋深
			EXECUTE ('SELECT ''''||st_depth from ' || line_table || ' where flow=''1'' and st_num=''' || return_record.startnum || '''') into query_value1;
			EXECUTE ('SELECT ''''||ed_depth from ' || line_table || ' where flow=''0'' and ed_num=''' || return_record.startnum || '''') into query_value2;
			IF (query_value1 is null AND query_value2 IS NULL) THEN
				return_record.upstreamdepth = NULL;
			ELSEIF (query_value1 is null) THEN
				return_record.upstreamdepth = query_value2;
			ELSEIF (query_value2 is null) THEN
				return_record.upstreamdepth = query_value1;
			ELSE
				return_record.upstreamdepth = query_value1 || ',' || query_value2;
			END IF;
			-- 查询下游埋深
			EXECUTE ('SELECT ''''||ed_depth from ' || line_table || ' where flow=''1'' and ed_num=''' || return_record.endnum || '''') into query_value1;
			EXECUTE ('SELECT ''''||st_depth from ' || line_table || ' where flow=''0'' and st_num=''' || return_record.endnum || '''') into query_value2;
			IF (query_value1 is null AND query_value2 IS NULL) THEN
				return_record.downstreamdepth = NULL;
			ELSEIF (query_value1 is null) THEN
				return_record.downstreamdepth = query_value2;
			ELSEIF (query_value2 is null) THEN
				return_record.downstreamdepth = query_value1;
			ELSE
				return_record.downstreamdepth = query_value1 || ',' || query_value2;
			END IF;
			
		i = i+3;
		return next return_record;
	END LOOP;

END;

$$ LANGUAGE plpgsql;


SELECT * from longitudinalsectionAnalysis('WS666621,WS666623,0,WS666623,WS666624,1');

牛老大

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
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 $$declarepsh_jz_area NUMERIC;psh_zd_area NUMERIC;psh_info RECORD;jzw_i...
复制链接

扫一扫