MySQL – Finding Nearest Record In Date/Time

Amongst other things, I have the habit of geotagging my photographs and for this purpose, I use my Garmin GPSMap 60CSx, an API I wrote to store tracks in MySQL & Geonames data in a MySQL [please refer to this if you wish to do so as well]. The work-flow might seem a bit complicated but from a personal perspective, it’s very much worth the effort. My camera and GPS unit are in sync with respect to time – enabling me to easily locate the GPS coordinates nearest in time to a photograph’s time-stamp – and, I can then use those GPS coordinates to look up the Geonames data [stored in my local MySQL database] and get details regarding the country, region/state, city/town/village. Once these details, they can be written into EXIF. In this write up, I will entail the method [or the MySQL syntax] I use to accomplish part one of this geotagging task – locating the GPS coordinates nearest in time to a photograph’s time-stamp. For completeness sake, the MySQL table structure that holds track data is given below:

1
2
3
4
5
6
7
8
9
10
CREATE TABLE IF NOT EXISTS `MyDatabase`.`TRK` (
  `id`          int( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `latitude`    float NULL ,
  `longitude`   float NULL ,
  `altitude`    float NULL ,
  `date`        date NULL ,
  `time`        time NULL
) ENGINE = MYISAM DEFAULT CHARSET latin1 AUTO_INCREMENT=1 ;
 
CREATE UNIQUE INDEX lldt ON `MyDatabase`.`TRK` (`latitude`,`longitude`,`date`,`time`);


MySQL Syntax

Suppose that we have a photograph taken on 2009-08-01 @ 09:31:28 and let us further suppose that we wish to find from our database the location [where this photograph was taken] by looking up the nearest record in time. The following MySQL syntax would easily accomplish it [remember: the GPX file stores date and time in UTC, not the local timezone! And EST is 4 hours behind UTC during this time of the year]:

-- datetime_EDT is the date & time at which the photograph was taken, '2009-08-01 09:31:28'
-- and this is in Eastern Standard Time [with Day Light Savings in effect]
 
-- datetime_UTC is the date & time at which the photograph was taken but in UTC/GMT
-- CONVERT_TZ('datetime_EDT', 'old_offset', 'new_offset')
 
-- trk_datetime_UTC is the date & time field generated on the fly by CONCATing date &
-- time fields from the MySQL table (that is nearest to datetime_UTC)
 
SELECT latitude, longitude, altitude, 
  '2009-08-01 09:31:28' AS datetime_EDT, 
  CONVERT_TZ('2009-08-01 09:31:28', '-04:00', '+00:00') AS datetime_UTC, 
  CONCAT(date, ' ', time) AS trk_datetime_UTC 
  FROM `MyDatabase`.`TRK` ORDER BY 
  ABS(UNIX_TIMESTAMP(datetime_UTC) - UNIX_TIMESTAMP(trk_datetime_UTC)) ASC LIMIT 1;
 
+------------+-------------+-------------+---------------------+---------------------+---------------------+
| latitude   | longitude   | altitude    | datetime_EDT        | datetime_UTC        | trk_datetime_UTC    |
+------------+-------------+-------------+---------------------+---------------------+---------------------+
| 47.4655750 | -87.8762530 | 191.1260000 | 2009-08-01 09:31:28 | 2009-08-01 13:31:28 | 2009-08-01 13:31:28 |
+------------+-------------+-------------+---------------------+---------------------+---------------------+
1 row in set (2.01 sec)



While I will try writing in detail about MySQL method/syntax to extract geographical data [country, region/state, city/town/village] that’s nearest to these GPS coordinates in a later post, we can always do a quick check using Google Maps! I know the photograph was shot in Copper Harbor, MI because I was there shooting the Copper Man Triathlon as swimmers were getting out of water, and the co-ordinates extracted from the GPS Tracks using the above MySQL syntax seem to confirm this.


Update (2009.11.30)

Use the following MySQL table structure and query for better performance.

1
2
3
4
5
6
7
8
9
10
CREATE TABLE IF NOT EXISTS `MyDatabase`.`TRK` (
  `id`          int( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `latitude`    float NOT NULL DEFAULT '0' ,
  `longitude`   float NOT NULL DEFAULT '0' ,
  `altitude`    float NOT NULL DEFAULT '0' ,
  `date_time`   float NOT NULL DEFAULT '0000-00-00 00:00:00' 
) ENGINE = MYISAM DEFAULT CHARSET latin1 AUTO_INCREMENT=1 ;
 
CREATE UNIQUE INDEX dt ON `MyDatabase`.`TRK` (`date_time`);
CREATE UNIQUE INDEX lldt ON `MyDatabase`.`TRK` (`latitude`,`longitude`,`date_time`);
-- datetime_EDT is the date & time at which the photograph was taken, '2009-08-01 09:31:28'
-- and this is in Eastern Standard Time [with Day Light Savings in effect]
 
-- datetime_UTC is the date & time at which the photograph was taken but in UTC/GMT
-- CONVERT_TZ('datetime_EDT', 'old_offset', 'new_offset')
 
SELECT latitude, longitude, altitude, date_time , 
  '2009-08-01 09:31:28' AS datetime_EDT, 
  CONVERT_TZ('2009-08-01 09:31:28', '-04:00', '+00:00') AS datetime_UTC 
  FROM TRK ORDER BY ABS(date_time - datetime_UTC) ASC LIMIT 1 ; 
 
+--------------+---------------+--------------+---------------------+---------------------+---------------------+
| latitude     | longitude     | altitude     | date_time           | datetime_EDT        | datetime_UTC        |
+--------------+---------------+--------------+---------------------+---------------------+---------------------+
|   47.4655750 |   -87.8762530 |  191.1260000 | 2009-08-01 13:31:28 | 2009-08-01 09:31:28 | 2009-08-01 13:31:28 | 
+--------------+---------------+--------------+---------------------+---------------------+---------------------+
1 row in set (0.81 sec)

One Reply to “MySQL – Finding Nearest Record In Date/Time”

Comments are closed.