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:
Connections crossing the date line are currently more problematic. Lines would have to be split, otherwise this is what you’ll get:
There seems to be an alignment problem on the first map – several flights terminate in the Atlantic off Africa. The second map looks OK.
The map is fine. You just can’t see the tiny Canary Islands (http://en.wikipedia.org/wiki/Canary_Islands)
Oh, my bad.
For instance, the date line issue was solved in JEQL (which is based on JTS):
(sorry for the second post in Italian!)
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.
Can you mention a little bit more about how you created this in QGIS. I have explored the data sets from openflights in ArcGIS and was able to come up with this…
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!
Very informative post and
Cheers! Mapping flight routes presents an interesting topic for research.
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,
city varchar (100),
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.
Functions in public schema are available from all other schemata as far as I know.
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).
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.
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.
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!
This approach won’t work for small regions like Germany. Maybe it would be possible using some custom transformations, but that is not trivial.
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.
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?
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.