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

2024-08-31 06:12
文章标签 计算 postgresql 距离 轨迹 对应 gps 工单

本文主要是介绍在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_orders和a_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记录之间的距离。
    此查询的结果将是一个表，其中包含订单ID、GPS ID、用户ID、坐标、记录时间、时差和连续GPS记录之间的行驶距离列。
    请注意，此查询假定数据库中存在a_orders、a_users和a_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函数查询即可。

这篇关于在Postgresql中计算工单的对应的GPS轨迹距离的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1123115。

相关文章

大模型研发全揭秘：客服工单数据标注的完整攻略

大模型研发全揭秘：客服工单数据标注的完整攻略

在人工智能（AI）领域，数据标注是模型训练过程中至关重要的一步。无论你是新手还是有经验的从业者，掌握数据标注的技术细节和常见问题的解决方案都能为你的AI项目增添不少价值。在电信运营商的客服系统中，工单数据是客户问题和解决方案的重要记录。通过对这些工单数据进行有效标注，不仅能够帮助提升客服自动化系统的智能化水平，还能优化客户服务流程，提高客户满意度。本文将详细介绍如何在电信运营商客服工单的背景下进行
阅读更多...
poj 1113 凸包+简单几何计算

poj 1113 凸包+简单几何计算

题意： 给N个平面上的点，现在要在离点外L米处建城墙，使得城墙把所有点都包含进去且城墙的长度最短。 解析： 韬哥出的某次训练赛上A出的第一道计算几何，算是大水题吧。 用convexhull算法把凸包求出来，然后加加减减就A了。 计算见下图： 好久没玩画图了啊好开心。 代码： #include <iostream>#include <cstdio>#inclu
阅读更多...
uva 1342 欧拉定理（计算几何模板）

uva 1342 欧拉定理（计算几何模板）

题意： 给几个点，把这几个点用直线连起来，求这些直线把平面分成了几个。 解析： 欧拉定理： 顶点数 + 面数 - 边数= 2。 代码： #include <iostream>#include <cstdio>#include <cstdlib>#include <algorithm>#include <cstring>#include <cmath>#inc
阅读更多...
uva 11178 计算集合模板题

uva 11178 计算集合模板题

题意： 求三角形行三个角三等分点射线交出的内三角形坐标。 代码： #include <iostream>#include <cstdio>#include <cstdlib>#include <algorithm>#include <cstring>#include <cmath>#include <stack>#include <vector>#include <
阅读更多...
XTU 1237 计算几何

XTU 1237 计算几何

题面： Magic Triangle Problem Description: Huangriq is a respectful acmer in ACM team of XTU because he brought the best place in regional contest in history of XTU. Huangriq works in a big compa
阅读更多...
音视频入门基础：WAV专题（10）——FFmpeg源码中计算WAV音频文件每个packet的pts、dts的实现

音视频入门基础：WAV专题（10）——FFmpeg源码中计算WAV音频文件每个packet的pts、dts的实现

一、引言 从文章《音视频入门基础：WAV专题（6）——通过FFprobe显示WAV音频文件每个数据包的信息》中我们可以知道，通过FFprobe命令可以打印WAV音频文件每个packet（也称为数据包或多媒体包）的信息，这些信息包含该packet的pts、dts： 打印出来的“pts”实际是AVPacket结构体中的成员变量pts，是以AVStream->time_base为单位的显
阅读更多...
PostgreSQL核心功能特性与使用领域及场景分析

PostgreSQL核心功能特性与使用领域及场景分析

PostgreSQL有什么优点？ 开源和免费 PostgreSQL是一个开源的数据库管理系统，可以免费使用和修改。这降低了企业的成本，并为开发者提供了一个活跃的社区和丰富的资源。 高度兼容 PostgreSQL支持多种操作系统（如Linux、Windows、macOS等）和编程语言（如C、C++、Java、Python、Ruby等），并提供了多种接口（如JDBC、ODBC、ADO.NET等
阅读更多...
轨迹规划-B样条

轨迹规划-B样条

B样条究竟是干啥的？白话就是给出一堆点，用样条的方式，给这些点连接起来，并保证丝滑的。 同时B样条分为准均匀和非均匀，以下为准均匀为例。 参考链接1：https://zhuanlan.zhihu.com/p/50626506https://zhuanlan.zhihu.com/p/50626506 参考链接2: https://zhuanlan.zhihu.com/p/536470972h
阅读更多...
计算数组的斜率，偏移，R2

计算数组的斜率，偏移，R2

模拟Excel中的R2的计算。         public bool fnCheckRear_R2(List<double[]> lRear, int iMinRear, int iMaxRear, ref double dR2)         {             bool bResult = true;             int n = 0;             dou
阅读更多...
PostgreSQL中的多版本并发控制（MVCC）深入解析

PostgreSQL中的多版本并发控制（MVCC）深入解析

引言 PostgreSQL作为一款强大的开源关系数据库管理系统，以其高性能、高可靠性和丰富的功能特性而广受欢迎。在并发控制方面，PostgreSQL采用了多版本并发控制（MVCC）机制，该机制为数据库提供了高效的数据访问和更新能力，同时保证了数据的一致性和隔离性。本文将深入解析PostgreSQL中的MVCC功能，探讨其工作原理、使用场景，并通过具体SQL示例来展示其在实际应用中的表现。 一、
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2