【Postgresql】地理空间数据的存储与查询，查询效率优化策略，数据类型与查询速度的影响

注：使用postgresql数据库会用到PostGIS 扩展。

一、安装PostGIS 扩展

在 PostgreSQL 中遇到错误 “type geography does not exist” 通常意味着你的 PostgreSQL 数据库还没有安装 PostGIS 扩展，或者 PostGIS 扩展没有被正确地安装在你的数据库中。geography 类型是 PostGIS 提供的，用于存储和处理地理空间数据，特别是考虑到地球曲率的地理数据。

要解决这个问题，请按照以下步骤操作：

1、确认 PostGIS 是否已安装：

你可以通过查询 PostgreSQL 的扩展列表来确认 PostGIS 是否已经安装在你的数据库中。运行以下 SQL 命令：

SELECT * FROM pg_extension;

如果列表中没有 PostGIS，那么你需要安装它。

2、安装 PostGIS：

如果你还没有安装 PostGIS，你可以通过 PostgreSQL 的包管理器（如 apt-get、yum、brew 等，取决于你的操作系统）或者通过 PostgreSQL 的 SQL 命令来安装它。但是，通常建议使用包管理器来安装，因为它会处理所有依赖项。

如果你正在使用 PostgreSQL 的 SQL 命令来安装扩展（这通常是在你已经有了 PostgreSQL 和相应的权限之后），你可以运行以下命令（但请注意，这通常不是安装 PostGIS 扩展的推荐方式，因为它假设你的系统上已经有了 PostGIS 的二进制文件）：

CREATE EXTENSION IF NOT EXISTS postgis;

但是，如果 PostGIS 的二进制文件没有在你的 PostgreSQL 的库路径中，这个命令将会失败。

3、使用包管理器安装 PostGIS：

根据你的操作系统，你可以使用相应的包管理器来安装 PostGIS。

需要根据postgresql的版本安装对应的postgis

psql --version #查看postgresql版本为postgresql 16.*

例如，在 Ubuntu 上，你可以使用 apt-get：

	`sudo apt-get update`
	`sudo apt-get install postgresql-16-postgis-3 # 注意版本号可能不同`

在 CentOS 上，你可以使用 yum：

sudo yum install postgis30_12 # 注意版本号可能不同

在 macOS 上，如果你使用 Homebrew，你可以安装 PostgreSQL 和 PostGIS：

brew install postgis

但是，对于 macOS 上的 Homebrew，你可能还需要在 PostgreSQL 数据库中手动启用 PostGIS 扩展。

4、先重启数据库，然后在数据库中启用 PostGIS：

重启数据库

systemctl restart postgresql

如果你已经通过包管理器安装了 PostGIS，但还没有在你的数据库中启用它，你需要登录到你的 PostgreSQL 数据库，并运行以下 SQL 命令来创建扩展：

CREATE EXTENSION IF NOT EXISTS postgis;

5、使用 SQL 查询

在 PostgreSQL 的命令行工具 psql 中，你可以通过查询数据库来确定 PostGIS 是否已经安装并可用。执行以下 SQL 命令：

SELECT PostGIS_Full_Version();

如果 PostGIS 已安装，这条命令将返回 PostGIS 的完整版本信息。如果返回错误或没有任何输出，则可能表示 PostGIS 没有安装或尚未在数据库中启用。

6、检查 `geography` 类型：

安装并启用 PostGIS 后，你应该能够在你的数据库中看到 geography 类型。你可以通过查询 PostgreSQL 的系统目录来确认这一点：

SELECT typname FROM pg_type WHERE typname = 'geography';

如果查询返回了结果，那么 geography 类型就已经在你的数据库中可用了。

如果你已经按照上述步骤操作，但仍然遇到问题，请检查你的 PostgreSQL 和 PostGIS 的版本兼容性，并确保你的数据库用户有足够的权限来创建扩展。

7、写入数据测试功能

举例： postgresql 数据类型geography 的写入方式

1）创建包含 geography 类型的表

	`CREATE TABLE locations (`
	`id SERIAL PRIMARY KEY,`
	`name VARCHAR(100),`
	`geom GEOGRAPHY(Point, 4326) -- 4326 是 WGS 84 坐标系统的 SRID`
	`);`

在这个例子中，geom 列用于存储地理位置数据，它是一个 geography 类型的点（Point）。4326 是 WGS 84 坐标系统的 SRID（Spatial Reference System Identifier），它是全球定位系统使用的标准坐标系统。

2）插入 geography 数据

方法一：使用文本字符串插入

你可以使用 Well-Known Text (WKT) 格式或 Well-Known Binary (WKB) 格式来插入地理数据。WKT 是一种文本标记法，用于表示空间数据。

	`INSERT INTO locations (name, geom)`
	`VALUES ('New York', ST_GeogFromText('POINT(-74.0060 40.7128)'));`

这里，ST_GeogFromText 函数将 WKT 字符串转换为 geography 类型的点。

方法二：使用经纬度直接插入

PostGIS 也允许你直接使用经纬度值来创建 geography 类型的点，使用 ST_MakePoint 函数结合 ST_SetSRID 和 ST_Transform（如果需要的话，但在这个例子中我们直接使用 WGS 84，所以不需要转换）。

	`INSERT INTO locations (name, geom)`
	`VALUES ('London', ST_SetSRID(ST_MakePoint(-0.1278, 51.5074), 4326)::geography);`

注意，这里 ST_MakePoint 创建了一个 geometry 类型的点，然后使用 ST_SetSRID 设置其 SRID 为 4326，最后通过显式类型转换（::geography）将其转换为 geography 类型。

二、报错记录

1、ERROR: extension "postgis" is not available DETAIL: Could not open extension control file "/usr/share/postgresql/16/extension/postgis.control": No such file or directory. HINT: The extension must first be installed on the system where PostgreSQL is running.

这个错误信息表明 PostgreSQL 无法找到 PostGIS 扩展的控制文件，这通常说明：

1）意味着 PostGIS 还没有被安装在你的系统上，或者它的安装位置不在 PostgreSQL 预期的位置。

2）安装的版本于数据库版本不匹配。

比如，你正在使用 PostgreSQL 16，你需要确保你安装的 PostGIS 版本与 PostgreSQL 16 兼容。如果可能的话，使用与你的 PostgreSQL 版本相对应的 PostGIS 版本。

3）重新启动 PostgreSQL 服务：
修改配置文件或安装新软件后，通常需要重新启动 PostgreSQL 服务来使更改生效。你可以使用你的系统的服务管理工具来重启 PostgreSQL（例如，在 Ubuntu 上使用 sudo systemctl restart postgresql）。

4）检查权限和错误日志：
如果问题仍然存在，检查 PostgreSQL 的错误日志以获取更多信息。此外，确保你的数据库用户有足够的权限来创建和使用扩展。

2、安装错了版本，先卸载再重新装

使用apt-get安装的软件包卸载方法相对直接，主要涉及到几个关键的apt-get命令。以下是根据高权威性来源信息总结的卸载步骤：

卸载软件包

卸载软件包并保留配置文件：

sudo apt-get remove package_name
这个命令会卸载指定的软件包，但会保留其配置文件。如果不需要配置文件，可以使用下一个命令。
卸载软件包并删除配置文件：

sudo apt-get --purge remove package_name
这个命令不仅会卸载软件包，还会删除其配置文件。这是完全卸载软件包并清理其残留配置文件的推荐方法。

清理不再需要的依赖包

在卸载软件包后，有时可能会留下一些不再被其他软件包依赖的依赖包。为了清理这些不再需要的依赖包，可以使用：

sudo apt-get autoremove

这个命令会自动删除那些现在自动安装但不再被需要的软件包。

卸载并清除所有配置和依赖

如果需要更彻底地清理，可以结合使用--purge和autoremove选项，但请注意，apt-get本身没有直接的命令组合来同时执行这两个操作。通常，你会先执行--purge remove，然后执行autoremove。

清理下载的软件包

为了节省磁盘空间，可以清理之前下载的.deb软件包文件：

sudo apt-get clean

这个命令会删除/var/cache/apt/archives/和/var/cache/apt/archives/partial/目录下的所有软件包文件。

三、理论基础

1、Geography和 Geometry 的区别

一：区别：
在这里插入图片描述

geography采用的是地理坐标系（球面坐标），geometry是采用的是笛卡尔坐标系（平面坐标）
如果数据在地理范围上是紧凑的（包含在州、县或市内），请使用基于笛卡尔坐标的geometry类型。
如果你需要测量在地理范围上是分散的数据集（覆盖世界大部分地区）的距离，请使用geography类型。

当提出这样一个问题时，支持非点的几何图形的需求变得非常明显：“从洛杉矶到巴黎的航班路线距离冰岛有多远?”
在这里插入图片描述
在笛卡尔平面坐标系统上使用地理坐标（紫色线）产生了一个非常错误的答案！使用大圆路线（红线）则能得出正确的答案。

三、SQL示例

1、地图上有N个点，以148.35824, 42.234270点为中心，地图画一个半径为200海里的圆，获取圆圈内的所有坐标点

# longitude，latitude是两个string类型字段存储经、纬度，没用使用point类型，目前是第一个查询速度快，后续使用point类型再更新查询效率

SELECT *
FROM aisgfw_history_data
WHERE ST_DWithin(
ST_GeogFromText('SRID=4326;POINT(' || longitude || ' ' || latitude || ')'),
ST_GeogFromText('SRID=4326;POINT(132.35824 21.234270)'),
300 * 1852
);
# 执行40秒

SELECT *
FROM aisgfw_history_data ais-- 假设你的表名为ais_data
WHERE ST_DistanceSphere(
ST_SetSRID(ST_MakePoint(148.35824, 42.234270), 4326),

                -- 创建一个地理点，注意经纬度顺序
ST_SetSRID(ST_MakePoint(ais.longitude, ais.latitude), 4326)

                -- 假设ais_data表中包含lon和lat字段
) <= 200 * 1852;      -- 200海里转换为米

# 执行16秒

这两个SQL查询都旨在从aisgfw_history_data表中筛选出与给定经纬度（148.35824, 42.234270）距离小于200海里的记录，但它们使用了不同的方法和函数。下面我将详细讲解这两个查询的区别、使用的函数的作用，以及哪个查询可能更高效。

第一个查询

	`SELECT *`
	`FROM aisgfw_history_data ais -- 注意这里的别名是ais，不是ais_data`
	`WHERE ST_DistanceSphere(`
	`ST_SetSRID(ST_MakePoint(148.35824, 42.234270), 4326),`
	`ST_SetSRID(ST_MakePoint(ais.longitude, ais.latitude), 4326)`
	`) <= 300 * 1852;`

函数作用：
- ST_MakePoint(x, y): 创建一个点，其中x是经度，y是纬度。
- ST_SetSRID(geom, srid): 设置几何对象的空间参考系统ID（SRID）。在这里，SRID被设置为4326，即WGS 84坐标系。
- ST_DistanceSphere(point1, point2): 计算两个地理点之间的球面距离（以米为单位）。
查询逻辑：
- 这个查询直接计算每个记录点与给定中心点之间的距离，并筛选出距离小于或等于200海里（转换为米）的记录。
第二个查询

	`WITH center_point AS (`
	`SELECT ST_SetSRID(ST_MakePoint(148.35824, 42.234270), 4326)::geography AS center_geog`
	`)`
	`SELECT ah.mmsi, ah.longitude AS lon, ah.latitude AS lat`
	`FROM aisgfw_history_data ah`
	`JOIN center_point cp ON ST_DWithin(`
	`ST_SetSRID(ST_MakePoint(ah.longitude, ah.latitude), 4326)::geography,`
	`cp.center_geog,`
	`300 * 1852`
	`);`

函数作用（新增/不同点）：
- ST_DWithin(geom1, geom2, distance): 判断第一个几何对象是否在给定的距离内与第二个几何对象相交。这里，它用于判断点是否在给定半径的圆内（尽管实际上并没有创建圆的多边形表示）。
查询逻辑：
- 使用WITH子句（公用表表达式，CTE）首先创建一个包含中心点坐标的临时表center_point。
- 然后，将aisgfw_history_data表与center_point表进行连接，连接条件是记录点与中心点之间的距离小于或等于300海里（转换为米）。

效率比较

第一个查询：
- 直接在WHERE子句中计算每个记录点与中心点的距离，这可能会导致对表中每一行都进行距离计算，如果表很大，则可能非常耗时。
- 但是，如果表中有适当的索引（尽管对于地理空间查询，直接在经纬度字段上创建常规索引可能不是最佳选择），并且查询优化器能够利用这些索引来减少需要计算距离的记录数，则性能可能会有所提高。
第二个查询：
- 使用WITH子句和连接（JOIN）来组织查询，这可以使查询的逻辑更清晰，但在性能上并不一定有优势。
- 然而，如果PostGIS能够优化ST_DWithin函数的使用，并且利用地理空间索引（如果已创建）来加速查询，则这个查询可能会更高效。
总结：
- 哪个查询更高效取决于多种因素，包括表的大小、数据的分布、是否存在地理空间索引，以及PostgreSQL和PostGIS的查询优化器的性能。
- 在没有具体数据和索引信息的情况下，很难确定哪个查询会更快。
- 在实际应用中，建议对两种查询都进行测试，并根据实际情况选择性能更好的查询。

注意：在第二个查询中，将ST_SetSRID(...)::geography的结果直接用于ST_DWithin是合理的，因为ST_DWithin函数可以接受geography类型的参数，并且已经隐含地考虑了地球的曲率。但是，在第一个查询中，虽然也使用了ST_SetSRID(...)::geography（尽管在查询中未明确写出，但假设ST_DistanceSphere的输入已经是geography类型），但重点是ST_DistanceSphere函数本身。

2、查询耗时优化，查询耗时与字段类型、索引的关系

直接看结果

-- geom为geometry类型，已经加2个索引GiST类型、SP-GiST类型
-- geom_geog为geography类型，加索引GiST类型

-- geom为geometry类型，4千万数据耗时30秒，
SELECT * FROM aisgfw_history_data
WHERE ST_DWithin(
ST_MakePoint(132.35824, 21.234270)::geography, -- 直接将ST_MakePoint结果转换为geography
geom::geography, -- 将geometry转换为geography类型
300 * 1852 -- 300海里转换为米
);

-- geom_geog 为geography类型， 4千万数据耗时1秒，
SELECT * FROM aisgfw_history_data
WHERE ST_DWithin(
ST_MakePoint(132.35824, 21.234270)::geography, -- 转换为geography 类型
geom_geog, -- geography类型
300 * 1852
);

-- geom为geometry类型，4千万数据耗时13秒，
SELECT *
FROM aisgfw_history_data
WHERE ST_DistanceSphere(
ST_MakePoint(132.35824, 21.234270)::geometry, -- 转换为geography类型
geom -- geometry类型
) < 300 * 1852;

3、如何实现postgresql输入10个坐标点，获取坐标连线图形内的所有点，点的存储表是物理表，空间表是临时的，按此要求重新输出

如果您的需求是点的存储表是物理表（即长期存储在数据库中），而空间表（包含多边形或多段线）是临时的（可能只在查询时构建），那么您可以在查询时动态地构造这个临时空间表，而不是事先将其存储在数据库中。

不过，在SQL查询中直接“构造”一个临时表通常意味着您会使用子查询、CTE（公用表表达式）或临时表（尽管这里的“临时”是指SQL执行期间的临时，而不是物理存储的临时表）。由于您可能不想在数据库中创建永久的临时表，我们将使用CTE或子查询。

以下是一个使用CTE来模拟临时空间表，并查询位于这个临时多边形内的所有点的示例：

	`-- 使用CTE定义临时多边形`
	`WITH TemporaryPolygons AS (`
	`SELECT`
	`ST_SetSRID(ST_GeomFromText('POLYGON((-73.935242 40.730610, -73.936242 40.730610, -73.936242 40.731610, -73.935242 40.731610, -73.935242 40.730610))'), 4326) AS geom`
	`)`
	`-- 查询位于临时多边形内的点`
	`SELECT p.id, p.geom`
	`FROM points p`
	`JOIN TemporaryPolygons tp ON ST_Within(p.geom, tp.geom);`

在这个查询中：

WITH TemporaryPolygons AS (...) 部分定义了一个CTE（TemporaryPolygons），它包含了一个由文本字符串构造的多边形，并使用ST_GeomFromText函数将其转换为几何对象，然后通过ST_SetSRID设置其空间参考系统。
接下来的SELECT查询从points表中选取点，并通过JOIN操作与CTE TemporaryPolygons进行连接，使用ST_Within函数来检查哪些点位于多边形内部。

这个查询在每次执行时都会重新计算多边形，而不会将其存储在数据库的任何物理表中。这样，您就可以根据需要灵活地定义不同的多边形，而无需预先在数据库中创建它们。

如果您需要在多次查询中重复使用相同的多边形，但又不希望将其存储在物理表中，您可以考虑将多边形定义为一个SQL变量（在PL/pgSQL函数或DO匿名代码块中）或在应用程序代码中定义它，然后在每次查询时将其作为参数传递。但是，对于简单的查询来说，CTE通常是一个更方便和直接的方法。

4、Geography数据类型的数据插入

# geom为geometry类型，geom_geog为geography类型

INSERT INTO aisgfw_history_data (mmsi, latitude, longitude, time, sog, cog, geom,

geom_geog)
VALUES ('367819000', 39.2678, -76.57027, '2024-09-04 16:10:42', 0, 232.1,
            ST_SetSRID(ST_MakePoint(-76.57027, 39.2678), 4326),
    ST_GeographyFromText('SRID=4326;POINT(-76.57027 39.2678)')

);

INSERT INTO aisgfw_history_data (mmsi, latitude, longitude, time, sog, cog, geom, geom_geog)
VALUES ('367819000', 39.2678, -76.57027, '2024-09-04 16:10:42', 0, 232.1,
            ST_SetSRID(ST_MakePoint(-76.57027, 39.2678), 4326),
            ST_SetSRID(ST_MakePoint(-76.57027,39.2678), 4326)
);

5、数据存储类型的考量：
PostgreSQL 支持多种地理空间数据类型，如 POINT、LINESTRING、POLYGON 、geography、geometry等。在创建表时，需要根据实际需求选择合适的数据类型来存储地理空间数据。几种数据类型的适用场景？数据量？查询效率？地理坐标更适合哪个？

PostgreSQL通过PostGIS扩展支持多种地理空间数据类型，这些数据类型为存储和查询地理空间数据提供了丰富的选项。下面将针对您提到的POINT、LINESTRING、POLYGON、geography、geometry等数据类型，从适用场景、数据量、查询效率以及地理坐标的适用性等方面进行详细分析。

1. POINT

适用场景：