注:使用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 的版本兼容性,并确保你的数据库用户有足够的权限来创建扩展。
二、报错记录
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/
目录下的所有软件包文件。
三、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、如何实现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
操作与CTETemporaryPolygons
进行连接,使用ST_Within
函数来检查哪些点位于多边形内部。
这个查询在每次执行时都会重新计算多边形,而不会将其存储在数据库的任何物理表中。这样,您就可以根据需要灵活地定义不同的多边形,而无需预先在数据库中创建它们。
如果您需要在多次查询中重复使用相同的多边形,但又不希望将其存储在物理表中,您可以考虑将多边形定义为一个SQL变量(在PL/pgSQL函数或DO匿名代码块中)或在应用程序代码中定义它,然后在每次查询时将其作为参数传递。但是,对于简单的查询来说,CTE通常是一个更方便和直接的方法。