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.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');
