pg数据库和距离计算相关函数

已于 2024-07-03 11:42:00 修改
阅读量7.2k 收藏 25
点赞数 3
文章标签： 距离计算 Postgis
于 2022-01-12 09:37:19 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/master_shifu_/article/details/122446518
版权

1.指定时间区间的轨迹段和几何图形空间是否相交

ST_intersects

指定时间区间的轨迹段和几何图形空间是否相交。
在这里插入图片描述

ST_Intersects简单的说就是判断geometry geomA 与geometry geomB 是否存在geometry格式的交集；也可以判断geography geogA 与 geography geogB 是否存在geography这种类型的交
ST_Intersects
在这里插入图片描述

2.postgresql计算两点距离（经纬度地理位置）

下面两种方法：

select 
ST_Distance(
    ST_SetSRID(ST_MakePoint(115.97166453999147,28.716493914230423),4326)::geography,
    ST_SetSRID(ST_MakePoint(106.00231199774656,29.719258550486572),4326)::geography
),
ST_Length(
    ST_MakeLine(
        ST_MakePoint(115.97166453999147,28.716493914230423),
        ST_MakePoint(106.00231199774656,29.719258550486572)
    )::geography
)
备注：sql 中的 4326 是指的wsg84 的系统,ST_Distance计算出来的距离单位是米

ST_GeomFromText('LINESTRING(115.97166453999147 28.716493914230423,106.00231199774656 29.719258550486572)')与
ST_MakeLine(
    ST_MakePoint(115.97166453999147,28.716493914230423),
    ST_MakePoint(106.00231199774656,29.719258550486572)
)等价

ST_GeomFromText('POINT(115.97166453999147 28.716493914230423)',4326)与
ST_SetSRID(ST_MakePoint(115.97166453999147,28.716493914230423),4326)等价

ST_SetSRID(ST_MakePoint(115.97166453999147,28.716493914230423),4326)::geography与
Geography(ST_SetSRID(ST_MakePoint(115.97166453999147,28.716493914230423),4326))、
ST_GeographyFromText('SRID=4326;POINT(115.97166453999147 28.716493914230423)')等价
(::geography是postgis中的转换类型语法,把geometry转成geography)

资料:
https://blog.csdn.net/seapeak007/article/details/83059638

3.PostgreSQL PostGIS 的5种空间距离排序(km)算法

该方法不推荐,建议使用本文第二点的方法

PostgreSQL PostGIS 的5种空间距离排序(knn)算法
在这里插入图片描述

4.某一个点到某条线的距离是不是在某个范围内：

4.1 ST_Contains
select ST_Contains(St_Astext(ST_Buffer(geography(geomfromtext('MULTILINESTRING((线的坐标点))')),25.00{以米为单位的距离})),st_astext(geography(geomfromtext('POINT(121.37805 37.54142)')))) as result

使用类似上面的方式，就可以输入以米为单位的距离判断某个点是否在某个距离范围内；

资料:
https://blog.csdn.net/duanmuxiao/article/details/45667221
postgis 中的距离计算

4.2 ST_distance和ST_DWithin
SELECT T.*,
	ST_distance (
	ST_geomfromtext ( 'POINT(' || T.longitude || ' ' || T.latitude || ')' ) :: geography,
	ST_geomfromtext ( 'LINESTRING(' || '115.157801 22.885121,115.157801 22.885121' || ')' ) :: geography 
	) AS distance 
FROM
	t_table T 
	where 1=1
	and ST_DWithin (
				ST_geomfromtext ( 'POINT(' || t.longitude || ' ' || t.latitude || ')' ) :: geography,
				ST_geomfromtext ( 'LINESTRING(' || '115.157801 22.885121,115.157801 22.885121' || ')' ) :: geography,
				50
				)
ORDER BY distance LIMIT 5

以上使用ST_distance计算距离进行展示,距离的单位是米,使用ST_DWithin 框定和设定的线段50米范围内的点查询出来,
LINESTRING的规则为必须为两个以上的点才能连成线,所以如果是同样一个点则会退化为计算两点之间的距离

5.Geometry SQL参考

Geometry SQL参考
在这里插入图片描述

6.ST_GeomFromGeoJSON

描述
仅适用于GeoJSON中的Geometry片段。如果您尝试在整个JSON文档上使用它，则会引发错误。
该函数支持3D对象，并且不会丢弃Geometry对象的z-index。
在这里插入图片描述
如果是多个[{“type”:“Point”,“coordinates”:[116,40]},{“type”:“Point”,“coordinates”:[118,50]}]使用ST_GeomFromGeoJSON转换会报如下错误

7. 空间扩展函数st_geomfromgeojson无法识别完整的geojson问题解决

PGSQL空间扩展函数st_geomfromgeojson无法识别完整的geojson问题解决
解决方法,将多要素geoJson转换为Geometry,再提取成Geojson
在这里插入图片描述

8.pg数据库几何类型转换：

postgis常用操作手册

1.wkt转geometry：st_geomfromtext(wkt)
select st_geomfromtext('Point(122 33)')
2.geometry转wkt：st_astext(geometry)
select st_astext(st_geomfromtext('Point(122 33)'))
3.geometry转geojson：st_asgeojson(geometry)
select st_asgeojson(st_geomfromtext('Point(122 33)'))
4.geojson转geometry：st_geomfromgeojson(geojson)
select
st_geomfromgeojson(
st_asgeojson(st_geomfromtext('Point(122 33)')))
5.geometry转geohash：st_geohash(geometry)
select st_geohash(st_geomfromtext('Point(116 39)'))
6.geohash转geometry：st_geomfromgeohash
select st_geomfromgeohash('wwfmzesx7yvjugxr3nzv')

目前没有找到类似这样的函数st_geomfromgeojson(‘[{“type”:“Point”,“coordinates”:[116,40]},{“type”:“Point”,“coordinates”:[118,50]}]’),如果有找到的朋友可以留言贴上

9.两个geometry之间的关系函数

postgis常用操作手册
within、disjoint、intersects、union、intersection,difference

St_within(geom A,geom B)返回A是否处于B中
St_disjoint(geom A,geom B)返回A是否不在B中
St_intersects(geom A,geom B)返回A是否和B有接触
St_union(geom A,geom B)返回A+B两个几何的合并
St_intersection(geom A,geom B)返回A和B的交集
st_isempty(geom A)判断几何是否空
St_difference(geom A,geom B)返回A与B不相交的部分几何
select
st_difference(
st_buffer(
st_geomfromtext('Point(116 39)'),0.7),
st_buffer(
st_geomfromtext('Point(117 39)'),0.7)) geom

10.ST_AsGeoJSON

ST_AsGeoJSON

ST_AsGeoJSON 返回输入几何体或地理的 GeoJSON 表示形式。有关 GeoJSON 的更多信息，请参阅 Wikipedia 中的 GeoJSON。

对于 3DZ 和 4D 几何体，输出几何体是输入 3DZ 或 4D 几何体的 3DZ 投影。也就是说，x、y, 和 z 坐标存在于输出中。对于 3DZ 几何体，输出几何体是输入 3DM 几何体的 2D 投影。也就是说，只有 x 和 y 坐标存在于输出中。

对于输入地理，ST_AsGeoJSON 返回输入地理的 GeoJSON 表示形式。地理的坐标使用指定的精度显示。
　　
在这里插入图片描述

11.各种表述经纬度格式样例

WKT


```xml
点: POINT(116.39088 39.90763)
线: LINESTRING(121.626446 37.28054,121.62621 37.278753,121.32149 37.013646)',4326)
面: POLYGON((121.366052 37.415496,121.370904 37.421809,121.366333 37.414761,121.366052 37.415496))',4326)

geojson

点:{"coordinates":[113.74582959807395,22.76202773278976],"type":"Point"}
线:{"coordinates":[[113.74582959807395,22.76202773278976],[113.74465565850886,22.760118452898325]],"type":"LineString"}
面:{"coordinates": [[[113.74450765757979, 22.738200342340846], [113.74454087933219, 22.738180567383907], [113.74501360109386, 22.738611458588714], [113.74548480024157, 22.7390345639931], [113.74636729923435, 22.739818391613596], [113.74722840534088, 22.74058739335397], [113.74812354546441, 22.741399968650043], [113.74809162003447, 22.741446861027796], [113.74681892304096, 22.740312771623962], [113.74572508344824, 22.739284702923754], [113.74543733463696, 22.73906097213535], [113.74515749772821, 22.7387950291764], [113.7449777979908, 22.738637860762736], [113.74450765757979, 22.738200342340846]]],"type": "Polygon" }
多面：{"type":"MultiPolygon","coordinates":[
	[[[113.74736722,22.735827824],[113.747830735,22.73623593],[113.749208873,22.737500099],[113.749696372,22.737904629],[113.751007855,22.738651366],[113.750993791,22.738702174],[113.750301027,22.738334007],[113.750043535,22.738185104],[113.74961,22.737890194],[113.749255711,22.737582235],[113.748680617,22.737071984],[113.747776519,22.736222474],[113.747413403,22.735898322],[113.747356162,22.73584176],[113.74736722,22.735827824]]],
	[[[113.747664086,22.736361802],[113.748795001,22.737413743],[113.749427089,22.737978835],[113.749429325,22.73805606],[113.748108327,22.7368266],[113.747649696,22.736388605],[113.747664086,22.736361802]]]
]}

geometry

01010000000000000000805E400000000000804040

Postgis关于Point类型的一些查询操作
点的WKT表述如POINT(116.39088 39.90763)，在Postgis中创建一个点几个方式：

ST_Point、ST_MakePoint：

–用法：ST_Point(float x_lon, float y_lat);
–输出：0101000000452A8C2D04195D402A6F47382DF44340
SELECT ST_Point(116.39088,39.90763)
上面虽然创建了点，但是SRID却是0（unknown），可以通过ST_SRID查看。下面方式设置坐标系为4326：

–用法：ST_Point(float x_lon, float y_lat);
–输出：0101000020E6100000452A8C2D04195D402A6F47382DF44340
SELECT ST_SetSRID(ST_Point(116.39088,39.90763),4326)
坐标顺序是经度（或x）在前，纬度（或y）在后。ST_MakePoint和ST_Point一样，ST_MakePoint可以快速高效的创建一个点。

12 ST_Buffer

根据输入的geometry或者geography，扩大radius_of_buffer的大小，radius_of_buffer的单位输入格式geometry为度，输入格式geography则为米，度转换为米需要 度数*(6371000（地球半径）PI/180)，米转换为度 距离180/(6371000*PI)

geometry ST_Buffer(geometry g1, float radius_of_buffer, text buffer_style_parameters = '');
geography ST_Buffer(geography g1, float radius_of_buffer, text buffer_style_parameters);

示例：

--将面积以边为单位扩大50米
select pg_typeof(ST_Buffer(ST_geomfromtext ( 'LINESTRING(' || '115.157801 22.885121,115.157801 22.885121' || ')' ),50));
--将面积以边为单位扩大50米对应的度数
select pg_typeof(ST_Buffer(ST_geomfromtext ( 'LINESTRING(' || '115.157801 22.885121,115.157801 22.885121' || ')' )::geometry ,50*180/(6371000*3.1415926)))

ST_geomfromtext 返回的类型就是geography，所以强制转换可以省略

13 Geography和 Geometry 的区别

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

在pg数据库中用public.geometry数据类型保存geometry数据，在使用上geography常用

Geography和 Geometry 的转换：

ST_geomfromtext ( 'LINESTRING(' || '115.157801 22.885121,115.157801 22.885121' || ')' )::geometry
ST_geomfromtext ( 'LINESTRING(' || '115.157801 22.885121,115.157801 22.885121' || ')' )::geography
geography(ST_geomfromtext ( 'LINESTRING(' || '115.157801 22.885121,115.157801 22.885121' || ')' ))

在这里插入图片描述
参考：Geography和 Geometry 的区别

14 经纬度定位网页查询

经纬度定位网页查询
在这里插入图片描述
GeoJSON(高德开放平台)
在这里插入图片描述

geojson在这里插入图片描述
Test GeoJSON
在这里插入图片描述
百度地图拾取坐标点
在这里插入图片描述

15 位置服务开发API指南

腾讯位置服务API开发指南
在这里插入图片描述
高德位置服务API开发指南
在这里插入图片描述

Master_Shifu_
关注
mysql计算两点之间距离st_distance_sphere函数
weixin_42683476的博客
06-20 4396
st_distance_sphere函数是mysql5.7提供的，可以直接查询两个经纬度之间相距多少米，并且该函数的计算结果要比st_distance转换为米的结果更精确。mysql5.7提供了st_distance_sphere函数。而st_distance则需要自己进行计算转换为单位米。mysql5.6提供了st_distance函数，泉州市中骏世界城永辉超市。距离计算并从近到远排序。计算结果（单位：米）泉州市城东万达麦当劳。
postgresql 计算距离
松松面包的博客
05-03 4367
计算距离，直接生成单位为米的结果：select ST_Length(Geography(ST_GeomFromText('LINESTRING(120.451737 36.520975,120.455636 36.520885)')));计算某一个点到某条线的距离是不是在某个范围内：select ST_Contains(St_Astext(ST_Buffer(geography(ST_GeomFro
mysql 计算st_distance_sphere取整数
最新发布
weixin_42489589的博客
08-29 183
我整理的一些关于【Git】的项目学习资料（附讲解～～）和大家一起分享、学习一下：https://d.51cto.com/xltfovMySQL 中使用 ST_Distance_Sphere 计算球面距离并取整 在地理信息系统（GIS）以及相关的开发场景中，计算两个地理坐标点之间的距离是一个常见需求。在 MySQL 中，S...
PostgreSQL（PgSQL）根据经纬度计算距离
malimingwq的博客
03-17 9254
今天在使用PostgreSQL的PostGIS功能来实现根据经纬度计算距离和计算某一经纬度附近X米之内的地点，实现SQL语句如下： 计算两经纬度之间距离： select ST_Distance(ST_GeomFromEWKT('SRID=4326;POINT(lng1 lat1)'),ST_SetSRID(ST_Point(lng2::double precision, lat2::double precision), 4326)) 计算(lng2, lat2)是否在(lng1, lat1)的X米之内 s
在pg数据库中获取俩点距离
qq_43757153的博客
09-04 671
PG数据是库是一个相对于MySQL来说更安全的开源数据库，但是在市面上对PG的使用还是比较少的，博主就结合最近的工作问题，总结一下在PG数据库中如何获取到两个坐标点之间的距离（当然，也有获取点到线，点到点的距离，下面只举例两个坐标点的距离获取方式）
postgresql中如何利用经纬度求两点之间的距离？
theskylife的博客
08-26 3356
在某些场景下，我们可能需要在已知两个点的经纬度的情况下，利用SQL来计算两个点之间的距离。本例将介绍下在postgresql环境下如何计算两个点的距离。
postgresql计算两点距离
xinshijimanon的专栏
05-31 9028
postgresql计算两点距离下面两种方法：select ST_Distance( ST_SetSRID(ST_MakePoint(115.97166453999147,28.716493914230423),4326)::geography, ST_SetSRID(ST_MakePoint(106.00231199774656,29.719258550486572),4326)::geo...
PostgreSQL-Distance
aiseu1033的博客
04-23 1108
create table pt_tmp( id int, pt geometry);insert into pt_tmp values (1,'point(1 1)'),(2,'point(2 1)'),(3,'point(2 7)');计算欧式距离：select p1.id as id1,p2.id as id2,st_distance(p1.pt,p2.pt) from pt_tmp p1,p...
postgresql-postGIS 计算距离
ywtech的博客
04-21 409
如何安装postGIS 启用postGIS 如何用SQL 访问计算距离 java中如何使用。 如何安装postGIS postgis31_11.x86_64 https://yum.postgresql.org/ https://yum.postgresql.org/howto/ 安装 postgresql yum仓库信息 yum -y install https://download.postgresql.org/pub/repos/yum/re...
PostgreSQL 地图两点之间，经纬度距离计算 getdistance_bygispoint，自定义数据库函数方式， 非PostGIS数据库函数方式计算
秋̶᭄ꦿ攻城狮࿆ྂ
07-13 1477
距离计算公式 以 lng为经度(longitude)缩写 lat为纬度(latitude)缩写 create or replace function getdistance_bygispoint ( i_latbegin real, i_lngbegin real, i_latend real, i_lngend real ) returns float as $body$ /* * 地图两点之间，经纬度距离计算 * kuojungwon
apg数据库跟pg数据库的区别
03-29
在APG数据库中，地理空间数据可以进行复杂的查询和分析，例如空间缓冲区分析、距离计算、路径查找等。 PG数据库则是一个通用的关系型数据库，它可以存储和管理各种类型的数据。PG数据库提供了丰富的SQL支持和强大的...
myql空间数据库和postgis空间数据库对比
marsGIS的博客
09-18 542
mysql空间扩展函数和postgis空间扩展函数对比mysql空间函数列表postgis空间函数PostGIS扩展函数 mysql空间函数列表 1 ST_StartPoint() LineString的起始点 2 ST_EndPoint() LineString的终点 3 ST_Transform() 变换几何的坐标 4 ST_GeoHash() 产生geohash值 5 ST_LongFromGeoHash() 从geohash值返回经度 6 ST_LatFromGeoHash() 从geohash值返
postGreSql数据库，使用postgis扩展，升级成地理空间数据库
从地图看世界的专栏
05-27 355
PostgreSQL是一个通用的关系型数据库管理系统，而PostGIS是建立在PostgreSQL之上的一个扩展，使其具备了地理空间数据处理和分析的能力。PostGIS提供了许多空间分析函数和操作，如缓冲区分析、距离计算、空间关系判断等，使得用户可以在数据库中进行复杂的地理空间分析。
postgresql计算两点距离（经纬度地理位置）
热门推荐
seapeak007的博客
10-15 1万+
转自：https://blog.csdn.net/xinshijimanon/article/details/80522919 postgresql计算两点距离 jpa中写法，加上::geography转化经纬度的坐标计算，\\作为转译符 @Query(value = "select f from bc_contact f where f.data_end='2' and " + ...
postgresql 计算距离(单位直接生成米)
duanmuxiao的专栏
05-12 1万+
之前用的是ST_Distance 函数，但是貌似需要进行一次单位的转换，而且网上有说那种转换不是特别准确，现在暂时将该算法记录在此： select st_distance(ST_GeomFromText('POINT(120.451737 36.520975)',900913),ST_GeomFromText('POINT(120.455636 36.520885)',900913))*60*1
pg,gp创建分区表、计算经纬度距离，转换漂移经纬度
kevin
03-23 1155
------------------------------------------------------------------------------------------------------ CREATE OR REPLACE FUNCTION "dts_vehicle_resource"."getdistance"("i_latbegin" float8, "i_lngbegin...
pgsql gis 一个点到多个点的距离, (经纬度)距离最近的10个点
写bug的z
11-10 1981
场景：知道所有门店的经纬度信息，知道所有的小区的经纬度信息，想要得到一个门店 最近的10个小区，原来是etl帮处理的算好了一张表，门店和小区距离的的数据关系表，例如： 城市 m门店 距离（km） 北京 门店a 1.4 北京 门店b 5.3 上海 门店a 1231.8 虽然给你算好了 只要把城市 门店放到sql里按照距离 取最近的10条就可以了，但是数据量太大了，快要1.2亿了。存储太大了，讨论下来领导说把这个表删掉,改成.
怎样在 PostgreSQL 中优化对地理坐标数据的距离计算？
发现生活，分享智慧，一起成长！
07-09 1139
PostgreSQL 中处理地理坐标数据并进行距离计算是常见的需求，但由于这类计算的复杂性，可能会导致性能问题。优化地理坐标数据的距离计算至关重要，以提高数据库操作的效率和响应性。
postgresql常用函数
迪迦奥特曼的博客
08-23 3582
一、集合对象关系函数 ST_Distance(geometry, geometry): 获取两个几何对象间的距离 ST_DWithin(geometry, geometry, float): 如果两个几何对象间距离在给定值范围内，则返回TRUE ST_Equals(geometry, geometry):判断两个几何对象是否相等（比如LINESTRING(0 0, 2 2) 和LINESTRING(0 0, 1 1, 2 2)是相同的几何对象） ST_Disjoint(geomet
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值