PostgreSQL的GIS、GEO入门笔记

参考内容

在PG10.X中需要用到存储经纬度和经纬度查找，本篇用作记录

刚开始百度的过程中总是看到各种PGSQL+PostGIS+OpenLayer或者什么GeoServer狼神的文章，对于我一个小白来说完全是一脸懵逼。在PGSQL中找到了几何数据类型，而且我也只是想保存个位置点然后查询附近的点或者计算下距离这种简单的应用，给我来一堆这么麻烦的东西显然会懒得看。

带着懒癌晚期患者的心情，开始慢慢入门了！

名词解释

PostgreSQL

嗯，你要是不知道就别看这篇文章了，没啥意义

PostGIS

这个是当时比较头疼的，为啥会有这么个东西呢？难道数据库本身就没法用这玩意儿？？？难道PG就不能像MySQL一样的简单吗？详情见我另一篇文章【MySQL的GIS、GEO入门笔记】

1986年，加州大学伯克利分校的Michael Stonebraker教授领导了Postgres的项目，它是PostgreSQL的前身。随后出现了PostGIS，PostGIS是对象－关系型数据库系统PostgreSQL的一个扩展，它的出现让人们开始重视基于数据库管理系统的空间扩展方式，而且使PostGIS有望成为今后管理空间数据的主流技术。

“关系型数据库+空间数据引擎”通常是近年来由GIS厂商研发的一种中间件解决方案。用户将自己的空间数据交给独立于数据库之外的空间数据引擎，有空间数据引擎来组织空间数据在关系型数据库中的存储;当用户需要访问数据的时候，再通知空间数据引擎，有引擎从关系型数据库中取出数据，并转化为客户可以使用的方式。

PostGIS在对象关系型数据库PostgreSQL上增加了存储管理空间数据的能力，相当于Oracle的spatial部分。PostGIS最大的特点是符合并且实现了OpenGIS的一些规范，是最著名的开源GIS数据库。

简单理解：PostGIS是PGSQL的一个扩展，既然是扩展，那就需要进行安装，而不是直接就带着的
PostGIS类似Oracle上的spatial（没接触过Oracle中的GIS）
PGSQL如果想用地理位置相关的功能，就要安装PostGIS
更详细介绍，推荐参阅：【PostGIS教程一：PostGIS介绍】

SRID

没错，第一次接触的时候我也是一脸懵逼，这是个啥？这么多怎么选？？？而且大多没有深入的介绍，都是一些草草描述。以下摘自百度百科。。。

SRID：Spatial Reference System Identifier，理解为空间引用标识符，或者说空间坐标系？

空间引用标识系统是由欧洲石油测绘组 (EPSG) 标准定义的，它是为绘图、测绘以及大地测量数据存储而开发的一组标准。该标准归石油天然气生产商 (OGP) 测绘和定位委员会所有。

几何图形实例默认 SRID 为零（就是几何图形！什么点线面体狼神的）

PGSQL中 geometry 实例的默认 SRID 为 0。利用 geometry 空间数据，执行计算是不需要空间实例的指定 SRID 的；因此，实例可驻留在未定义的平面空间。

地域实例必须使用支持的 SRID

PGSQL支持基于 EPSG 标准的 SRID。必须使用 geography 实例的支持 PGSQL 的 SRID 执行计算或将方法用于地域空间数据。SRID 必须与 sys.spatial_reference_systems 目录视图中显示的 SRID 中的一个匹配。如前所述，在使用 geography 数据类型对空间数据执行计算时，结果将取决于在创建数据时使用的是哪个椭圆体，因为为每个椭圆体都分配了一个特定空间引用标识符 (SRID)。

对 geography 实例使用方法时，PGSQL使用等于 4326 的默认 SRID，它将映射到 WGS84 空间引用系统。如果要使用 WGS84 （或 SRID 4326）之外的某个空间引用系统中的数据，您需要确定地域空间数据的特定 SRID。

WGS84、GCJ02

关于这个，相信接触过位置开发的小伙伴都多少了解过，为什么坐标定位会有偏移？那可能你的GCJ02（国测局02年制定的坐标系，又被大家称为火星坐标系）没有转成WGS84导致的。更多的内容请自行了解一下。这个对于位置的计算来说很重要。

总结来说就是：

来自GPS的信号的坐标基本都是WGS84坐标系（Android和iOS都不会转的，需要自己手动转换）
对于微信来说，来自腾讯地图上的坐标（不论是点选的还是怎么着的），都是加密后的GCJ02坐标系
对于微信公众号来说，公众号开启的获取用户位置功能返回的就是WGS84的坐标（恶不恶心？可能对于地图服务商来说不准提供WGS84的坐标，但是手机GPS获取的都是WGS84的坐标，所以就会出现这种情况啦！）

开始入门

一、PostGIS的安装

常规流程请参考：【PostGIS教程二：PostGIS安装和创建空间数据库】
首先进行本环节前默认你已经安装了PostgreSQL了！如果你的StackBuilder可以正常安装，那么可以直接通过StackBuilder安装的（我的不行，各种报错），所以这里提供给你一个补救措施：手动安装步骤

有兴趣的可以前往PostGIS官网下载页面查看扩展安装包的安装说明：【传送门】
找到下载页面上如图的位置，下载离线的exe安装文件

image.png

没啥兴趣的话，请直接打开这个官网的下载页面,根据你的PGSQL版本下载对应的.exe即可，一般都可以安装最新版的PostGIS
下载后运行exe安装即可。在设置安装组件时，最好选择"Create spatial database"创建一个新的模板库，以便在创建数据库时可以以此作为模板。当然创建在哪里其实没有多大影响，在当前数据库或者新建一个都可以。

二、启用PostGIS扩展

在安装好PostGIS扩展后，是需要在数据库中启用该扩展才可以生效的，官网描述：

PostGIS is an optional extension that must be enabled in each database you want to use it in before you can use it. Installing the software is just the first step. DO NOT INSTALL it in the database called postgres.

Connect to your database with psql or PgAdmin. Run the following SQL. You need only install the features you want:

就是说安装完EXE扩展后，你还要在你需要开启GIS特性的每一个数据库中都根据你的需要运行如下SQL去开启GIS特性。（但不要在postgres这个数据库中开启，这个是安装PG时候自动创建的库）

-- Enable PostGIS (as of 3.0 contains just geometry/geography)
-- 启用PostGIS功能（仅包括geometry/geography相关）
CREATE EXTENSION postgis;

-- enable raster support (for 3+)
-- 启用栅格扩展
CREATE EXTENSION postgis_raster;

-- Enable Topology
-- 启用拓扑扩展
CREATE EXTENSION postgis_topology;

-- Enable PostGIS Advanced 3D
-- and other geoprocessing algorithms
-- sfcgal not available with all distributions
CREATE EXTENSION postgis_sfcgal;
-- fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;
-- rule based standardizer
CREATE EXTENSION address_standardizer;
-- example rule data set
CREATE EXTENSION address_standardizer_data_us;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;

通过执行SELECT postgis_full_version();可以查看当前启用的PostGIS版本

三、快乐初体验

在启用了GIS的数据库中如下开始体验：

-- Create table with spatial column
-- 创建一个表mytable，包含了一个空间列geom，格式是GEOMETRY(Point,26910)
-- 这里的26910是一个SRID，稍后会做解释
CREATE TABLE mytable (
  id SERIAL PRIMARY KEY,
  geom GEOMETRY(Point, 26910),
  name VARCHAR(128)
);
 
-- Add a spatial index
-- 给geom列添加一个空间索引，索引类型为GIST
CREATE INDEX mytable_gix
  ON mytable
  USING GIST (geom);
 
-- Add a point
-- 插入一个数据点
INSERT INTO mytable (geom) VALUES (
  ST_GeomFromText('POINT(0 0)', 26910)
);
 
-- Query for nearby points
-- 查询与给定点周围1000米的点
SELECT id, name
FROM mytable
WHERE ST_DWithin(
  geom,
  ST_GeomFromText('POINT(0 0)', 26910),
  1000
);

讲到这里，你基本就是已经可以开始快乐的玩耍了。但接下来，对于一个刚接触的小白白来说才是开始

应用举例：尝试插入一批位置点并查询附近的点的过程总结

这里就不过多描述从哪弄这些点了（推荐一下腾讯地图开放平台的地图云？可以直接自己在可视化上搞点儿数据然后API接口拉下来即可）

关于SRID

很多盆友都会遇到SRID的问题，如何选择SRID让我头疼了许久，也踩了许多坑。上面举例中用的SRID是26910，有的例子中最开始用的SRID是26918，而对于我们GPS返回的坐标来说，使用的是WGS84坐标系，对应的SRID应该是4326的地理坐标系。

所以建议在数据库中保存GPS坐标时使用4326的SRID进行保存，这样在地理位置计算上可以做到更加统一。

坐标为"地理（geographics）"形式或者说是" 纬度（latitude）/经度（longitude）"形式的数据非常常见。
与Mercator（墨卡托）、UTM（通用横轴墨卡托）、Stateplane中的坐标不同，地理坐标不是笛卡尔平面坐标（Cartesian coordinates）。地理坐标并不表示平面上与原点的线性距离，相反，这些球坐标描述了地球上的角坐标。在球坐标中，点由该点与参考子午线（经度）的旋转角度和该点与赤道的角度（纬度）指定。

你可以将地理坐标看作近似的笛卡尔平面坐标，并继续进行空间计算，然而，关于距离、长度和面积的测量将会是毫无意义的。由于球坐标测量角度距离，因此单位以"度"表示。此外，索引和真/假测试（如相交和包含）可能会变得非常错误，因为越与极点或国际日期线接近的区域，点与点之间的距离变得越大。

更多关于SRID和地理（geography）的原理解释可参阅知乎怪叔叔不睡觉的专栏：【PostGIS教程十二：地理】👈强烈推荐参阅！！！茅厕顿开那种哦！！！！

这里只给你做一个不是那么正确但是便于理解的建议：

数据库中存储GPS位置（Point）或者范围等内容的列建议设置SRID为4326？
这些列中存储的GPS数据需要在存储前转换为WGS84坐标系再存储，不要存GCJ02的坐标！
由于4326中规定的度量单位是度（经度、纬度的那个度），而不是我们常用的米，所以在计算点的位置关系的时候，根据需要进行SRID的转换（比如计算GPS点之间在地球上的距离等场景），比如需要将SRID4326的坐标点Point转换为geography格式，这时再通过ST_DWithin函数查询周边范围的时候就是以米为单位了。

那有童鞋会问了，为什么不在存储的时候就存储成地理位置坐标系（geography），而是存储为SRID=4326的geometry呢？因为对于geography来说，相较于geometry会少很多空间计算的函数，在你深入了解号geograhy和geometry之间的曲别和联系后，你就可以根据场景灵活的选择使用哪种空间类型了。对于入门的我们来说，统一使用geometry不是什么坏处，毕竟它和geography之间可以直接转换！

比如：ST_X()这个函数，只支持geometry类型，如果我们存储的是geography的话，需要获取这个点的X坐标，需要先将其转换为geometry。。。