
To David Hancock The offset is wrong for the airport MMHO Hermosillo in your data because you encoded it wrongly as America/Chihuahua, while it should be America/Hermosillo. You had a typo in the email which spelled it Hermisillo, I don't know if that is a simple typo when sending or it was the root of the problem. Hermosillo is in the state of Sonora, which is the only state in Mexico that does not use Daylight Saving. Regards, Jesper Nørgaard Welen No virus found in this outgoing message. Checked by AVG. Version: 7.5.524 / Virus Database: 270.7.1/1687 - Release Date: 2008-09-23 18:32

Jesper Norgaard Welen wrote:
To David Hancock
The offset is wrong for the airport MMHO Hermosillo in your data because you encoded it wrongly as America/Chihuahua, while it should be America/Hermosillo. You had a typo in the email which spelled it Hermisillo, I don't know if that is a simple typo when sending or it was the root of the problem. Hermosillo is in the state of Sonora, which is the only state in Mexico that does not use Daylight Saving.
Do you have the URL for those mappings between airport code and TZ code? Best regards, Rui Lopes

Rui Lopes wrote: Do you have the URL for those mappings between airport code and TZ code? Unfortunately I don't, and I don't know of a simple way to get it either. But I once fostered the idea that with ESRI map functionality I could make a conversion utility from Latitude Longitude to the timezone that it was located in, to produce the timezone name for each point on the map given from the user. But it was kept on the idea phase, I never implemented it. If somebody needs this badly and could come up with a money compensation for my work, like US $100, I would surely do it since it was something I was interested in adding to World Time Explorer before. Of course you would need the Latitude and Longitude of the airport codes to do what *you* mentioned, but I assume that would be trivial? Regards, - Jesper Nørgaard Welen No virus found in this outgoing message. Checked by AVG. Version: 7.5.524 / Virus Database: 270.7.4 - Release Date: 2008-09-27 0:00

Jesper Norgaard Welen wrote:
Of course you would need the Latitude and Longitude of the airport codes to do what *you* mentioned, but I assume that would be trivial?
The UN/LOCODE (CODE FOR TRADE AND TRANSPORT LOCATIONS) database has coordinates for many locations, and airport codes for airports. http://www.unece.org/cefact/locode Generating the list for all the airports should be relatively trivial if you put the map in spatially-enabled database such as PostGIS. Something like SELECT unlocode.code, tz.tzid FROM unlocode, tz WHERE ST_within (tz.geom, unlocode.location) AND unlocode.class = airport; assuming that unlocode is a table for the UN/LOCODE database and the coordinates have been turned into point geometries (of course, the SQL above is just the idea) The main difficulty would be to have a good enough map, so that the UN/LOCODE coordinates do not fall in water. May be a bit of buffering around the point and an intersection with tz polygons would do the trick. Eric.

Eric Muller wrote:
Jesper Norgaard Welen wrote:
Of course you would need the Latitude and Longitude of the airport codes to do what *you* mentioned, but I assume that would be trivial?
The UN/LOCODE (CODE FOR TRADE AND TRANSPORT LOCATIONS) database has coordinates for many locations, and airport codes for airports. http://www.unece.org/cefact/locode
I've tried to use the geonames.org database, but some airports seem to have wrong coordinates (when seen in google maps). I'll try to compare it with LOCODE db.
Generating the list for all the airports should be relatively trivial if you put the map in spatially-enabled database such as PostGIS. Something like
SELECT unlocode.code, tz.tzid FROM unlocode, tz WHERE ST_within (tz.geom, unlocode.location) AND unlocode.class = airport;
assuming that unlocode is a table for the UN/LOCODE database and the coordinates have been turned into point geometries (of course, the SQL above is just the idea)
the tz table is from your shapefile at http://efele.net/maps/tz/world/ ?
The main difficulty would be to have a good enough map, so that the UN/LOCODE coordinates do not fall in water. May be a bit of buffering around the point and an intersection with tz polygons would do the trick.
By map you mean the TZ map from your shapefile? Can you elaborate a bit about the buffering and intersection? (I'm starting to enter into the GIS world, so I don't yet known the lingo). Thanks. Best regards, Rui Lopes

Rui Lopes wrote:
the tz table is from your shapefile at http://efele.net/maps/tz/world/ ?
That's one, but Jesper has another one (and we are trying to reconcile our logical differences). Anything that matches a tzid with a geometry should be appropriate.
By map you mean the TZ map from your shapefile?
Yes, same as above.
Can you elaborate a bit about the buffering and intersection? (I'm starting to enter into the GIS world, so I don't yet known the lingo).
Buffering is "growing" a geometry by some fixed amount; think adding a ring around a polygon. So to test if a point is within a geometry, and to account for errors, you can try to test if a ring around the point intersects with the geometry. The size of the ring should be determined from the precision of the maps. Eric.
participants (3)
-
Eric Muller
-
Jesper Norgaard Welen
-
Rui Lopes