Great Circle Flight Lines in PostGIS

There is an excellent post by Anita Graser about creating Great Circles in PostGIS.

However as of PostGIS version 2.1 this can be done in a different (better) way, using the geography functions.

PostGIS Great Circles

For more information about geography, see:
Introduction to PostGIS – Geography

This allows us to create the great circles without having to add in a new projection.

So we first need to create our three tables in PostGIS:

The data itself can be found at: openflights.org/data.html

We can then load our data through PGAdminIII. You can just right click on a table and select import. Remember to not load the “uid” column, because it is our primary key which will be populated automatically and not in the original data. You will also want to define it as the primary key.

Now we need a geometry column in the airports dataset.

We can define our geometry in the airports dataset from the Latitude and Longitude columns.

And create a spatial index.

Then we can create a flights table.

This table will have a source geometry and a destination geometry along with a few other attributes. I added a primary key to this table as well.

To filter out a specific airport, for example Honolulu we use the “Airport ID”.

Then we add in the actual line geometry column.

And populating the great circle geomtrey:

This is works great to an extent, but QGIS still has some trouble with lines that cross the date-line.

Screenshot[32]

We can fix this using a Pacific centered projection like EPSG:3832.

Screenshot[33]

We can either set QGIS to the projection. Or we can set our geometry to this projection when creating the flight lines.

Thanks to:
The World Is A Village – PostGIS: using latitude and longitude to create geometry
http://gis.stackexchange.com/questions/84443/what-is-this-postgis-query-doing-to-show-great-circle-connections

One thought on “Great Circle Flight Lines in PostGIS

  1. Pingback: Uçuş ağını görselleştirmek: PostGIS Örneği | mete ercan pakdil

Leave a Reply

Your email address will not be published.