当前位置：首页 > article >正文

clickhouse计算前后两点间经纬度距离

article 2025/1/16 6:51:15

问题

在这里插入图片描述
计算如图所示前后两点经纬度的距离？

方法

1、用开窗函数将如图所示数据下移一行

select
	longitude lon1,
	latitude lat1,
	min(longitude) over(
	order by time1 asc rows between 1 PRECEDING and 1 PRECEDING) lon2,
	min(latitude) over(
	order by time1 asc rows between 1 PRECEDING and 1 PRECEDING) lat2
from
	table1

在这里插入图片描述
完整sql

select
	time1 ,
	lon1,
	lat1,
	lon2,
	lat2,
	greatCircleDistance(a.lon1,
	a.lat1,
	a.lon2,
	a.lat2) distance
from
	(
	select
		time1,
		longitude lon1,
		latitude lat1,
		min(longitude) over(
		order by time1 asc rows between 1 PRECEDING and 1 PRECEDING) lon2,
		min(latitude) over(
		order by time1 asc rows between 1 PRECEDING and 1 PRECEDING) lat2
	from
		table1

3、简化版sql

select
	time1 ,
	longitude,
	latitude,
	greatCircleDistance(longitude , latitude ,
	min(longitude) over(
	order by time1 asc rows between 1 PRECEDING and 1 PRECEDING),
	min(latitude) over(
	order by time1 asc rows between 1 PRECEDING and 1 PRECEDING)) distance
from
	table1