How to calculate latlong difference from last 15 min ago and last 1 min ago without using correlation subqueries

Question

Problem

I would like to find logs that are active and do not hit a distance quota of 12.5km, comparing from last 15 min ago and latest (within last 1 min ago)

Sample data

select version();

version()
5.7.39

CREATE TABLE IF NOT EXISTS `consignment` (
  `consignment_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `start_time` datetime DEFAULT NULL,
  `stop_time` datetime DEFAULT NULL,
  `truck_truck_id` varchar(12) NOT NULL,
  PRIMARY KEY (`consignment_id`),
  KEY `consignment_truck_truck_id_index` (`truck_truck_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `consignment`
--

INSERT INTO `consignment` (`consignment_id`, `start_time`, `stop_time`, `truck_truck_id`) VALUES
(1, NULL, NULL, 'DEF699'),
(2, NULL, NULL, 'LOL999'),
(11, '2022-11-28 08:36:47', NULL, 'ABC123');

Records: 3  Duplicates: 0  Warnings: 0

CREATE TABLE IF NOT EXISTS `gps_log` (
  `gps_log_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `lat` double NOT NULL,
  `long` double NOT NULL,
  `log_time` datetime NOT NULL,
  `consignment_consignment_id` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`gps_log_id`),
  KEY `gps_log_consignment_consignment_id_index` (`consignment_consignment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;

INSERT INTO `gps_log` (`gps_log_id`, `lat`, `long`, `log_time`, `consignment_consignment_id`) VALUES
(1, 36.47579591525347, -86.677500994023, '2022-11-28 08:11:51', 1),
(2, 36.47053036255657, -86.6787666877346, '2022-11-28 08:12:56', 2),
(3, 36.475240655695934, -86.71250224047003, '2022-11-28 08:38:29', 11),
(4, 36.475240655695934, -86.71250224047003, '2022-11-28 08:49:19', 11),
(5, 36.475240655695934, -86.71250224047003, '2022-11-28 08:53:22', 11),
(6, 36.475240655695934, -86.71250224047003, '2022-11-28 09:38:55', 11),
(7, 36.475240655695934, -86.71250224047003, '2022-11-28 09:38:55', 1),
(8, 36.475240655695934, -86.71250224047003, '2022-11-28 09:53:55', 1);

ALTER TABLE `gps_log`
  ADD CONSTRAINT `gps_log_consignment_consignment_id_foreign` FOREIGN KEY (`consignment_consignment_id`) REFERENCES `consignment` (`consignment_id`) ON UPDATE CASCADE;
COMMIT;

Records: 8  Duplicates: 0  Warnings: 0

Records: 8  Duplicates: 0  Warnings: 0

SELECT * FROM `gps_log`

gps_log_id	lat	long	log_time	consignment_consignment_id
1	36.47579591525347	-86.677500994023	2022-11-28 08:11:51	1
2	36.47053036255657	-86.6787666877346	2022-11-28 08:12:56	2
3	36.475240655695934	-86.71250224047003	2022-11-28 08:38:29	11
4	36.475240655695934	-86.71250224047003	2022-11-28 08:49:19	11
5	36.475240655695934	-86.71250224047003	2022-11-28 08:53:22	11
6	36.475240655695934	-86.71250224047003	2022-11-28 09:38:55	11
7	36.475240655695934	-86.71250224047003	2022-11-28 09:38:55	1
8	36.475240655695934	-86.71250224047003	2022-11-28 09:53:55	1

SELECT * FROM `consignment`

consignment_id	start_time	stop_time	truck_truck_id
1	null	null	DEF699
2	null	null	LOL999
11	2022-11-28 08:36:47	null	ABC123

Currently stuck at...

-- My attempt

SELECT `gps_log`.*, ST_Distance_Sphere( POINT (36.475240655695934, -86.71250224047003), -- IDK how to get data here: gps_log_id: 3
                              POINT(gps_log.`long`, gps_log.`lat`)) 
          AS `distance_in_km` 
FROM `gps_log`
INNER JOIN (
    SELECT `tmp`.* 
    FROM `gps_log` as `tmp`
    WHERE `tmp`.`consignment_consignment_id` IN (
                                SELECT c.`consignment_id` 
                                FROM `consignment` as c
                                WHERE c.`start_time` IS NOT NULL
                                AND c.`stop_time` IS NULL
                                ) 
    AND `tmp`.`log_time` > NOW() - INTERVAL 36000 MINUTE -- Compare data from 15 min ago and data from 1 min ago... we simulate 3600
    ORDER BY `gps_log_id` DESC
    LIMIT 1
) AS table_2
ON `gps_log`.`gps_log_id` = `table_2`.`gps_log_id`

gps_log_id	lat	long	log_time	consignment_consignment_id	distance_in_km
6	36.475240655695934	-86.71250224047003	2022-11-28 09:38:55	11	14257834.99373771

Desired output

Get logs have a dist < 12.5km from 15 min ago:

gps_log_id	lat	long	log_time	consignment_consignment_id	distance_in_km
6	36.475240655695934	-86.71250224047003	2022-11-28 09:38:55	11	0
8	36.475240655695934	-86.71250224047003	2022-11-28 09:53:55	1	0

fiddle

Edit

For those with a similar problem, but seeking a simpler solution, the best solution is to create a speed field in table. However, due to the limitations of the project manager/client, I am required to calculate distance instead.

Answer 1

I'm unclear on the specifics, but I am pretty sure it takes two steps:

1. Find the rows that match the "active", "distance quota", and "15 to 1 minute ago". See the Comment where I asked for specifics.
2. Find the latest of each. This involves a "groupwise max" algorithm; see the tag I added.

Then put the two pieces together. MySQL 8.0 has CTEs, which would make it easier. But, since you are on 5.7, it gets messier

CREATE TEMPORARY TABLE t
    SELECT ... ((step 1));

Then apply the group-wise max to t.

If you have thousands of rows, optimization will be a problem. This is because the distance calc is slow. We can discuss that after you get the code working [slowly] with the groupwise-max.

If you will have millions of rows and the resolution needed is only enough to distinguish two trucks, see this for picking something less bulky than DOUBLE (8 bytes each) for lat and lng: http://mysql.rjweb.org/doc.php/latlng#representation_choices