The Tutorial blog series helps SQLstream developers build streaming SQL applications. This blog is the second in the Geospatial Visualization tutorial.  The first blog in the series set out the streaming use case for connecting SQLstream to a Google Earth visualization, and described the initial steps required to capture the data and create a display list using Rails.  In the second part of this tutorial, we’re going to discuss the meat of the application –  how to render the display list.

Rendering the Display List

To keep Google Earth continuously updated with the data flowing from SQLstream we’ll have to serve two different KML files: one will contain a KML Placemark for each quake, and the other gives the URL of the quakes feed and tells GE to continuously refresh it (in KML, this is the NetworkLink). We’re going to be serving compressed KML to cut down on the transmission time, so we’ll need the rubyzip gem we installed earlier included in our web app. Stop the server, go to $QUAKE/quakekml, and edit the file “Gemfile” to add this line to the end of the file:

gem 'rubyzip'

then issue these commands:

linux> bundle
linux> bundle package
linux> rails generate controller home index
linux> rails generate controller quakes start feed

The first two commands bundle up the application, including the new gem. The third command creates a Rails controller for our home page, while the last command creates a controller with two actions, one for each of our services. If you restart the server now, you can view these services at

http://localhost:3000/home/index, http://localhost:3000/quakes/start and 

Edit $QUAKE/quakekml/config/routes.rb and add this line after the “get” commands to make home/index the home page for the web app:

root :to => "home#index"

You’ll also have to remove the file $QUAKE/quakekml/public/index.html. Restart the server and visit http://localhost:3000, you should now see the home#index default page rather than Rails’ startup page. Note that it shows you the name of the template file for this page, relative to the $QUAKE/quakekml directory. Edit $QUAKE/quakekml/app/views/home/index.html.erb to create the content for your landing page, at some point in the body add this line to create a link to the start service:

<%= link_to 'Earthquake events (open in Google Earth)',
      :controller => 'quakes', :action => 'start' %>

You should also add this link to the scaffolding for quake events:

<%= link_to 'Edit quake events', quake_events_path %>

You shouldn’t have to restart the server, just refresh http://localhost:3000 to see the changes.

The Ruby code that implements our services is in $QUAKE/quakekml/app/controllers, starter code has already been written by the “rails generate” commands we’ve been issuing. The ancestor of all of our controller classes is in application_controller.rb, we’ll add a method for setting up parameters available in any request (in this case, only one, the path to the server) and two methods for sending text so that the browser recognizes it as KML or KMZ:

1 require 'zip/zip'
3 class ApplicationController < ActionController::Base
4 protect_from_forgery
6 # Set up the common params to be computed
7 # after the request is received (can't go
8 # initialize). These are available in all views.
9 #
10 def setup_common
11 @path = + ':' + request.port.to_s
12 end
14 # Output the kmz, given the kml
15 #
16 def send_kmz(kml)
17 t ="zipout-#{request.remote_ip}")
18 do |zos|
19 zos.put_next_entry("sqlstream.kml")
20 zos.print kml
21 end
23 send_file t.path,
24 :type => "application/",
25 :filename => "sqlstream.kmz"
27 t.close
28 end
30 # Output the kml directly
31 #
32 def send_kml(kml)
33 render :text => kml,
34 :layout => false,
35 :content_type => "application/"
36 end
37 end

Next we edit quakes_controller.rb to write the methods that respond to the quakes/start and quakes/feed requests. Each method uses Rails’ template support to render a KML template, with an option set to prevent it from being laid out like an HTML page. The start method sets an instance variable, @refresh, to the number of seconds we want to wait before refreshes. The feed method uses Rails’ database support to store all of the quake event rows in @quakes. These instance variables are expanded in the templates $QUAKES/quakekml/app/controllers/quakes_controller.rb

 1 class QuakesController < ApplicationController
 2+++++def start
 4+++++++++@refresh = 60
 5+++++++++kml = render_to_string :template => 'quakes/start.kml',
 6+++++++++++++:layout => false
 7+++++++++send_kmz kml
10+++++def feed
12+++++++++@quakes = QuakeEvent.all
13+++++++++kml = render_to_string :template => 'quakes/feed.kml',
14+++++++++++++:layout => false
15+++++++++send_kmz kml

The path for the template files is relative to $QUAKES/quakekml/app/views, the quakes directory there should already exist and contain the default templates generated by Rails. We’ll create two new template files, starting with the one for quakes/start:


 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <kml
 3 <Document>
 4   <name>Earthquake Monitor</name>
 5   <open>1</open>
 6   <visibility>1</visibility>
 7   <LookAt>
 8     <longitude>-122.418955</longitude>
 9     <latitude>37.775410</latitude>
10     <altitude>359000.0</altitude>
11     <range>37000.0</range>
12     <altitudeMode>relativeToGround</altitudeMode>
13   </LookAt>
14   <NetworkLink>
15     <name>Quakes</name>
16     <open>0</open>
17     <visibility>1</visibility>
18     <refreshVisibility>0</refreshVisibility>
19     <flyToView>0</flyToView>
20     <Link>
21       <href><%= url_for(:controller => 'quakes', 
           :action => 'feed', : only_path => false) %></href>
22       <refreshMode>onInterval</refreshMode>
23       <refreshInterval><%= @refresh %></refreshInterval>
24       <viewRefreshMode>onStop</viewRefreshMode>
25       <viewRefreshTime>1.0</viewRefreshTime>
26     </Link>
27    </NetworkLink>
28   </Document>
29   </kml>

The start KML begins with a LookAt element specifying the starting view (directly above SQLstream HQ!). The NetworkLink element includes two substitutions handled by Rails: at line 21 we insert the URL for the quakes/feed service, and at line 23 we insert the refresh rate.


1 <?xml version="1.0" encoding="UTF-8"?>
2 <kml xmlns=""
3 <Document>
4   <name>Pins</name>
5   <open>1</open>
6   <visibility>1</visibility>
7   <Style id="pin">
8     <IconStyle id="pin">
9       <scale>1.0</scale>
10      <Icon>
11          <href>http://<%= @path %>/images/pin.png</href>
12      </Icon>
13     </IconStyle>
14     <LabelStyle>
15         <color>ff0000dd</color>
16<        <scale>1.2</scale>
17     </LabelStyle>
18   </Style>
19   <Folder>
20     <name>Earthquakes</name>
21     <open>0</open>
22     <visibility>1</visibility>
23     <description></description>
24     <%= render :partial => "quake_event", :collection => @quakes %>
25   </Folder>
26 </Document>
27 </kml>

At line 11 we use the @path variable to specify the location of an image we want to appear on the globe at each quake location. You should place an image in $QUAKES/quakekml/public/images/pin.png. The other substitution, at line 24, causes a partial template to be rendered for each record in the @quakes collection. According to Rails' naming conventions, the partial must be in this controller's view directory with the name _quake_event.html.erb:


 1 <Placemark>
 2   <name><%= quake_event.mag %></name>
 3   <open>1</open>
 4   <visibility>1</visibility>
 5   <description><![CDATA[<%= render :partial => 'quake_description',
         :locals => {:quake_event => quake_event} %>]]></description>
 6   <styleUrl>pin</styleUrl>
 7   <Point id="quake">
 8     <extrude>false</extrude>
 9     <coordinates><%= quake_event.lon %>,
           <%= %>,0</coordinates>
10   </Point>
11 </Placemark>

The quake event records are inserted at lines 2 and 9. At line 5 we reference another partial that renders the HTML for the popup that appears when you click on the pin in Google Earth:


1 <table style="text-align: center; width: 300px;" border="0" 
      cellpadding="2" cellspacing="2">
2  <tbody>
3   <tr align="left">
4     <td>on <%= quake_event.when.strftime("%Y/%m/%d") %>
          at <%= quake_event.when.strftime("%X %Z") %></td>
5   </tr>
6    <tr align="left">
7      <td>at lat: <%= number_with_precision(, :precision => 6) %>
          lon: <%= number_with_precision(quake_event.lon, :precision => 6) %></td>
8    </tr>
9   </tbody>
10 </table>

Note that this separation into multiple templates lets us express code in Ruby files, KML in KML files, and HTML in HTML files. Details about how the data is presented, such as how we format a timestamp, are taken out of the code and expressed in markup language.

You should now have a working KML renderer. Go to your app's home page and follow the 'Edit quake events' link to add one or more fake quakes. Use SQLstream's lat/lon from start.kml (above) for at least one of them. Now follow the Google Earth link from your app's home page (you may have to instruct your browser to open KML/KMZ files in Google Earth, if you've never done this before). Google Earth should open and zoom to SQLstream HQ, and there should be a pin indicating an earthquake there.

You can now refine the visualization of a quake event by updating the templates and refreshing the display (you can refresh the quakes/feed stream by right-clicking on 'Quakes' in the Places tree on the left and selecting 'Refresh'). We have a tool to display whatever quake events are dropped into the database, the next step is to feed it from SQLstream.

Next time

Part 3 of the visualization tutorial concludes this series and will be published next week. It will discuss the final key element - how to get the data flowing.