小编点评

**PostGIS 数据连接简介** PostGIS 是一个空间数据库扩展，允许在 SQL 中运行空间查询。它提供了多种方法来与地理空间数据进行连接，包括空间关系查询、地理分析和空间聚合。 **数据准备** 要使用 PostGIS 连接空间数据，需要先将数据导入数据库中。可以使用 `COPY` 语句从 PostGIS 数据格式的 CSV 文件中导入数据，也可以使用 `PostGIS Intersect` 或 `PostGIS Buffer` 函数将其他数据库中的地理对象转换为 PostGIS 数据类型。 **空间连接数据准备** PostGIS 支持两种空间连接类型： * **SRID（空间参考系代码）**：用于存储地理空间数据的空间坐标。 * **GeoJSON**：用于存储包含地理坐标的 JSON 数据。 **空间关系查询** 可以使用 `ST_Contains`, `ST_Intersects` 和 `ST_Distance` 等函数进行空间关系查询。例如，以下查询所有包含“Broad St”的社区的名称： ```sql SELECT name FROM nyc_neighborhoods WHERE ST_Contains(geom, ST_GeomFromText('SRID=26918;POINT(583571.9059213118 4506714.341192182)')); ``` **空间聚合** PostGIS 支持各种空间聚合函数，如 `SUM()`, `COUNT()` 和 `AVG()`. 例如，以下查询所有 Manhattan社区的人口总数： ```sql SELECT SUM(nyc_census_blocks.popn_total) FROM nyc_census_blocks WHERE ST_Intersects(geom, '0106000020266900000100000001030000000100000008000000E94FC98149DE214196EF19D1FE3351414271D0B694E821416A36040C4E33514144C142A7CDE721410BCEE0D1A63251412D3D82D36FE72141C6594A6E713251414DA687822AE621415F9731007932514177DB296DE4DA2141B7FDD828003351412DB80BB38EDB2141C844CE8054335141E94FC98149DE214196EF19D1FE335141 ``` **参考资料** * [1] 13. Spatial Joins — Introduction to PostGIS * [2] PostGIS教程九：空间连接 * [3] PostGIS 3.3.3dev Manual * [4] PostGIS Cheat Sheet

正文

1. 概述

PostGIS 是PostgreSQL数据库一个空间数据库扩展，它添加了对地理对象的支持，允许在 SQL 中运行空间查询

PostGIS官网：About PostGIS | PostGIS

PostGIS官方教程：PostGIS 简介 — Introduction to PostGIS

PostGIS相关教程：文章目录汇总 - 知乎 (zhihu.com)

本文基于官方教程描述PostGIS中的空间连接

数据准备可参考:

• PostGIS教程三：加载空间数据 - 知乎 (zhihu.com)
• 5. Loading spatial data — Introduction to PostGIS

数据介绍可参考:

• PostGIS教程四：关于教程使用的数据 - 知乎 (zhihu.com)
• 6. About our data — Introduction to PostGIS

2. 空间连接

空间连接是基于空间关系进行表连接的数据表连接操作

在简单的空间关系中我们查询Broad St地铁站所在的社区使用的sql语句为：

-- 1. 查询Broad St的位置
SELECT ST_AsEWKT(geom) FROM nyc_subway_stations WHERE name = 'Broad St';
-- 空间位置为 SRID=26918;POINT(583571.9059213118 4506714.341192182)

-- 2. 查询包含这个位置的社区
SELECT name FROM nyc_neighborhoods WHERE ST_Contains(geom, ST_GeomFromText('SRID=26918;POINT(583571.9059213118 4506714.341192182)'));

使用空间连接，将表连接起来，只需一次查询即可：

SELECT nyc_subway_stations.name, nyc_neighborhoods.name 
FROM nyc_subway_stations 
JOIN nyc_neighborhoods 
ON ST_Contains(nyc_neighborhoods.geom, nyc_subway_stations.geom)
WHERE nyc_subway_stations.name = 'Broad St';

2.1 连接与汇总

使用JOIN和GROUP BY实现空间表的连接与汇总

比如：查询Manhattan各个社区的人口和种族构成？

使用简单查询，可以这样实现：

-- 1. 查询Manhattan的社区和空间位置
SELECT name, geom FROM nyc_neighborhoods WHERE boroname = 'Manhattan';
-- 结果：East Village...

-- 2. 查询每个社区的人口
SELECT SUM(nyc_census_blocks.popn_total) FROM nyc_census_blocks WHERE ST_Intersects(nyc_census_blocks.geom, '0106000020266900000100000001030000000100000008000000E94FC98149DE214196EF19D1FE3351414271D0B694E821416A36040C4E33514144C142A7CDE721410BCEE0D1A63251412D3D82D36FE72141C6594A6E713251414DA687822AE621415F9731007932514177DB296DE4DA2141B7FDD828003351412DB80BB38EDB2141C844CE8054335141E94FC98149DE214196EF19D1FE335141');
-- 结果：82266

-- 3. 查询每个社区的黑人与白人数量计算比例
SELECT SUM(nyc_census_blocks.popn_white)/82266, SUM(nyc_census_blocks.popn_black)/82266 FROM nyc_census_blocks WHERE ST_Intersects(nyc_census_blocks.geom, '0106000020266900000100000001030000000100000008000000E94FC98149DE214196EF19D1FE3351414271D0B694E821416A36040C4E33514144C142A7CDE721410BCEE0D1A63251412D3D82D36FE72141C6594A6E713251414DA687822AE621415F9731007932514177DB296DE4DA2141B7FDD828003351412DB80BB38EDB2141C844CE8054335141E94FC98149DE214196EF19D1FE335141');
-- 结果：0.63， 0.08

使用空间连接和汇总，只需查询一次即可：

SELECT nyc_neighborhoods.name, SUM(nyc_census_blocks.popn_white)/SUM(nyc_census_blocks.popn_total),
SUM(nyc_census_blocks.popn_black)/SUM(nyc_census_blocks.popn_total) 
FROM nyc_census_blocks 
JOIN nyc_neighborhoods
ON ST_Intersects(nyc_census_blocks.geom, nyc_neighborhoods.geom)
WHERE nyc_neighborhoods.boroname = 'Manhattan'
GROUP BY nyc_neighborhoods.name;

3. 参考资料

[1]13. Spatial Joins — Introduction to PostGIS

[2]PostGIS教程九：空间连接 - 知乎 (zhihu.com)

[3]PostGIS 3.3.3dev Manual

[4]PostGIS Cheat Sheet