Include Airport Timezones

[aconway]

Will you be adding the airport timezone to your data?

[davidmegginson]

Thank you for the suggestion! I'll investigate how hard it would be. There should be either external APIs or libraries that can do it based on lat/lon.

[aconway]

Yes, I've implemented assigning timezones based on Lat/Lon using the Haversine formula, however it is not always accurate. For example the nearest timezone identifier to Dallas is Denver (Mountain Time), when it should be Chicago (Central Time).

[aconway]

Hi David, in case you are thinking of adding timezones, here is a script to inject timezone boundary data into a database. I then ran sql to assign the correct timezone identifier to the airports from your data. Hope you find this useful.

Cheers,

#!/bin/bash
cur_dir=`realpath "$0"`
tmp_dir="$cur_dir/tz-tmp"
json_file="$tmp_dir/timezones.json"
sql_file="$tmp_dir/timezones.sql"

wget -nH https://github.com/evansiroky/timezone-boundary-builder/releases/download/2021c/timezones-with-oceans.geojson.zip -P $tmp_dir

for f in "$tmp_dir/*.zip"; do unzip -p "$f" > "$json_file"; done;

echo "Parse Polygons..."
# Process the Polygons first.
# data provided as [longitude,latitude] this will flip it to [latitude,longitude]
jq -r '.features[]
    | select(.geometry.type == "Polygon")
    | [
        (.properties.tzid),
        (.geometry.coordinates[0]
            | map("point(\(.[1]),\(.[0]))") | join(","))
    ]
    | "INSERT INTO Timezone VALUES(\"\(.[0])\", polygon(linestring(\(.[1])))) ON DUPLICATE KEY UPDATE area = VALUES(area);"
' "$json_file" > "$sql_file"

echo "Parse MultiPolygons..."
# Append the remaining MultiPolygons.
# data provided as [longitude,latitude] this will flip it to [latitude,longitude]
jq -r '.features[]
    | select(.geometry.type == "MultiPolygon")
    | [
        (.properties.tzid),
        ([
            (.geometry.coordinates[]
                | map("polygon(linestring(\(map("point(\(.[1]),\(.[0]))") | join(","))))")
                | .[0]
            )
        ] | join(","))
    ]
    | "INSERT INTO Timezone VALUES(\"\(.[0])\", multipolygon(\(.[1]))) ON DUPLICATE KEY UPDATE area = VALUES(area);"
' "$json_file" >> "$sql_file"

echo "Database injection..."
mysql dbtable < "$sql_file"

rm -r "$tmp_dir"

echo 'Done'

[MichaelNZ85]

Hi David,

I would also like to see this feature. At the moment I am getting around this by storing a JSON file with a list of airport codes and timezones. I managed to collate the data using this file and the Google Timezones API (very expensive!) for entries that didn't exist in the other file.

It would be so much more convenient if your file had everything.

[jaluebbe]

I am using the Python package timezonefinder which seems to work well using the lat/lon position as input.

[Samuel-Webster]

Hi @davidmegginson,

Fantastic data set! Another +1 for a timezone field, as detection based on lat long can be quite off at times, I would do what I can to of course contribute and help set the Australian and New Zealand locations.

This part is not related I know, but while looking through the data set, I thought about an is_closed or is_decomissioned boolean field rather than "closed" being a type? As there is a slight loss in metadata about what the airport was before it was closed.

[nx01-a]

Another +1 for timezone data. Happy to help contribute to populating the data!