【职场技术栈学习实录】PostgreSQL 与 PostGIS详解

系列文章介绍

本系列是我作为一名后端开发，在日常工作中积累的技术学习与实践笔记。在这里，我将分享工作中后端开发领域的技术栈、架构设计、代码实现以及问题解决的经验与心得。出于保护公司隐私的原因，实际业务场景无法详细展开，但我会通过抽象化的需求和通用的技术问题，来介绍与探讨如何在实际工作中应用这些技术。

---

---

前言

作为一名后端开发，数据库是我们所构建的系统架构中不可或缺的一环。在学校学习的过程中，曾经学习过关系型数据库MySQL与非关系型数据库Redis，但是工作中会因为各种原因，接触到很多其他的数据库，比如本文中所要介绍的PostgreSQL。接下来我会从几个方面展开探讨，包括PostgreSQL是什么、为什么要使用PostgreSQL以及如何使用PostgreSQL。

---

一、PostgreSQL是什么？

引自官方网站 postgresql.org ： PostgreSQL 是一个强大的、开源的对象关系型数据库系统，它使用并扩展了 SQL 语言，并结合了许多功能，可以安全地存储和扩展最复杂的数据工作负载。

PostgreSQL具有以下特点：

1. 开源: PostgreSQL 是一个完全开源的数据库系统，遵循 PostgreSQL 许可证。
2. 可扩展: 支持用户自定义数据类型、函数、操作符等。
3. 标准符合性: 高度符合 SQL 标准，支持 ACID 事务。
4. 多版本并发控制（MVCC）: 支持高并发访问，确保数据一致性。
5. 丰富的功能: 包括复杂查询、外键、触发器、视图、存储过程等。

二、为什么要使用PostgreSQL？如何使用？

从上文的介绍中可以看出，PostgreSQL作为一个关系型数据库，是我们所熟知的MySQL的竞品，那么为什么不直接使用MySQL呢？或者说，相比于MySQL，PostgreSQL有什么优点呢？

实际上，在我第一段实习公司用的主要是MySQL，而第二段实习公司数据库选型是PostgreSQL，进来时我便问过我的mentor这个问题。在上手公司业务的过程中，我慢慢理解了其中缘由。相比于MySQL，PostgreSQL具有以下两个优点，使得它十分契合现在公司的业务。

1.Postgis 空间索引

PostGIS 是 PostgreSQL 的一个扩展，用于支持地理空间数据的存储、查询和分析。空间索引是 PostGIS 中用于加速空间查询的关键技术之一。通过使用空间索引，可以显著提高对空间数据的查询性能，尤其是在处理大量地理空间数据时。

在公司业务中，需要处理大量地图相关数据，包括场景识别、轨迹匹配、地理数据挖掘等需求，由于PostGIS 的存在，使得 PostgreSQL 与业务自然契合。

接下来详细介绍 PostGIS 的使用。

部署

# 部署
docker run--name postgis -p5432:5432-e POSTGRES_PASSWORD=postgres -d postgis/postgis:16-3.4
# 激活
CREATE EXTENSION postgis;
# 输入下面指令，如果能正常返回便是激活成功了
SELECT postgis full version();

Geometry数据类型

Geometry 数据类型是用于存储和管理地理空间数据的一种专门的数据类型。在PostgreSQL的关系数据库管理系统中，通过PostGIS扩展，Geometry 类型允许用户保存表示点、线、多边形等几何对象的信息。这些几何对象可以用来构建复杂的地理信息系统(GIS)应用，支持空间查询和分析，例如计算距离、进行空间叠加操作或创建缓冲区等。

接下来我将由浅入深地演示一下Geometry 数据类型的相关内置函数。首先导入示例数据：https://s3.amazonaws.com/s3.cleverelephant.ca/postgis-workshop-2020.zip，导入成功会看到Tables中多出了6张表，其中有三张表是我们演示的主表，nyc_neighbours 、nyc_streets 、 nyc_subway_stations，分别代表着美国地图上的社区、街道、地铁站，也正好对应着 PostGIS 中 Geometry 数据类型的三个概念——面、线、点。

1. 单表操作

Alantic Commons街道

-- geom是二进制存储的地理信息数据，需要使用ST_AsText()函数进行解析
SELECT ST_AsText(geom