6

I've been researching CLDR and IANA in order to find a centralized mapping of UN/LOCODEs to Olsen Timezones.

Ideally I would like to have for example:

+--------------+--------------------+
|un_locode     |timezone            |
+--------------+--------------------+
|USLAX         | America/Los_Angeles|
+--------------+--------------------+

for every UN/LOCODE.

Are my nube skills failing me in understanding how to use these sources to reach my goal? (If so please help point me towards the scripting that would allow me to automate providing these mappings).

Or, do these sources fail to have the data correlation that I'm looking for? (If so please let me know if you have a reliable source).

mattbrosenberg
  • 83
  • 1
  • 7

4 Answers4

3

We faced the exact same problem and hence had to provide a solution.

This solution involves linking the UN/LOCODES database with a geolocation/timezone database. There are a few caveats to this approach that were captured by Matt Johnson's answer and the accompanying comments.

Namely:

  • the UN/LOCODE database of coordinates is not complete[1] and sometime has inaccurate data[2]
  • in some cases, a 1 to 1 mapping between the UN/LOCODE and a timezone is impossible due to the political nature of the timezones.
  • the two points above are worsened by the inaccuracy of free coordinates-to-timezone databases. It is helpful to get a dataset that also includes territorial waters so that ports timezones can be properly linked to the country they belong.

The following repository https://github.com/Portchain/un_locodes_sql contains the code to extract and link the data. It outputs a SQL file that can be imported into a PostgreSQL DB. The geolocation/timezone data is based on the geo-tz[3] module which seems to source its data from timezone-boundary-builder[4].

Again, the list provided by our repository is of course incomplete and inaccurate. If you see any error in the data, please open a github issue and let's make an accurate, open source list of UN/LOCODE, coordinates and timezone information.

Nicolas
  • 51
  • 4
2

The GeoNames free database of cities (which is available to download) provides: city names, latitude/longitude and, most importantly, timezone information. You can fairly quickly make your own database connecting this information with the UN/LOCODE code lists based on the name/country/coordinates.

Cato Minor
  • 2,992
  • 3
  • 29
  • 42
1

I've not seen such a source. You could try to create one by mapping the lat/lon coordinates for those entries that have them, and correlating to IANA time zone by one of the methods listed here.

However, be sure to read Wikipedia's article about UN/LOCODE, especially describing errors with coordinates. Also note that many of the coordinates simply not in the data - why? I don't know.

The list of UN/LOCODE for the US is here, and show Los Angeles to be US LAX (not UNLAX). Its coordinates field is blank.

If you can find some other reliable source of UN/LOCODE to lat/lon, then you are in business. A quick search found that GeoNames claims to have this in their premium data subscription, but I haven't investigated further.

Community
  • 1
  • 1
Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • Thank you Matt, from my research and from the post you referenced, it's not recommended to resolve timezones to locations using lat/lon because timezones are primarily politically governed, not geographically. China is a good example in that the entire country is offset UTC+8 by political institution, but if you were to resolve one of the westernmost Chinese cities by lat/lon, you could end up with a timezone offset as much as UTC+6. – mattbrosenberg Nov 11 '15 at 13:52
  • @mattbrosenberg - That depends entirely on the method you use to resolve it. If you take a simple calculation, then you're absolutely correct. But most of the methods on the referenced post use maps with boundaries. Of course maps always carry a political context. If you disagree with those maps from a political perspective, then you can adjust them according to your views - and many solutions indeed do that. – Matt Johnson-Pint Nov 11 '15 at 17:39
  • WRT to China, note that politically the entire country is on UTC+8 (`Asia/Shanghai`), but some of the solutions do indeed include `Asia/Urumqi` (UTC+6) in their results. See [Xijiang - Urumqi Time](https://en.wikipedia.org/wiki/Time_in_China#Xinjiang). In particular, solutions that use [tz_world maps](http://efele.net/maps/tz/world/) include this zone, but others such as Google's API do not. – Matt Johnson-Pint Nov 11 '15 at 17:39
  • Also note that even if you were to find a list of UN/LOCODE to IANA time zone somewhere else, and even if that list was meticulously hand crafted by some official authority, it would carry the same type of political assertions. For example, Urumqi is `"CN URM"` - does it map to `Asia/Urumqi` or `Asia/Shanghai`? The [zone definition for `Asia/Urumqi`](https://github.com/eggert/tz/blob/5452ea663bc47939e312d0180a3cb936704e07b0/asia#L521-L524) has the UTC+6 offset, so there is context just in the mapping alone. – Matt Johnson-Pint Nov 11 '15 at 17:45
0

CLDR's map is here: https://unicode.org/reports/tr35/#Time_Zone_Identifiers

I saw CLDR tagged but not mentioned.

Steven R. Loomis
  • 4,228
  • 28
  • 39