PostGIS建库SQL：CAD图形数据+Excel属性数据入库

drop table if exists building;

create table building

(

     id serial4 primary key,

     building_no int,

     geom geometry

);

-- 将多段线转为建筑物面

insert into building(geom)

     select (st_dump(st_polygonize(geom))).geom

     from 

     (

         select 

              st_union(geom) as geom 

         from 

              polylines 

     ) foo;

-- 查看坐标系

select st_asewkt(geom) from building limit 5;

select st_asewkt(geom) from texts limit 5;

-- 为building设置坐标系

update building set geom = st_setsrid(geom,21419);

-- 将建筑物编号注记，更新到建筑物面图层/表

update building a

    set building_no = cast(b."text" as int)

    from texts b 

    where st_contains(a.geom,b.geom)=true;

-- 创建属性表,表结构要和CSV表头保持一致

drop table if exists info;

create table info(

     编号 int,

     名称 varchar(20),

     用途 varchar(10),

     建筑年代 int,

     建筑面积 float,

     楼层 int,

     结构 varchar(10)

);

-- 将CSV属性表导入到info表里

copy info from 'd:/temp/building_utf8.csv' with csv header DELIMITER ',' encoding 'UTF8';

-- 联接属性

create table jmd 

as 

    select a.*,b.名称 as building_name,b.用途 usage

    from building a,info b

    where a.building_no = b.编号

--为建筑物表添加属性字段

alter table building

     add column "name" varchar(20),

     add column usage varchar(10),

     add column build_year int,

     add column area float,

     add column struct varchar(10);

-- 多表更新

update building a

    set "name" = b.名称,usage = b.用途, build_year = cast(b.建筑年代 as int)

    from info b

    where 

         a.building_no = b.编号;