正品海外购网站有哪些,怎么做直播网站超管,哈尔滨公司做网站,wordpress 图站注#xff1a;使用postgresql数据库会用到PostGIS 扩展。
一、安装PostGIS 扩展
在 PostgreSQL 中遇到错误 “type geography does not exist” 通常意味着你的 PostgreSQL 数据库还没有安装 PostGIS 扩展#xff0c;或者 PostGIS 扩展没有被正确地安装在你的数据库中。geo…注使用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 坐标系统的 SRIDSpatial 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海里的圆获取圆圈内的所有坐标点
# longitudelatitude是两个string类型字段存储经、纬度没用使用point类型目前是第一个查询速度快后续使用point类型再更新查询效率 SELECT * FROM aisgfw_history_data WHERE ST_DWithin( ST_GeogFromText(SRID4326;POINT( || longitude || || latitude || )), ST_GeogFromText(SRID4326;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): 设置几何对象的空间参考系统IDSRID。在这里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 (...) 部分定义了一个CTETemporaryPolygons它包含了一个由文本字符串构造的多边形并使用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(SRID4326;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
适用场景
用于表示单个地理位置点如城市位置、地标等。
数据量
数据量相对较小因为每个点仅包含一对坐标经度和纬度。
查询效率
查询效率较高因为点的表示简洁且易于索引。
地理坐标的适用性
非常适合存储具体的地理坐标点。
2. LINESTRING
适用场景
用于表示一系列有序的、连接的点如道路、河流等线性特征。
数据量
数据量相对较大因为包含多个点及其连接关系。
查询效率
查询效率取决于数据复杂度和索引的使用情况。使用适当的空间索引如GIST或SP-GIST可以显著提高查询效率。
地理坐标的适用性
适用于表示连续的线性地理特征。
3. POLYGON
适用场景
用于表示具有闭合边界的平面区域如国家边界、湖泊等。
数据量
数据量较大因为需要存储多个点以形成闭合多边形。
查询效率
类似LINESTRING查询效率也取决于数据复杂度和索引的使用情况。使用空间索引可以优化查询性能。
地理坐标的适用性
非常适合表示具有明确边界的地理区域。
4. geography
适用场景
用于存储和查询地球上两点之间的最短路径大圆距离相关的地理空间数据。
数据量
与POINT、LINESTRING、POLYGON类似但特别适用于需要精确计算地理距离的场景。
查询效率
提供了对地球曲率的考虑因此在进行地理距离计算时更准确但可能稍慢于简单的二维平面计算。
地理坐标的适用性
非常适合需要精确地理距离计算的场景如物流、导航等。
5. geometry
适用场景
是一个非常通用的地理空间数据类型可以表示点、线、面等多种几何形状。
数据量
数据量根据具体表示的几何形状而异可能非常大。
查询效率
类似于LINESTRING和POLYGON查询效率取决于数据复杂度和索引的使用情况。
地理坐标的适用性
通用性强适用于各种地理空间数据的存储和查询。
总结
在选择地理空间数据类型时需要考虑以下因素
数据的性质和特点根据要表示的地理空间特征选择合适的类型。数据量考虑数据类型对存储空间的需求。查询效率选择能够支持高效查询的索引策略。地理坐标的适用性确保所选类型能够准确表示所需的地理坐标信息。
综上所述每种地理空间数据类型都有其特定的适用场景和优势。在实际应用中应根据具体需求和数据特点进行选择。 【PostgreSQL】地理空间数据的数据类型定义、索引优化、查询优化策略-CSDN博客
