Wednesday, November 10, 2010

Search your geo data using spatial queries from Fusion Tables!

I’m Kathryn Hurley, a Developer Programs Engineer for Fusion Tables, and I have to say, I really dig Fusion Tables. The power it gives you to visualize your data is amazing! Plus, the team is working hard to add new features that give even more power to you, the developer.

It is my pleasure to announce that Fusion Tables now offers spatial queries! Using spatial queries, developers can (1) find all features that are located within (or overlap with) a given distance from a location (2) find all features that are located within (or overlap with) a given bounding box, and (3) find a given number of features closest to a location.

To demonstrate some of the new syntax, let’s go through a potential use case of spatial queries: creating a store locator map. For this demo, I set up a table in Fusion Tables, 297050, containing the locations of a fictitious pizza restaurant chain in the San Francisco bay area. The table contains the name, location, and whether or not that location delivers.

Now let’s say we want to display all the restaurants that fall within a bounding box. To do so, use the syntax:

SELECT * FROM 297050 WHERE ST_INTERSECTS(Address, RECTANGLE(LATLNG(37.2, -122.3), LATLNG(37.6, -121.9)))

The map to the left shows this query in action using the FusionTablesLayer. The example shows all rows in the pizza table that fall within the bounding box specified with a lower-left coordinate of 37.2, -122.3 and a upper-right coordinate of 37.6, -121.9.

This is the code for the FusionTablesLayer:

map = new google.maps.Map(document.getElementById('map_canvas'), {
   center: new google.maps.LatLng(37.4, -122.1),
   zoom: 10,
   mapTypeId: google.maps.MapTypeId.ROADMAP
});
tableid = 297050;
layer = new google.maps.FusionTablesLayer(tableid, {
   query: "SELECT Address FROM " + tableid + " WHERE ST_INTERSECTS(Address, RECTANGLE(LATLNG(37.3, -122.3), LATLNG(37.6, -121.9)))",
   map: map   
});

We can also find the 10 features closest to a particular coordinate, such as the latitude, longitude coordinate of your house or a major city center. To find the 10 nearest features to Mountain View, CA at coordinate 37.4,-122.1., we use the syntax:

SELECT * FROM 297050 ORDER BY ST_DISTANCE(Address, LATLNG(37.4, -122.1)) LIMIT 10

These 2 examples demonstrate how to create a store locator-like map, but the possibilities are endless! You could create an application that finds the most popular bike trail routes in your area or enable crowd sourcing to identify roads that might need more street lamps.

To learn more about the full spatial query offering in Fusion Tables, please see the Fusion Tables documentation. You can also join the Fusion Tables User Group to receive announcements about new features or post questions you might have about Fusion Tables. Or follow us on Twitter: @GoogleFT. And, most importantly, have fun creating awesome apps!