MySQL的GIS、GEO入门笔记

探索和学习MySQL中GIS相关功能和特性

这里记录了学习和了解MySQL中GIS特性相关内容的过程。

MySQL官方论坛中GIS的举例

测试数据已经导入成功，下面开始对GIS相关函数和GEOHASH进行了解和体验；

mysql中geometry类型的简单使用

MySQL空间数据类型

经纬度信息存储在geometry格式的字段中，该字段必须非空。

MySQL8.0前按照longitude-latitude的顺序存储位置

MySQL8.0前按照longitude-latitude的顺序存储位置

MySQL8.0前按照longitude-latitude的顺序存储位置

插入数据时候可使用如下语句：

MySQL存储geometry信息的方式采用了25bytes，相比WKB的21bytes，多了4bytes的坐标系表示，组成部分如下：

WTF字符串格式说明

select ST_GeomFromText(WTF格式字符串)

WKT(Well-known text)是一种文本标记语言,用于表示矢量几何对象、空间参照系统及空间参照系统之间的转换。通过WTF字符串生成geometry的方法：

点： POINT(x y)

线： LINESTRING(x1 y1, x2 y2, x3 y3...)

多边形： POLYGON((0 0, 10 0, 10 10,0 10,0 0),(5 5,7 5,7 7,5 7,5 5))

多点集： MULTIPOINT(0 0, 20 20, 60 60) 或 MULTIPOINT((0 0),(5 5),(5 0))

多线集： MULTILINESTRING((10 10, 20 20), (15 15, 30 15))

多多边形集： MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))

例如两点一线组成的几何集： GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))

A geometry is syntactically well-formed if it satisfies conditions such as those in this (nonexhaustive) list:

Collections are not empty (except GeometryCollection)

更多内容参见

ST_PointFromText('POINT(X Y)')

ST_LineStringFromText('LINESTRING(0 0,1 1,2 2)')

ST_PolygonFromText('POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7,5 5))')

ST_GeomCollFromText()

更多内容参见

参见

Point(x,y)

LineString((x1,y1),(x2,y2)...)

Polygon(LineString(),LineString()....)

参见

ST_AsText()

ST_AsBinary()

ST_AsWKT()

参见

ST_Dimension(geom) ：返回geom的维度（-1，0，1，2）

ST_Envelope(geom) ：返回geom的最小外接矩形（MBR）

ST_GeometryType(geom) ：返回geom的类型

ST_IsEmpty(geom) ：该函数并不能真实的判空，当geom为任何有效的几何值时返回0，无效的几何值返回1；

ST_IsSimple(geom) ：当geom无任何异常几何点返回1（如自相交和自切线等），否则返回0

ST_SRID(geom) ：返回geom的坐标系ID

参见

ST_X(Point) ：获取Point的X值

ST_Y(Point) ：获取Point的Y值

参见

ST_StartPoint(linestrng) ： 线的起点

ST_EndPoint(linestring) ：返回线的最后一个点

ST_IsClosed(linestring或multilinestring) ：线是否闭合（若为线，则判断起点与终点是否一致；若为线组，则判断组内每个元素是否符合闭合线）

ST_Length(linestring) ：返回线的长度，若入参为线集，则返回集合内所有长度的和

ST_NumPoiints(linestring) ：返回点的数量；

ST_PointN(linestring,N) ：返回第N个点（从1开始）

参见

具体不在一一列举，主要有计算多边形面积、中心点、最小外接圆，最大内接圆等函数，列举几个可能会用到的：

ST_Area(Poly|mPoly) ：返回双精度的面积或面积的和

'ST_Centroid(Poly|mPoly)'：返回数学上的中心点

ST_ExteriorRing(Poly) ：返回外接圆

参见

ST_Buffer说明

不再列举，主要有：ST_Buffer（不懂干啥用），ST_ConvexHull(geom)凸包，ST_Dfference(g1,g2)比较差异，ST_Intersecton(g1,g2)交叉点，ST_SymDifference(g1,g2)对称差分，ST_Union(g1,g2)连接、合并等。

检查geometry Objects之间的空间关系的方法。

参见

计算两个Object之间的空间关系的函数，有两个间距离、相交、不相交，包含、相等、相切、重叠、接触、在内等等空间关系。下面列举几个可能会常用的方法：

ST_Contains(g1,g2) ：g1是否完全包含g2

ST_Within(g1,g2) ：g1是否包含于g2中

ST_Distance(g1,g2) ：返回g1和g2之间的距离，已坐标单位计算的

ST_Equals(g1,g2) ：返回g1和g2是否相等

参见

MBRContains(g1,g2) ：g1的mbr是否包含g2的mbr

MBRWithin(g1,g2) ：g1的mbr是否在g2的mbr内

MBRCoveredBy(g1,g2) ：g1的mbr是否被g2的mbr覆盖

MBRCovers(g1,g2) ：g1的mbr是否覆盖g2的mbr

MBRDisjoint(g1,g2) ：g1的mbr,g2的mbr是否不相交

MBRIntersects(g1,g2) ：g1mbr，g2mbr是否相交

MBREqual(g1,g2) ：g1的mbr,g2的mbr的外接是否相等

MBREquals(g1,g2) ：g1的mbr,g2的mbr的外接是否相等

MBROverlaps(g1,g2) ：g1mbr、g2mbr

其他函数请参看原文

GeoHash介绍

GeoHash Wiki百科

MySQL中自带函数 st_geohash(longtude,latitude,max_length) 或 st_geohash(point, max_length) 即可生成某一点的geohash值。

返回一个geohash字符串中的latitude或longitude

返回一个geohash解析出的point数据

官方文档

通过geometry生成一个GeoJSON Object， select st_asgeojson(geometry,max_length,options)

通过GeoJSON生成GeoMetry对象。

ST_GeomFromGeoJSON(jsonstring, [options [, srid]])

具体使用方法参见官方文档

官方文档

MySQL中提供的方便空间运算的函数们

select ST_Distance_Spher(geomPoint1,geomPoint2 [, radius])

此方法用于计算两点或多个点之间的地球上的距离（是地球球面距离而不是直线距离），返回单位为米，

select ST_IsValid(ST_GeomFromText('LINESTRING(0 0,1 1)'))

判断入参是否是符合地理位置描述的格式。返回1（符合）或者0（不符）；

例如：

返回0：

select st_isvalid(st_geomfromtext('linestring(0 0, -0.00 0, 0.0 0)')

返回1：

select st_isvalid(st_geomfromtext('linestring(0 0,1 1)')

select st_astext(st_makeenvelope(pt1, pt2))

返回两点构成的包络。（此计算是基于笛卡尔坐标系而非球面）

例如：

SELECT ST_AsText ( st_makeenvelope ( st_geomfromtext ( 'point(0 0)' ), st_geomfromtext ( 'point(1 1)' ) ) )

返回结果：

POLYGON((0 0,1 0,1 1,0 1,0 0))

效果说明

JS抽稀算法

select st_simplify(geometry, max_distance)

用道格拉斯-普克算法（抽稀函数）简化geometry，并返回与原格式相同格式的结果。

例如，以下点集拟合为直线，步长0.5：

SELECT st_simplify ( st_geomfromtext ( 'LINESTRING(0 0,0 1,1 1,1 2,2 2,2 3,3 3)' ), 0.5 )

返回结果：

LINESTRING(0 0, 0 1, 1 1, 2 3, 3 3)

再如，步长1.0：

SELECT st_simplify ( st_geomfromtext ( 'LINESTRING(0 0,0 1,1 1,1 2,2 2,2 3,3 3)' ), 1.0 )

返回结果：

LINESTRING(0 0, 3 3)

SELECT ST_Validate(geometry)

验证geometry是符合正确的地理位置信息格式。例如 Point(0 0) 是合格的； Linestring(0 0) 是非法的； Linestring(0 0, 1 1) 是合格的

了解了上述MySQL中关于集合对象的功能，下面来实践一下

由上面geohash长度-精度对应表可知，前6位表示±610米左右的误差，这里先查询前六位范围之后再用上述方法精确筛选一次即可：

可将上述查询方法封装为MySQL函数方便和简化程序调用.

该方法是运用了内置的几何关系运算函数 ST_Contains 和 ST_MakeEnvelop 来实现的，0.5对应大概500米左右的范围，具体如下；

链接: https://pan.baidu.com/s/1cW-kv6DIgtYMw5I3bNFzKA

提取码: jagn

前言：因为最近陆陆续续接手了几个物种的基因组项目，这也是生信分析中很大的一块。其中最基础的是组装和注释（当然我们实验室也做组装方法学的研究）。现在随着很多物种基因组的发表，纯基因组想发个很好的文章没有新颖的故事感觉也挺难的。看最近关于基因组的文章，尤其是已经release过的物种，好像都是在炒泛基因组/SV的概念。因为我们做的是多倍体物种，所以更多的就牵扯到多倍体进化，物种的比较等等比较基因组学的内容。所以最近一直在陆陆续续的系统学习一些这方面的分析。 先介绍几个概念。 Orthologs(直系同源物)是在两个物种的最后共同祖先（LCA）中来自单个基因的一对基因。直系同源物是同源性基因，是物种形成事件的结果。Paralogs（旁系同源物)是同源基因，是重复事件的结果。下图就可以看到，不同物种间的alpha-chain gene互为Orthologs(直系同源物)。这时候可以引用一个新名词orthogroup （正交群）就用来形容自一组物种的LCA中的单个基因的基因组（在图中就是alpha chain gene）。然后同一物种间alpha 和beta chain gene互为Paralogs(旁系同源物)。最后所有这些关系都可以由OrthoFinder来识别。在介绍基因家族收缩和扩张之前，有一个概念是绕不过去的，就是基因家族。   基因家族（gene family），是来源于同一个祖先，由一个基因通过基因重复而产生两个或更多的拷贝而构成的一组基因，它们在结构和功能上具有明显的相似性，编码相似的蛋白质产物，同一家族基因可以紧密排列在一起，形成一个基因簇，但多数时候，它们是分散在同一染色体的不同位置，或者存在于不同的染色体上的，各自具有不同的表达调控模式。在基因组项目中，通常会选择自己要研究的物种和其近缘的物种通过比对来寻找基因家族。 谈论到直系同源基因分析的时候，大部分教程都是介绍OrthoMCL，这是2003年发表的一个工具，目前的引用次数已经达到了3000多，但这个软件似乎在2013年之后就不在更新，而且安装时还需要用到MySQL（GitHub上有人尝试从MySQL转到sqlite）。   而OrthoFinder则是2015年出现的软件，目前已有400多引用。该软件持续更新，安装更加友好，因此决定使用它来做直系同源基因的相关分析。 那么，OrthoFinder能做什么？ OrthoFinder: solving fundamental biases inwhole genome comparisons dramatically improves orthogroup inference accuracy提到，它的优点就是比其他的直系同源基因组的推断软件准确，并且速度还快。   此外它还能分析所提供物种的系统发育树，将基因树中的基因重复事件映射到物种树的分支上，还提供了一些比较基因组学中的统计结果。   OrthoFinder的分析过程分为如下几步:   1. BLAST all-vs-all搜索。使用BLASTP以evalue=10e-3进行搜索，寻找潜在的同源基因。(除了BLAST, 还可以选择DIAMOND和MMSeq2） 2. 基于基因长度和系统发育距离对BLAST bit得分进行标准化。 3. 使用RBNHs确定同源组序列性相似度的阈值 4. 构建直系同源组图(orthogroup graph)，用作MCL的输入 5. 使用MCL对基因进行聚类，划分直系同源组OrthoFinder2在OrthoFinder的基础上增加了物种系统发育树的构建，流程如下： 1. 为每个直系同源组构建基因系统发育树 2. 使用STAG算法从无根基因树上构建无根物种树 3. 使用STRIDE算法构建有根物种树 4. 有根物种树进一步辅助构建有根基因树 5. 基于Duplication-Loss-Coalescent 模型，有根基因树可以用来推断物种形成和基因复制事件，最后记录在统计信息中。===安装=== 对于我这种安装软件总是无能的人，conda真是拯救了我。 conda install -c bioconda -c conda-forge orthofinder ==测试例子运行=== orthofinder -f ExampleData -S mmseqs 注： -f 指定文件夹         -S 指定序列搜索程序，有blast,mmseqs, blast_gz, diamond可用 so easy！！！ OrthoFinder的基本使用就是如此简单，而且最终效果也基本符合需求。   如果你想根据多序列联配(MSA)结果按照极大似然法构建系统发育树，那么你需要加上-M msa。这样结果会更加准确，但是代价就是运行时间会更久，这是因为OrthoFinder要做10,000 - 20,000个基因树的推断。   OrthoFinder默认用mafft进行多序列联配，用fasttree进行进化树推断。多序列联配软件还支持muscle, 进化树推断软件还支持iqtree, raxml-ng, raxml。例如参数可以设置为-M msa -Amafft -T raxml.   并行化参数: -t参数指定序列搜索时的线程数，-a指的是序列搜索后分析的CPU数。 ===结果文件=== 运行结束后，会在ExampleData里多出一个文件夹，Results_ Jun07, 其中Jun07是我运行的日期   (1) Results Files: Orthogroups 包含一个主文件“Orthogroups.csv”和两个支持文件： Orthogroups.csv，每一行为一个group，每一列为一个物种，行列交汇处为基因名称。 Orthogroups_UnassignedGenes.csv，包含所有未分配到任何group的基因名称。 Orthogroups.txt，OrthoMCL格式的输出结果，内容等同于Orthogroups.csv。 （2）Results Files: Comparative_Genomics_Statistics 包含一些统计数据，可用于比较基因组分析、绘图以及质控。 Statistics_Overall.csv和Statistics_PerSpecies.csv，提供基本的描述信息 Orthogroups_SpeciesOverlaps.tsv，两两物种的group共享矩阵 - G50：group中的基因数，使得50％的基因处于该大小或更大的group中。 - O50：最小数量的group，使得50％的基因处于该大小或更大的group中。  - Number of single-copy orthogroups：每个物种中只有一个基因的group（相当于单拷贝核心基因）。这些group是构建物种树和许多其他分析的理想选择。 - Unassigned gene：未与任何其他基因划分到一个group的基因。 (3) Results Files: Orthologues         两两物种间的直系同源基因，每一行为一个group，第一列为group编号，第二列为第一个物种的基因，第三列为第二个物种的基因。同一物种的基因名以“,”分割。直系同源物可以是一对一，一对多或多对多。 (4) Results Files: Gene_Trees and Species_Tree         每个group的基因树和定根的物种树以newick格式输出，可以用各种看树软件展示，如MEGA、iTOL、Dendroscope和FigTree等，个人推荐用iTOL。例子的species的tree展示：===其它用法=== 1.  添加新物种到之前的分析（previous_orthofinder_directory指的是包含“SpeciesIDs.txt”的目录） orthofinder -bprevious_orthofinder_directory -f new_fasta_directory   2. 从之前的分析中移除物种 从输出目录下找到工作目录“WorkingDirectory”中的“SpeciesIDs.txt”文件，在要移除的物种那一行最前面加上一个“#”并保存，然后运行（previous_orthofinder_directory指的是包含“SpeciesIDs.txt”的目录）： orthofinder -bprevious_orthofinder_directory   3. 同时添加和删除物种 编辑好“SpeciesIDs.txt”后，运行： orthofinder -b previous_orthofinder_directory-f new_fasta_directory

说起数据库，大家耳熟能详的商业数据库产品当推Oracle、微软的SqlServer和IBM的

DB2等，而开源数据库中则有两大产品MySQL和PostgreSQL。

PostgreSQL 是一种对象-关系型数据库管理系统（ORDBMS），也是目前功能最强大、

特性最丰富和最复杂的自由软件数据库系统。它起源于伯克利（BSD）的数据库研究计划，

目前是最重要的开源数据库产品开发项目之一，有着非常广泛的用户。

PostgreSQL 可以说是最富特色的自由数据库管理系统，也有人认为可以是最强大的自由

软件数据库管理系统。PostgreSQL 是唯一支持事务、子查询、多版本并行控制系统、数据完

整性检查等特性的唯一的一种自由软件的数据库管理系统。能在多平台下---包括Linux、

FreeBSD和Windows等---运行，并且支持多语言的开发。

在两大开源数据库产品的对比中，一般认为MySQL速度更快，所以得到更为广泛的使

用；而PostgreSQL性能更为先进，PostgreSQL 提供很多 MySQL 目前所不支持的特性，比

如触发器、视图、存储过程等等，在记录数超千万之后性能表现尤其出色。

当前的最新版本是PostgreSQL 8.2.3，官方网站是：

www.postgresql.org

1.2 什么是PostGIS

PostGIS在对象关系型数据库PostgreSQL上增加了存储管理空间数据的能力，相当于

Oracle的spatial部分。PostGIS最大的特点是符合并且实现了OpenGIS的一些规范，是最著

名的开源GIS数据库。

当前的最新版本是PostGIS 1.2.1，官方网站是：

www.postgis.org

二 PostgreSQL和PostGIS的安装

2.1 在windows下安装。

新版本的PostgreSQL在其安装程序中集成了PostGIS，只需要在安装过程中选中

PostGIS和pgsql项就可以了。

2.2 在Debian下安装PostGIS

# apt-get install postgresql-8.1 postgresql-8.1-postgis

当前Etch中的版本：PostgreSQL是8.1.7，而PostGIS是1.1.6，虽然不是最新的版本，

但已经足够了。

还需要做的工作是：

1) 创建一个专门用于PostGIS的数据库并为专用数据库导入PostGIS支持：

# su – postgres

$ cd /usr/share/postgresql-8.1-postgis

$ createdb wen1

$ createlang plpgsql wen1

$ psql -d wen1 -f lwpostgis.sql

$ psql -d wen1 -f spatial_ref_sys.sql

2) 创建一个专门的用户，并把相应的数据库和数据表的属主设置为该用户：

# su – postgres

$ psql

$ create user wen1 password ‘123456’

$ alter database wen1 owner to wen1

$ alter table spatial_ref_sys owner to wen1

$ alter table geometry_columns owner to wen1

3) 修改PostgreSQL配置文件以便可以远程使用。

修改PostgreSQL的配置文件/etc/postgresql/8.1/main/Postgresql.conf，将其中的一句：

listen_address=’localhost’

前面的注释去掉，并把’localhost’该为’*’。

修改Postgresql的配置文件/etc/postgresql/8.1/main/pg_hba.conf，在文件后面加一句：

host all all 192.168.0.0/24 password

这句的意思是：同网络中192.168.0.*的机器可以以密码的形式使用所有的数据库。更具

体的参数意义直接看该配置文件中的注释就可以了。

这里一定要配置正确，否则无法在远程连接PostgreSQL数据库。

2.3 源码安装PostgreSQL和PostGIS

参阅以前写的老文章。

三 PostGIS的使用

要使用PostGIS，需要两方面的预备知识，一是要熟悉基本的SQL语法；二是熟悉

PostgreSQL数据库的基本使用。

(一) 快速入门

我们使用前面创建的数据库wen2，用户为wen1，现在创建一个包含三个点的数据库

test1：

首先在Etch下以wen1登陆，然后打开wen2数据库：

$ psql -d wen2

再在PostgreSQL的控制台下输入以下命令：

wen2=>create table test1 (myID int4, pt geometry, myName

varchar )

wen2=>insert into test1 values (1, 'POINT(0 0)', 'beijing'

)

wen2=>insert into test1 values (2, 'POINT(31.5 60.87)',

'shanghai' )

wen2=>insert into test1 values (3, 'POINT(10.77 85.902)',

'tianjin' )

这样我们利用PostGIS创建了一个包含三个点的GIS数据表。

为了能在QGIS中打开这一图层，我们还需要为数据表创建一个主键：

wen2=>alter table test1 add primary key (myID)

可以看到PostGIS的使用其实就相当于使用一个经过扩展的SQL语法，上述语句熟悉

SQL语法的人一看都很熟悉，都是普通的SQL语句，不同的只不过是增加了PostGIS特殊的

geometry数据类型。

你可以再试试这些SQL语句：

select * from test1

select myID,AsText(pt) from test1

select Distance(pt, 'POINT(0 0)') from test1

(二) PostGIS的Geometry数据类型

Geometry可以说是PostGIS最重要的一个概念，是“几何体”的意思，由于PostGIS很

好地遵守OGC的”Simple Feature for Specification for

SQL”规范，目前支持的几何体类型包

含其实例有：

POINT(1 1)

MULTIPOINT(1 1, 3 4, -1 3)

LINESTRING(1 1, 2 2, 3 4)

POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))

MULTIPOLYGON((0 0, 0 1, 1 1, 1 0, 0 0), (5 5, 5 6, 6 6, 6 5, 5

5))

MULTILINESTRING((1 1, 2 2, 3 4),(2 2, 3 3, 4 5))

而geometry具体表现又有两种形式，一种叫做WKT(Well Known Text)形式，如上面的

例子。或者使用如下SQL语句浏览：

select AsText(pt) from test1

另一种叫做“Canonical Form”形式，看上去是一串古怪的数字，其实是一种增强的十六

进制编码，使用如下SQL语句就可以浏览了：

select pt from test1

(三) 读写PostGIS数据

建设好PostGIS数据库之后，我们现在需要进行读写GIS数据了，包括把GIS写入到

PostGIS数据库中以及在应用程序中使用PostGIS数据库的数据。读写PostGIS目前主要有以

下四种方式：

3.1 使用psql语言

Psql语言是PostgreSQL内嵌的一个命令行工具，其语法基本上和标准的SQL语法是一

致的，可以使用Psql工具，结合标准SQL语法和一些PostGIS的扩展对PostGIS数据库进行

读写 *** 作。

具体例子如上面“快速入门”中的例子。

这种方式功能强大，但全部需要手工 *** 作，烦琐且繁重。

3.2 使用一些小工具

有两个很有用的小的转换工具，一是shp2pg；一是ogr2ogr

3.2.1 shp2pgsql和pgsql2shp

shp2pgsql和pgsql2shp是PostGIS自身携带的一对在Shape文件和PostGIS数据库之间进

行转换的工具，在Debian中安装好PostGIS之后，这两个程序已经位于可执行文件的搜索路

径上，因此可以在系统中任何位置使用。

假如当前目录下有完整的china.shp文件(应该有三个同名不同扩展名的文件)，需要把其

中数据输入到上述的wen2数据库中的数据表china_postgis中，具体 *** 作为( *** 作用户为

wen1)：

$ shp2pgsql china china_postgis >tmp.sql

$ psql -d wen2 -f roads.sql

这个工具很好用，缺点在于使用范围有限，只针对Shape文件格式。

3.2.2 ogr2ogr

PostGIS本身的shp2pg工具只把shape文件转换到PostGIS 数据库中，那如何把其他的

GIS数据加入呢？比如说MapInfo的mid格式，ESRI的e00格式呢？这就要使用ogr这个工

具了。

Ogr目前是gdal的一个组成部分，那什么是gdal呢？其官方主页(http://www.gdal.org)上

如此介绍：

GDAL is a translator library for raster geospatial data formats

that is released under an X/MIT

style Open Source license by the Open Source Geospatial

Foundation. As a library, it presents a

single abstract data model to the calling application for all

supported formats. It also comes with a

variety of useful commandline utilties for data translation and

processing.

简单地说，Gdal是一个各种Gis数据格式的转换软件库，ogr则是转换矢量GIS数据的

软件库。

目前ogr能够支持的数据格式包括：

Arc/Info Binary Coverage、DWG、ESRI Personal

GeoDatabase、ArcSDE、ESRI

Shapefile、GML、GRASS、Mapinfo File、Microstation DGN、ODBC、Oracle

Spatial和

PostgreSQL等。应该说，这就基本包括了我们平常用到的所有矢量型GIS文件格式了。

Gdal支持的栅格数据格式参阅http: //www.gdal.org/formats_list.html

下面我们从源码编译安装gdal---因为我在Debian下使用apt方式安装的gdal并不支持

postgresql数据库，其实更为简便的方法也许是去gdal.org下载一个Fwtools的工具包，可以

直接运行不用繁琐的编译，既有Windows的，也有Linux下用的，只不过这个包有些大。

1) 下载最新的gdal-1.3.2

2) 解压

3) ./configure --prefix=/opt/gdal

--with-pg=/opt/pg/bin/pg_config --without-ogdi

这里我习惯把软件安装在/opt目录下。

--with-pg参数很重要，使ogr可以支持PostGIS，后面的参数是我的PostGIS安装的地方。

我在这里编译很多次失败，经过仔细查找，发现问题出在ogdi上，所以暂时我只好使

它不支持ogdi。

4) make

5) make install

下面是使用过程，假设现在我要把一个rai.mid文件导入到数据库data1中：

$ ogr2ogr –f Postgresql PG:dbname=data1 rai.mid

注意，f参数后面跟的是导入的数据类型，最后那个文件才是要导出的文件。PG后面说

明的是数据库的名字，需要是已经创建好的数据库，而数据表则由程序自动创建。还要特别

注意权限问题，以上命令我是由postgres用户执行的。

还有一个要注意的是主键问题。一般情况下，你使用shp2pg或者 ogr2ogr向postgis中

增加了Gis数据后，在GIS客户端添加postgis图层的时候，常常会因为数据表没有定义主键

而出错，解决的办法是，在服务器上使用psql或者其他sql工具，使用命令：

alter table table_name add primary key (column_name)

另外我们发现ogr竟然是不支持最常见的GIS格式e00格式，好象是因为版权方面的原

因吧。解决的方法就是去sf.net查找相关工具。我在上面就找到一个e002pg工具，支持将

e00文件导入到postGIS数据库中。

3.3 在其他GIS软件中读写PostGIS数据

比如在QGIS中，能够打开PostGIS图层，还有SPIT插件可以把Shape文件输入到

PostGIS数据库中。

其他GIS软件如uDig，Grass等，甚至连ArcInfo都支持或部分支持读写PostGIS数据。

3.4 利用接口在应用程序中读写PostGIS数据

广大的开源GIS程序员几乎为每一种程序设计语言设计好了读写PostGIS的接口，如利

用PostgreSQL的JDBC库，可以使用Java语言在程序中读写PostGIS数据；利用libpq库，

可以使用C语言读写PostGIS数据。

(四) 使用PostGIS函数

4.1 PostGIS函数

要能熟练使用PostGIS，掌握PostGIS的所有函数是关键。通过掌握这些PostGIS函数，

我们能够高效、稳定地处理各种地理数据。由于PostGIS的函数设计时都遵守OpenGIS规范，

我们也可以把这些PostGIS叫做OpenGIS函数。

4.2 PostGIS函数的分类

PostGIS函数大致可以分为以下四类

1) 字段处理函数

这类函数当前一共有3个，分别是：

AddGeometryColumn(var1,var2,var3,var4,var5,var6)：为已有的数据表增加一个地理几何

数据字段。Var1代表数据表的模式(schema)的名字，一般是public，也可以省略，则使用当

前的缺省模式；var2是已有的数据表的名字；var3是新的地理数据字段的名字；var4是

SRID值，不确定的话就取-1吧；var5是地理数据的类型，可以是POINT等；var6是指该几

何数据是二维还是三维数据。

前面的SQL语句 create table test1 (myID int4, pt geometry, myName

varchar )更规范的写

法为：

create table test1 (myID int4, myName varchar )

select AddGeometryColumn('test1','pt',-1,'GEOMETRY',2)

DropGeometryColumn函数显然是删除一个地理数据字段的；

SetSRID函数显然是设置SRID值的。

2) 几何关系函数

这类函数目前共有10个，分别是：

Distance Equals Disjoint Intersects Touches Crosses Within

Overlaps Contains Relate

3) 几何分析函数

这类函数目前共有12个，分别是：Centroid Area Lenth PointOnSurface Boundary

Buffer

ConvexHull Intersection SymDifference Difference GeomUnion

MemGeomUnion

4) 读写函数

这类函数很多，主要是用于在各种数据类型之间的转换，尤其是在于Geometry数据类

型与其他如字符型等数据类型之间的转换，函数名如AsText、GeomFromText等，其作用是

显然的。

4.3 PostGIS函数使用示例。

假设在我们的wen2数据库中，有两张表，一张为guojia，是从“国家基础地理数据”

网站下载的国界数据表，数据类型为LINE；二为shengqu_polygon，也从同一个网站下载，

地理数据类型为多边形。

1) 查询我国边境线的长度：

wen1=>select sum(length(the_geom)) as lenth from guojie

2) 查询我国面积最大的省区名字：

wen1=>select name area(the_geom) as myarea

from shengqu_polygon

order by myarea DESC

LIMIT 1

(五) 使用PostGIS扩展函数

除了上述遵循OpenGIS的函数之外，PostGIS还自行扩展了一些当前OpenGIS规范之外

的函数，主要包括以下几类：

5.1 管理类函数

扩展的管理类函数主要包括一些软件版本查询函数，如

postgis_version()、postgis_geos_version()、postgis_proj_version()函数等，分别查询当前的

PostGIS的版本及其使用的Geos和Proj库的版本。

5.2 数据类型的输入输出函数

除了OpenGIS定义的地理数据类型之外，PostGIS还对数据类型进行了扩展，这种扩展

主要是两方面的扩展，一是把二维的数据向三维和四维扩展；二就是在WKT和WKB数据

类型基础上扩展出EWKT和EWKB数据类型。

PostGIS提供了在这些地理数据类型和常用数据类型如字符型、浮点型数据之间进行转

换的函数。

5.3 量算函数

如length3d函数是对length2d函数的扩展。

5.4 几何 *** 作函数

如addBBox(geometry)函数给所给的几何体加上一个边框。

如simplify(geometry,tolerance)函数可以对折线和多边形利用Douglas-Peuker算法进行一

些节点进行删除，从而使表现的图形更简单而清晰，在网络传输数据时具有更高的效率。

5.5 *** 作符号

5.6 其他扩展函数

(六) 建立PostGIS索引

当数据库的记录增大的时候，如果没有建立索引的话， *** 作的效率就显著下降。

POstGIS建议当记录数超过几千的时候就应该建立索引，而GIS数据库一般都是海量数据，

所以对PostGIS而言，索引就非常重要。

Shapfile文件为ESRI公司的文件存储格式，并且得到了业界广泛的支持。Shapfile格式是一种简单的，用非拓朴关系的形式存储几何位置和地

理特征的属性信息的格式。虽然GeoServer采用Shapfile文件可以快速的创建网上地图服务，但它的缺点确很明显：

1、Shapefile只支持一个图层，在实际中没有意义。

2、直接保用SHP文件不安全，Shapfile文件很容易被病毒或其他原因误删除。

3、GeoServer中用Shapfile文件作数据源的效率是很低的。

4、Shapfile中的汉字GeoServer不能解析，会出现乱码。

5、数据库可以方便的对地理信息进行查询。

用PostGIS管理空间数据

PostGIS支持GIST空间索引（附录1）、规范窗体，能很大的提高处理效率。

OGC格式只提供二维的几何体，且相关联的SRID从未深入的用于输入输出请求，PostGIS支持OpenGIS组织"Simple

Features for

SQL"规范指定的所有GIS对象和函数，并进行了扩展，格式是EWKB、EWKT，其中增加了对3DZ，3DM和4D

坐标系的支持(当然三维、四维数据的OGC标准还未完全制定)，深入引进了SRID信息。

空间数据表结构：PostGIS中存在两个必需的元数据表格， SPATIAL_REF_SYS（空间参考表格） 和

GEOMETRY_COLUMNS（几何体属性列），两个表用于存储空间数据库使用的坐标系统数字ID和文本描述。

PostGIS的shp2pgsql命令可以将Shapfile直接导入到数据库中也可以导出为SQL文件，推荐先导出为SQL文件再将此文件在SQL运行窗口中执行可将数据导入数据库。

Shapfile到SQL语句：

shp2pgsql路径shp数据文件名 新建的数据表名 >

路径SQL文件名.sql

Shapfile直接入库：

shp2pgsql -c 路径shp数据文件名 新建的数据表名 数据库名｜psql -d 数据库名

举例说明：

如将一Shapfile文件“c:road.shp”导入到数据表“road”中，数据库为“sjzmap”。

1、运行“命令提示符”。

2、切换至PostgreSQL数据库安装目录中的bin目录下。

3、执行此目录下的shp2pgsql命令：“shp2pgsql c:road.shp road >

c:road.sql”。

4、如将此文件直接导入数据库（不推荐）：“shp2pgsql -c c:road.shp road

sjzmap | psql -d sjzmap”。

5、使用pgAdmin3

选择数据库，再导入表。

---