« Importing Geonames.org Data Into MySQL | PHP – Calculating Distance Between Two Locations Given Their GPS Coordinates »
MySQL – Finding Nearest Record In Date/Time
August 2nd, 2009 @ 11:46:50 | GPS, MySQL
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)
[...] locate the GPS coordinates nearest in time to a photograph’s time-stamp [please refer to this post] – and, I can then use those GPS coordinates to look up the Geonames data [stored in my local [...]
I’ve been using a GPS for several years. Recently it was time to upload more current maps and the brand ( not Garmin ) I was using wanted $80 for the map update. Given the age and technology of the unit I determined that a new GPS would be a better choice. After considerable research I chose the Garmin Nuvi 265WT. I am amazed at the much improved features and functionality of this Garmin. Given the abilities of this unit, its price is fantastic ! Access to the Garmin website allows for personalizing/customizing which makes the Nuvi distinctly yours and use of the website is completely user friendly ( not so much my experience with the previous brand GPS I used ).
So far, the 265wt has proven incredibly accurate and the verbal prompts are so on target that I seldom need to glance at the screen. I couldn’t be happier with my Garmin…Quite the upgrade for me !
I am not new to blogging and actually value your web site. There is much innovative content that peaks my interest. I am going to bookmark your site and keep checking you out.
Nice introduce!I just now have new choice to purchasing GPS equipment.I have new car and I wanted very fastgps toolstaking travel by car.
Great Blog! I have a similar site, and get a lot of spam. Do you face such problems? Can you please recommend some steps I decide on combat spam. Thanks.
I am consequently happy by myself observed the site. Anyone basically suggested everybody basically everything that My spouse and i elected for you to hear for you to along with afterward a lot of. Fabulous writing along with many thanks repeatedly pertaining to engaging in that zero fee!