管點(diǎn)、管線空間數(shù)據(jù)建庫SQL腳本
-- 創(chuàng)建管點(diǎn)表
drop table if exists ws_point;
create table ws_point
(
???? MapNo varchar(5),
???? X float,
???? Y float,
???? SurfH float,
???? WDeep float,
???? PCode varchar(10),
???? Subsid varchar(10),
???? Feature varchar(10),
???? RoadName varchar(10),
???? Mtype varchar(10),
???? Msize varchar(20),
???? Wdepth float,
???? Pdate varchar(10)
);
-- 創(chuàng)建管線表
drop table if exists ws_line;
create table ws_line
(
???? OID int primary key,
???? S_Point varchar(5),
???? E_Point varchar(5),
???? S_Deep float,
???? E_Deep float,
???? S_H float,
???? E_H float,
???? PCode varchar(10),
???? DType varchar(10),
???? Material varchar(10),
???? DSize varchar(10),
???? RoadName varchar(20),
???? Memo varchar(20),
???? Pdate varchar(10)
);
-- 導(dǎo)入CSV
copy ws_point from 'd:/temp/ws_point_utf8.csv' with csv header delimiter ',' encoding 'UTF8';
copy ws_line from 'd:/temp//ws_line_utf8.csv' with csv header delimiter ',' encoding 'UTF8';
-- 為管點(diǎn)表創(chuàng)建幾何圖形
alter table ws_point
????add column geom geometry(point,3857);
alter table ws_line
????add column geom geometry;
--生成管點(diǎn)幾何圖形
update ws_point?
????set geom = st_makepoint(x,y);
-- 生成管線段幾何圖形
do $$
declare?
???? myrec record;
???? pt1? ?geometry;
???? pt2? ?geometry;
BEGIN
for myrec in select oid,s_point,e_point?
????????????????????from ws_line
????????????????????where not(s_point is null) and not(e_point is null)
LOOP
???? select?
???? ???? geom?
???? from?
???? ???? ws_point?
???? where?
???? ???? mapno=myrec.s_point?
???? into?
???? ???? pt1;
????
???? select?
???? ???? geom?
???? from?
???? ???? ws_point?
???? where?
???? ???? mapno=myrec.e_point?
???? into?
???? ???? pt2;
????
???? update ws_line
???? ???? set geom = st_makeline(pt1,pt2)
???? ???? where?
???? ???????? oid = myrec.oid;
END LOOP;
end;
$$
-- 方法2：使用SQL語句生成管線幾何圖形
update ws_line a
????set geom = st_makeline(b.geom,c.geom)
????from ws_point b,ws_point c
????where a.s_point = b.mapno and a.e_point = c.mapno;
