【gis】输电线路选址的系统设计和postgis实现

本文档由MacOS的Pages文稿转换而来，转换步骤如下：

1）首先将Pages文稿导出为rtfd格式。rtfd是包含附件的RTF格式，存储方式是在一个目录下包含一个RTF文件和一些图片附件等；

2）将rtfd目录中的RTF文件转换为html文件，这个步骤由MacOS原生的textutil工具完成，具体用法可以通过`man textutile`查看，转换命令如下：

`textutil foo.rtfd/TXT.rtf -convert html`

这一步转换丢弃了Pages文稿中的图片附件。

3）将html文件转换为strict markdown文件，这一步基于pandoc工具，可以直接下载Mac安装包安装，转换命令如下：

`pandoc --from html --to markdown_strict foo.rtfd/TXT.html -o foo.md`

Topic

Fire risk of electric assets

输电廊道上的潜在环境风险（特别是火灾）管控

Nested asset types

Level1 

Land Use mask layer

Level2 

Power transmission corridor

Level3 

Power lines

Tower

trees

Weather station

Title

Environmental risk prevention of power transmission facilities

Aims

为电力单位的植被管理团队提供关于资产的计划、实践和报告的精确模型，来简化他们高成本和复杂的现场数据收集和检查工作。我们希望帮助电力设施维护团队优化工作流程并降低业务成本。

Provide the vegetation management team of the electricity agency with accurate models of asset planning, practice and reporting to simplify their high-cost and complex field data collection and inspection work. We hope to help the power facility maintenance team optimize work processes and reduce business costs.

电力单位无法通过例行的野外作业来预测哪些资产需要修护或清理。希望帮助他们根据周围环境评估周围环境的状况，特别是输电廊道上植被的空间特征，预测这些环境因子的变化，来感知对公用事业资产的威胁。 

The electricity agency cannot predict which assets need repairing or cleaning through routine field operations. Hope to help them assess the condition of the surrounding environment based on the surrounding environment, especially the spatial characteristics of the vegetation on the power transmission corridor, predict the changes of these environmental factors, and perceive the threat to public utility assets. 

 

为电力单位提供快速高效的数据收集和检查、提升数据准确性和质量、帮助他们实现有效的团队协调与协作。我们旨在通过分析挖掘空间数据，来帮助电力单位识别和预测环境危害例如野火，评估经济损失。

To provide electricity agency with fast and efficient data collection and inspection, improve data accuracy and quality, and help them achieve effective team coordination and collaboration. We aim to analyze and mining spatial data to help power companies identify and predict environmental hazards such as wildfires and assess economic losses.

Decisions

初步构思的决策项（功能点）

1. 为了安全，廊道内房屋都要被拆迁，总共涉及多少栋房屋，总共需要赔偿多少钱？
2. 为了安全，廊道内的耕地都要被弃用，廊道内的耕地面积有多少，涉及的土地价值有多少？
3. 距离故障输电塔5公里内的居民区有多少个？
4. 有多少条道路需要设置警示牌来注意高压危险？
5. 输电廊道上需要砍掉的离输电线路太近的树木有多少颗？
6. 输电廊道上哪些树木因为靠近输电线路而需要修剪？
7. 所有输电廊道1公里缓冲区内的用地类型有哪些？用地面积占比最大的和最小的用地类型分别是什么？
8. 按照受树木干扰威胁程度对编号为A001对输电线路上的所有输电塔排序，并给出前三个输电塔之间的距离？
9. 编号为X003的输电廊道单位面积的输电塔个数是多少？单位长度的树木个数是多少？

Pyramid

ER diagram

Tables

Column	Details	Type	Not null cons	Unique cons	Primary key	Foreign key	Check	Exclusion
ID		integer	Y	Y	Y
LUTYPE	Land use types:  forest  residential  road  facilities farmland  water industrial	varchar(50)	Y
PEOPLE	How many people in the block	bigint
HOUSE	How many house in the block	bigint
geom	Geometry

Column	Details	Type	Not null cons	Unique cons	Primary key	Foreign key	Check	Exclusion
ID		integer	Y	Y	Y
geom	Geometry

Column	Details	Type	Not null cons	Unique cons	Primary key	Foreign key	Check	Exclusion
ID		integer	Y	Y	Y
TEXTURE	texture of electrical wire “A” “B” “C”	varchar(50)	Y
LPARALLEL	Parallel lines of a electrical wire	integer	Y
geom

Column	Details	Type	Not null cons	Unique cons	Primary key	Foreign key	Check	Exclusion
ID		integer	Y	Y	Y
HEIGHT	Height of tower(meter	decimal	Y
TEXTURE	Texture of tower include Q235 Q345 Q460	varchar(50)
CORROSION	loss ratio of sectional area in ten years.  include  in[0,1]	decimal
geom	Geometry
WIRES_ID						Y

Column	Details	Type	Not null cons	Unique cons	Primary key	Foreign key	Check	Exclusion
ID		integer	Y	Y	Y
TREETYPE	varieties of trees “A” “B” “C” “D” “E”	varchar(50)
AGE	How old of the tree 1-100	integer
geom	Geometry

SQL

这部分内容应参考postgis官方文档。

创建表之前需要创建数据库，应用postgis扩展。

Create table

QGIS创建的polygon和linestring类型的要素类默认是multi型的，所以创建表时geom字段也要对应带multi的类型。

(base) jade_mayer in ~/projects/lhx/asset/work/sql λ psql -d electric -f createtable_ucfsxxx.sql

创建表前先删除shema及其下面的table，保证每次测试从头开始，直到成功。同时，每次执行sql语句都按事务提交，这样发生错误会自动回滚操作，sql脚本如下：

BEGIN;

DROP SCHEMA IF EXISTS ucfsxxx CASCADE; -- drop the schema and its table
CREATE SCHEMA ucfsxxx;
create table ucfsxxx.landuse (ID serial, LUTYPE varchar(50), PEOPLE bigint, HOUSE bigint, geom geometry(MULTIPOLYGON, 32649) );
create table ucfsxxx.corridors (ID serial, geom geometry(MULTIPOLYGON, 32649) );
create table ucfsxxx.wires (ID serial, TEXTURE varchar(50), LPARALLEL integer, geom geometry(MultiLineString, 32649) );
create table ucfsxxx.towers (ID serial, HEIGHT decimal, TEXTURE varchar(50), CORROSION decimal, geom geometry(POINT, 32649), WIRES_ID integer );
create table ucfsxxx.trees (ID serial, TREETYPE varchar(50), AGE integer, geom geometry(POINT, 32649) );

COMMIT; 

Create constraints

(base) jade_mayer in ~/projects/lhx/asset/work/sql λ psql -d electric -f createconstraints_ucfsxxx.sql

单独创建约束，包括主键和外键约束。

BEGIN;

ALTER TABLE ucfsxxx.landuse DROP CONSTRAINT IF EXISTS landuse_pkey;
ALTER TABLE ucfsxxx.landuse ADD CONSTRAINT landuse_pkey PRIMARY KEY (ID);

ALTER TABLE ucfsxxx.corridors DROP CONSTRAINT IF EXISTS corridors_pkey;
ALTER TABLE ucfsxxx.corridors ADD CONSTRAINT corridors_pkey PRIMARY KEY (ID);

ALTER TABLE ucfsxxx.wires DROP CONSTRAINT IF EXISTS wires_pkey;
ALTER TABLE ucfsxxx.wires ADD CONSTRAINT wires_pkey PRIMARY KEY (ID);

ALTER TABLE ucfsxxx.towers DROP CONSTRAINT IF EXISTS towers_pkey;
ALTER TABLE ucfsxxx.towers ADD CONSTRAINT towers_pkey PRIMARY KEY (ID);

-- foreign key adding process
ALTER TABLE ucfsxxx.towers DROP CONSTRAINT IF EXISTS towers_fkey;
ALTER TABLE ucfsxxx.towers 
	ADD CONSTRAINT towers_fkey FOREIGN KEY (WIRES_ID)
	REFERENCES ucfsxxx.wires (ID)
	ON DELETE CASCADE;

ALTER TABLE ucfsxxx.trees DROP CONSTRAINT IF EXISTS trees_pkey;
ALTER TABLE ucfsxxx.trees ADD CONSTRAINT trees_pkey PRIMARY KEY (ID);

COMMIT;

Insert data

在QGIS中随机生成实验数据，用到不少技巧，包括在属性表字段计算器中自定义Python函数对属性赋值。

插入数据时首先利用shp2pgsql将矢量shapefile文件转换为sql insert语句，然后利用psql执行。shp2pgsql可以选择以wkt或wkb方式插入数据。

1) landuse

(base) jade_mayer in ~/projects/lhx/asset/work/sql λ shp2pgsql -a -s 32649 -I ../../data/electric/landusegrid.shp ucfsxxx.landuse > insertdata_ucfsxxx_landuse.sql

生成对insert语句摘要:

SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
INSERT INTO "ucfsxxx"."landuse" ("id","lutype","people","house",geom) VALUES ('0','road','0','0','SRID=32649;MULTIPOLYGON(((907360.083389083 3817074.50277449,907660.083389083 3817074.50277449,907660.083389083 3816774.50277449,907660.083389083 3816474.50277449,907660.083389083 3816174.50277449,907660.083389083 3815874.50277449,907660.083389083 3815574.50277449,907660.083389083 3815274.50277449,907660.083389083 3814974.50277449,907660.083389083 3814674.50277449,907660.083389083 3814374.50277449,907660.083389083 3814074.50277449,907660.083389083 3813774.50277449,907660.083389083 3813474.50277449,907660.083389083 3813174.50277449,907660.083389083 3812874.50277449,907660.083389083 3812574.50277449,907660.083389083 3812274.50277449,907660.083389083 3811974.50277449,907660.083389083 3811674.50277449,907660.083389083 3811374.50277449,907660.083389083 3811074.50277449,907660.083389083 3810774.50277449,907660.083389083 3810474.50277449,907660.083389083 3810174.50277449,907660.083389083 3809874.50277449,907660.083389083 3809574.50277449,907660.083389083 3809274.50277449,907660.083389083 3808974.50277449,907660.083389083 3808674.50277449,907660.083389083 3808374.50277449,907660.083389083 3808074.50277449,907660.083389083 3807774.50277449,907660.083389083 3807474.50277449,907660.083389083 3807174.50277449,907660.083389083 3806874.50277449,907660.083389083 3806574.50277449,907660.083389083 3806274.50277449,907660.083389083 3805974.50277449,907660.083389083 3805674.50277449,907660.083389083 3805374.50277449,907660.083389083 3805074.50277449,907660.083389083 3804774.50277449,907660.083389083 3804474.50277449,907660.083389083 3804174.50277449,907660.083389083 3803874.50277449,907660.083389083 3803574.50277449,907660.083389083 3803274.50277449,907660.083389083 3802974.50277449,907660.083389083 3802674.50277449,907660.083389083 3802374.50277449,907660.083389083 3802074.50277449,907660.083389083 3801774.50277449,907660.083389083 3801474.50277449,907660.083389083 3801174.50277449,907660.083389083 3800874.50277449,907660.083389083 3800574.50277449,907660.083389083 3800274.50277449,907660.083389083 3799974.50277449,907660.083389083 3799674.50277449,907660.083389083 3799374.50277449,907660.083389083 3799074.50277449,907660.083389083 3798774.50277449,907660.083389083 3798474.50277449,907660.083389083 3798174.50277449,907660.083389083 3797874.50277449,907660.083389083 3797574.50277449,907660.083389083 3797274.50277449,907660.083389083 3796974.50277449,907660.083389083 3796674.50277449,907660.083389083 3796374.50277449,907660.083389083 3796074.50277449,907660.083389083 3795774.50277449,907660.083389083 3795474.50277449,907660.083389083 3795174.50277449,907660.083389083 3794874.50277449,907660.083389083 3794574.50277449,907660.083389083 3794274.50277449,907360.083389083 3794274.50277449,907360.083389083 3794574.50277449,907360.083389083 3794874.50277449,907360.083389083 3795174.50277449,907360.083389083 3795474.50277449,907360.083389083 3795774.50277449,907360.083389083 3796074.50277449,907360.083389083 3796374.50277449,907360.083389083 3796674.50277449,907360.083389083 3796974.50277449,907360.083389083 3797274.50277449,907360.083389083 3797574.50277449,907360.083389083 3797874.50277449,907360.083389083 3798174.50277449,907360.083389083 3798474.50277449,907360.083389083 3798774.50277449,907360.083389083 3799074.50277449,907360.083389083 3799374.50277449,907360.083389083 3799674.50277449,907360.083389083 3799974.50277449,907360.083389083 3800274.50277449,907360.083389083 3800574.50277449,907360.083389083 3800874.50277449,907360.083389083 3801174.50277449,907360.083389083 3801474.50277449,907360.083389083 3801774.50277449,907360.083389083 3802074.50277449,907360.083389083 3802374.50277449,907360.083389083 3802674.50277449,907360.083389083 3802974.50277449,907360.083389083 3803274.50277449,907360.083389083 3803574.50277449,907360.083389083 3803874.50277449,907360.083389083 3804174.50277449,907360.083389083 3804474.50277449,907360.083389083 3804774.50277449,907360.083389083 3805074.50277449,907360.083389083 3805374.50277449,907360.083389083 3805674.50277449,907360.083389083 3805974.50277449,907360.083389083 3806274.50277449,907360.083389083 3806574.50277449,907360.083389083 3806874.50277449,907360.083389083 3807174.50277449,907360.083389083 3807474.50277449,907360.083389083 3807774.50277449,907360.083389083 3808074.50277449,907360.083389083 3808374.50277449,907360.083389083 3808674.50277449,907360.083389083 3808974.50277449,907360.083389083 3809274.50277449,907360.083389083 3809574.50277449,907360.083389083 3809874.50277449,907360.083389083 3810174.50277449,907360.083389083 3810474.50277449,907360.083389083 3810774.50277449,907360.083389083 3811074.50277449,907360.083389083 3811374.50277449,907360.083389083 3811674.50277449,907360.083389083 3811974.50277449,907360.083389083 3812274.50277449,907360.083389083 3812574.50277449,907360.083389083 3812874.50277449,907360.083389083 3813174.50277449,907360.083389083 3813474.50277449,907360.083389083 3813774.50277449,907360.083389083 3814074.50277449,907360.083389083 3814374.50277449,907360.083389083 3814674.50277449,907360.083389083 3814974.50277449,907360.083389083 3815274.50277449,907360.083389083 3815574.50277449,907360.083389083 3815874.50277449,907360.083389083 3816174.50277449,907360.083389083 3816474.50277449,907360.083389083 3816774.50277449,907360.083389083 3817074.50277449)))');
INSERT INTO "ucfsxxx"."landuse" ("id","lutype","people","house",geom) VALUES ('1','road','0','0','SRID=32649;MULTIPOLYGON(((895960.083389083 3809574.50277449,896260.083389083 3809574.50277449,896560.083389083 3809574.50277449,896860.083389083 3809574.50277449,897160.083389083 3809574.50277449,897460.083389083 3809574.50277449,897760.083389083 3809574.50277449,898060.083389083 3809574.50277449,898360.083389083 3809574.50277449,898660.083389083 3809574.50277449,898960.083389083 3809574.50277449,899260.083389083 3809574.50277449,899560.083389083 3809574.50277449,899860.083389083 3809574.50277449,900160.083389083 3809574.50277449,900460.083389083 3809574.50277449,900760.083389083 3809574.50277449,901060.083389083 3809574.50277449,901360.083389083 3809574.50277449,901660.083389083 3809574.50277449,901960.083389083 3809574.50277449,902260.083389083 3809574.50277449,902560.083389083 3809574.50277449,902860.083389083 3809574.50277449,903160.083389083 3809574.50277449,903460.083389083 3809574.50277449,903760.083389083 3809574.50277449,904060.083389083 3809574.50277449,904360.083389083 3809574.50277449,904660.083389083 3809574.50277449,904960.083389083 3809574.50277449,905260.083389083 3809574.50277449,905560.083389083 3809574.50277449,905860.083389083 3809574.50277449,906160.083389083 3809574.50277449,906460.083389083 3809574.50277449,906760.083389083 3809574.50277449,907060.083389083 3809574.50277449,907360.083389083 3809574.50277449,907360.083389083 3809274.50277449,907060.083389083 3809274.50277449,906760.083389083 3809274.50277449,906460.083389083 3809274.50277449,906160.083389083 3809274.50277449,905860.083389083 3809274.50277449,905560.083389083 3809274.50277449,905260.083389083 3809274.50277449,904960.083389083 3809274.50277449,904660.083389083 3809274.50277449,904360.083389083 3809274.50277449,904060.083389083 3809274.50277449,903760.083389083 3809274.50277449,903460.083389083 3809274.50277449,903160.083389083 3809274.50277449,902860.083389083 3809274.50277449,902560.083389083 3809274.50277449,902260.083389083 3809274.50277449,901960.083389083 3809274.50277449,901660.083389083 3809274.50277449,901360.083389083 3809274.50277449,901060.083389083 3809274.50277449,900760.083389083 3809274.50277449,900460.083389083 3809274.50277449,900160.083389083 3809274.50277449,899860.083389083 3809274.50277449,899560.083389083 3809274.50277449,899260.083389083 3809274.50277449,898960.083389083 3809274.50277449,898660.083389083 3809274.50277449,898360.083389083 3809274.50277449,898060.083389083 3809274.50277449,897760.083389083 3809274.50277449,897460.083389083 3809274.50277449,897160.083389083 3809274.50277449,896860.083389083 3809274.50277449,896560.083389083 3809274.50277449,896260.083389083 3809274.50277449,895960.083389083 3809274.50277449,895960.083389083 3809574.50277449)))');
INSERT INTO "ucfsxxx"."landuse" ("id","lutype","people","house",geom) VALUES ('2','road','0','0','SRID=32649;MULTIPOLYGON(((907660.083389083 3804774.50277449,907960.083389083 3804774.50277449,908260.083389083 3804774.50277449,908560.083389083 3804774.50277449,908860.083389083 3804774.50277449,909160.083389083 3804774.50277449,909460.083389083 3804774.50277449,909760.083389083 3804774.50277449,910060.083389083 3804774.50277449,910360.083389083 3804774.50277449,910660.083389083 3804774.50277449,910960.083389083 3804774.50277449,911260.083389083 3804774.50277449,911560.083389083 3804774.50277449,911860.083389083 3804774.50277449,912160.083389083 3804774.50277449,912460.083389083 3804774.50277449,912760.083389083 3804774.50277449,913060.083389083 3804774.50277449,913360.083389083 3804774.50277449,913660.083389083 3804774.50277449,913960.083389083 3804774.50277449,914260.083389083 3804774.50277449,914560.083389083 3804774.50277449,914860.083389083 3804774.50277449,915160.083389083 3804774.50277449,915460.083389083 3804774.50277449,915760.083389083 3804774.50277449,916060.083389083 3804774.50277449,916360.083389083 3804774.50277449,916360.083389083 3804474.50277449,916060.083389083 3804474.50277449,915760.083389083 3804474.50277449,915460.083389083 3804474.50277449,915160.083389083 3804474.50277449,914860.083389083 3804474.50277449,914560.083389083 3804474.50277449,914260.083389083 3804474.50277449,913960.083389083 3804474.50277449,913660.083389083 3804474.50277449,913360.083389083 3804474.50277449,913060.083389083 3804474.50277449,912760.083389083 3804474.50277449,912460.083389083 3804474.50277449,912160.083389083 3804474.50277449,911860.083389083 3804474.50277449,911560.083389083 3804474.50277449,911260.083389083 3804474.50277449,910960.083389083 3804474.50277449,910660.083389083 3804474.50277449,910360.083389083 3804474.50277449,910060.083389083 3804474.50277449,909760.083389083 3804474.50277449,909460.083389083 3804474.50277449,909160.083389083 3804474.50277449,908860.083389083 3804474.50277449,908560.083389083 3804474.50277449,908260.083389083 3804474.50277449,907960.083389083 3804474.50277449,907660.083389083 3804474.50277449,907660.083389083 3804774.50277449)))');
INSERT INTO "ucfsxxx"."landuse" ("id","lutype","people","house",geom) VALUES ('3','road','0','0','SRID=32649;MULTIPOLYGON(((901060.083389083 3809274.50277449,901360.083389083 3809274.50277449,901360.083389083 3808974.50277449,901660.083389083 3808974.50277449,901660.083389083 3808674.50277449,901960.083389083 3808674.50277449,901960.083389083 3808374.50277449,902260.083389083 3808374.50277449,902260.083389083 3808074.50277449,902560.083389083 3808074.50277449,902560.083389083 3807774.50277449,902860.083389083 3807774.50277449,902860.083389083 3807474.50277449,903160.083389083 3807474.50277449,903160.083389083 3807174.50277449,903460.083389083 3807174.50277449,903460.083389083 3806874.50277449,903760.083389083 3806874.50277449,903760.083389083 3806574.50277449,904060.083389083 3806574.50277449,904060.083389083 3806274.50277449,904360.083389083 3806274.50277449,904360.083389083 3805974.50277449,904660.083389083 3805974.50277449,904660.083389083 3805674.50277449,904960.083389083 3805674.50277449,904960.083389083 3805374.50277449,905260.083389083 3805374.50277449,905260.083389083 3805074.50277449,905560.083389083 3805074.50277449,905560.083389083 3804774.50277449,905860.083389083 3804774.50277449,905860.083389083 3804474.50277449,906160.083389083 3804474.50277449,906160.083389083 3804174.50277449,906460.083389083 3804174.50277449,906460.083389083 3803874.50277449,906760.083389083 3803874.50277449,906760.083389083 3803574.50277449,907060.083389083 3803574.50277449,907360.083389083 3803574.50277449,907360.083389083 3803274.50277449,907360.083389083 3802974.50277449,907060.083389083 3802974.50277449,906760.083389083 3802974.50277449,906760.083389083 3803274.50277449,906460.083389083 3803274.50277449,906460.083389083 3803574.50277449,906160.083389083 3803574.50277449,906160.083389083 3803874.50277449,905860.083389083 3803874.50277449,905860.083389083 3804174.50277449,905560.083389083 3804174.50277449,905560.083389083 3804474.50277449,905260.083389083 3804474.50277449,905260.083389083 3804774.50277449,904960.083389083 3804774.50277449,904960.083389083 3805074.50277449,904660.083389083 3805074.50277449,904660.083389083 3805374.50277449,904360.083389083 3805374.50277449,904360.083389083 3805674.50277449,904060.083389083 3805674.50277449,904060.083389083 3805974.50277449,903760.083389083 3805974.50277449,903760.083389083 3806274.50277449,903460.083389083 3806274.50277449,903460.083389083 3806574.50277449,903160.083389083 3806574.50277449,903160.083389083 3806874.50277449,902860.083389083 3806874.50277449,902860.083389083 3807174.50277449,902560.083389083 3807174.50277449,902560.083389083 3807474.50277449,902260.083389083 3807474.50277449,902260.083389083 3807774.50277449,901960.083389083 3807774.50277449,901960.083389083 3808074.50277449,901660.083389083 3808074.50277449,901360.083389083 3808074.50277449,901360.083389083 3808374.50277449,901360.083389083 3808674.50277449,901060.083389083 3808674.50277449,901060.083389083 3808974.50277449,901060.083389083 3809274.50277449)))');
INSERT INTO "ucfsxxx"."landuse" ("id","lutype","people","house",geom) VALUES ('4','road','0','0','SRID=32649;MULTIPOLYGON(((910960.083389083 3811074.50277449,911260.083389083 3811074.50277449,911260.083389083 3810774.50277449,911260.083389083 3810474.50277449,911260.083389083 3810174.50277449,911260.083389083 3809874.50277449,911260.083389083 3809574.50277449,911260.083389083 3809274.50277449,911260.083389083 3808974.50277449,911260.083389083 3808674.50277449,911260.083389083 3808374.50277449,911260.083389083 3808074.50277449,911260.083389083 3807774.50277449,911260.083389083 3807474.50277449,911260.083389083 3807174.50277449,911260.083389083 3806874.50277449,911260.083389083 3806574.50277449,911260.083389083 3806274.50277449,911260.083389083 3805974.50277449,911260.083389083 3805674.50277449,911260.083389083 3805374.50277449,911260.083389083 3805074.50277449,911260.083389083 3804774.50277449,910960.083389083 3804774.50277449,910960.083389083 3805074.50277449,910960.083389083 3805374.50277449,910960.083389083 3805674.50277449,910960.083389083 3805974.50277449,910960.083389083 3806274.50277449,910960.083389083 3806574.50277449,910960.083389083 3806874.50277449,910960.083389083 3807174.50277449,910960.083389083 3807474.50277449,910960.083389083 3807774.50277449,910960.083389083 3808074.50277449,910960.083389083 3808374.50277449,910960.083389083 3808674.50277449,910960.083389083 3808974.50277449,910960.083389083 3809274.50277449,910960.083389083 3809574.50277449,910960.083389083 3809874.50277449,910960.083389083 3810174.50277449,910960.083389083 3810474.50277449,910960.083389083 3810774.50277449,910960.083389083 3811074.50277449)))');
INSERT INTO "ucfsxxx"."landuse" ("id","lutype","people","house",geom) VALUES ('5','road','0','0','SRID=32649;MULTIPOLYGON(((907660.083389083 3799974.50277449,907960.083389083 3799974.50277449,908260.083389083 3799974.50277449,908560.083389083 3799974.50277449,908860.083389083 3799974.50277449,909160.083389083 3799974.50277449,909460.083389083 3799974.50277449,909760.083389083 3799974.50277449,910060.083389083 3799974.50277449,910360.083389083 3799974.50277449,910660.083389083 3799974.50277449,910960.083389083 3799974.50277449,911260.083389083 3799974.50277449,911560.083389083 3799974.50277449,911860.083389083 3799974.50277449,912160.083389083 3799974.50277449,912460.083389083 3799974.50277449,912760.083389083 3799974.50277449,913060.083389083 3799974.50277449,913360.083389083 3799974.50277449,913660.083389083 3799974.50277449,913960.083389083 3799974.50277449,914260.083389083 3799974.50277449,914560.083389083 3799974.50277449,914860.083389083 3799974.50277449,915160.083389083 3799974.50277449,915460.083389083 3799974.50277449,915760.083389083 3799974.50277449,915760.083389083 3799674.50277449,915460.083389083 3799674.50277449,915160.083389083 3799674.50277449,914860.083389083 3799674.50277449,914560.083389083 3799674.50277449,914260.083389083 3799674.50277449,913960.083389083 3799674.50277449,913660.083389083 3799674.50277449,913360.083389083 3799674.50277449,913060.083389083 3799674.50277449,912760.083389083 3799674.50277449,912460.083389083 3799674.50277449,912160.083389083 3799674.50277449,911860.083389083 3799674.50277449,911560.083389083 3799674.50277449,911260.083389083 3799674.50277449,910960.083389083 3799674.50277449,910660.083389083 3799674.50277449,910360.083389083 3799674.50277449,910060.083389083 3799674.50277449,909760.083389083 3799674.50277449,909460.083389083 3799674.50277449,909160.083389083 3799674.50277449,908860.083389083 3799674.50277449,908560.083389083 3799674.50277449,908260.083389083 3799674.50277449,907960.083389083 3799674.50277449,907660.083389083 3799674.50277449,907660.083389083 3799974.50277449)))');
INSERT INTO "ucfsxxx"."landuse" ("id","lutype","people","house",geom) VALUES ('6','road','0','0','SRID=32649;MULTIPOLYGON(((898060.083389083 3805974.50277449,898360.083389083 3805974.50277449,898660.083389083 3805974.50277449,898960.083389083 3805974.50277449,899260.083389083 3805974.50277449,899560.083389083 3805974.50277449,899860.083389083 3805974.50277449,900160.083389083 3805974.50277449,900460.083389083 3805974.50277449,900760.083389083 3805974.50277449,901060.083389083 3805974.50277449,901360.083389083 3805974.50277449,901660.083389083 3805974.50277449,901960.083389083 3805974.50277449,902260.083389083 3805974.50277449,902560.083389083 3805974.50277449,902860.083389083 3805974.50277449,903160.083389083 3805974.50277449,903460.083389083 3805974.50277449,903760.083389083 3805974.50277449,904060.083389083 3805974.50277449,904060.083389083 3805674.50277449,903760.083389083 3805674.50277449,903460.083389083 3805674.50277449,903160.083389083 3805674.50277449,902860.083389083 3805674.50277449,902560.083389083 3805674.50277449,902260.083389083 3805674.50277449,901960.083389083 3805674.50277449,901660.083389083 3805674.50277449,901360.083389083 3805674.50277449,901060.083389083 3805674.50277449,900760.083389083 3805674.50277449,900460.083389083 3805674.50277449,900160.083389083 3805674.50277449,899860.083389083 3805674.50277449,899560.083389083 3805674.50277449,899260.083389083 3805674.50277449,898960.083389083 3805674.50277449,898660.083389083 3805674.50277449,898360.083389083 3805674.50277449,898060.083389083 3805674.50277449,898060.083389083 3805974.50277449)))');
INSERT INTO "ucfsxxx"."landuse" ("id","lutype","people","house",geom) VALUES ('7','road','0','0','SRID=32649;MULTIPOLYGON(((904060.083389083 3805674.50277449,904360.083389083 3805674.50277449,904360.083389083 3805374.50277449,904360.083389083 3805074.50277449,904360.083389083 3804774.50277449,904360.083389083 3804474.50277449,904360.083389083 3804174.50277449,904360.083389083 3803874.50277449,904360.083389083 3803574.50277449,904360.083389083 3803274.50277449,904360.083389083 3802974.50277449,904360.083389083 3802674.50277449,904360.083389083 3802374.50277449,904360.083389083 3802074.50277449,904360.083389083 3801774.50277449,904360.083389083 3801474.50277449,904360.083389083 3801174.50277449,904360.083389083 3800874.50277449,904360.083389083 3800574.50277449,904360.083389083 3800274.50277449,904360.083389083 3799974.50277449,904360.083389083 3799674.50277449,904360.083389083 3799374.50277449,904360.083389083 3799074.50277449,904360.083389083 3798774.50277449,904360.083389083 3798474.50277449,904360.083389083 3798174.50277449,904360.083389083 3797874.50277449,904360.083389083 3797574.50277449,904360.083389083 3797274.50277449,904060.083389083 3797274.50277449,904060.083389083 3797574.50277449,904060.083389083 3797874.50277449,904060.083389083 3798174.50277449,904060.083389083 3798474.50277449,904060.083389083 3798774.50277449,904060.083389083 3799074.50277449,904060.083389083 3799374.50277449,904060.083389083 3799674.50277449,904060.083389083 3799974.50277449,904060.083389083 3800274.50277449,904060.083389083 3800574.50277449,904060.083389083 3800874.50277449,904060.083389083 3801174.50277449,904060.083389083 3801474.50277449,904060.083389083 3801774.50277449,904060.083389083 3802074.50277449,904060.083389083 3802374.50277449,904060.083389083 3802674.50277449,904060.083389083 3802974.50277449,904060.083389083 3803274.50277449,904060.083389083 3803574.50277449,904060.083389083 3803874.50277449,904060.083389083 3804174.50277449,904060.083389083 3804474.50277449,904060.083389083 3804774.50277449,904060.083389083 3805074.50277449,904060.083389083 3805374.50277449,904060.083389083 3805674.50277449)))');
INSERT INTO "ucfsxxx"."landuse" ("id","lutype","people","house",geom) VALUES ('8','residential','670','67','SRID=32649;MULTIPOLYGON(((905860.083389083 3811674.50277449,906160.083389083 3811674.50277449,906460.083389083 3811674.50277449,906760.083389083 3811674.50277449,907060.083389083 3811674.50277449,907360.083389083 3811674.50277449,907360.083389083 3811374.50277449,907360.083389083 3811074.50277449,907360.083389083 3810774.50277449,907360.083389083 3810474.50277449,907360.083389083 3810174.50277449,907360.083389083 3809874.50277449,907360.083389083 3809574.50277449,907060.083389083 3809574.50277449,906760.083389083 3809574.50277449,906460.083389083 3809574.50277449,906160.083389083 3809574.50277449,905860.083389083 3809574.50277449,905860.083389083 3809874.50277449,905860.083389083 3810174.50277449,905860.083389083 3810474.50277449,905860.083389083 3810774.50277449,905860.083389083 3811074.50277449,905860.083389083 3811374.50277449,905860.083389083 3811674.50277449)))');
INSERT INTO "ucfsxxx"."landuse" ("id","lutype","people","house",geom) VALUES ('9','residential','1830','183','SRID=32649;MULTIPOLYGON(((909760.083389083 3806874.50277449,910060.083389083 3806874.50277449,910360.083389083 3806874.50277449,910360.083389083 3807174.50277449,910660.083389083 3807174.50277449,910960.083389083 3807174.50277449,910960.083389083 3806874.50277449,910960.083389083 3806574.50277449,910960.083389083 3806274.50277449,910960.083389083 3805974.50277449,910960.083389083 3805674.50277449,910960.083389083 3805374.50277449,910960.083389083 3805074.50277449,910960.083389083 3804774.50277449,910660.083389083 3804774.50277449,910360.083389083 3804774.50277449,910060.083389083 3804774.50277449,909760.083389083 3804774.50277449,909760.083389083 3805074.50277449,909760.083389083 3805374.50277449,909760.083389083 3805674.50277449,909760.083389083 3805974.50277449,909760.083389083 3806274.50277449,909760.083389083 3806574.50277449,909760.083389083 3806874.50277449)))');
INSERT INTO "ucfsxxx"."landuse" ("id","lutype","people","house",geom) VALUES ('10','residential','720','72','SRID=32649;MULTIPOLYGON(((901660.083389083 3805674.50277449,901960.083389083 3805674.50277449,902260.083389083 3805674.50277449,902560.083389083 3805674.50277449,902860.083389083 3805674.50277449,903160.083389083 3805674.50277449,903460.083389083 3805674.50277449,903760.083389083 3805674.50277449,904060.083389083 3805674.50277449,904060.083389083 3805374.50277449,904060.083389083 3805074.50277449,904060.083389083 3804774.50277449,904060.083389083 3804474.50277449,904060.083389083 3804174.50277449,904060.083389083 3803874.50277449,904060.083389083 3803574.50277449,904060.083389083 3803274.50277449,903760.083389083 3803274.50277449,903460.083389083 3803274.50277449,903160.083389083 3803274.50277449,902860.083389083 3803274.50277449,902560.083389083 3803274.50277449,902560.083389083 3802974.50277449,902260.083389083 3802974.50277449,901960.083389083 3802974.50277449,901660.083389083 3802974.50277449,901660.083389083 3803274.50277449,901660.083389083 3803574.50277449,901660.083389083 3803874.50277449,901660.083389083 3804174.50277449,901660.083389083 3804474.50277449,901660.083389083 3804774.50277449,901660.083389083 3805074.50277449,901660.083389083 3805374.50277449,901660.083389083 3805674.50277449)))');
INSERT INTO "ucfsxxx"."landuse" ("id","lutype","people","house",geom) VALUES ('11','residential','1620','162','SRID=32649;MULTIPOLYGON(((907660.083389083 3802374.50277449,907960.083389083 3802374.50277449,908260.083389083 3802374.50277449,908560.083389083 3802374.50277449,908860.083389083 3802374.50277449,908860.083389083 3802074.50277449,908860.083389083 3801774.50277449,909160.083389083 3801774.50277449,909160.083389083 3801474.50277449,909160.083389083 3801174.50277449,909160.083389083 3800874.50277449,909160.083389083 3800574.50277449,909160.083389083 3800274.50277449,909160.083389083 3799974.50277449,908860.083389083 3799974.50277449,908560.083389083 3799974.50277449,908260.083389083 3799974.50277449,907960.083389083 3799974.50277449,907660.083389083 3799974.50277449,907660.083389083 3800274.50277449,907660.083389083 3800574.50277449,907660.083389083 3800874.50277449,907660.083389083 3801174.50277449,907660.083389083 3801474.50277449,907660.083389083 3801774.50277449,907660.083389083 3802074.50277449,907660.083389083 3802374.50277449)))');
INSERT INTO "ucfsxxx"."landuse" ("id","lutype","people","house",geom) VALUES ('12','residential','680','68','SRID=32649;MULTIPOLYGON(((898360.083389083 3813174.50277449,898660.083389083 3813174.50277449,898960.083389083 3813174.50277449,899260.083389083 3813174.50277449,899260.083389083 3812874.50277449,899260.083389083 3812574.50277449,899560.083389083 3812574.50277449,899560.083389083 3812274.50277449,899560.083389083 3811974.50277449,899560.083389083 3811674.50277449,899560.083389083 3811374.50277449,899560.083389083 3811074.50277449,899260.083389083 3811074.50277449,898960.083389083 3811074.50277449,898660.083389083 3811074.50277449,898360.083389083 3811074.50277449,898360.083389083 3811374.50277449,898360.083389083 3811674.50277449,898360.083389083 3811974.50277449,898360.083389083 3812274.50277449,898360.083389083 3812574.50277449,898360.083389083 3812874.50277449,898360.083389083 3813174.50277449)))');
INSERT INTO "ucfsxxx"."landuse" ("id","lutype","people","house",geom) VALUES ('13','residential','1930','193','SRID=32649;MULTIPOLYGON(((904960.083389083 3800574.50277449,905260.083389083 3800574.50277449,905560.083389083 3800574.50277449,905860.083389083 3800574.50277449,906160.083389083 3800574.50277449,906160.083389083 3800274.50277449,906160.083389083 3799974.50277449,906160.083389083 3799674.50277449,906460.083389083 3799674.50277449,906460.083389083 3799374.50277449,906460.083389083 3799074.50277449,906460.083389083 3798774.50277449,906460.083389083 3798474.50277449,906460.083389083 3798174.50277449,906460.083389083 3797874.50277449,906160.083389083 3797874.50277449,905860.083389083 3797874.50277449,905560.083389083 3797874.50277449,905260.083389083 3797874.50277449,904960.083389083 3797874.50277449,904960.083389083 3798174.50277449,904960.083389083 3798474.50277449,904960.083389083 3798774.50277449,904960.083389083 3799074.50277449,904960.083389083 3799374.50277449,904960.083389083 3799674.50277449,904960.083389083 3799974.50277449,904960.083389083 3800274.50277449,904960.083389083 3800574.50277449)))');
INSERT INTO "ucfsxxx"."landuse" ("id","lutype","people","house",geom) VALUES ('14','residential','1160','116','SRID=32649;MULTIPOLYGON(((911860.083389083 3802674.50277449,912160.083389083 3802674.50277449,912460.083389083 3802674.50277449,912760.083389083 3802674.50277449,912760.083389083 3802374.50277449,912760.083389083 3802074.50277449,912760.083389083 3801774.50277449,912760.083389083 3801474.50277449,912760.083389083 3801174.50277449,912760.083389083 3800874.50277449,912460.083389083 3800874.50277449,912160.083389083 3800874.50277449,911860.083389083 3800874.50277449,911860.083389083 3801174.50277449,911860.083389083 3801474.50277449,911860.083389083 3801774.50277449,911860.083389083 3802074.50277449,911860.083389083 3802374.50277449,911860.083389083 3802674.50277449)))');
INSERT INTO "ucfsxxx"."landuse" ("id","lutype","people","house",geom) VALUES ('15','residential','800','80','SRID=32649;MULTIPOLYGON(((913360.083389083 3813174.50277449,913660.083389083 3813174.50277449,913960.083389083 3813174.50277449,913960.083389083 3812874.50277449,914260.083389083 3812874.50277449,914260.083389083 3812574.50277449,914260.083389083 3812274.50277449,914260.083389083 3811974.50277449,914260.083389083 3811674.50277449,913960.083389083 3811674.50277449,913660.083389083 3811674.50277449,913660.083389083 3811974.50277449,913660.083389083 3812274.50277449,913660.083389083 3812574.50277449,913360.083389083 3812574.50277449,913360.083389083 3812874.50277449,913360.083389083 3813174.50277449)))');
INSERT INTO "ucfsxxx"."landuse" ("id","lutype","people","house",geom) VALUES ('16','residential','520','52','SRID=32649;MULTIPOLYGON(((898060.083389083 3802374.50277449,898360.083389083 3802374.50277449,898660.083389083 3802374.50277449,898960.083389083 3802374.50277449,898960.083389083 3802074.50277449,898960.083389083 3801774.50277449,898960.083389083 3801474.50277449,898960.083389083 3801174.50277449,898960.083389083 3800874.50277449,898960.083389083 3800574.50277449,898960.083389083 3800274.50277449,898960.083389083 3799974.50277449,898960.083389083 3799674.50277449,898960.083389083 3799374.50277449,898660.083389083 3799374.50277449,898360.083389083 3799374.50277449,898060.083389083 3799374.50277449,898060.083389083 3799674.50277449,898060.083389083 3799974.50277449,898060.083389083 3800274.50277449,898060.083389083 3800574.50277449,898060.083389083 3800874.50277449,898060.083389083 3801174.50277449,898060.083389083 3801474.50277449,898060.083389083 3801774.50277449,898060.083389083 3802074.50277449,898060.083389083 3802374.50277449)))');
INSERT INTO "ucfsxxx"."landuse" ("id","lutype","people","house",geom) VALUES ('59','facilities','0','30','SRID=32649;MULTIPOLYGON(((905860.083389083 3803874.50277449,906160.083389083 3803874.50277449,906160.083389083 3803574.50277449,906460.083389083 3803574.50277449,906460.083389083 3803274.50277449,906760.083389083 3803274.50277449,906760.083389083 3802974.50277449,907060.083389083 3802974.50277449,907360.083389083 3802974.50277449,907360.083389083 3802674.50277449,907360.083389083 3802374.50277449,907360.083389083 3802074.50277449,907360.083389083 3801774.50277449,907360.083389083 3801474.50277449,907360.083389083 3801174.50277449,907360.083389083 3800874.50277449,907360.083389083 3800574.50277449,907360.083389083 3800274.50277449,907360.083389083 3799974.50277449,907360.083389083 3799674.50277449,907360.083389083 3799374.50277449,907360.083389083 3799074.50277449,907360.083389083 3798774.50277449,907360.083389083 3798474.50277449,907360.083389083 3798174.50277449,907060.083389083 3798174.50277449,906760.083389083 3798174.50277449,906460.083389083 3798174.50277449,906460.083389083 3798474.50277449,906460.083389083 3798774.50277449,906460.083389083 3799074.50277449,906460.083389083 3799374.50277449,906460.083389083 3799674.50277449,906160.083389083 3799674.50277449,906160.083389083 3799974.50277449,906160.083389083 3800274.50277449,906160.083389083 3800574.50277449,905860.083389083 3800574.50277449,905860.083389083 3800874.50277449,905860.083389083 3801174.50277449,905860.083389083 3801474.50277449,905860.083389083 3801774.50277449,905860.083389083 3802074.50277449,905860.083389083 3802374.50277449,905860.083389083 3802674.50277449,905860.083389083 3802974.50277449,905860.083389083 3803274.50277449,905860.083389083 3803574.50277449,905860.083389083 3803874.50277449)))');
CREATE INDEX ON "ucfsxxx"."landuse" USING GIST ("geom");
COMMIT;
ANALYZE "ucfsxxx"."landuse";

(base) jade_mayer in ~/projects/lhx/asset/work/sql λ psql -d electric -f insertdata_ucfsxxx_landuse.sql

2) corridors

(base) ╭─jade_mayer@jadedeMacBook-Pro.local ~/projects/lhx/asset/work/sql  

╰─➤  shp2pgsql -a -s 32649 -I ../../data/electric/corridor.shp ucfsxxx.corridors > insertdata_ucfsxxx_corridors.sql

SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
INSERT INTO "ucfsxxx"."corridors" ("id",geom) VALUES ('1','SRID=32649;MULTIPOLYGON(((900431.401168016 3813724.43093568,900842.746310664 3813792.10039208,905364.630899904 3795973.03142384,904998.230582148 3795807.27044294,900431.401168016 3813724.43093568)))');
INSERT INTO "ucfsxxx"."corridors" ("id",geom) VALUES ('2','SRID=32649;MULTIPOLYGON(((915682.677089411 3811768.91410345,915824.576420721 3811375.40355647,901314.891396027 3800395.72725539,901070.031294653 3800784.97004961,915682.677089411 3811768.91410345)))');
INSERT INTO "ucfsxxx"."corridors" ("id",geom) VALUES ('3','SRID=32649;MULTIPOLYGON(((908251.791492791 3808741.76251186,908680.760876266 3808885.48568314,912085.442105027 3795116.8567566,911679.841975661 3794899.12614235,908251.791492791 3808741.76251186)))');
CREATE INDEX ON "ucfsxxx"."corridors" USING GIST ("geom");
COMMIT;
ANALYZE "ucfsxxx"."corridors";

(base) ╭─jade_mayer@jadedeMacBook-Pro.local ~/projects/lhx/asset/work/sql  

╰─➤  psql -d electric -f insertdata_ucfsxxx_corridors.sql 

3) wires

(base) ╭─jade_mayer@jadedeMacBook-Pro.local ~/projects/lhx/asset/work/sql  

╰─➤  shp2pgsql -a -s 32649 -I ../../data/electric/lines.shp ucfsxxx.wires > insertdata_ucfsxxx_wires.sql    

Shapefile type: Arc

Postgis type: MULTILINESTRING[2]

SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
INSERT INTO "ucfsxxx"."wires" ("id","texture","lparallel",geom) VALUES ('0','A','4','SRID=32649;MULTILINESTRING((900636.5535975 3813639.75413615,905165.736008584 3796005.32318679))');
INSERT INTO "ucfsxxx"."wires" ("id","texture","lparallel",geom) VALUES ('1','C','8','SRID=32649;MULTILINESTRING((901239.522308813 3800617.15255198,915731.165868238 3811551.7556298))');
INSERT INTO "ucfsxxx"."wires" ("id","texture","lparallel",geom) VALUES ('2','B','8','SRID=32649;MULTILINESTRING((908484.344745666 3808740.09140699,911884.424397029 3795029.9954808))');
CREATE INDEX ON "ucfsxxx"."wires" USING GIST ("geom");
COMMIT;
ANALYZE "ucfsxxx"."wires";

(base) ╭─jade_mayer@jadedeMacBook-Pro.local ~/projects/lhx/asset/work/sql  

╰─➤  psql -d electric -f insertdata_ucfsxxx_wires.sql    

SET

SET

BEGIN

INSERT 0 1

INSERT 0 1

INSERT 0 1

CREATE INDEX

COMMIT

ANALYZE

4) towers

(base) ╭─jade_mayer@jadedeMacBook-Pro.local ~/projects/lhx/asset/work/sql  

╰─➤  shp2pgsql -a -s 32649 -I ../../data/electric/tower.shp ucfsxxx.towers > insertdata_ucfsxxx_towers.sql

towers的外键为wires的id值，shp2pgsql工具按照shapefile文件中属性表对外键硬编码。

SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('0','10.000000000000000','Q460','0.257226169236944','2','SRID=32649;POINT(910229.357570035 3801703.69941984)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('1','10.000000000000000','Q460','0.595001890620853','2','SRID=32649;POINT(909921.922163612 3802943.36711747)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('2','10.000000000000000','Q460','0.926653365085727','2','SRID=32649;POINT(909550.742251734 3804440.07093606)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('3','10.000000000000000','Q235','0.107097002532980','0','SRID=32649;POINT(902228.122071123 3807442.96146986)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('4','10.000000000000000','Q460','0.764574360166533','2','SRID=32649;POINT(910524.897615313 3800511.99722863)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('5','10.000000000000000','Q345','0.876450905215033','1','SRID=32649;POINT(907998.615388773 3805717.19518637)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('6','10.000000000000000','Q345','0.585417619254082','1','SRID=32649;POINT(912085.885739774 3808801.22625411)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('7','10.000000000000000','Q235','0.803980161920522','0','SRID=32649;POINT(904654.830359787 3797994.54101191)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('8','10.000000000000000','Q345','0.335692673080407','1','SRID=32649;POINT(903392.492779184 3802241.66653035)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('9','10.000000000000000','Q235','0.975697769202892','0','SRID=32649;POINT(903354.625993738 3803056.90371495)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('10','10.000000000000000','Q345','0.797543748209514','1','SRID=32649;POINT(905741.508778961 3804014.10576236)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('11','10.000000000000000','Q235','0.725244938598841','0','SRID=32649;POINT(904476.446488924 3798689.08094599)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('12','10.000000000000000','Q235','0.561460984883780','0','SRID=32649;POINT(901739.861019738 3809344.01225881)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('13','10.000000000000000','Q460','0.373695482550835','2','SRID=32649;POINT(909081.693896087 3806331.40837792)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('14','10.000000000000000','Q235','0.011268869407585','0','SRID=32649;POINT(903956.749712202 3800712.52714265)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('15','10.000000000000000','Q345','0.429739097158447','1','SRID=32649;POINT(902860.739354471 3801840.43442643)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('16','10.000000000000000','Q235','0.629686752824783','0','SRID=32649;POINT(901611.976829065 3809841.93102765)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('17','10.000000000000000','Q460','0.694773740151728','2','SRID=32649;POINT(908735.009378961 3807729.33974227)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('18','10.000000000000000','Q460','0.459200839399469','2','SRID=32649;POINT(908813.83002354 3807411.51230617)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('117','10.000000000000000','Q345','0.490495322984722','1','SRID=32649;POINT(905990.108671286 3804201.68566912)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('118','10.000000000000000','Q460','0.969076490333167','2','SRID=32649;POINT(910987.845876406 3798645.25711685)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('119','10.000000000000000','Q460','0.621726461284831','2','SRID=32649;POINT(909629.413066582 3804122.84765639)');
CREATE INDEX ON "ucfsxxx"."towers" USING GIST ("geom");
COMMIT;
ANALYZE "ucfsxxx"."towers";

如果是手工编写insert语句插入数据的话，最好通过嵌套查询来插入外键，避免硬编码带来的数据不一致。

SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('0','10.000000000000000','Q460','0.257226169236944', (select id from "ucfsxxx"."wires" where "texture" = 'B'),'SRID=32649;POINT(910229.357570035 3801703.69941984)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('1','10.000000000000000','Q460','0.595001890620853',(select id from "ucfsxxx"."wires" where "texture" = 'B'),'SRID=32649;POINT(909921.922163612 3802943.36711747)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('2','10.000000000000000','Q460','0.926653365085727',(select id from "ucfsxxx"."wires" where "texture" = 'B'),'SRID=32649;POINT(909550.742251734 3804440.07093606)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('3','10.000000000000000','Q235','0.107097002532980',(select id from "ucfsxxx"."wires" where "texture" = 'A'),'SRID=32649;POINT(902228.122071123 3807442.96146986)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('4','10.000000000000000','Q460','0.764574360166533',(select id from "ucfsxxx"."wires" where "texture" = 'B'),'SRID=32649;POINT(910524.897615313 3800511.99722863)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('5','10.000000000000000','Q345','0.876450905215033',(select id from "ucfsxxx"."wires" where "texture" = 'C'),'SRID=32649;POINT(907998.615388773 3805717.19518637)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('6','10.000000000000000','Q345','0.585417619254082',(select id from "ucfsxxx"."wires" where "texture" = 'C'),'SRID=32649;POINT(912085.885739774 3808801.22625411)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('7','10.000000000000000','Q235','0.803980161920522',(select id from "ucfsxxx"."wires" where "texture" = 'A'),'SRID=32649;POINT(904654.830359787 3797994.54101191)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('8','10.000000000000000','Q345','0.335692673080407',(select id from "ucfsxxx"."wires" where "texture" = 'C'),'SRID=32649;POINT(903392.492779184 3802241.66653035)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('9','10.000000000000000','Q235','0.975697769202892',(select id from "ucfsxxx"."wires" where "texture" = 'A'),'SRID=32649;POINT(903354.625993738 3803056.90371495)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('10','10.000000000000000','Q345','0.797543748209514',(select id from "ucfsxxx"."wires" where "texture" = 'C'),'SRID=32649;POINT(905741.508778961 3804014.10576236)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('11','10.000000000000000','Q235','0.725244938598841',(select id from "ucfsxxx"."wires" where "texture" = 'A'),'SRID=32649;POINT(904476.446488924 3798689.08094599)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('12','10.000000000000000','Q235','0.561460984883780',(select id from "ucfsxxx"."wires" where "texture" = 'A'),'SRID=32649;POINT(901739.861019738 3809344.01225881)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('13','10.000000000000000','Q460','0.373695482550835',(select id from "ucfsxxx"."wires" where "texture" = 'B'),'SRID=32649;POINT(909081.693896087 3806331.40837792)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('14','10.000000000000000','Q235','0.011268869407585',(select id from "ucfsxxx"."wires" where "texture" = 'A'),'SRID=32649;POINT(903956.749712202 3800712.52714265)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('15','10.000000000000000','Q345','0.429739097158447',(select id from "ucfsxxx"."wires" where "texture" = 'C'),'SRID=32649;POINT(902860.739354471 3801840.43442643)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('16','10.000000000000000','Q235','0.629686752824783',(select id from "ucfsxxx"."wires" where "texture" = 'A'),'SRID=32649;POINT(901611.976829065 3809841.93102765)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('17','10.000000000000000','Q460','0.694773740151728',(select id from "ucfsxxx"."wires" where "texture" = 'B'),'SRID=32649;POINT(908735.009378961 3807729.33974227)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('18','10.000000000000000','Q460','0.459200839399469',(select id from "ucfsxxx"."wires" where "texture" = 'B'),'SRID=32649;POINT(908813.83002354 3807411.51230617)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('117','10.000000000000000','Q345','0.490495322984722',(select id from "ucfsxxx"."wires" where "texture" = 'C'),'SRID=32649;POINT(905990.108671286 3804201.68566912)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('118','10.000000000000000','Q460','0.969076490333167',(select id from "ucfsxxx"."wires" where "texture" = 'B'),'SRID=32649;POINT(910987.845876406 3798645.25711685)');
INSERT INTO "ucfsxxx"."towers" ("id","height","texture","corrosion","wires_id",geom) VALUES ('119','10.000000000000000','Q460','0.621726461284831',(select id from "ucfsxxx"."wires" where "texture" = 'B'),'SRID=32649;POINT(909629.413066582 3804122.84765639)');
CREATE INDEX ON "ucfsxxx"."towers" USING GIST ("geom");
COMMIT;
ANALYZE "ucfsxxx"."towers";

(base) ╭─jade_mayer@jadedeMacBook-Pro.local ~/projects/lhx/asset/work/sql  

╰─➤  psql -d electric -f insertdata_ucfsxxx_towers.sql   

5) trees

(base) ╭─jade_mayer@jadedeMacBook-Pro.local ~/projects/lhx/asset/work/sql  

╰─➤  shp2pgsql -a -s 32649 -I ../../data/electric/trees.shp ucfsxxx.trees > insertdata_ucfsxxx_trees.sql 

SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('0','A','64','SRID=32649;POINT(900318.162002609 3817002.02541447)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('1','E','19','SRID=32649;POINT(915812.748228274 3812925.09470808)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('2','A','45','SRID=32649;POINT(921340.465379076 3806349.4071055)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('3','B','47','SRID=32649;POINT(913123.432593348 3807051.11660264)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('4','A','81','SRID=32649;POINT(908761.996994007 3803197.69918918)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('5','D','32','SRID=32649;POINT(898821.913470232 3804978.48337123)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('6','A','26','SRID=32649;POINT(900288.79030073 3809749.09513203)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('7','E','13','SRID=32649;POINT(906779.929679673 3803423.67810228)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('8','A','58','SRID=32649;POINT(916795.790977947 3795380.48832359)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('9','B','19','SRID=32649;POINT(916514.829478604 3801207.53789508)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('10','A','10','SRID=32649;POINT(901496.886684177 3800680.23194214)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('336','A','82','SRID=32649;POINT(900573.796004189 3804660.60374422)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('337','E','92','SRID=32649;POINT(916617.751402311 3810361.98837194)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('338','A','99','SRID=32649;POINT(896577.503503247 3814452.15364327)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('339','B','42','SRID=32649;POINT(918687.398280973 3802817.63757262)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('340','A','11','SRID=32649;POINT(921228.053358628 3813259.48151093)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('341','E','14','SRID=32649;POINT(895987.271688811 3813298.90411164)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('342','A','57','SRID=32649;POINT(902057.494825574 3801901.48065478)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('343','E','85','SRID=32649;POINT(918316.541879209 3794780.22805749)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('344','A','95','SRID=32649;POINT(906790.162934799 3796232.10917634)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('1163','E','73','SRID=32649;POINT(911618.507128729 3796312.82073766)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('1164','A','25','SRID=32649;POINT(911637.168407266 3795987.57893649)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('1165','D','27','SRID=32649;POINT(911624.341064156 3795881.83406374)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('1166','A','47','SRID=32649;POINT(911766.449314806 3795630.38514715)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('1167','B','40','SRID=32649;POINT(911669.370866997 3795652.41412069)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('1168','A','92','SRID=32649;POINT(911722.914031437 3795328.70520865)');
INSERT INTO "ucfsxxx"."trees" ("id","treetype","age",geom) VALUES ('1169','E','24','SRID=32649;POINT(911900.997272177 3795352.30969589)');
CREATE INDEX ON "ucfsxxx"."trees" USING GIST ("geom");
COMMIT;
ANALYZE "ucfsxxx"."trees";

(base) ╭─jade_mayer@jadedeMacBook-Pro.local ~/projects/lhx/asset/work/sql  

╰─➤  psql -d electric -f insertdata_ucfsxxx_trees.sql  

为决策提供情报（证据）

到此为止，数据库已经建好。接下来就是细化每个decision，给出在数据库中的查询语句。

描述：新建三条输电线路，一条是东北-西南走向，另外两条是西北-东南走向。每条线路都要占用一定范围的土地，称为输电走廊。

Decision 1

在输电廊道和道路交叉口的两端要分别设置两个警示牌（每个交叉口共需要设置4个警示牌），提醒过往行人车辆注意安全。请问修建这三条输电线路后总共需要设置多少个道路警示牌？

所属层：输电廊道层

select
 count(l.*) * 4 as warning_board_number
from
 ucfsxxx.corridors  c, 
 ucfsxxx.landuse l 
where
 l.lutype = 'road'
 AND ST_Intersects(c.geom, l.geom);

Decision 2

假设只有居民区会统计人口数据，来评估三条输电廊道内会影响多少人的生活（某个居民区受影响人数按照通电廊道侵占居民区的面积占居民区的总面积比例换算）。输电廊道会经过的居民区的编号和人口数分别是？每个居民区受输电廊道影响面积占居民区总面积比例是多少？估算每个居民区受影响的人口分别是多少？

所属层：用地类型层

SELECT 
 l.id as landuseblock_id,
 ST_Area(ST_Intersection(l.geom, c.geom)) / ST_Area(l.geom) as intersection_area_ratio,
 l.people,
 l.people * ST_Area(ST_Intersection(l.geom, c.geom)) / ST_Area(l.geom) as influenced_people
FROM
 ucfsxxx.landuse as l,
 ucfsxxx.corridors as c
WHERE 
 l.lutype = 'residential'
 AND ST_Intersects(l.geom, c.geom)

Decision 3

三条输电线路的材料费造价是多少？输电线路包括输电线缆和铁塔，一条线路可能有多条并行线缆。输电线缆材质类型A，B，C的每公里价格分别是3000， 5000， 7000；铁塔材质类型Q235，Q345，Q460每个的价格分别为6000， 8000， 10000。我们可以提供的证据包括

每条线路的长度（公里）、材质、并行线路数、包含铁塔数、铁塔材质，这些证据对于该决策非常重要。

所属层：输电线路层

select
 w.id as wires, 
 ST_Length(w.geom) / 1000 as length_km, 
 w.lparallel, w.texture as wires_type,  
 count(t.texture) as towers, 
 t.texture as tower_type 
from 
 ucfsxxx.wires w 
join 
 ucfsxxx.towers t 
on 
 w.id = t.wires_id 
group by 
 w.texture, 
 w.lparallel, 
 t.texture, 
 w.geom, 
 w.id;

Decision 4

三条输电廊道的总占地面积是多少？由于三条输电廊道的规划，需要在现有用地规划的基础上新增多少面积的设施用地(facilities)？这里认为输电廊道所在地需要规划为设施用地。

所属层：输电廊道层

select 
 c.id as corridor, 
 st_area(c.geom)/1000000 as total_area_km, 
 st_area(c.geom)/1000000 - sum(ST_Area(ST_Intersection(c.geom, l.geom))/1000000) as need_new_facilities_area_km2  
from 
 ucfsxxx.corridors as c, 
 ucfsxxx.landuse as l 
where 
 st_intersects(c.geom, l.geom) 
 and l.lutype='facilities' 
group by 
 c.id;

Decision 5

输电廊道内都有哪些树种？分别有多少颗树？平均年龄各是多少？

所属层：输电廊道层

select 
 count(t.*), 
 t.treetype, 
 avg(t.age) 
from 
 ucfsxxx.trees as t, 
 ucfsxxx.corridors as c 
where 
 st_contains(c.geom, t.geom) 
group by 
 t.treetype;

Decision 6

有多少颗树会因为修建输电线路而被砍掉？这些树的平均年龄是多少？假定离输电线缆直线距离50m内的树要被砍掉。

所属层：输电线路层

select 
 count(t.*) trees_will_die , 
 avg(t.age) as average_age 
from 
 ucfsxxx.trees as t, 
 ucfsxxx.wires as w 
where 
 st_distance(w.geom, t.geom) < 50;

Decision 7

每个铁塔在出厂时候厂家给出了该铁塔在10年内的截面锈蚀率数据（loss ratio of sectional area in ten years），请问在10年后有多少个铁塔需要更换？假定锈蚀率大于0.9的铁塔需要被更换？

所属层：输电线路层

select 
 count(*) 
from 
 ucfsxxx.towers t 
where 
 t.corrosion > 0.9;