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

最新推荐文章于 2025-05-19 11:03:31 发布

driftingalone98

最新推荐文章于 2025-05-19 11:03:31 发布

阅读量1.3k

点赞数 24

CC 4.0 BY-SA版权

分类专栏：职场技术栈学习实录文章标签：学习 postgresql 数据库后端 sql

本文链接：https://blog.youkuaiyun.com/driftingalone98/article/details/146278325

职场技术栈学习实录专栏收录该内容

3 篇文章

订阅专栏

系列文章介绍

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

前言

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

一、PostgreSQL是什么？

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

PostgreSQL具有以下特点：

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

二、为什么要使用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)
FROM nyc_streets
WHERE name ='Atlantic Commons';

在这里插入图片描述
可以看到，使用了内置函数 ST_AsText() 查询出来的是一个字符串类型 text 存储的 MULTILINESTRING，即多线段字符串，这里存储了两组坐标，分别是(586781.7015777241 4504202.153143394) 和 (586863.5196448397 4504215.9881700)，代表了一条线段，也就是一条街道的起点与终点。

Alantic Commons街道相邻的街道

SELECT name ,ST_GeometryType(geom)
FROM nyc_streets
WHERE ST Dwithin(
	geom,
	ST GeomFromText('LINESTRING(586782 4504202,586864 4504216)'，26918),
	0.1
);

在这里插入图片描述
ST_DWithin(geometry A, geometry B, radius) 表示如果几何图形 A 与几何图形 B 的距离为’radius’或更近，则返回 true在PostGlS中，投影单位26918指的是一个特定的投影坐标系统(ProjectedCoordinate System,PCS)。这个数字实际上是该PCS的一个代码，它对应于北美洲的一种投影–NAD83/UTM zone 18N，单位是米。NAD83是“North American Datum of1983”的缩写，这是一个大地测量参考框架，用于描述北美的地理位置。当提到26918时，具体表示的是基于NAD83坐标系、位于UTM第18号区域(Zone 18N)的投影。这个区域覆盖了北美洲的部分地区，包括美国东北部的一些州和加拿大东部的部分省份。radius设为0.1，表示这条街道0.1m范围以内。
因此，这条SQL表示选出Alantic Commons街道相邻的街道，查询出来有两条记录。

曼哈顿各社区的面积

SELECT name，ST_Area(geom)，ST_GeometryType(geom)
FROM nyc_neighborhoods
WHERE boroname ='Manhattan';

在这里插入图片描述
ST_Area() 用于计算面积，ST_MultiPolygon 表示多边形。

曼哈顿最西边的地铁站

SELECT ST_X(geom),name，ST_GeometryType(geom)
FROM nyc_subway_stations
ORDER BY ST_X(geom)
LIMIT 1;

在这里插入图片描述
ST_X()表示提取X轴的坐标，对其进行升序排序，limit 1 取其最小值，即最西边的地铁站。

亚特兰蒂斯穿过了哪个街区和行政区？

SELECT name, boroname
FROM nyc_neighborhoods
WHERE ST Intersects(
	geom,
	ST GeomFromText('LINESTRING(586782 4504202,586864 4504216)'，26918)
);

在这里插入图片描述
ST_Intersects() 表示取交集，即将geom数据与亚特兰蒂斯的geom数据取交集，返回的结果就是穿过亚特兰蒂斯的社区与街道。

2. 多表联查

PostGIS也支持多表联查，由于PostgreSQL遵守了SQL规范，语法与我们平时使用的MySQL完全相同。

“Little ltaly”社区有哪个地铁站?它在哪条地铁路线上?

SELECT s.name,s.routes
FROM nyc_subway_stations As s
JOIN nyc_neighborhoods As n
ON ST_Contains(n.geom，s.geom)
WHERE n.name ='Little Italy';

在这里插入图片描述
ST_CONTAINS（）表示后者包含于前者，即社区包含的地铁站。

哪个社区的人口密度最高？

SELECT
	n.name,
	Sum(c.popn_total)/(ST_Area(n.geom)/1000000.0)As popn_per_sqkm
	FROM nyc_census blocks As c
JOIN nyc_neighborhoods As n
ON ST Intersects(c.geom,n.geom)
GROUP BY n.name,n.geom
ORDER BY popn_per_sqkm DESC LIMIT 2;

在这里插入图片描述

nyc_census_blocks表示人口普查的信息，将其与社区取交集，以社区名称和地理信息进行汇总，然后使用人口总数除以区域面积，得到的就是区域的人口密度。

3. 嵌套语句

求两地铁站的距离？

SELECT ST_Distance(
	select geom from nyc_subway_stations where id = 6)，
	select geom from nyc_subway_stations where id = 7)
);

在这里插入图片描述
从上述案例中可以看到PostgreSQL在地理信息计算上独特的便利性，除此上述场景以外，在实际的运用场景中，PostGIS还可以用于求更复杂的地理信息，比如求地球上某两个坐标的实际距离，会涉及到求球面上的距离等。

2.物化视图

1.什么是物化视图？

物化视图 (MATERIALIZED VIEW) 是一种数据库对象，它存储了从SQL查询结果中派生出来的数据。与虚拟视图不同，物化视图实际上将查询结果保存在磁盘上，这样就可以提高那些需要频繁执行的复杂查询的性能通过定期更新或刷新，物化视图能够保持最新的数据状态，使得后续的查询可以直接使用这些预计算的结果从而显著减少查询时间和系统资源的消耗。这种技术特别适用于数据仓库和OLAP(在线分析处理)环境，其中经常需要对大量历史数据进行复杂的分析查询。如下图，物化视图本质上来说是使用空间换时间，以牺牲存储空间为代价，换取查询时间的大幅度降低。
在这里插入图片描述

2.物化视图的分类

（1）同步物化视图

同步物化视图是指当基础数据(即源表)发生变化时，物化视图的内容会立即或者几乎是立即更新以反映这些变化。同步更新确保了物化视图总是包含最新的数据。

优点:
数据始终是最新的，这通常对于实时分析和决策支持系统非常重要。查询性能高，因为查询可以直接从预先计算好的数据。

缺点:
更新可能会占用大量资源，尤其是在数据量非常大或者变更频繁的情况下。如果同步更新的时间较长，可能会导致用户在段时间内无法访问视图。对于事务性系统来说，可能会影响主表的写入。

（2）异步物化视图

异步物化视图是指在事件触发时进行更新，往往是通过后台周期性地触发、或是基于特定事件更新。即不是即时发生的，而是按照预定的时间间隔或由其他机制触发。

优点：
减少了对系统资源的需求，特别是在更新大型类据集时。可以更灵活地安排更新时间，例如在低峰时段进行更新以避免影响其他重要操作。

缺点:
视图中的数据可能不是最新的，这可能不适合需要实时数据分析的应用场景。如果更新间隔过长，查询结果可能会变得不准确或不相关。

3. MySQL ？PostgreSQL !

目前而言，支持物化视图的数据库包括 PostgreSQL、Hive、Oracle、DB2、SQL Server(View Index)、ClickHouse、Doris / StarRocks、OceanBase等。没错，MySQL不在此列，这是MySQL不会被使用的另一个重要原因。在OLAP场景下，我们经常使用下面的数据仓库架构。

在这里插入图片描述

在海量数据存储与高性能读的场景下，我们往往需要从大量不同的来源获取数据，我们会将其存储在支持物化视图的数据库中，比如图上所写几种数据库。这一步，我们一般不对数据作任何加工。
创建第一个同步物化视图，也就是我们平时所说的DWD，这一步主要是为了对海量数据进行筛选。
创建第二个同步物化视图，也就是DWS，为了大数据与后端处理数据的便利性和高性能，这一步要将表作宽表化。举例来说，可能DWD中有三张表a,b,c，我们要将其合并为一个表，这样避免了后期频繁地做多表联查。
最后我们会对数据做一个异步物化视图，生成聚合结果，这一步是为了后续的生成数据报表、大规模的数据运算等服务的。

从上述过程中可以看出，有物化视图的存在，数据的流向变得流程化，这种设计理念正在越来越多地被企业所接纳。

以上就是今天要分享的内容，本文介绍了什么是PostgreSQL 数据库、为什么要使用PostgreSQL 数据库以及如何使用它。