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”