将shp文件导入PostgreSQL数据库

将shp文件导入PostgreSQL数据库使用的工具是PostgreSQL自带的工具：shp2pgsql 

此工具在PostgreSQL安装目录下的bin目录下

使用方法：

shp2pgsql [OPTIONS] shapefile [schema.]table

使用说明：

The shp2pgsql data loader converts ESRI Shape files into SQL suitable for insertion into a PostGIS/PostgreSQL database. The loader has several operating modes distinguished by command line flags:

-d
Drops the database table before creating a new table with the data in the Shape file.

-a
Appends data from the Shape file into the database table. Note that to use this option to load multiple files, the files must have the same attributes and same data types.

-c
Creates a new table and populates it from the Shape file. This is the default mode.

-p
Only produces the table creation SQL code, without adding any actual data. This can be used if you need to completely separate the table creation and data loading steps.

-D
Use the PostgreSQL "dump" format for the output data. This can be combined with -a, -c and -d. It is much faster to load than the default "insert" SQL format. Use this for very large data sets.

-s <SRID>
Creates and populates the geometry tables with the specified SRID.

-k
Keep identifiers' case (column, schema and attributes). Note that attributes in Shapefile are all UPPERCASE.

-i
Coerce all integers to standard 32-bit integers, do not create 64-bit bigints, even if the DBF header signature appears to warrant it.

-I
Create a GiST index on the geometry column.

-w
Output WKT format, for use with older (0.x) versions of PostGIS. Note that this will introduce coordinate drifts and will drop M values from shapefiles.

-W <encoding>
Specify encoding of the input data (dbf file). When used, all attributes of the dbf are converted from the specified encoding to UTF8. The resulting SQL output will contain a SET CLIENT_ENCODING to UTF8 command, so that the backend will be able to reconvert from UTF8 to whatever encoding the database is configured to use internally.

Note that -a, -c, -d and -p are mutually exclusive.

 

1. 生成sql：

找到此工具目录，执行：

shp2pgsql -W gbk -s 4326 -g geom_data /usr/local/upload/sample.shp tbl_shp_data>shp_data.sql

执行完毕后会在当前目录找到shp_data.sql文件；

 

2. 将生成的sql文件导入数据库中：

psql -d input_db_name -f shp_data.sql -U postgres

 

3.转换投影坐标系(如需要的话)：

进入PostGreSQL数据库中，执行：

SELECT UpdateGeometrySRID('tbl_shp_data', 'geom_data', 900913); #变更地理信息字段的SRID

 

End.