Search code examples
mysqlgps

Mysql query to avoid GPS issues

I record from devices the location each 10 second and store it into a mysql table with this fields:

CREATE TABLE `schedules_trackings` (
  `id` bigint(20) NOT NULL,
  `device_id` bigint(20) UNSIGNED NOT NULL,
  `schedule_id` bigint(20) UNSIGNED NOT NULL,
  `latitude` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `longitude` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `altitude` double DEFAULT NULL,
  `accuracy` double DEFAULT NULL,
  `heading` double DEFAULT NULL,
  `speed` double DEFAULT NULL,
  `activity` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `battery` double DEFAULT NULL,
  `segmentid` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `uuid` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `odometer` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Sometimes some devices lose the gps signal or there is a gps glitch, this cause an issue when loading the data into the google maps drawing some straight line very far from the road.

I didn't find out a solution over the internet but my thought was this...if i check the distance between the next and previous coordinate and exclude from results record with distance more then 300mt i think will solve the issue.

It is a good solution? How to build such complex query?

Solution

  • As per my comment, you have a latitude and a longitude.

    Except for locations towards the poles, all you need to do is ask whether the new latitude is reasonably close to the old latitude, and whether the new longitude is reasonably close to (the moudulus of) the old longitude. You don't need the overhead of calculating the distance between points.

    I would guess that for most habitable places on Earth, a margin of error of plus-or-minus 0.01 for (the modulus of) longitude, and plus-or-minus 0.005 for latitude would be acceptable