八、shp polygon文件写入pg库
因为postGIS导入shp文件没有字段备注和表说明,且如果字段名过长,还会截短字段名。
所以为了规范化,我这里不用PostGIS 2.0 Shapefile and DBF Loader Exporter工具导入shp文件,都用py脚本把数据读入postgresql数据库中。
为了使程序简单明了,沿用之前的流程,将程序分成两个部分,第一部分,shp文件导出txt;第二部分,txt文件导入pg库。
3个shp文件,一共6个py脚本。
cnty_pts_shptotxt.py
cnty_pts_txttopg.py
pref_pts_shptotxt.py
pref_pts_txttopg.py
pref_pgn_shptotxt.py
pref_pgn_txttopg.py
其中cnty_pts和pref_pts只有表名不一样,表结构是一样的。
pref_pgn是一个面数据,跟其他不一样。
这里再看一下它的建表语句。
建表语句
--建表
CREATE TABLE public.v6_time_pref_pgn_utf_wgs84 (
gid SERIAL8 PRIMARY KEY NOT NULL,
name_py varchar(254),
name_ch varchar(30),
name_ft varchar(30),
x_coor float8,
y_coor float8,
pres_loc varchar(40),
type_py varchar(15),
type_ch varchar(15),
lev_rank varchar(1),
beg_yr int8,
beg_rule varchar(1),
end_yr int8,
end_rule varchar(1),
note_id int8,
obj_type varchar(7),
sys_id int8,
geo_src varchar(10),
compiler varchar(15),
gecomplr varchar(15),
checker varchar(10),
ent_date varchar(10),
beg_chg_ty varchar(27),
end_chg_ty varchar(27),
geom geometry);
--建立索引
CREATE INDEX v6_time_pref_pgn_utf_wgs84_index ON v6_time_pref_pgn_utf_wgs84 USING
btree(gid);
--表说明
COMMENT ON TABLE public.v6_time_pref_pgn_utf_wgs84 IS '第6版中国历史地理时间序列面数据';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.gid IS '主键ID';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.name_py IS '拼音名称';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.name_ch IS '简体中文名称';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.name_ft IS '繁体中文名称';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.x_coor IS '经度';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.y_coor IS '纬度';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.pres_loc IS '现所在地';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.type_py IS '建制类型拼音';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.type_ch IS '建制类型简体中文';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.lev_rank IS '建制等级';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.beg_yr IS '建制开始时间';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.beg_rule IS '开始时间精度';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.end_yr IS '建制结束时间';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.end_rule IS '结束时间精度';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.note_id IS '系统id';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.obj_type IS 'geometry对象类型';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.sys_id IS '系统id';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.geo_src IS 'geometry数据来源';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.compiler IS '编辑人员';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.gecomplr IS '绘制人员';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.checker IS '审核人员';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.ent_date IS '结束时间';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.beg_chg_ty IS '建制开始原因';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.end_chg_ty IS '建制结束原因';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.end_chg_ty IS '建制结束原因';
COMMENT ON COLUMN public.v6_time_pref_pgn_utf_wgs84.geom IS 'geometry数据';
Insert语句
INSERT INTO v6_time_cnty_pts_utf_wgs84(name_py,name_ch,name_ft,x_coor,y_coor,pres_loc,type_py,type_ch,lev_rank,beg_yr,beg_rule,end_yr,end_rule,note_id,obj_type,sys_id,geo_src,compiler,gecomplr,checker,ent_date,beg_chg_ty,end_chg_ty,geom) VALUES('Jiangxia Jun','江夏郡','江夏郡',0.000000,0.000000,null,'Jun','郡','3',607,'4',620,'4',34370,'POLYGON',97862,'FROM_FD','钱林书','满志敏',null,'2015/05/25','更名','更名',st_geomfromtext('MULTIPOLYGON (((114.310866787992 29.3626074988766,114.306357694529 29.3636015383897,114.30074958209 29.365705608762,114.292586407783 29.366346651936,114.288138309584 29.3659626575143)))',4326))
读shp文件
pref_pgn_shptotxt.py
# coding=gbk
try:
from osgeo import gdal
from osgeo import ogr
except ImportError:
import gdal
import ogr
# pathStr,shp文件的全路径
def ReadVectorFile(pathStr):
# 返回结果是一个list
result=[]
# 支持中文路径
gdal.SetConfigOption("GDAL_FILENAME_IS_UTF8", "NO")
# 属性表字段支持中文
gdal.SetConfigOption("SHAPE_ENCODING", "")
strVectorFile = pathStr
# 注册所有的驱动
ogr.RegisterAll()
#