By
2011-08-20
GIS, PostGIS, QGIS, Visualization
19 Comments

Visualizing Global Connections

Today, I’ve been experimenting with data from OpenFlights.org. They offer airport, airline and route data for download. The first idea that came to mind was to connect airports on a shared route by lines. This kind of visualization just looks much nicer if the connections are curved instead of simple straight lines.

Luckily, that’s pretty easy to do using PostGIS. After loading airport positions and route data, we can create the connection lines like this (based on [postgis-users] Great circle as a linestring):

UPDATE experimental.airroutes
SET the_geom = 
(SELECT ST_Transform(ST_Segmentize(ST_MakeLine(
       ST_Transform(a.the_geom, 953027),
       ST_Transform(b.the_geom, 953027)
     ), 100000 ), 4326 ) 
 FROM experimental.airports a, experimental.airports b
 WHERE a.id = airroutes.source_id  
   AND b.id = airroutes.dest_id
);

The CRS used in the query is not available in PostGIS by default. You can add it like this (source: spatialreference.org):

INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 953027, 'esri', 53027, '+proj=eqdc +lat_0=0 +lon_0=0 +lat_1=60 +lat_2=60 +x_0=0 +y_0=0 +a=6371000 +b=6371000 +units=m +no_defs ', 'PROJCS["Sphere_Equidistant_Conic",GEOGCS["GCS_Sphere",DATUM["Not_specified_based_on_Authalic_Sphere",SPHEROID["Sphere",6371000,0]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Equidistant_Conic"],PARAMETER["False_Easting",0],PARAMETER["False_Northing",0],PARAMETER["Central_Meridian",0],PARAMETER["Standard_Parallel_1",60],PARAMETER["Standard_Parallel_2",60],PARAMETER["Latitude_Of_Origin",0],UNIT["Meter",1],AUTHORITY["EPSG","53027"]]');

This is an example visualization (done in QGIS) showing only flight routes starting from Vienna International Airport:

Flight routes from Vienna International

Connections crossing the date line are currently more problematic. Lines would have to be split, otherwise this is what you’ll get:

Date line trouble

19 comments
  1. mattwigway said:

    There seems to be an alignment problem on the first map – several flights terminate in the Atlantic off Africa. The second map looks OK.

  2. mattwigway said:

    Oh, my bad.

    • underdark said:

      Thanks Antonio,
      JEQL seems really interesting. I’m hoping that the issue will be dealt with in QGIS too some day. Most developers and sponsors live far away from the date line so it is not of high priority to them.

    • underdark said:

      Hi Michael,

      Actually, data preparation was all done in PostGIS with the query I mentioned in the post. It creates the great circle lines which can then be visualized in QGIS in any global coordinate system.
      The most important part of the query is the creation of the line (ST_MakeLIine) on the sphere and adding of additional vertices (ST_Segmentize) to preserve the shape when reprojecting back to e.g. WGS84.

      Those are some very nice results you posted on your blog!

      • Thanks Underdark,

        Very informative post and
        Cheers! Mapping flight routes presents an interesting topic for research.

  5. Hi,
    i’m playing with PostGIS and openflights seem like a good data source to use for learning. I have a database called GIS based on postgis_template. I wanted to create a new schema called openflights, but as i found out, i wouldn’t be able to use functions defined in the public schema. Or would i? Don’t you know a way to make them accessible from another schema?
    Another question: I’ve gone through the airports data and found lat/long and elevation coordinates. How should i load them into a table?

    I was thinking of something like
    CREATE TABLE airports (
    id int primary key,
    name varchar(100),
    city varchar (100),
    country varchar(100),
    iata char(3),
    icao char(4));

    SELECT AddGeometryColumn(‘openflights’,’airports’,’geom’,4326,’POINT’,3) but would it be possible to bulk insert them from the file this way? I don’t wanna end up inserting them one by one :-).

    Thanks for your reply and interesting articles.

    • underdark said:

      Hi Michal,
      Functions in public schema are available from all other schemata as far as I know.
      @airport data:
      First, create the airports table with all columns available in the CSV.
      Then you can use the COPY command to insert all data at once.
      Afterwards, add the geometry column and then run and UPDATE on it that creates points out of the lat and lon values (probably elevation too if you plan on using it).
      Good luck!
      Anita

      • Thanks for help. However, i managed to run into troubles immediately. Trying to copy the file with COPY airports.dat WITH DELIMITER ‘,’ NULL ”; i get the following error: extra data after last expected column on line 4442, which is “5881,”Mata’aho Airport”,”Angaha, Niuafo’ou Island”,”Tonga”,”NFO”,”NFTO”,-15.5708,-175.633,160,13,”U”.
        I suppose that the comma in “Angaha, Niuafo’ou Island” is causing the problem, but when i delete it in notepad or excel, i get another error telling me that the syntax for integer is wrong on the first line in the first column.
        Didn’t you run into the same problem? I assumed that the comma inside the quoted string should be fine, but as it seems, it is not.

  6. OK, i managed to make it work. Thanks a lot for your help, using PostGIS with QGIS is a real fun and something i’m probably gonna be interested in a lot! Once more, thank you.

  7. raplin said:

    I have a problem to create curved lines within germany. Qgis shows only straight lines and no curved lines.
    With the example above is it ok.

    Can anyone help me!

    • underdark said:

      This approach won’t work for small regions like Germany. Maybe it would be possible using some custom transformations, but that is not trivial.

  8. Bruno said:

    It is my first time using a GIS software and I’ve just installed QGIS. I would like to know how can I create it? I don’t even know where should I paste the above code. Can you help me? Thanks a lot and congratulation for this post!

    • For this approach, you need PostGIS (the spatial extension of Postgres database). QGIS is only used in the last step to visualize the results. If you don’t know GIS and databases, this is going to be quite a learning curve but there are many tutorials available.

  9. Dwayne said:

    You know of any nice implementations of this? Examples or whatever.

    • There are multiple examples written in R on the web. Do you have more specific requirements?

      • Dwayne said:

        Written in R? Well, I’m trying to make this global connections map in RaphaelJS: http://raphaeljs.com/world/ – and currently looking for ways to make it better graphically. Thank you.