Monthly Archives: November 2014

Mapvember 2014

Mapvember: A map/tutorial a day for every day in November.

Some days had more than one map, some had tutorials, one just had a photo. Some were very easy, others would have take a couple of days of work.

Excellent experience, good learning experience and an opportunity to post previous projects that were a bit short of being great. A little time consuming at times though. I started making the maps around half way through October, so I had almost the first week ready when November began, but the days ticked by quickly. Happy to have done it. I encourage everyone to join in next year, or any other month.

Visitor Statistics:

Total views: 3047
Uniques: 2289
Pageviews: 4327

Top 10 Countries:

Country Visitors
United Kingdom 1147
United States 363
Germany 183
France 120
Canada 85
Italy 67
Spain 66
Australia 56
Switzerland 42
India 39

Mapvember Countries

Top 10 Cities:

City Visitors
Glasgow 340
London 212
Edinburgh 122
Rostock 45
Aberdeen 42
Stirling 35
San Jose 31
Vienna 28
Berlin 28
Zagreb 26

Mapvember Cities

Other Months:
August Visitors: 303
September Visitors: 641
October Visitors: 523
November Visitors: 3047

AdSense:

Total AdSense earnings: £1.87

Which actually covers my servers costs. So break even!

Most popular posts:

Referrals:

Site Visitors
Reddit.com 747
OSGeo.org 381
Twitter 122
GIS.StackExchange.com 71
Facebook 70
Flickr 15

Top SubReddits:

SubReddit Visitors
/r/glasgow 200
/r/scotland 120
/r/london/ 46
/r/gis 19
/r/QGIS/ 4

Thanks for visiting.

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