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:
Connections crossing the date line are currently more problematic. Lines would have to be split, otherwise this is what you’ll get: