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;

-- 創(chuàng)建屬性表,表結構要和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';

-- 聯(lián)接屬性

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.編號;