在Postgresql中计算工单的对应的GPS轨迹距离

一、概述

在某个App开发中,要求记录用户的日常轨迹,在用户巡逻设备的时,将记录的轨迹点当做该设备巡逻时候的轨迹。

由于业务逻辑上没有明确的指示人员巡逻工单-GPS位置之间的关系,所以通过时间关系进行轨迹划定。

二、创建测试表

首先创建测试表,包括用户表、工单表以及GPS轨迹表。

  1. 用户表
--用户表
CREATE TABLE a_users (userid varchar NULL,username varchar NULL
);
  1. 工单表
--用户表
CREATE TABLE a_orders (orderid varchar NULL,relateduserid varchar NULL,order_time timestamptz NULL
);
  1. GPS轨迹表
--GPS记录表
CREATE TABLE a_gps_recording (gpsid int4 NULL,userid varchar NULL,x float4 NULL,y float4 NULL,recordtime timestamptz NULL
);

三、插入测试数据

为三张表分别插入测试数据。

--测试数据
---- 轨迹数据
INSERT INTO a_gps_recording
(gpsid, userid, x, y, recordtime)
VALUES(900000001, '0', 124.00001, 34.00001, '2024-08-30 08:56:39.000 +0800');
INSERT INTO a_gps_recording
(gpsid, userid, x, y, recordtime)
VALUES(900000002, '0', 124.00001, 34.00002, '2024-08-30 08:56:39.500 +0800');
INSERT INTO a_gps_recording
(gpsid, userid, x, y, recordtime)
VALUES(900000003, '0', 124.00002, 34.00003, '2024-08-30 08:56:39.600 +0800');
INSERT INTO a_gps_recording
(gpsid, userid, x, y, recordtime)
VALUES(900000004, '0', 124.00003, 34.00004, '2024-08-30 08:56:39.700 +0800');
INSERT INTO a_gps_recording
(gpsid, userid, x, y, recordtime)
VALUES(900000005, '0', 124.00004, 34.00005, '2024-08-30 08:56:39.800 +0800');
INSERT INTO a_gps_recording
(gpsid, userid, x, y, recordtime)
VALUES(900000006, '1', 124.10004, 34.00005, '2024-08-30 05:55:00.000 +0800');
INSERT INTO a_gps_recording
(gpsid, userid, x, y, recordtime)
VALUES(900000007, '1', 124.20004, 34.00005, '2024-08-30 05:56:00.000 +0800');
INSERT INTO a_gps_recording
(gpsid, userid, x, y, recordtime)
VALUES(900000008, '1', 124.30004, 34.00005, '2024-08-30 05:57:00.000 +0800');
INSERT INTO a_gps_recording
(gpsid, userid, x, y, recordtime)
VALUES(900000009, '1', 124.40004, 34.00005, '2024-08-30 06:00:00.000 +0800');
INSERT INTO a_gps_recording
(gpsid, userid, x, y, recordtime)
VALUES(900000010, '1', 124.50004, 34.00005, '2024-08-30 06:01:00.000 +0800');
INSERT INTO a_gps_recording
(gpsid, userid, x, y, recordtime)
VALUES(900000011, '1', 124.60004, 34.00005, '2024-08-30 06:02:00.000 +0800');
---- 工单数据
INSERT INTO a_orders
(orderid, relateduserid, order_time)
VALUES('100000', '0', '2024-08-30 08:56:39.000');
INSERT INTO a_orders
(orderid, relateduserid, order_time)
VALUES('100001', '1', '2024-08-30 07:20:39.000');
INSERT INTO a_orders
(orderid, relateduserid, order_time)
VALUES('100002', '1', '2024-08-30 06:00:00.000');
---- 用户数据
INSERT INTO a_users (userid, username) VALUES('1', '用户1');
INSERT INTO a_users (userid, username) VALUES('2', '用户2');
INSERT INTO a_users (userid, username) VALUES('3', '用户3');
INSERT INTO a_users (userid, username) VALUES('0', '用户0');

四、查询思路

4.1 建立表工单表和用户表之间的关联,筛选工单

第一个CTE order_user连接a_ordersa_users表,以检索与订单ID“100002”关联的用户名。

4.2 建立订单与轨迹之间的关联查询
  1. 第二个CTE order_gps在订单时间的5分钟时间窗口内从a_gps_recording表中选择与用户相关的gps记录。它计算订单时间和GPS记录时间之间的时间差。
  2. 第三个CTE ordered_data根据记录时间为每个GPS记录分配一个行号。这将有助于计算连续GPS记录之间的距离。
4.3 距离计算
  1. 主查询从ordered_dataCTE中选择所需的列,并使用ST_distance函数计算连续GPS记录之间的距离。ST_Transform函数用于将坐标转换为合适的投影(EPSG:3857)以进行距离计算。
  2. ordered_data(别名od1)和自身(别名od2)之间的左JOIN条件为od1.rn=od2.rn+1,可确保计算连续GPS记录之间的距离。
    此查询的结果将是一个表,其中包含订单IDGPS ID用户ID坐标记录时间时差连续GPS记录之间的行驶距离列。
    请注意,此查询假定数据库中存在a_ordersa_usersa_gps_recording表。确保将表名和列名替换为数据库中的实际名称。

五、查询SQL

--查询指定工单/用户的距离
select orderid,gpsid,userid,x,y,recordtime,time_Diff,distance_in_meters from (-- ■■■■■■order_user■■■■■with order_user as (select ao.*,au.username from a_orders ao left join a_users au on ao.relateduserid = au.userid and ao.orderid = '100002'),-- ■■■■■■order_gps■■■■■order_gps as (select ao.username,ao.orderid,agr.gpsid,agr.userid,agr.x,agr.y,agr.recordtime,abs(EXTRACT(EPOCH FROM age(ao.order_time , agr.recordtime)) / 60) as time_Diff from order_user ao join a_gps_recording agron  --距离工单前后5分钟的位置作为工单相关的距离abs(EXTRACT(EPOCH FROM age(ao.order_time , agr.recordtime)) / 60) < 5 --工单号为100002and ao.relateduserid = agr.userid and ao.orderid = '100002'),-- ■■■■■■ordered_data■■■■■ordered_data as (SELECT orderid,gpsid,userid,x,y,recordtime,time_diff,row_number() OVER (ORDER BY recordtime) AS rnFROM order_gps)SELECT od1.orderid,od1.gpsid,od1.userid,od1.x,od1.y,od1.recordtime,od1.time_Diff,od1.recordtime AS time1,od2.recordtime AS time2,-- ■■■■■■转换地理坐标系到平面坐标系,计算距离■■■■■ST_Distance(ST_Transform(ST_SetSRID(ST_MakePoint(od1.x, od1.y), 4326), 3857), ST_Transform(ST_SetSRID(ST_MakePoint(od2.x, od2.y), 4326), 3857)) AS distance_in_metersFROM ordered_data od1left JOIN ordered_data od2 ON od1.rn = od2.rn + 1
) t;

六、测试结果

运行上述SQL,得到如下查询结果,最后一列distance_in_meters为计算出的相邻两个GPS点之间的距离。

测试结果

进一步计算工单对应的总距离,只需要运行sum函数查询即可。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/415980.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

备受500强企业青睐的安全数据交换系统,到底有什么优势?

网络隔离成为常见的安全手段 网络隔离技术已成为许多企业进行网络安全建设的重要手段之一&#xff0c;党政单位、金融机构、半导体企业、以及能源电力、医疗、生物制药等等行业及领域的企业都会选择方式不一的网络隔离技术来保护自己的网络安全&#xff0c;规避互联网中的网络…

python开发--模板语句

这部分是导航栏部分的代码&#xff0c;由于导航栏在各个页面都需要用&#xff0c;为了提高代码复用率将导航栏部分作为一个模板。 在下面代码图中&#xff0c;红色框部分相当于一个插槽&#xff0c;其他页面&#xff0c;如部门列表、用户列表等将在这个位置展示。 这部分是用户…

全国地市未来产业水平数据集(2008-2023年)

未来产业&#xff0c;作为驱动经济社会高质量发展的核心引擎&#xff0c;是指依托科技创新和模式创新&#xff0c;引领全球新一轮科技革命和产业变革&#xff0c;具有前瞻性、先导性、战略性的新兴产业领域。也是实现生产力大解放&#xff0c;推动生产力质的跃迁并形成新质生产…

路径处理秘籍:Golang path包最佳实践与技巧

路径处理秘籍&#xff1a;Golang path包最佳实践与技巧 引言基本概念和功能path包简介路径的概念&#xff1a;相对路径与绝对路径常见操作函数概览 路径清理和拼接path.Cleanpath.Joinpath.Split 路径提取与处理path.Basepath.Dirpath.Ext处理不同操作系统的路径分隔符 路径匹配…

kubeadm方式升级k8s集群

一、注意事项 升级前最好备份所有组件及数据&#xff0c;例如etcd 不要跨两个大版本进行升级&#xff0c;可能会存在版本bug&#xff0c;如&#xff1a; 1.19.4–>1.20.4 可以 1.19.4–>1.21.4 不可以 跨多个版本的可以逐个版本进行升级。 二、查看当前版本 [rootk8s…

AI时代的程序员:关于创业、应用开发与快速成长的经验分享 | CSDN杭州线下分享

写在前面 上周六参加了一个CSDN组织的线下技术沙龙&#xff0c;做了一个分享&#xff0c;所以本篇内容对当时分享的内容做一个整理&#xff0c;感谢CSDN平台和鲲志大佬的组织&#xff0c;让大家有了一次深入的沟通交流。 先贴照片留念&#xff1a; 本来是想弄个详细点的逐字稿…

【qt】多线程实现倒计时

1.界面设计 设置右边的intvalue从10开始倒计时 2.新建Thread类 新建Thread类&#xff0c;使其继承QThread类&#xff0c;多态重写run函数&#xff0c;相当于线程执行函数 3.重写run函数 重写run函数&#xff0c;让另一个进程每隔1s发出一个信号&#xff0c;主线程使用conne…

大零售时代:开源 AI 智能名片、2+1 链动与 O2O 商城小程序引领融合新趋势

摘要&#xff1a;本文深入探讨了当今零售业态的发展趋势&#xff0c;指出在数据匹配的时代&#xff0c;人依然在零售中发挥着重要作用。通过对大零售理念的阐述&#xff0c;分析了跨行业跨业态融合的必然性&#xff0c;强调了业态融合的指导思想以及实现方式。同时&#xff0c;…

《OpenCV计算机视觉》—— 对图片的各种操作

文章目录 1、安装OpenCV库2、读取、显示、查看图片3、对图片进行切割4、改变图像的大小5、图片打码6、图片组合7、图像运算8、图像加权运算 1、安装OpenCV库 使用pip是最简单、最快捷的安装方式 pip install opencv-python3.4.2还需要安装一个包含了其他一些图像处理算法函数的…

【教程】MySQL数据库学习笔记(六)——数据查询语言DQL(持续更新)

写在前面&#xff1a; 如果文章对你有帮助&#xff0c;记得点赞关注加收藏一波&#xff0c;利于以后需要的时候复习&#xff0c;多谢支持&#xff01; 【MySQL数据库学习】系列文章 第一章 《认识与环境搭建》 第二章 《数据类型》 第三章 《数据定义语言DDL》 第四章 《数据操…

华为云征文|华为云Flexus X实例docker部署srs6并调优,协议使用webrtc与rtmp

华为云征文&#xff5c;华为云Flexus X实例docker部署srs6并调优&#xff0c;协议使用webrtc与rtmp 什么是华为云Flexus X实例 华为云Flexus X实例云服务是新一代开箱即用、体验跃级、面向中小企业和开发者打造的高品价比云服务产品。Flexus云服务器X实例是新一代面向中小企业…

CRM系统为贷款中介行业插上科技的翅膀

CRM&#xff08;客户关系管理&#xff09;系统为贷款中介公司插上了科技的翅膀&#xff0c;极大提升了贷款中介企业的运营效率、客户管理能力和市场竞争力。鑫鹿贷款CRM系统基于互联网、大数据分析、人工智能、云计算等前沿技术&#xff0c;帮助贷款中介公司实现业务流程的自动…

注册安全分析报告:央视网

前言 由于网站注册入口容易被黑客攻击&#xff0c;存在如下安全问题&#xff1a; 暴力破解密码&#xff0c;造成用户信息泄露短信盗刷的安全问题&#xff0c;影响业务及导致用户投诉带来经济损失&#xff0c;尤其是后付费客户&#xff0c;风险巨大&#xff0c;造成亏损无底洞…

Android 11 (R)AMS Activity内部机制

一、AMS是如何被管理的 如我们在Android 11(R)启动流程中介绍的一样&#xff0c;AMS和ATMS是在SystemServer中被启动的 ActivityTaskManagerService atm mSystemServiceManager.startService(ActivityTaskManagerService.Lifecycle.class).getService(); mActivityManagerSe…

名城优企游学活动走进龙腾半导体:CRM助力构建营销服全流程体系

8月29日&#xff0c;由纷享销客主办的“数字中国 高效增长——名城优企游学系列活动之走进龙腾半导体”研讨会在西安市圆满落幕&#xff0c;来自业内众多领袖专家参与本次研讨会&#xff0c;深入分享交流半导体行业的数字化转型实践&#xff0c;探讨行业数字化、智能化转型之路…

Linux【3】文件目录进阶

目录 cd 回到家目录 在最近两次目录来回切花 相对路径&#xff1a;从当前位置开始&#xff0c;前面没有/ or ~ mkdir rm 不可恢复 删除目录 -f 有则删&#xff0c;无也不报错 ls通配符仍适用&#xff01;【批量删除】 cd 回到家目录 cd cd ~ 在最近两次目录来回…

Python 数据分析— Pandas 基本操作(上)

文章目录 学习内容&#xff1a;一、Series 创建及操作二、DataFram 的创建与操作三、Pandas 的读写操作四、选择指定行列数据 学习内容&#xff1a; 一、Series 创建及操作 ** Series : Pandas 的一种数据结构形式&#xff0c;可理解为含有索引的一维数组。** **&#xff08;…

【Altium Designer脚本开发】——PCB平面绕组线圈 V1.4

PCB平面绕组线圈工具用于生成平面电机线圈&#xff0c;应用场景可参考平面电机的书籍、CNKI论文或平面电机的视频。此工具运行环境在Altium Designer中&#xff0c;可用于Altium Designer全系列的版本中。 以下工具可以定制和试用 原理图文档处理工具 ➡️物料编码自动查找工具…

c++ 156函数

inline内联函数 #include<iostream> using namespace std;inline void printA() {int a 10;cout << "a:" << a << endl;}void main() {//printA();//c编译器会这样 把函数体机械地放到main函数里面{int a 10;cout << "a:"…

云计算之ECS

目录 一、ECS云服务器 1.1 ECS的构成 1.2 ECS的实例规格 1.3 镜像 1.4 磁盘 1.5 安全组 1.6 网络 1.7 产品结构 二、块存储介绍 2.1 快存储定义 2.2 块存储性能指标 2.3 快存储常用操作-云盘扩容 2.4 块存储常见问题 三、快照介绍 3.1 快照定义 3.2 快照常见问题…