Originally posted on 2022-01-27 at blog.tlarsendataguy.com, and shared here with permission.
This month I moved my public NASR database to a new home on Snowflake. You can find the connection details on GitHub, as well as a brief description of all of the tables available.
What is the NASR database?
Every 28 days the Federal Aviation Administration (FAA) publishes data files describing the National Airspace System of the USA. These files are a jumble of formatted text files, XML documents, and shape files. I have created an extensive data pipeline in Alteryx to parse these files and publish them to a publicly-accessible database. The database format makes it much easier to incorporate NASR data into your data applications. You do not have to do the parsing yourself.
How do I access the NASR database?
The NASR database is accessible via a publicly-available username and password, available on GitHub. You will need to install the Snowflake ODBC driver, which is publicly available from Snowflake.
Why Snowflake?
I originally used a PostgreSQL database hosted on Azure. I wanted to add a large (~50 billion records) table containing 1 arc-second resolution elevation data. The storage costs in PostgreSQL would have been significant. Snowflake storage costs are very small and the data is compressed. Instead, users pay for running queries. I estimated that my total cost of ownership in Snowflake would be lower than PostgreSQL.
My estimate is based entirely on how much the database is used. It is a niche dataset and relatively small. The major exception is the “elevation” table, which contains those 50 billion records of elevation data. If I start seeing users downloading that entire table, I will have to re-evaluate and limit access to that table.
How should we access the elevation data?
The elevation table is very simple with 3 fields:
- Lon: The longitude of the point, in tenths of a second
- Lat: The latitude of the point, in tenths of a second
- Elevation: The elevation of the point, in meters
The best way to access the elevation table is to provide constraints on Lon and Lat. For example, when I generate my data-driven landscapes, I specify a bounding box in my SQL query (20nm or 30nm, depending on the render). A 30nm bounding box yields ~3.2 million records. Try to keep your own queries somewhere under 5 million records.
Wait, tenths of a second?
The Lat and Lon fields are integers to facilitate accurate queries and uniqueness validation. To fit the data into an integer of a useful resolution, decimal degrees need to be converted to tenths of a second (multiplied by 36,000).
But why tenths of a second rather than whole seconds?
The elevation data is derived from 1 arc-second resolution geotiff files published by the US Geological Survey. Each pixel in the geotiff represents the elevation of a plot of land 1 arc-second by 1 arc-second in size. For example, a plot of land starting at 100°W, 35°N would have longitude ranging from -360,000 seconds to -359,999 seconds. Latitude would range from 125,999 seconds to 126,000 seconds.
I made the decision to use the centerpoint of each second as the identifier for Lon and Lat. From the example above, that middle point would be -359,999.5 seconds longitude and 125,999.5 seconds latitude. This results in a decimal, which cannot be represented in integers. So I increased the resolution of the Lon and Lat fields to tenths of a second to accurately store the middle point of each pixel.
I really want to query large volumes of this elevation data. How can I?
My intention is to always provide this NASR data freely, without charging subscription fees. This means I am absorbing all of the costs of hosting the data and processing it on an Azure VM every 28 days. I have a limited budget which can be exceeded by too many users running heavy queries. For now, I am keeping access wide open to anyone who wants to use it. If I start incurring high costs, I will move to a different model: The data will still be free, but the publicly-available credential will be replaced by per-user credentials, with each user limited to a certain number of credits each day or month. In this model, you would have to request your own user account to use the data.
If you want to help offset my costs, the easiest way is to sponsor the repository on GitHub. The more costs that are offset by sponsorships, the less I will care about heavy-use queries.
I want to see how you parsed these files. Can I?
Sure! Every workflow I created and use for NASR is available on GitHub. They are all licensed under the MIT license, so you can use them freely in your own projects.
Are you affiliated with the FAA or any other government agency?
No, I am completely independent of the FAA. I provide this data in this format because I really like it and want to make it easy for others to use it, too. If you have any issues with my NASR database, you should contact me directly.
Do you provide any warranties regarding data accuracy?
Obviously, I put a great deal of effort to parse the raw data files correctly. However, because this is a hobbyist project, I am not able to make warranty of any kind as to the accuracy of the data. I do not encourage you to use it in production or safety-critical systems.
Responses