管点、管线空间数据建库SQL脚本
-- 创建管点表
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)
);
-- 创建管线表
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)
);
-- 导入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';
-- 为管点表创建几何图形
alter table ws_point
add column geom geometry(point,3857);
alter table ws_line
add column geom geometry;
--生成管点几何图形
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;
