PostgreSQL技术大讲堂 时空数据库--PostGIS使用技巧 陈卫星老师

主要内容：时空数据库--PostGIS使用技巧

1、PostGIS概述

2、PostGIS安装

3、PostGIS使用技巧与应用场景

PostGIS是地理信息、时空数据库, 包括空间类型、轨迹等类型, 基于这些类型的索引, 操作符等。应用广泛, 例如地图、基于地理位置的社交、共享出行、外卖等应用。

Objectives

　　• PostGIS概述

　　• PostGIS安装

　　• PostGIS使用技巧与应用场景

PostGIS概述

　　• PostGIS通过向PostgreSQL添加对空间数据类型、空间索引和空间函数的支持，将PostgreSQL数据库管理系统转换为空间数据库。

　　• 因为PostGIS是建立在PostgreSQL之上的，所以PostGIS自动继承了重要的"企业级"特性以及开放源代码的标准。

　　• PostGIS仅仅只是PostgreSQL的一个插件，但是它将PostgreSQL 变成了一个强大的空间数据库!

　　• Oracle Spatial和SQL Server(2008和之后版本)也是空间数据库。

PostGIS发展历史

　 • 2001年5月，Refractions Research 发布了第一版PostGIS。PostGIS 0.1 具有空间对象、空间索引和一些空间函数。PostGIS 0.1是一个适合存储和检索的数据库，但不适合分析。

　　• 随着空间函数数量的增加，相关标准化组织的需求变得明确。开放地理空间联盟(OGC)的“Simple Features for SQL”(SFSQL)规范提供了 函数命名和要求的指导性原则。

　　• 后续版本中，PostGIS函数的数量有所增加，但其功能仍然有限。许多函数(如ST_Intersects()、ST_Buffer()、ST_Union()) 都很难编写，从头开始写这些函数花费了几年时间。

　　• “Geometry Engine, Open Source” (GEOS)库为实现SFSQL规范提供了必要的算法。通过结合GEOS， PostGIS在0.8版中提供了对SFSQL的完整支持。

　　• 随着PostGIS数据容量的增长，用于存储几何图形的描述(元数据)被证明效率相对较低。对于像点和短线这样的存储对象，元数据的描述占据了多达300%的开销。在PostGIS 1.0中，通过缩减元数据头和所需的维度，大大减少了开销。

　　• PostGIS最新的更新致力于提高对于标准的遵从性，增加了对ISO SQL/MM 标准中制定的基于曲线的几何图形和函数签名的支持。

　　• PostGIS 1.4大大提高了几何图形测试例程的速度。

PostGIS安装

　　1、下载地址：https://postgis.net

　 2、安装所需要的系统软件包： libxml2 libxml2-dev gdal-bin libgdal-dev

　　3、源代码安装三步骤：

　　./configure

　　make

　　make install

　　4、安装插件

　　create extension postgis;

　　• 安装完插件后产生以下表和视图：

550.jpg

　　• spatial_ref_sys ：存储着合法的空间坐标系统。

　　• geometry_columns ：用于记录那些表格是有几何信息的。

　　• geography_columns：用于记录那些表格是有地理信息的

　PostGIS几何关系函数

　　• 这类函数描述几何对象的距离、包含、范围、相等等几何关系，常见函数如下：

　　ST_Distance

　　ST_Equals

　　ST_Disjoint

　　ST_Intersects

　　ST_Touches

　　ST_Within

　　ST_Overlaps

　　ST_Contains

PostGIS读写函数

　　• 这类函数主要用于各种数据类型之间的转换，尤其是Geometry数据类型与其他字符型等数据类型之间的转换，常见函数如下：

　　ST_AsText

　　ST_GeomFromText

　　ST_AsGeoJSON

　　ST_AsHEXEWKB

　　ST_AsKML

　　ST_AsLatLonText

PostGIS几何对象创建函数

　　• 这类函数用于点、线、多变形等几何对象创建，常见函数如下：

　　ST_GeomFromEWKT

　　ST_GeomFromEWKB

　　ST_MakePoint

　　ST_MakeBox2D

　　ST_LineFromText

　　ST_Polygon

PostGIS几何对象编辑函数

　　• 这类函数提供对几何图像的平移、翻转、旋转、放大等功能，常见函数如下：

　　ST_AddPoint

　　ST_Reverse

　　ST_Rotate

　　ST_Scale

　　ST_Snap

　　ST_Transform

　　ST_Translate

　　ST_TransScale

PostGIS空间关系及测量函数

　　• 这类函数实现几何对象最远、最近、长度、面积等计算，常见函数如下：

　　ST_3DClosestPoint

　　ST_3DDistance

　　ST_3DDWithin

　　ST_3DDFullyWithin

　　ST_3DIntersects

　　ST_3DLongestLine

　　ST_3DMaxDistance

　　ST_3DShortestLine

　　ST_Area

PostGIS使用技巧一

　　1、创建存储有关城市(cities)的信息，一个是 ID 编号，一个是城市名

　　db1=# CREATE TABLE cities ( id int4, name varchar(50) );

　　2、添加一个空间栏用于存储城市的位置。习惯上这个栏目叫做 the_geom 。它记录了数据是什么类型(点、线、面)、有几维(这里是二维)以及空间坐标系统。此处使用 EPSG:4326 坐标系统

　　db1=# SELECT AddGeometryColumn ('cities', 'the_geom', 4326, 'POINT', 2);

　　3、查看表结构，增加了一个存储空间的列

551.jpg

　　4、插入数据，对于空间栏，使用 PostGIS 的 ST_GeomFromText 可以将文本转化为坐标与参考系号的记录

　　db1=# INSERT INTO cities (id, the_geom, name) VALUES (1,ST_GeomFromText('POINT(-0.1257 51.508)',4326),'London, England');

　　db1=# INSERT INTO cities (id, the_geom, name) VALUES (2,ST_GeomFromText('POINT(-81.233 42.983)',4326),'London, Ontario');

　　db1=# INSERT INTO cities (id, the_geom, name) VALUES (3,ST_GeomFromText('POINT(27.91162491 -33.01529)',4326),'East London,SA');

　　5、简单查询

　　select * from cities;

　　上面简单查询出来的坐标是无法阅读的 16 进制格式。要以 WKT 文本显示，使用 ST_AsText() 或 ST_AsEwkt() 函数,也可以使用 ST_X() 和 ST_Y() 显示一个维度的坐标：

　　SELECT id, ST_AsText(the_geom), ST_AsEwkt(the_geom), ST_X(the_geom), ST_Y(the_geom) FROM cities;

552.jpg

　　6、空间查询，PostGIS 为 PostgreSQL 扩展了许多空间操作功能。以上已经涉及了转换空间坐标格式的 ST_GeomFromText 。多数空间操作以 ST(spatial type)开头，在 PostGIS 文档相应章节有罗列。这里回答一个具体的问题：以米为单位并假设地球是完美椭球，上面三个城市相互的距离是多少?

552.jpg

553.jpg

PostGIS使用技巧二

　　1、创建POI表, 表示地图上的餐饮店位置

　　create table tbl_poi (

　　id int primary key,

　　info text,

　　pos geometry

　　);

　　2、生成10万条记录, 经纬度圈定在一个范围内随机生成

　　insert into tbl_poi select generate_series(1,100000), md5(random()::text),

　　ST_SetSRID(ST_MakePoint(120+random(), 35+random()),4326);

　　3、输入一个经纬度, 由近到远排序输出10条记录

　　select id,info,pos <-> ST_SetSRID(ST_MakePoint(120, 35),4326) as distance

　　from tbl_poi order by pos <-> ST_SetSRID(ST_MakePoint(120, 35),4326) limit 4;

　　4、创建索引

　　create index on tbl_poi using gist (pos);

　　5、使用索引加速, 输入一个经纬度, 由近到远排序输出10条记录

　　select id,info,pos <-> ST_SetSRID(ST_MakePoint(120, 35),4326) as distance

　　from tbl_poi order by pos <-> ST_SetSRID(ST_MakePoint(120, 35),4326) limit 5;

　　6、通过索引访问的执行计划

　　explain select id,info,pos <-> ST_SetSRID(ST_MakePoint(120, 35),4326) as distance

　　from tbl_poi order by pos <-> ST_SetSRID(ST_MakePoint(120, 35),4326) limit 5;

　　QUERY PLAN

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

　　Limit (cost=0.28..13.25 rows=5 width=45)

　　-> Index Scan using tbl_poi_pos_idx on tbl_poi (cost=0.28..259358.28 rows=100000 width=45)

　　Order By: (pos <-> '0101000020E61000000000000000005E400000000000804140'::geometry)

PostGIS制图

　　以 PostGIS 数据制图需要相应的客户端支持。包括 Quantum GIS、gvSIG、uDig 在内的多种客户端均可以。

554.jpg

PostGIS应用场景

　　• 地理数据包括观测数据、分析测定数据、遥感数据和统计调查数据。地理数据库已广泛的应用于单车、导航，旅游、水利，农业、安平城市等应用场景，渗透到人民生活点点滴滴中。

555.jpg