shp文件保存到mysql

本文介绍了使用 ogr2ogr 工具进行不同地理空间数据格式之间的转换方法。涵盖 MapInfo 到 PostGIS、ESRI Shapefile 互转、GeoDatabase 转换等常见操作,并提供了详细的命令行示例。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

原文:http://chenpeng.info/html/2802?utm_source=tuicool&utm_medium=referral   点击打开链接


其中,ogr2ogr 是FWtools里面的一个工具。下载地址:http://fwtools.maptools.org/  点击打开链接



sqlite转tab

ogr2ogr -f ‘MapInfo File’ output.tab -a_srs wgs84 -lco encoding=gbk -nln $table input.db -sql ‘$sql’

tab转pg

ogr2ogr -append -f ‘PostgreSQL’ PG:’dbname=$db user=$user port=$port host=$host ‘ -a_srs WGS84 GBK:input.tab -nln $table

sqlite转pg

ogr2ogr -append -f ‘PostgreSQL’ PG:’host=$host dbname=$db user=$user port=$port’ input.db -a_srs WGS84 -lco GEOMETRY_NAME=shape –config PG_USE_COPY YES -lco GEOM_TYPE=geometry -a_srs wgs84 -sql ‘$sql’ -nln $table

pg转sqlite

ogr2ogr -append -f SQLite output.db -dsco SPATIALITE=NO -lco FORMAT=WKT -nln $table PG:’host=$host dbname=$db user=$user port=$port’ -sql ‘$sql’

 

 

数据转换
(1)MapInfo ==> ESRI Shape

[postgres@localhost ~]$ ogr2ogr -f “ESRI Shapefile” mydata.shp mydata.tab

(2)ESRI Shape ==>MapInfo

[postgres@localhost ~]$ ogr2ogr -f “MapInfo File” tabsites.tab sites.shp

(3)MapInfo ==> PostGIS

[postgres@localhost ~]$ ogr2ogr -f “PostgreSQL” PG:”host=localhost user=postgres dbname=postgis password=123456″

(4)postgis==>ESRI Shapefile

[postgres@localhost ~]$ ogr2ogr -f “ESRI Shapefile” mydata.shp PG:”host=localhost dbname=postgis user=postgres password=123456″ “mytable”

(5)PostGIS ==> KML

[postgres@localhost ~]$ ogr2ogr -f “KML” neighborhoods.kml PG:”host=localhost dbname=postgis user=postgres password=123456″ -sql “select gid,name,the_geom from neighborhoods”

(6)批量转换
将postgis中所有的表都导出到mydatadump文件夹下,导出格式是ESRI Shapefile

[postgres@localhost ~]$  ogr2ogr -f “ESRI Shapefile” mydatadump PG:”host=myhost user=myloginname dbname=mydbname password=mypassword”

部分导出,将指定的表导出到mydatadump中,格式为ESRI Shapefile

[postgres@localhost ~]$ ogr2ogr -f “ESRI Shapefile” mydatadump PG:”host=myhost user=myloginname dbname=mydbname password=mypassword” neighborhood parcels

(7)ESRI GeoDatabase (*.mdb) ==>PostGIS

[postgres@localhost ~]$ ogr2ogr -f “PostgreSQL” PG:”host=localhost user=someuser dbname=somedb password=somepassword port=5432″ C:\GISData\Geonames.mdb -a_srs EPSG:26986

导入指定的featureclass,重投影,重命名geometry列

[postgres@localhost ~]$ ogr2ogr -f “PostgreSQL” PG:”host=localhost user=someuser dbname=somedb”

/home/postgres/Data/Geonames.mdb GEONAMES_ANNO_HYDRO -a_srs EPSG: 26986 -t_srs EPSG:4269 -nln ma_hydro -lco GEOMETRY_NAME=the_geom_4269

(8)ESRI Shapefile ==>MySQL

[postgres@localhost ~]$ ogr2ogr -f “MySQL” MYSQL:”mydb,host=myhost,user=mylogin,password=mypassword,port=3306″ -nln “world” -a_srs “EPSG:4326″ path/to/world_adm0.shp

(9)Non-spatial Data ==>PostgreSQL

[postgres@localhost ~]$  ogr2ogr -f “PostgreSQL” PG:”host=myserver user=myusername dbname=mydbname password=mypassword” sometable.dbf -nln “sometable”

下面是如何将shp和tab文件导入到postgis数据库中的命令方式

[postgres@localhost ~]$ ogr2ogr -f PostgreSQL PG:”host=localhost dbname=postgis user=postgres password=850315″ sites.shp

[postgres@localhost ~]$ ogr2ogr -f PostgreSQL PG:”host=localhost dbname=postgis user=postgres password=850315″ mytabfile.tab

通过 -nln指定导入数据库中的表名,而不是默认的文件名作为表名

[postgres@localhost ~]$ ogr2ogr -f “PostgreSQL” PG:”host=myhost user=myloginname dbname=mydbname password=mypassword” mytabfile.tab -nln newtablename

通过 -a_srs 选项指定输出的投影

[postgres@localhost ~]$ ogr2ogr -f “PostgreSQL” -a_srs “EPSG:2249″ PG:”host=myhost user=myloginname dbname=mydbname password=mypassword” mytabfile.tab

通过-sql 选项后面的SQL语句对postgis中的数据经行筛选后到处到shp文件中

ogr2ogr -f “ESRI Shapefile” mydata.shp PG:”host=myhost user=myloginname dbname=mydbname password=mypassword” -sql “SELECT name, the_geom FROM neighborhoods”

可以通过ogrinfo命令来查看元数据

[postgres@localhost ~]$ ogrinfo sites.shp

 

 

official site:

http://www.gdal.org/ogr2ogr.html

 

converts simple features data between file formats

SYNOPSIS

Usage:

Usage: ogr2ogr [--help-general] [-skipfailures] [-append] [-update]
               [-select field_list] [-where restricted_where] 
               [-progress] [-sql <sql statement>] [-dialect dialect]
               [-preserve_fid] [-fid FID]
               [-spat xmin ymin xmax ymax] [-geomfield field]
               [-a_srs srs_def] [-t_srs srs_def] [-s_srs srs_def]
               [-f format_name] [-overwrite] [[-dsco NAME=VALUE] ...]
               dst_datasource_name src_datasource_name
               [-lco NAME=VALUE] [-nln name]
               [-nlt type|PROMOTE_TO_MULTI|CONVERT_TO_LINEAR]
               [-dim 2|3|layer_dim] [layer [layer ...]]

Advanced options :
               [-gt n]
               [[-oo NAME=VALUE] ...] [[-doo NAME=VALUE] ...]
               [-clipsrc [xmin ymin xmax ymax]|WKT|datasource|spat_extent]
               [-clipsrcsql sql_statement] [-clipsrclayer layer]
               [-clipsrcwhere expression]
               [-clipdst [xmin ymin xmax ymax]|WKT|datasource]
               [-clipdstsql sql_statement] [-clipdstlayer layer]
               [-clipdstwhere expression]
               [-wrapdateline] [-datelineoffset val]
               [[-simplify tolerance] | [-segmentize max_dist]]
               [-addfields]
               [-fieldTypeToString All|(type1[,type2]*)] [-unsetFieldWidth]
               [-fieldmap identity | index1[,index2]*]
               [-splitlistfields] [-maxsubfields val]
               [-explodecollections] [-zfield field_name]
               [-gcp pixel line easting northing [elevation]]* [-order n | -tps]

DESCRIPTION

This program can be used to convert simple features data between file formats performing various operations during the process such as spatial or attribute selections, reducing the set of attributes, setting the output coordinate system or even reprojecting the features during translation.

-f  format_name:
output file format name (default is ESRI Shapefile), some possible values are:

     -f "ESRI Shapefile"
     -f "TIGER"
     -f "MapInfo File"
     -f "GML"
     -f "PostgreSQL"
	 
-append:
Append to existing layer instead of creating new
-overwrite:
Delete the output layer and recreate it empty
-update:
Open existing output datasource in update mode rather than trying to create a new one
-select  field_list:
Comma-delimited list of fields from input layer to copy to the new layer. A field is skipped if mentioned previously in the list even if the input layer has duplicate field names. (Defaults to all; any field is skipped if a subsequent field with same name is found.) Starting with OGR 1.11, geometry fields can also be specified in the list.
-progress:
(starting with GDAL 1.7.0) Display progress on terminal. Only works if input layers have the “fast feature count” capability.
-sql  sql_statement:
SQL statement to execute. The resulting table/layer will be saved to the output.
-dialect  dialect:
SQL dialect. In some cases can be used to use (unoptimized) OGR SQL instead of the native SQL of an RDBMS by passing OGRSQL. Starting with GDAL 1.10, the “SQLITE” dialect can also be used with any datasource.
-where  restricted_where:
Attribute query (like SQL WHERE)
-skipfailures:
Continue after a failure, skipping the failed feature.
-spat  xmin ymin xmax ymax:
spatial query extents. Only features whose geometry intersects the extents will be selected. The geometries will not be clipped unless -clipsrc is specified
-geomfield  field:
(OGR >= 1.11) Name of the geometry field on which the spatial filter operates on.
-dsco  NAME=VALUE:
Dataset creation option (format specific)
-lco  NAME=VALUE:
Layer creation option (format specific)
-nln  name:
Assign an alternate name to the new layer
-nlt  type:
Define the geometry type for the created layer. One of NONE, GEOMETRY, POINT, LINESTRING, POLYGON, GEOMETRYCOLLECTION, MULTIPOINT, MULTIPOLYGON or MULTILINESTRING. And CIRCULARSTRING, COMPOUNDCURVE, CURVEPOLYGON, MULTICURVE and MULTISURFACE for GDAL 2.0 non-linear geometry types. Add “25D” to the name to get 2.5D versions. Starting with GDAL 1.10, PROMOTE_TO_MULTI can be used to automatically promote layers that mix polygon or multipolygons to multipolygons, and layers that mix linestrings or multilinestrings to multilinestrings. Can be usefull when converting shapefiles to PostGIS (and other target drivers) that implements strict checks for geometry type. Starting with GDAL 2.0, CONVERT_TO_LINEAR can be used to to convert non-linear geometries types into linear geometries by approximating them.
-dim  val:
(starting with GDAL 1.10) Force the coordinate dimension to val (valid values are 2 or 3). This affects both the layer geometry type, and feature geometries. Starting with GDAL 1.11, the value can be set to “layer_dim” to instruct feature geometries to be promoted to the coordinate dimension declared by the layer.
-a_srs  srs_def:
Assign an output SRS
-t_srs  srs_def:
Reproject/transform to this SRS on output
-s_srs  srs_def:
Override source SRS
-preserve_fid:
Use the FID of the source features instead of letting the output driver to automatically assign a new one.
-fid  fid:
If provided, only the feature with this feature id will be reported. Operates exclusive of the spatial or attribute queries. Note: if you want to select several features based on their feature id, you can also use the fact the ‘fid’ is a special field recognized by OGR SQL. So, ‘-where “fid in (1,3,5)”‘ would select features 1, 3 and 5.

Srs_def can be a full WKT definition (hard to escape properly), or a well known definition (ie. EPSG:4326) or a file with a WKT definition.


Advanced options :

-oo  NAME=VALUE:
(starting with GDAL 2.0) Input dataset open option (format specific)
-doo  NAME=VALUE:
(starting with GDAL 2.0) Destination dataset open option (format specific), only valid in -update mode
-gt  n:
group  n features per transaction (default 20000 in OGR 1.11, 200 in previous releases). Increase the value for better performance when writing into DBMS drivers that have transaction support.
-clipsrc  [xmin ymin xmax ymax]|WKT|datasource|spat_extent:
(starting with GDAL 1.7.0) clip geometries to the specified bounding box (expressed in source SRS), WKT geometry (POLYGON or MULTIPOLYGON), from a datasource or to the spatial extent of the  -spat option if you use the  spat_extent keyword. When specifying a datasource, you will generally want to use it in combination of the  -clipsrclayer-clipsrcwhere or  -clipsrcsql options
-clipsrcsql  sql_statement:
Select desired geometries using an SQL query instead.
-clipsrclayer  layername:
Select the named layer from the source clip datasource.
-clipsrcwhere  expression:
Restrict desired geometries based on attribute query.
-clipdst  xmin ymin xmax ymax:
(starting with GDAL 1.7.0) clip geometries after reprojection to the specified bounding box (expressed in dest SRS), WKT geometry (POLYGON or MULTIPOLYGON) or from a datasource. When specifying a datasource, you will generally want to use it in combination of the -clipdstlayer, -clipdstwhere or -clipdstsql options
-clipdstsql  sql_statement:
Select desired geometries using an SQL query instead.
-clipdstlayer  layername:
Select the named layer from the destination clip datasource.
-clipdstwhere  expression:
Restrict desired geometries based on attribute query.
-wrapdateline:
(starting with GDAL 1.7.0) split geometries crossing the dateline meridian (long. = +/- 180deg)
-datelineoffset:
(starting with GDAL 1.10) offset from dateline in degrees (default long. = +/- 10deg, geometries within 170deg to -170deg will be splited)
-simplify  tolerance:
(starting with GDAL 1.9.0) distance tolerance for simplification. Note: the algorithm used preserves topology per feature, in particular for polygon geometries, but not for a whole layer.
-segmentize  max_dist:
(starting with GDAL 1.6.0) maximum distance between 2 nodes. Used to create intermediate points
-fieldTypeToString  type1, …:
(starting with GDAL 1.7.0) converts any field of the specified type to a field of type string in the destination layer. Valid types are : Integer, Real, String, Date, Time, DateTime, Binary, IntegerList, RealList, StringList. Special value  All can be used to convert all fields to strings. This is an alternate way to using the CAST operator of OGR SQL, that may avoid typing a long SQL query.
-unsetFieldWidth:
(starting with GDAL 1.11) set field width and precision to 0.
-splitlistfields:
(starting with GDAL 1.8.0) split fields of type StringList, RealList or IntegerList into as many fields of type String, Real or Integer as necessary.
-maxsubfields  val:
To be combined with -splitlistfields to limit the number of subfields created for each split field.
-explodecollections:
(starting with GDAL 1.8.0) produce one feature for each geometry in any kind of geometry collection in the source file
-zfield  field_name:
(starting with GDAL 1.8.0) Uses the specified field to fill the Z coordinate of geometries
-gcp  ungeoref_x ungeoref_y georef_x georef_y elevation:
(starting with GDAL 1.10.0) Add the indicated ground control point. This option may be provided multiple times to provide a set of GCPs.
-order  n:
(starting with GDAL 1.10.0) order of polynomial used for warping (1 to 3). The default is to select a polynomial order based on the number of GCPs.
-tps:
(starting with GDAL 1.10.0) Force use of thin plate spline transformer based on available GCPs.
-fieldmap:
(starting with GDAL 1.10.0) Specifies the list of field indexes to be copied from the source to the destination. The (n)th value specified in the list is the index of the field in the target layer definition in which the n(th) field of the source layer must be copied. Index count starts at zero. There must be exactly as many values in the list as the count of the fields in the source layer. We can use the ‘identity’ setting to specify that the fields should be transferred by using the same order. This setting should be used along with the -append setting.
-addfields:
(starting with GDAL 1.11) This is a specialized version of -append. Contrary to -append, -addfields has the effect of adding, to existing target layers, the new fields found in source layers. This option is useful when merging files that have non-strictly identical structures. This might not work for output formats that don’t support adding fields to existing non-empty layers.

PERFORMANCE HINTS

When writing into transactional DBMS (SQLite/PostgreSQL,MySQL, etc…), it might be beneficial to increase the number of INSERT statements executed between BEGIN TRANSACTION and COMMIT TRANSACTION statements. This number is specified with the -gt option. For example, for SQLite, explicitly defining -gt 65536 ensures optimal performance while populating some table containing many hundredth thousand or million rows. However, note that if there are failed insertions, the scope of -skipfailures is a whole transaction.

For PostgreSQL, the PG_USE_COPY config option can be set to YES for significantly insertion performance boot. See the PG driver documentation page.

More generally, consult the documentation page of the input and output drivers for performance hints.

EXAMPLE

Example appending to an existing layer (both flags need to be used):

% ogr2ogr -update -append -f PostgreSQL PG:dbname=warmerda abc.tab

Example reprojecting from ETRS_1989_LAEA_52N_10E to EPSG:4326 and clipping to a bounding box

% ogr2ogr -wrapdateline -t_srs EPSG:4326 -clipdst -5 40 15 55 france_4326.shp europe_laea.shp

Example for using the -fieldmap setting. The first field of the source layer is used to fill the third field (index 2 = third field) of the target layer, the second field of the source layer is ignored, the third field of the source layer used to fill the fifth field of the target layer.

% ogr2ogr -append -fieldmap 2,-1,4 dst.shp src.shp

More examples are given in the individual format pages.


### 将SHP文件数据导入到MySQL数据库的方法 #### 准备工作 为了成功将SHP文件导入至MySQL数据库,需先确认几个前提条件。确保所使用的操作系统已安装必要的软件包,如`shp2mysql`转换工具,并验证SHP文件及其关联文件位于同一目录下[^1]。 #### 创建并配置MySQL数据库 对于UTF-8编码的SHP文件,在创建新的MySQL数据库时应指定字符集为UTF8以支持多语言文字存储。这一步骤可通过SQL语句完成: ```sql CREATE DATABASE geodata CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ``` #### 使用命令行工具进行转换 采用`shp2mysql`命令来处理SHP文件,将其转化为适用于MySQL执行的SQL脚本。具体指令如下所示: ```bash shp2mysql shp文件.shp 表名 数据库名 > 生成的sql文件.sql ``` 注意:上述命令中的参数应当替换为具体的文件名称、目标表名以及对应的数据库名称。该过程会自动生成一个`.sql`结尾的新文件,其中包含了用于构建地理空间特征记录所需的全部INSERT语句[^2]。 #### 修改生成的SQL文件 由于默认情况下产生的SQL文件可能不符合预期结构,因此建议编辑此文件以适应特定需求。主要调整包括但不限于向表格增加名为`the_geom`的空间列,类型设为GEOMETRY,并修正某些语法细节以便于后续操作顺利实施: ```sql ALTER TABLE 表名 ADD COLUMN the_geom GEOMETRY NOT NULL; UPDATE 表名 SET the_geom=ST_GeomFromText(geom_column); ``` 这里假设原始坐标字符串存放在某一列内(例如`geom_column`),而新加入的`the_geom`字段用来保存经由标准函数解析后的几何对象实例[^3]。 #### 执行SQL脚本加载数据 最后一步是在选定的目标数据库环境中运行之前准备好的SQL脚本,从而正式引入来自SHP源的数据条目。可以借助多种方式达成这一目的,比如直接利用客户端界面提交查询请求或是调用命令行实用程序如`mysqlimport`或简单的`source`命令: ```bash mysql -u 用户名 -p 密码 -D 数据库名 < 路径/生成的sql文件.sql ``` 输入正确的登录凭证之后,系统将会按照指示批量插入所有待处理项直至结束整个事务周期[^4]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值