City database

Posted by | No Tags | Software developement | No Comments on City database

For a project in progress we would like to display the users‘ activity on a world map, as a kind of heat map. This request, however simple it may seem, poses several challenges.

City database

First of all, we had to find a database that contains each city of the world, at least in the form of country +city, so that we would be able to display them on a map on the basis of the countries. To start with, I would have been happy with the complete list of all the towns of Hungary.

I searched among the entries of Wikipedia, but I was surprised to see that no such list exists, only in a strongly categorized and divided form (on the basis of population). I could have gathered the data from these entries, but it would have required a significant amount of time, so I decided to take a different direction.

Following some Google search, I came across the company MaxMind and their free city database: http://www.maxmind.com/en/worldcities

This seemed to be a treasure chest for us, as it not only contains each and every settlement (not only the towns) of the world, but their GPS coordinates as well! It may not be complete, but with its 2.5 million entries it is more than what I expected.

Checking

A database of this size is difficult to check appropriately. I had the idea to open it in Excel and create some statistics on it. Unfortunately Excel can only manage tables of up to about 1 million lines, so this idea soon failed.

Never mind, being a programmer, my inventory is somewhat wider than the Excel table; so I wrote a program that draw the location of the cities on a static world map (screenshot from Google Maps) on the basis of the GPS coordinates.

My starting map:

worldmap

Drawing

In possession of the GPS coordinates it seemed fairly easy to mark the towns on the map: the longitudinal degrees are between -180° and +180°; the 0° is Greenwich. On the map I marked the place of Greenwich, and I adjusted the X coordinates to this point.

The problems started at the latitudinal coordinates. It is easiest to use a picture to demonstrate what the result of the Y coordinates calculated with direct proportions was:

worldmap-cities-bad1

Somehow everything became distorted; as if the globe was compressed. The mistake is the most prominent at the Scandinavian Peninsula. Oh, yes, GLOBE. During my school studies in one of the Geography lessons I was told about the different projections. As, obviously, it is impossible to draw the surface of a sphere onto a rectangle without distortions.

Projection

I quickly found out what projection method the Google Maps uses: Mercator projection. By drawing the lines of some of the latitudinal degrees it can immediately be seen that in the drawn map the vertical distances gradually increase with the distance of these lines from the Equator:

mercator-projection

Knowing its name, it is not very difficult to search for the mathematics of the projection method:  http://stackoverflow.com/questions/14329691/covert-latitude-longitude-point-to-a-pixels-x-y-on-mercator-projection

Drawing again

After entering and adjusting the formula to the measurements of my picture, I managed to draw the following map:

worldmap-cities

Here every point seems to be in its proper place and the GPS coordinates seem correct.

Heat map

We are going to create the heat map on the basis of the GPS coordinates too. As we have managed to calculate the two-dimensional X, Y coordinates of every GPS coordinate for drawing them on the map, this formula can be used for creating the heat map. The only difference is that a much lower resolution is sufficient for a heat map (an approximately 50×30 grid).


No Comments

Leave a comment