A little bit about Fusion Tables

Written on 22 May 2011, 10:49pm

Tagged with: , , ,

Google Fusion Tables is a modern data management and publishing web application that makes it easy to host, manage, collaborate on, visualize, and publish data tables online.

So as far as an user is concerned, a Fusion Table is a virtual table stored in the cloud, on which he can perform the regular CRUD operations. Here comes the nice part: the language on which you can perform these operations is SQL-like. The API is designed so that you can use URLs like
http://www.google.com/fusiontables/api/query?sql=select+*+from+1234567
The output of this request is a CSV text, with the contents of the selected table.

Let’s assume that you have a Fusion table where you store some physical addresses for some items. Below is a very simple PHP script which reads the contents of the fusion table, parses it, extracts the address, then performs a geocoding request using the geocoding API, parses the JSON object and outputs the latitude and longitude of the given address.

<?php
//run sql query to get data from fusion table
$url = 'http://www.google.com/fusiontables/api/query?sql=select+*+from+822057';
$lines = file($url);

$i = 0;
foreach ($lines as $line_num => $line) 
{
	//parse csv line
	$csv = str_getcsv($line);

	//extract the address
	$address	= $csv[7];
	
	if($address)
	{
		//we need to geocode the address to get the latitude and longitude
		$url_address = urlencode ($address);
		$geocode_url = "http://maps.google.com/maps/api/geocode/json?address=$url_address&sensor=false";
		$geocode = file_get_contents($geocode_url);
		$output = json_decode($geocode);

		$latitude = $output->results[0]->geometry->location->lat;
		$longitude = $output->results[0]->geometry->location->lng;

		//sleep for a second
		sleep(1);

		//do something nice with all this data

	}
}

?>

jQuery quickies

Written on 8 May 2011, 11:13pm

Tagged with: ,

1. Get all the text input fields or textareas having IDs starting with a pattern

$('input, textarea').filter('[id^='+fieldID+']').each(function() 
{
	var field_value = this.value;
	var field_id = this.id;
	//do things here...
}

2. Add options to a select (in the example below – add the Google supported languages)

//Get the Languages array
	var languages = google.language.Languages;

	//populate the languages select
	$.each(languages, function(key, value)
		{   
		$('#my_select').
		append($("<option></option>").
		attr("value",value).
		text(key)); 
		}
	);

(more…)

Google Maps: radius search in Oracle

Written on 21 April 2011, 12:11pm

Tagged with: , ,

Recently I had to implement a ColdFusion + Oracle application using Google Maps. One of the requirements was search by distance (or radius search) for some items, already geocoded.
The items are stored in T_ITEM, with the columns:
ITEM_ID
COORDX --latitude
COORDY --longitude

The latitude and longitude are already calculated, and stored in database as VARCHAR.
The task was to create some sort of function to accept:
-latitude
-longitude
-radius
and to return the items from T_ITEM located within radius kilometers from the given coordinates.

In implementing this task, I started with (more…)