PostgreSQL shapefile 导入导出
Shapefile文件组介绍 一个Shapefile它由一组多个文件组成.shp, .dbf, .shx三是必要的，其他文件可选): .shp: 必要的图形格式，用于保存元素的几何实体。 .dbf: 必要的。属性数据格式，以dBase IV存储每个几何形状的属性数据的数据表格式。 .shx: 必要的。图形索引格式，几何位置索引，记录每个几何位置.shp文件中的位置可以加快向前或向后搜索几何的效率。
可选择以下文件： .prj: 可选的。投影格式，用于保存地理坐标系统与投影信息，投影描述文本文件。 .sbn & .sbx: 可选。几何空间索引。 .fbn & .fbx: 可选Shapefiles几何空间索引。 .cpg: 可选。用于描述.dbf文件的代码页，指示其使用的字符编码。 .ain & .aih: 可选。列表中活动字段的属性索引。 .ixs & .mxs: 可选。可读写Shapefile后者是文件的地理编码索引ODB地理编码索引格式。 .atx: .dbf文件名格式为文件属性索引shapefile.columnname.atx (ArcGIS 8及后续版本)。 .shp.xml: 可选的。以XML保存元数据的格式。
1.pgsql2shp & shp2pgsql 导出指令行工具 shapefile
1.1）PostgreSQL 空间数据表导出为 shapefile 进入 $PG_HOME/bin 脚本执行在目录下 pgsql2shp [root@pgmaster ~]# cd /usr/pgsql-12/bin/ [root@pgmaster bin]#
查看命令参数的细节 [root@pgmaster bin]# pgsql2shp --help pgsql2shp: illegal option -- - RELEASE: 3.0.5 (df5f8b7) USAGE: pgsql2shp [<options>] <database> [<schema>.]<table> pgsql2shp [<options>] <database> <query>
OPTIONS: -f <filename> Use this option to specify the name of the file to create. -h <host> Allows you to specify connection to a database on a machine other than the default. -p <port> Allows you to specify a database port other than the default. -P <password> Connect to the database with the specified password. -u <user> Connect to the database as the specified user. -g <geometry_column> Specify the geometry column to be exported. -b Use a binary cursor. -r Raw mode. Do not assume table has been created by the loader. This would not unescape attribute names and will not skip the 'gid' attribute. -k Keep PostgreSQL identifiers case. -m <filename> Specify a file containing a set of mappings of (long) column names to 10 character DBF column names. The content of the file is one or more lines of two names separated by white space and no trailing or leading space. For example: COLUMNNAME DBFFIELD1 AVERYLONGCOLUMNNAME DBFFIELD2 -? Display this help screen.
将 PostgreSQL 数据库中的 geometry 表导出为 shapefile 导出 testdb01 数据库的 public 模式下的 shapefile 表 [root@pgmaster ~]# cd /usr/pgsql-12/bin/ [root@pgmaster bin]# mkdir -p "/opt/geom_exp/shape/geom_test01_point" [root@pgmaster bin]# pgsql2shp -f "/opt/geom_exp/shape/geom_test01_point/geom_test01_point.shp" -h 127.0.0.1 -u test -p 5432 -P 1q2w3e testdb01 public.geom_test01_point Initializing... Done (postgis major version: 3). Output shape: Point Dumping: X [2 rows]. [root@pgmaster bin]# [root@pgmaster bin]# ls -l /opt/geom_exp/shape/geom_test01_point/ total 20 -rw-r--r-- 1 root root 5 Jun 6 16:45 geom_test01_point.cpg -rw-r--r-- 1 root root 631 Jun 6 16:45 geom_test01_point.dbf -rw-r--r-- 1 root root 256 Jun 6 16:45 geom_test01_point.prj -rw-r--r-- 1 root root 156 Jun 6 16:45 geom_test01_point.shp -rw-r--r-- 1 root root 116 Jun 6 16:45 geom_test01_point.shx [root@pgmaster bin]# 导出成功后，自动生成5份相关文件：.cpg，.dbf，.prj，.shp，.shx
1.2）shapefil 导入到 PostgreSQL 在空间数据库中
将 geom_test02_linestr.shp 导入导 testdb01 数据库的 test_schema (自动生成表 geom_test02_linestr ） [root@pgmaster ~]# cd /usr/pgsql-12/bin/ [root@pgmaster bin]# [root@pgmaster bin]# shp2pgsql -help shp2pgsql: illegal option -- h RELEASE: 3.0.5 (df5f8b7) USAGE: shp2pgsql [<options>] <shapefile> [[<schema>.]<table>] OPTIONS: -s [<from>:]<srid> Set the SRID field. Defaults to 0. Optionally reprojects from given SRID. (-d|a|c|p) These are mutually exclusive options: -d Drops the table, then recreates it and populates it with current shape file data. -a Appends shape file into current table, must be exactly the same table schema. -c Creates a new table and populates it, this is the default if you do not specify any options. -p Prepare mode, only creates the table. -g <geocolumn> Specify the nam of the geometry/geography column (mostly useful in append mode). -D Use postgresql dump format (defaults to SQL insert statements). -e Execute each statement individually, do not use a transaction. Not compatible with -D. -G Use geography type (requires lon/lat data or -s to reproject). -k Keep postgresql identifiers case. -i Use int4 type for all integer dbf fields. -I Create a spatial index on the geocolumn. -m <filename> Specify a file containing a set of mappings of (long) column names to 10 character DBF column names. The content of the file is one or more lines of two names separated by white space and no trailing or leading space. For example: COLUMNNAME DBFFIELD1 AVERYLONGCOLUMNNAME DBFFIELD2 -S Generate simple geometries instead of MULTI geometries. -t <dimensionality> Force geometry to be one of '2D', '3DZ', '3DM', or '4D' -w Output WKT instead of WKB. Note that this can result in coordinate drift. -W <encoding> Specify the character encoding of Shape's attribute column. (default: "UTF-8") -N <policy> NULL geometries handling policy (insert*,skip,abort). -n Only import DBF file. -T <tablespace> Specify the tablespace for the new table. Note that indexes will still use the default tablespace unless the -X flag is also used. -X <tablespace> Specify the tablespace for the table's indexes. This applies to the primary key, and the spatial index if the -I flag is used. -? Display this help screen.
An argument of `--' disables further option processing. (useful for unusual file names starting with '-') [root@pgmaster bin]#
导入语法： shp2pgsql -s SRID -a -W GBK 文件名.shp 命名空间名.表名 | psql -h localhost -U postgres -d 数据库名-p 5432
执行导入：
[root@pgmaster bin]# shp2pgsql -s 4326 -c -W UTF-8 "/opt/geom_exp/shape/geom_test01_point/geom_test01_point.shp" test_schema.geom_test01_point | psql -h 127.0.0.1 -U test -d testdb01 -p 5432 Field id is an FTDouble with width 11 and precision 0 Shapefile type: Point Postgis type: POINT[2] Password for user test: SET SET BEGIN CREATE TABLE ALTER TABLE addgeometrycolumn ----------------------------------------------------------------- test_schema.geom_test01_point.geom SRID:4326 TYPE:POINT DIMS:2 (1 row)
INSERT 0 1 INSERT 0 1 COMMIT ANALYZE [root@pgmaster bin]#
注意： 1）导入时，需要输入数据库连接密码 2）如果表已经存在、要用插入模式，则参数 -c 换成 -a； 3）如果表已存在、想要先删除已存在的表，重新创建表和导入，则 -c 换成 -d 4）导入时请指定坐标系 -s 参数，例如： -s 4326 或者 -s 3587。如果是目的表已经存在、采用append写入的场景，srid必须设置成和目的表的空间字段的srid一致，否则无法写入
下面我把shp2pgsql导入执行的sql语句保存到sql文件、进行内容差异对比：
[root@pgmaster bin]# cd /opt/geom_exp/
[root@pgmaster geom_exp]# mkdir -p /opt/geom_exp/shp2sql/
[root@pgmaster geom_exp]# shp2pgsql -s 4326 -c -W UTF-8 "/opt/geom_exp/shape/geom_test01_point/geom_test01_point.shp" test_schema.geom_test01_point > ./shp2sql/geom_test01_point.4326.sql Field id is an FTDouble with width 11 and precision 0 Shapefile type: Point Postgis type: POINT[2] [root@pgmaster geom_exp]# [root@pgmaster geom_exp]# [root@pgmaster geom_exp]# [root@pgmaster geom_exp]# shp2pgsql -s 3857 -c -W UTF-8 "/opt/geom_exp/shape/geom_test01_point/geom_test01_point.shp" test_schema.geom_test01_point > ./shp2sql/geom_test01_point.3857.sql Field id is an FTDouble with width 11 and precision 0 Shapefile type: Point Postgis type: POINT[2] [root@pgmaster geom_exp]# [root@pgmaster geom_exp]# [root@pgmaster geom_exp]# shp2pgsql -c -W UTF-8 "/opt/geom_exp/shape/geom_test01_point/geom_test01_point.shp" test_schema.geom_test01_point > ./shp2sql/geom_test01_point.noSRID.sql Field id is an FTDouble with width 11 and precision 0 Shapefile type: Point Postgis type: POINT[2] [root@pgmaster geom_exp]#
SRID=4326方式导入的数据的内容如下：
testdb01=> select id,name,st_asewkt(geom) from test_schema.geom_test01_point ; id | name | st_asewkt ----+------+--------------------------------------- 1 | test | SRID=4326;POINT(-0.125 52.5) 2 | test | SRID=4326;POINT(27.9116248 -33.01532) (2 rows)
testdb01=> select * from test_schema.geom_test01_point ; gid | id | name | geom -----+----+------+---------------------------------------------------- 1 | 1 | test | 0101000020E6100000000000000000C0BF0000000000404A40 2 | 2 | test | 0101000020E6100000F8382E3E60E93B40C47C7901F68140C0 (2 rows)
2. QGIS工具导入导出 shapefile 我本地安装的是QGIS Desktop 3.24.0 2.1）数据库导出shapefile步骤： 1）Browser->PostgreSQL->右键 New Connection 创建数据库连接。填写对应参数：Name，Host，Port，Database，以及认证配置(Authentication)->Basic，配置数据库连接账号（User name）和密码（Password）
2）选择想要导出的schema下的包含geometry类型的表，右键导出（Export Layer -> To File），也可以先双击表（双击会默认执行"Add Layer to Project"）、再在Layers中勾选右键导出
3）导出参数配置： Format：ESRI Shapefile，默认为该格式，如果想要导出其他格式（GeoJSON、CSV等）则选择对应的格式导出 File name：指定导出文件名，一般设置为何表名相同。默认导出路径在执行文件同路径下（C:\Program Files\QGIS 3.24.0\bin），如果要指定其他路径导出，则需要选择导出目录+指定导出文件名（文件名指定为路径+文件名） CRS: 指定导出坐标系，默认 EPSG:4326 - WGS84 Encoding: UTF-8 导出生成.cpg文件，用于描述.dbf文件的代码页，指明其使用的字符编码 Select fields to export and their export options：导出字段选择和其他参数配置，默认全选 其他参数默认配置
4）点击“OK”导出shapefile文件
2.2）shapefile导入PostgreSQL数据库中步骤： 1）菜单：Database->DB Manager
2）Providers 选择 PostGIS 连接（如果未配置则先配置） 3）展开选择对应的schema 4）点击菜单栏下方的"Import Layer/File..."按钮 5）配置导入参数： Input：选择要导入的shapefile（xxx.shp） Output table: schema: 导入的目的schema(默认填充) Table: 导入的目的表(默认填充) Option：其他选项，根据事情情况选择是否勾选。 注意：Geometry column 如果不勾选配置，默认导入后的表的空间字段名称为 geom，如果需要修改，则勾选并指定列名
3.OGC/EPSG/SRS/CRS 等相关概念：
OGC: Open Geospatial Consortium (OGC) 开放地理空间信息联盟，它是一个非盈利的、国际化的、自愿协商的标准化组织，制定了一套标准，各种大厂商都几乎执行这些标准，使得不同厂商、不同产品之间可以通过统一的接口进行互操作。
EPSG: European Petroleum Survey Group (EPSG) 是一个协会，现在已经改名叫 OGP(Internation Association of Oil & Gas Producers)。它负责维护并发布坐标参考系统的数据集参数，以及坐标转换描述，该数据集被广泛接受并使用。例如：CGCS2000、WGS84坐标系在 EPSG 中都有定义，并有对应的code。查询官网：https://spatialreference.org/ SRS & CRS: 是同一个东西，都是坐标系。例如：WGS84。
EPSG: 4326 (WGS84) 是目前最流行的地理坐标系统。国际上会为每个坐标系统都会被分配一个EPSG代码，EPSG: 4326 就是 WGS84 的代码。GPS是基于WGS84的，所以通常我们得到的坐标数据都是 WGS84 的。 EPSG: 3857 (Pseudo-Mercator) 伪墨卡托投影，也被称为球体墨卡托，Web Mercator。它是基于墨卡托投影的，把 WGS84 坐标系投影到正方形上。 EPSG: 2436 (北京范围的投影坐标系) 北京的东(E)西(W)范围
查询与北京有关的坐标系命令： select * from spatial_ref_sys where srtext ~* 'china';
SRID: 是坐标系在OGC标准中的唯一ID，和EPSG中的code是一致的。