PGCon2011 - Final (2011.06.11)

PGCon 2011
The PostgreSQL Conference

Speakers
Blake Crosby
Schedule
Day Talks - 2 - 2011-05-20
Room DMS 1120
Start time 15:00
Duration 01:00
Info
ID 356
Event type Lecture
Track GIS
Language used for presentation English
Feedback

Using PostgreSQL for Flight Planning

How PostgreSQL is being used at www.worldflightplanner.com

Postgres is a powerful database, especially when coupling it with PostGIS to make it spatially aware. Using PostgreSQL and PostGIS allows World Flight Planner to provide advanced flight planning tools to both private and commercial pilots.

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..)