Saturday 28 August 2010

Mapping with Google Fusion Tables

My workplace, United Nations Association of Norway (UNA Norway), has 700 member schools across the country. We wanted to include a map on our webpage to show all our members. The map should be automatically updated when new schools are added or removed. From previous experience, I knew that having 700 markers on a single map is problematic. Google Fusion Tables overcomes this limitation in a clever way.

Especially for nerds: We use SugarCRM to keep track of our members. SugarCRM is highly customisable and it's easy to add extra fields for latitude and longitude positions. I wrote a PHP script which synchronises a SugarCRM database (MySQL) with Google Fusion Tables. The script first checks if the last modifed date or the number of rows are equal. If not, the script loops through all rows (members) in the SugarCRM database. If the member exist in Fusion Tables, it's updated if modified date is different. If the member don't exist in Fusion Tables, it's inserted. Fusion Tables members that don't exist in SugarCRM database are deleted. This script helped me to get started.

When the data are available in Google Fusion Tables, it's easy to create an embeddable map:


Full screen view

Google Fusion Tables avoids the browser limitation of showing maximum 200 markers by generating transparent PNG tiles instead of indvidual markers. This url shows you a PNG image of our member schools around Oslo: