Earth Friends, a social network visualization

Last week I blogged about a new project I was working on. For the past few days I went through the code again and decided to clean it up a bit for the release. It is pretty much completed now, therefor I have made it available on Facebook.

Summary:
“Earth Friends is a free Facebook application to visualize your social network on Google Earth. Locate your friends by using the Google Earth Webplugin or download your personal datafile for use with the Desktop version of Google Earth.”

Rewriting…

While my code from last week was running fine, there was a lot of room for improvement. Basically three major parts changed:

1. The database structure
2. Using a template engine
3. File compression

MySQL

The first thing I worked on was the database itself. The local database is primarily used for converting a location (defined by a city, state and country name) to a latitude and longitude.
Each time we need a location, we query the database, simple as that.

So let’s look at the following example:
First assume you have about 100 friends on Facebook. Probably most of them share their current location, but some of them might not want to share their location with Facebook apps or simply did not filled out the location at all.
If 75 friends filled this field with their current location, the old code would query the database 76 times (75 times for your friends + 1 time for your location).

While this looks like a lot of queries, the time for a query to complete also depends on the design of the database. As I was using MaxMind cities database, I initially imported all the data into one MySQL table. Just because it was convient to work with. However, the dataset has about 2.699.356 entries (cities).
Doing a search 75 times within this table was not going to be fast…

Besides the users’ patience, I am also limited by PHP’s execution time limit. I think that scripts are often allowed to run for 30 or 60 seconds, although I can’t stand a website that is taking longer than 10 seconds to show up.

So what to do next? Can we find a way to split the database into multiple small tables? Just limiting the number of entries to a fixed number (e.g. 100.000) wasn’t going to work as I would need some kind of lookup system in order to figure out where I could find my city.

Improvement one
The easiest solution I found for it (not saying its perfect) was by splitting the database by country. In my case I have 231 countries in the database, thus 231 tables are created.

Taking a look at the top 3 countries in our database reveals the following:

• Russia – 176.934 cities
• USA – 141.989 cities
• China – 117.508 cities

The average number per country is around the 20.000 cities. While the top 3 shows a significant higher quantity than the average, look ups are performed much faster than before.

Improvement two
We could’ve stopped here with improving the database part as the results were reasonable. However, it is a small effort to tweak it a bit more.

In our example, we query 76 locations from the database. But wait… what did we query? The location of friends! And what do a lot of friends have in common? Right, they share the same location. So by first creating a list of cities we need to look up, we can reduce the number of queries required to collect our data.

Smarty Template Engine

While PHP provides a nice environment for rapid prototyping, it can also become a mess easily. Using print or echo statements is fine for testing purposes but it is better to keep code separated from HTML.

This is why you need to use template engines. When using a template engine, you first collect data from your database and then parse your variables and arrays to the template engine. In your templates you specify where this data need to be placed. (Summary: PHP is used for data collection and prepared into arrays, Template engines use these arrays and are used for the actual design of the website).
Fortunately, there are free open source template engines such as Smarty.

Take a look at the following code:

1
2
3
4
5
6
7
8
9
10

{* Add placemark for friends *}
{foreach $friendlist as $friend}
  <Placemark>
    <name>{$friend.name}</name>
    <styleUrl>#{$friend.uid}</styleUrl>
    <Point>	
      <coordinates>{$friend.lon},{$friend.lat},0</coordinates>
    </Point>
  <Placemark>
{/foreach}

This is an actual code snippet from the template responsible for generating the KML. This particular section is used to display your friends icon on Google Earth.

Within the template I can specify a block (in the example its a Placemark) that will be looped. After setting up Smarty and collecting data from the database, I pass the result ($friendlist) to Smarty. In the template engine, it will now perform loop through this foreach loop and place the variables in the correct location.

A few more benefits of using a template engine is that you can store the results in a cache. By caching the results we can skip ‘expensive’ MySQL queries if we know the page hasn’t changed. By specifying the cache lifetime (for example 30 minutes), we can make sure that Smarty will regenerate the page if the cached page is older than 30 minutes.

ZIP

In the previous version of Earth Friends, I embedded the KML file into the header of the website (in Javascript). While this method works fine for small data sets, it has a large impact on page loading and render times when data sets are growing.

KML files are plain text files formatted in XML. Besides KML files, Google Earth also accept compressed KML files which have the extension KMZ. KMZ files are basically KML files compressed with ZIP.
Tests show noticeable differences in loading times when using KMZ. For example, my test data set in KML was about 693 KB. After compressing this file using zip (max. compression), the size was reduced to 92 KB. That’s around 13% of the original file size! As a result, loading times were reduced significantly.

Where can I find the application?

Ready to try out this application on facebook? Just click the following link to open up Earth Friends: Earth Friends application on Facebook.

After authorizing Earth Friends to access your profile data, it will reload the page and launch the Google Earth browser plugin. If the plugin is not installed, please follow the instructions that are displayed instead. The plugin should work in Windows and Mac OS X.

Important: Make sure you set your own location (with the correct privacy settings) or the curves will not appear!

More information can be found on Earth Friends Community page on Facebook.

How to use this?

To help you get started with Earth Friends, I have created two screencasts which demonstrate you how to add Earth Friends to your Facebook account and use the application.

Tutorial 1: How to use Earth Friends (View in 720p HD)

Tutorial 2: How to download the KMZ file for Google Earth Desktop (View in 720p HD)

Where are you?

Last weekend I’ve been working on a new project. Since I already had some experience  generating KML files for use with Google Earth (wikileaks projects!) I started to think of something else that I could visualize… perhaps see where my friends are?

Because I’m using Facebook to connect with my friends, I decided to dig into the documentation of the facebook APIs. Apparently there are multiple ways to get hold of your and your friends information. The most commonly used APIs are: Graph API / FQL . The first one lets you retrieve information about a friend or page by loading a specific URL, the second one lets you actually send a SQL query to retrieve the  information.

Since I want to make this a hassle free experience, I decided to make a Facebook application which would use the Google Earth Web plugin. This way, users only need to download the plugin, but everything works just in the browser.

How does it work?

Basically a Facebook app is just a website running on some server. In my case, I’m hosting my application on the same domain as my blog. Since the application is embedded into the Facebook website, normal users won’t notice. The app itself can be written in all kinds of languages but for the sake of simplicity I used PHP.

First we need to connect to Facebook using an API/SDK. This allows us to authenticate and securely connect to the Facebook servers. After enstablishing a connection, we use FQL to query: Our friendslist and the location of our friends. Unfortunately the friendslist only contains the name of the location and not the geospatial coordinates.

Therefor I had to create a lookup database that would translate a City/State/Country name into a geospatial coordinate (latitude and longitude). This was done by downloading a free database from Maxmind.com.

Now we should have all data available to create our KML file on the fly. For now I embed the KML result into the javascript header which seems to work fine for ~200 friends. I still need to do some benchmarks to see how well this scales. A demonstration video of the result can be found below:

Video

Can I try?

Since this project is still WIP, it is not available yet in the Facebook Application Directory. I’m planning to release this application for free soon.