如何通过DB操作地理空间数据

?从公众号转载，关注微信公众号掌握更多技术动态

---------------------------------------------------------------

一、PostgreSQL

????PostgreSQL是一个强大的对象关系数据库管理系统（ORDBMS）。它是在BSD风格的许可下发布的，因此是自由和开放源代码的软件。和许多其他开源程序一样，PostgreSQL不是由任何一家公司控制、运维的，而是有一个由众多开发人员和公司组成的全球社区来开发它。

????PostgreSQL从一开始就考虑到类型扩展?—— 能够在运行时添加新的数据类型、函数和访问方法的机制。正因为如此，PostGIS扩展可以由单独的开发团队开发，但仍然可以非常紧密地集成到PostgreSQL数据库中。

1.PostgreSQL和MySQL对比

(1)PostgreSQL相对于MySQL的优势

• 在SQL的标准实现上要比MySQL完善，而且功能实现比较严谨；

• 存储过程的功能支持要比MySQL好，具备本地缓存执行计划的能力；

• 对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强；

• PG主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。

• PG的主备复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。

• MySQL的存储引擎插件化机制，存在锁机制复杂影响并发的问题，而PG不存在。

(2)MySQL相对于PG的优势

• innodb的基于回滚段实现的MVCC机制，相对PG新老数据一起存放的基于XID的MVCC机制，是占优的。因此MySQL的速度是高于PG的；

• MySQL采用索引组织表，这种存储方式非常适合基于主键匹配的查询、删改操作，但是对表结构设计存在约束；

• MySQL的优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作；

• MySQL分区表的实现要优于PG的基于继承表的分区实现，主要体现在分区个数达到上千上万后的处理性能差异较大。

??? PG具备更高的可靠性，对数据一致性完整性的支持高于MySQL，因此PG更加适合严格的企业应用场景（比如金融、电信、ERP、CRM）；而MySQL查询速度较快，更加适合业务逻辑相对简单、数据可靠性要求较低的互联网场景（比如google、facebook、alibaba）

2.表空间

????有时我们需要把不同的表放到不同的存储介质或不同的文件系统下，这时就需要使用表空间。表空间允许数据库管理员在文件系统中定义用来存放表示数据库对象的文件的位置。一旦被创建，表空间就可以在创建数据库对象时通过名称引用。通过使用表空间，管理员可以控制一个PostgreSQL安装的磁盘布局。这么做至少有两个用处：

• 如果初始化集簇所在的分区或者卷用光了空间，而又不能在逻辑上扩展或者做别的什么操作，那么表空间可以被创建在一个不同的分区上，直到系统可以被重新配置。

• 表空间允许管理员根据数据库对象的使用模式来优化性能。例如，一个很频繁使用的索引可以被放在非常快并且非常可靠的磁盘上，如一种非常贵的固态设备。同时，一个很少使用的或者对性能要求不高的存储归档数据的表可以存储在一个便宜但比较慢的磁盘系统上。

3.模式

????模式是数据库领域的一个基本概念，有些数据库把用户和模式合二为一了，而PG中有清晰的模式定义，相对更加灵活自由。用户更加方便。

那么什么是模式呢？模式是数据库中的一个概念，可以将其理解为一个命名空间或目录，不同的模式下可以有相同名称的表、函数等对象而不会产生冲突。提出模式的概念是为了便于管理，只要有权限，各个模式的对象可以互相调用。如果没有模式的概念，那么不同用户或者开发者在创建各自数据库对象命名时可能会发生冲突以及相互干扰等问题，有了模式，就可以避免这些问题。总结起来模式有如下好处：

• 允许多个用户使用同一个数据库且用户之间不会互相干扰。

• 把数据库对象放在不同的模式下组织成逻辑组，使数据库对象更加便于管理

• 第三方应用可以放在不同的模式中，这样就不会和其他对象的名字产生冲突了。

????一个数据库包含一个或者多个模式，模式中又包含了表、函数以及操作符等数据库对象。其中的层级关系是：数据库-模式-对象。需要注意的是，虽然能创建多个数据库实例，但不能同时访问不同数据库中的对象，

当需要访问另一个数据库中的表或其他对象时，需要重新连接到这个数据库，而模式却没有此限制，一个用户在连接到一个数据库后，就可以同时访问这个数据库中多个模式的对象。

????所以，在使用MySQL或Oracle的用户迁移到Postgres时，需要注意其中的不同之处。

4.序列

????在 PostgreSQL 数据库中，一个“序列”是一个数据库对象，它可以自动为整数字段生成一个唯一的值。这通常用于创建自增的主键。

创建序列

创建一个新的序列可以通过?CREATE SEQUENCE?语句来完成。例如：

sqlCopy codeCREATE SEQUENCE my_sequence START WITH 1 INCREMENT BY 1;

这将创建一个新的序列，起始值为 1，并

5.常用语法

①字符串拼接

pg的字符串连接使用 ||，注意不是+

#将2个字符串hello和word拼接在一起SELECT 'hello' || 'world';--结果: helloworld
#将3个字符串hello,空格和word拼接在一起SELECT 'hello' || ' ' || 'world';--结果：hello world
#将字符串hello和数字123456拼接在一起SELECT 'hello' || 123456;--结果：hello123456

②查找字符位置

注：position函数返回值是从1开始的，不是从0开始的下标值。如果返回0表示没找到字符。

#查找@在字符串hello@163.com中的位置SELECT position('@' IN 'hello@163.com');
#查找b在字符串hello@163.com中的位置注：因为b不在字符串hello@163.com中，返回0，表示没找到字符b。SELECT position('b' IN 'hello@163.com');
#查找子字符串函数：substring(‘hello@163.com’, start, count);参数1:字符串，参数2:起始位置，参数3:count注意：start的位置, count值的区别查询子字符串hello方法1. start=1,count=5SELECT substring('hello@163.com',1,5);--结果：hello
方法2. start=0,count=6SELECT substring('hello@163.com',0,6);--结果：hello

二、PostGIS介绍

1.什么是空间数据库

????PostGIS是一个空间数据库，Oracle Spatial和SQL Server(2008和之后版本）也是空间数据库。空间数据库像存储和操作数据库中其他任何对象一样去存储和操作空间对象。

空间数据与数据库关联起来的三个要素：数据类型、索引和函数

• 空间数据类型用于指定图形为点（point）、线（line）和面（polygon）

• 多维度空间索引被用于进行空间操作的高效处理（注意是多维度哦，而不是只有针对二维空间数据的索引）

• 空间函数构建于SQL语言中，用于进行空间属性和空间关系的查询

????空间数据类型、空间索引和空间函数组合在一起，提供了灵活的结构用于空间数据库的性能优化和分析。

(1)开端

????在传统的第一代地理信息系统（GIS）实现中，所有的空间数据都存储在平面文件中，需要专门的GIS软件来解释和操作这些数据。这些第一代管理系统旨在满足用户的需求，其中所有所需的数据都在用户的组织领域中。它们是专为处理空间数据而构建的专有的、独立的系统，即应用程序和平面文件之间的耦合性非常高，平面文件里的空间数据没有数据独立性。

????第二代空间系统将一些数据存储在关系数据库（RDBMS）中（通常是“属性”或非空间信息部分），但仍然缺乏直接集成所具有的灵活性，包含空间信息部分的数据仍然没有数据独立性。

????真正的空间数据库诞生于人们开始把空间要素当作第一级数据库对象的时候。

????空间数据库将空间数据和对象关系数据库（Object Relational database）完全集成在一起。实现从以GIS为中心向以数据库为中心的转变。

????有了空间数据库之后，就不再需要专门的GIS数据引擎（GIS Data Engine）去处理和操纵空间数据了，应用程序只需要通过SQL语言就能轻松地操纵空间数据。空间数据库管理系统也可用于地理信息以外的应用。例如，空间数据库可以用于管理与人体解剖、大规模集成电路、分子结构和电磁场等相关的数据。

(2)空间数据类型

????普通数据库拥有字符串（string）、数值（number）和日期（date）这些数据类型，空间数据库添加了额外的数据类型（空间数据类型）以用于表达地理特征（geographic features）。

????这些空间数据类型抽象并封装了诸如边界（boundary）和维度（dimension）等空间结构。在许多方面，空间数据类型可以简单的理解为形状（shape）

????空间数据类型按类型层次结构组织。每个子类型继承其父类型的结构（属性）和行为（方法或函数）。

(3)空间索引和边界框（bounding box）

????普通数据库提供索引机制以允许对数据子集进行快速、随机地访问。标准的数据类型（number、string、date）的索引通常是B-tree索引（B树索引），B树索引使用自然排序顺序（natural sort order）对数据进行分区，以便将数据放入分层树中。

????数字、字符串和日期的自然排序顺序很容易确定 —— 每个值都小于、大于或等于其他值。但是由于多边形（Polygon）可以重叠，可以相互包含，并且可以排列在二维（或更多维数）空间中，因此无法使用B树索引有效地索引它们。

????空间数据库提供了一个“空间索引（spatial index）”，它回答了“哪些对象在这个特定的边界框内？”这个问题。边界框（bounding box）—— 或者称为包围盒BBOX，——是平行于坐标轴且包含给定地理要素（feature）的最小的矩形。

????使用边界框是为了判断”A被包含在B中吗？"这个问题，对多边形进行计算，计算量非常大而且难以计算，但在计算矩形的情况下，计算比较容易，而且速度非常快。即使是最复杂的多边形和线串（LineString)也可以用一个简单的边界框来表示。

????索引必须快速执行才能起到理想的作用。因此，空间索引不像B树索引那样提供精确的结果，而是提供近似的结果。"多边形内部包含哪些线段“将由空间索引解释为”这个多边形边界框内部包含哪些线段边界框？“

各种数据库实际实现的空间索引差异很大，最常见的实现是R-tree（在PostGIS中使用），但在其他空间数据库中也有基于四叉树（Quadtrees）的实现和基于网格的索引（grid-based indexes）的实现。

(4)空间函数

????关于查询的数据操作，普通数据库提供的函数功能包括连接字符串、对字符串执行哈希操作、对数值进行数学运算以及从日期中提取信息等。

空间数据库为分析几何信息、确定空间关系和操作几何图形提供了一套完整的空间函数。

空间函数中的大部分可以被归纳为以下五类：

• 转换 —— 在geometry（PostGIS中存储空间信息的格式）和外部数据格式之间进行转换的函数

• 管理 —— 管理关于空间表和PostGIS组织的信息的函数

• 检索 —— 检索几何图形的属性和空间信息测量的函数

• 比较 —— 比较两种几何图形的空间关系的函数

• 生成 —— 基于其他几何图形生成新图形的函数

????函数列表可能非常长，OGC?SFSQL定义了一组通用空间函数规范，PostGIS实现了这些规范（并另外实现了其他有用的空间函数）。

2.PostGIS是什么

????PostGIS通过向PostgreSQL添加对空间数据类型、空间索引和空间函数的支持，将PostgreSQL数据库管理系统转换为空间数据库。因为PostGIS是建立在PostgreSQL之上的，所以PostGIS自动继承了重要的"企业级"特性以及开放源代码的标准。可以说PostGIS仅仅只是PostgreSQL的一个插件，但是它将PostgreSQL变成了一个强大的空间数据库！

(1)为什么选择PostgreSQL？

????熟悉开源数据库的人提出的一个常见问题是：“为什么PostGIS不是基于MySQL构建的？”

PostgreSQL的特点：

• 被证明默认情况下强大的可靠性和事务完整性（ACID）

• 严谨地支持SQL标准（完整SQL92)

• 可插、拔的类型扩展和功能扩展

• 面向社区的发展模式（开源）

• 不限制列大小（"TOAST"机制-The Oversized-Attribute Storage Technique，超尺寸属性存储技术））以支持大型GIS对象

• 通用索引结构（Generic Index Structure - GIST）允许R-Tree索引

• 易于添加自定义功能、函数

????这些因素结合在一起，PostgreSQL提供了一条非常简单的开发路径来添加新的空间类型。在私有或商业软件世界中，只有Illustra（现在的?Infomix Universal Server）允许这么容易的扩展。这并不是巧合，Illustra是80年代以来对原始PostgreSQL代码库的私有改造。

????因为将类型添加到PostgreSQL的开发路径非常简单，所以使用PostgreSQL是正确的。当MySQL在版本4.1中发布基本空间数据类型时，PostGIS团队查看了它们的代码，这坚定了最初使用PostgreSQL的决定。????因为MySQL空间对象必须作为一种特殊情况被强行添加在字符串类型的顶部，所以MySQL代码分散在整个代码库中。PostGIS 0.1的开发花费了不到一个月的时间，但做一个“MyGIS" 0.1可能需要更长的时间，可能永远也不会成功。

3.geometry和geography

geometry：既可以存放平面坐标系又可以存放地理坐标系数据

geography：地理坐标系，在POSTGIS1.5版本添加的此功能

4.shapfile

????一个"shapefile"通常指带有.shp、.shx、.dbf和其他扩展名且前缀名称一致的文件集合。

一个shapfile必须有的文件：

• .shp —— 存储地理要素的几何信息

• .shx —— 存储要素几何图形的索引信息

• .dbf —— 存储地理要素的属性信息（非几何信息）

可选文件包括：

• .prj —— 存储空间参考信息，即地理坐标系统信息和投影坐标系统信息。使用well-known文本格式进行描述。

????PostGIS shapefile工具将shapefile数据从二进制转换为一系列的SQL命令，然后在数据库中运行以加载数据，从而使shapefile数据在PostGIS中可用。