Spincloud Labs: Political boundaries overlay in Google maps (Part 1)

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:

mkdir borders
cd borders
wget http://mappinghacks.com/data/TM_WORLD_BORDERS-0.2.zip
mkdir boundaries_shp
unzip TM_WORLD_BORDERS-0.2.zip -d boundaries_shp
wget http://kartoweb.itc.nl/RIMapper/code/shp2mysql_0_4.zip
unzip shp2mysql_0_4.zip -d shp2mysql_04
wget http://dl.maptools.org/dl/shapelib/shapelib_1_2_10.zip
unzip shapelib_1_2_10.zip
cd shapelib-1.2.10
cd ..
rm ./shp2mysql_04/src/*.o
sed 's/^OBJS.*$/OBJS = \.\.\/\.\.\/shapelib-1\.2\.10\/shpopen\.o \.\.\/\.\.\/shapelib-1\.2\.10\/dbfopen\.o/' shp2mysql_04/src/Makefile > shp2mysql_04/src/Makefile2
mv shp2mysql_04/src/Makefile2 shp2mysql_04/src/Makefile
cd shp2mysql_04/src
cd ../../
cp shp2mysql_04/src/shp2mysql .
./shp2mysql -d boundaries_shp/TM_WORLD_BORDERS-0.2.shp world_boundaries test_db > wb_dump.sql
rm *.zip
rm -rf shp2mysql_04
rm -rf shapelib-1.2.10
rm -rf boundaries_shp

Make the above script executable then run it:

chmod +x process_boundaries.sh

If you’re interested in the details about this script then read on otherwise happily skip it. The final SQL file is linked at the end of this post.

We’ll start by bringing the shape files:

mkdir borders
cd borders
wget http://mappinghacks.com/data/TM_WORLD_BORDERS-0.2.zip
mkdir boundaries_shp
unzip TM_WORLD_BORDERS-0.2.zip -d boundaries_shp

The files are in ESRI’s shp format which wasn’t exactly what I needed. I had to export them in a more usable format, SQL in my case. For this I used the shp2mysql utility for which I didn’t find the MacOS executable so we’ll compile it from source. Since it has a dependency to shapelib we have to download and compile it along:

mkdir shp2mysql
wget http://kartoweb.itc.nl/RIMapper/code/shp2mysql_0_4.zip
unzip shp2mysql_0_4.zip -d shp2mysql
wget http://dl.maptools.org/dl/shapelib/shapelib_1_2_10.zip
unzip shapelib_1_2_10.zip
cd shapelib-1.2.10
cd ..

Now you should have both shapelib and shp2mysql downloaded with shapelib succesfully compiled.  Now we’ll compile shp2mysql: we have to delete all .o files (they have been compiled for a different platform), fix the Makefile then issue the make command

rm shp2mysql/src/*.o
sed 's/^OBJS.*$/OBJS = ../../shapelib-1.2.10/shpopen.o ../../shapelib-1.2.10/dbfopen.o/' shp2mysql/src/Makefile > shp2mysql/src/Makefile2
mv shp2mysql/src/Makefile2 shp2mysql/src/Makefile
cd shp2mysql/src
cp shp2mysql/src/shp2mysql .

which should execute successfully.
We’re ready to export the world boundaries into SQL format. Switch to the boundaries folder then issue the export command as follows:

cd ..
./shp2mysql -d boundaries_shp/TM_WORLD_BORDERS-0.2.shp world_boundaries test_db > wb_dump.sql

this will create a SQL file called wb_dump.sql ready to be loaded into your database.

Running the script will generate a file called wb_dump.sql which is ready to be imported on your spatially enabled database:

All we have to do now is to import the sql file into the database. Note that the database has to be spatially enabled (MySQL is by default, I’m using 5.0). Issue the following command:

mysql -uroot -p my_database < wb_dump.sql

Now we should have country borders for the whole world imported in the database.
For convenience, I have made the world boundaries SQL file available for download (4.3 MB).

In the second part of this how-to, we’ll use this table to create the country borders overlay for Google Maps.

  • LB

    I get an “unrecoverable zip file read error” from Winzip when I try to open the SQL dump zipfile. I tried a few downloads … do I need a different unzip tool, or can you re-post it?

    Thanks in advance

  • http://newsplore.com Florin

    I zipped the file using the archiving utility on my MacBook. The file unzips correctly on both Mac and Linux. It should be the same with WinZip…

  • Miguel

    By the way, a new version of world_borders has been added: TM_WORLD_BORDERS-0.3.zip

    It includes regions, which I am interested in. I’ll look into it and post it here if it requieres of any change.

  • Andy

    This is a well written article, Thanks!
    However, I have had problems and wondered if you were aware of any issues with some of the polygons? e.g. After importing the sql file, and running this query:
    SELECT ID, NAME, CONTAINS( ogc_geom, GeomFromText('POINT(60 15)') ) AS C
    FROM `world_boundaries` WHERE CONTAINS( ogc_geom, GeomFromText('POINT(60 15)') ) = 1

    It only returns United States Minor Outlying Islands. 60,15 is well within Sweden.
    Any suggestions would be helpful!

  • Andy

    Apologies, I had gotten the lat and long the wrong way round!

  • http://www.brianherbert.com Brian Herbert

    This is exactly what I was looking for. Thanks!

  • cblp

    tnx dude! It is very helpfull in my current project!!!

  • vishay

    thanks so much for this great tutorial!

    i’m trying to run Andy’s query and I’m getting some strange behavior:

    SELECT ID, NAME, CONTAINS( ogc_geom, GeomFromText(‘POINT(15 60)’) ) AS C
    FROM `world_boundaries` WHERE CONTAINS( ogc_geom, GeomFromText(‘POINT(15, 60)’) ) = 1;

    returns Norway, Russia, Sweden, and the US. i’m very confused by this. can you explain this behavior? (there are many similar queries for which i get multiple results)


  • Pingback: Закрашиваем страны – google maps – WebGuruz()

  • Pingback: MySQL GIS – Part 1 | MySQL Fanboy()

  • Michal

    Great article and great work… However, I am getting “MySQL server has gone away” error when I try to import your sql dump file. I tried increasing the timeout value but no luck there. My server seems to choke on large countries (Canada and Russia) with lots of points – any tips???

  • http://www.nullepart.com Sylvain

    Thx for it, that was my dream. Import SQL failed into wrong characters in the GEOMETRY format field. Could I replace it with FLOAT  format fileld.
    Merci beaucoup, S

  • http://www.nullepart.com Sylvain

    Merci beaucoup,
    when inserting the first sql record, the field “ogc_geom” (Geometry type)
     is filled with :
    “GeometryFromText(‘MULTIPOLYGON(((-61.686667999999997 17.024441000000138 ,-61.738059999999962
    But how could sql deal with GeometryFromText and MULTIPOLYGON ?
    Where these methods come from ?
    Thanx a lot, S.

  • Anonymous

    Did you try the github project https://github.com/florind/Gborders ?

  • http://www.nullepart.com Sylvain

    I read it, but since I don’t know RUBY at all, I guess it’s aimed to write JavaScript from SQL db. So I can do the same with PHP.
    I m not sur to have understand : where GeometryFromText and MULTIPOLYGON come from ?
    http://facstaff.unca.edu/mcmcclur/GoogleMaps/EncodePolyline/encodeForm.html ?
    Or from a Ruby class ?
    Other question, the points encodong from “-1.789000, -8.9850000 >TO> “{cohD|qilBrfL|an@wuH`tAaz@”” is made when feeding the database or when extracting datas from db.
    Still not possible for me to import sql because of geometry field.
    I m sad, looks like the right aswer to my problem. almost touching the end…
    Merci, Sylvain.

  • Daniele Pignedoli

    Thanks very much!
    I was looking exactly for the world countries boundaries!

  • Marc-André Gauthier

    Thanks so much !