Using PostgreSQL for Flight Planning

Talk Type: 
45 Minute Talk
Technical Level: 
Creative Commons - No Commercial or Modification

Using PostgreSQL and PostGIS allows you to access and modify your spatial data using SQL. This talk will provide an overview on the advantages to using PostGIS, how to properly store and query your geographic data, and offer some solutions to common problems.

This talk will comprise:

  • A primer on storing geographical data in postgres
    • Everything you needed to know about projections, sphereoids.
    • Cartesian Trig Vs. Spherical Trig
    • Types of data we need to store (points, line strings, other geometries)
  • Why we decided to use Postgres+PostGIS
  • How we store global scale meteorological data (converting gridded data in binary format into rows in the postgres database for fast querying)
  • Some of the challenges for storing aeronautical information in postgres.
    • How the data was organized in the database (a huge challenge, as identifiers for airports and navigational aids are not globally unique)
    • Odd airspace shapes (such as pie, or "pac man" shapes)
  • Describe the overall relationships of aeronautical data (airports, navigational aids, fixes) with each other.
  • Some custom functions we wrote to get the job done.
  • Examples of how to use PostGIS functions to do fast computations, such as:
    • Find me the closest airport to my current location.
    • Does my flight path take me through restricted airspace?
    • What heading do I need to take when I cross my next waypoint (bearings).
  • Displaying geospatial data, How we did it (map server, mapnik, openlayers, etc..)