Overlay | Tales From The Cloud

Update Sep.21.2011: I took the code in the two parts and made a github project off of it called Gborders. The code is simpler and there are more options to generate the borders overlay based on geographic regions. Happy forks!

In Part 1 we imported world political borders into a database table. In this second part we’ll use the table and generate a script that will be used to add the borders overlay to Google Maps. We’ll use the cool Ruby and some fancy GIS words along the way.

We left-off with a database table containing all borders. The goal today is to produce a Javascript file that will be used for overlaying polygons representing countries, over a map using the Google Maps API.

Let’s examine the table data first (I’m using mysql through the command line):

mysql> desc world_boundaries;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| ID        | int(11)      | NO   | PRI | NULL    |       |
| FIPS      | varchar(255) | YES  |     | NULL    |       |
| ISO2      | varchar(255) | YES  |     | NULL    |       |
| ISO3      | varchar(255) | YES  |     | NULL    |       |
| UN        | int(11)      | YES  |     | NULL    |       |
| NAME      | varchar(255) | YES  |     | NULL    |       |
| AREA      | int(11)      | YES  |     | NULL    |       |
| POP2005   | bigint(20)   | YES  |     | NULL    |       |
| REGION    | int(11)      | YES  |     | NULL    |       |
| SUBREGION | int(11)      | YES  |     | NULL    |       |
| LON       | double       | YES  |     | NULL    |       |
| LAT       | double       | YES  |     | NULL    |       |
| ogc_geom  | geometry     | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

A lot of information here but we’ll need just these fields: name (country name), iso2 (two letter country codes) the_geom, iso2 (border geometry) and region (grouping countries by regions). To check the data-set let’s examine a small country. I’ll pick the tiny State of Vatican for its size:

mysql> select iso2, AsText(ogc_geom), region from world_boundaries where iso2='VA';
+------+---------------------------------------------------------------------------------------------------------------------------------------------------+--------+
| iso2 | AsText(ogc_geom)                                                                                                                                  | region |
+------+---------------------------------------------------------------------------------------------------------------------------------------------------+--------+
| VA   | MULTIPOLYGON(((12.445090330889 41.903117521785,12.451653339581 41.907989033391,12.456660170954 41.901426024699,12.445090330889 41.903117521785))) |    150 |
+------+---------------------------------------------------------------------------------------------------------------------------------------------------+--------+
1 row in set (0.00 sec)

The only surprise here is that the country border is described as a MULTIPLOYGON spatial type which describe a collection of polygons that don’t interset. This is in order to accommodate countries that have islands under ownership.

Let’s see how these points look on the map. We’ll use the excellent polygon encoder utility written by Mark McClure. Copy Vatican’s point set in the “Input Text” input box (choose lng/lat option):

12.445090330889, 41.903117521785
12.451653339581, 41.907989033391
12.456660170954, 41.901426024699
12.445090330889, 41.903117521785

Here’s the result:
va_polygon
Not very accurate but we’ll have to live with it, it’s a free data set after all…
Continue reading “Spincloud Labs: Political boundaries overlay in Google maps (Part 2)”

Update Sep.21.2011: I took the code in this post and part deux and made a github project off of it called Gborders. The code is simpler and there are more options to generate the borders overlay based on geographic regions. Happy forks!

One thing I needed when designing the Meteoalarm mashup for Spincloud were the political boundaries for all European countries. With them at hand, I would use the polygon overlay from the Coogle API and fill the country polygons with the respective weather warning colors.

This first part is a tutorial on how to import world political borders into a MySQL database table.
The second part in these series will use this table to create a script that will add the country borders overlay to Google Maps.

So after learning that Google Maps API doesn’t give programatic access to the political boundaries I moved on to doing it myself. I read about the encoded polylines and figured that I only missed the country boundaries as encoded polygons so I started searching for readily-available resources. Sure enough there were nowhere to be found.

The next best thing was to find the country boundaries in some GIS format and generate the polyligons myself. The information seems to be sparse but after a bit of research I found was the CIA World Databank but the format is incomplete, it only defines the polygons with no country names or any other administrative hook that I would be able to use. I then spent hours trying to find a better resource until I remembered that Mapping Hacks (an excellent book by the way) has data set for the examples in the book. I had the first breakthrough, the website makes available updated world borders files here, they are mirrored from thematicmapping.org.

We’ll use a utility called shp2mysql to export the boundaries to SQL. I’m on a MacBook and no shp2mysql binaries for my platform were available in the original package so we’ll have to compile it first then run it to be able to export the shapefile to SQL.

Below are detailed all steps needed to to produce the borders SQL file on Leopard 10.5.x (note that you have to have the Developer Tools installed). In short, copy and run the following script on your mac in a file called process_boundaries.sh. The script creates a file called wb_dump.sql which is ready to be imported into database:
Continue reading “Spincloud Labs: Political boundaries overlay in Google maps (Part 1)”