（文档）PG第115讲-空间数据库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;

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

• 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、查看表结构，增加了一个存储空间的列

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;

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

SELECT p1.name,p2.name,ST_DistanceSphere(p1.the_geom,p2.the_geom) FROM cities AS p1, cities AS p2 WHERE p1.id > p2.id;

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 在内的多种客户端均可以。

PostGIS应用场景

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