Stream Computing Blog

The Tutorial blog series helps SQLstream developers build streaming SQL applications. This blog is the third and final part of 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. The second part of this tutorial presented the core of the application – how to render the display list. And in this the concluding part of the visualization tutorial, the final key element is discussed – how to get the data flowing.

Getting the Data Flowing

The last step is to tie SQLstream and Postgres together. First we need to give SQLstream the credentials to access Postgres. Create a new file:

$QUAKES/quake.postgres.properties

1 URI=jdbc:postgresql://localhost/quakekml_development
2 DRIVER=org.postgresql.Driver
3 CONNPARAMPREFIX=dbConn_
4 dbConn_databaseName=quakekml_development
5 dbConn_user=USER
6 dbConn_password=PASSWORD
7 dbConn_applicationName=SQLstream TableReader Adapter

On lines 5 and 6 insert the Postgres user and password you set up (same as in $QUAKES/quakekml/config/database.yml). Create a directory under $SQLSTREAM_HOME/plugin called jndi (if it’s not there already), copy your quake.postgres.properties file there and restart the SQLstream server.

linux> cd $SQLSTREAM_HOME/plugin
linux> mkdir jndi
linux> cp $QUAKES/quake.postgres.properties jndi/
linux> cd $SQLSTREAM_HOME
linux> bin/sqlstreamd

If you haven’t loaded webfeed.sql and usgs.sql as described in the beginning of this post, you should do so now. Now we need to write the SQL that bridges the databases:

$QUAKES/viz.sql

 1 SET SCHEMA '"WebFeed"';
 2 
 3 CREATE OR REPLACE FOREIGN DATA WRAPPER "TableUpdate"
 4     LIBRARY 'class com.sqlstream.plugin.tableupdate.TableUpdateStreamControlPlugin'
 5 ++++LANGUAGE java
 6 ++++DESCRIPTION 'adapter for doing insert/update/merge/delete to an external database';
 7 
 8 --
 9 -- Create SQL/MED foreign server and foreign stream for database output
10 --
11 CREATE OR REPLACE SERVER "Postgres_TableUpdate"
12 FOREIGN DATA WRAPPER "TableUpdate"
13 OPTIONS (
14 ++++connParams 'quake.postgres',
15 ++++sqlDialect 'Postgres 8.x',
16 ++++pollingMillis '5000',
17 ++++commitCount '1000',
18 ++++commitMillis '2000')
19 DESCRIPTION 'Postgres database with visualization results';
20 
21 --
22 -- Display list for quake events
23 --
24 CREATE OR REPLACE FOREIGN STREAM "QuakeEventsDB" (
25 ++++"SQLS_opcode" CHAR(2) NOT NULL,
26 ++++"SQLS_chg" VARBINARY(32),
27 ++++"id" INTEGER options("insert" 'skip', "update" 'skip'),
28 ++++"when" TIMESTAMP,
29 ++++"lat" DOUBLE,
30 ++++"lon" DOUBLE,
31 ++++"mag" DOUBLE,
32 ++++"created_at" TIMESTAMP,
33 ++++"updated_at" TIMESTAMP
34     )
35 ++++SERVER "Postgres_TableUpdate"
36 ++++OPTIONS (
37 ++++++++TYPE 'tableUpdates',
38 ++++++++MASTER 'true',
39 ++++++++updatesTable 'quake_events')
40 ++++DESCRIPTION 'table updated with quake events';
41 
42 --
43 -- Pump quake events into display list
44 --
45 CREATE OR REPLACE PUMP "1000-QuakeEventsPump" STOPPED
46 DESCRIPTION 'pump from "SmallQuakesDay" view to "QuakeEventsDB" foreign stream' AS
47 ++++INSERT INTO "QuakeEventsDB" (
48 ++++++++"SQLS_opcode", "when", "lat", "lon", "mag",
49 ++++++++"created_at", "updated_at")
50 ++++SELECT STREAM 'IN',
51 ++++++++q.ROWTIME,
52 ++++++++CAST(SUBSTRING("point", 1, POSITION(' ' IN "point") - 1) AS DOUBLE),
53 ++++++++CAST(SUBSTRING("point", POSITION(' ' IN "point") + 1) AS DOUBLE),
54 ++++++++"mag",
55 ++++++++CURRENT_TIMESTAMP, CURRENT_ROW_TIMESTAMP
56 ++++FROM "SmallQuakesDay" as q;

The first 20 lines set up the table updater (note the reference to our properties file on line 14). We then create a foreign stream to describe the Postgres table quake_events. When Rails created the table, it added an auto-incrementing id field and two timestamps, created_at and updated_at. The options specified on line 27 cause SQLstream to ignore the id column and let Postgres maintain it. At line 45 we describe the pump that reads from SmallQuakesDay and inserts into the stream we defined above. In the select clause we use the ‘IN’ opcode to indicate that we’re inserting and the ROWTIME of the quake record to set the ‘when’ column in the display list. We parse the ‘point’ column the USGS provides, which is in the format "lat<space>lon", on lines 52 and 53. To set the Rails timestamps correctly we use CURRENT_TIMESTAMP to get the creation time and CURRENT_ROW_TIMESTAMP to get the time of this update. Load this file into SQLstream with:

linux> sqllineClient < viz.sql

Now all that’s left to do is start the pump. Create this one-line file:

$QUAKE/start-pump.sql

1 ALTER PUMP "1000-QuakeEventsPump" START;

and start the pump:

linux> sqllineClient < start-pump.sql

Data should now be flowing from the USGS web service, through SQLstream, into Postgres, and rendered to Google Earth from Postgres via Rails. To verify that data is flowing, you can use the ‘Edit quake events’ in your web app. When you follow the Google Earth link, you should see pins scattered across the globe indicating the past day’s earthquakes, and updated every 60 seconds. You’ll probably have to zoom out a bit to see them, unless San Francisco is having another bad day. This is what I saw when I ran it:

And finally

That concludes the streaming data visualization tutorial. In case you’ve missed the earlier posts, the complete series can be found using these links:

++++++++Part 1: Capture the data and create the display list

++++++++Part 2: Rendering the display list

++++++++Part 3: Making it work with flowing data

Please contact us if you have any questions.

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 http://localhost:3000/quakes/feed. 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:

$QUAKE/quakekml/app/controllers/application_controller.rb

 1 require 'zip/zip'
 2
 3 class ApplicationController < ActionController::Base
 4+++++protect_from_forgery
 5
 6+++++# Set up the common params that have to be computed
 7+++++# after the request is received (can't go in
 8+++++# initialize). These are available in all views.
 9+++++#
10+++++def setup_common
11+++++++++@path = request.host + ':' + request.port.to_s
12+++++end
13
14+++++# Output the kmz, given the kml
15+++++#
16+++++def send_kmz(kml)
17+++++++++t = Tempfile.new("zipout-#{request.remote_ip}")
18+++++++++Zip::ZipOutputStream.open(t.path) do |zos|
19+++++++++++++zos.put_next_entry("sqlstream.kml")
20+++++++++++++zos.print kml
21+++++++++end
22
23+++++++++send_file t.path,
24+++++++++++++:type => "application/vnd.google-earth.kmz",
25+++++++++++++:filename => "sqlstream.kmz"
26
27+++++++++t.close
28+++++end
29
30+++++# Output the kml directly
31+++++#
32+++++def send_kml(kml)
33+++++++++render :text => kml,
34+++++++++++++:layout => false,
35+++++++++++++:content_type => "application/vnd.google-earth.kml+xml"
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
 3+++++++++setup_common
 4+++++++++@refresh = 60
 5+++++++++kml = render_to_string :template => 'quakes/start.kml',
 6+++++++++++++:layout => false
 7+++++++++send_kmz kml
 8+++++end
 9
10+++++def feed
11+++++++++setup_common
12+++++++++@quakes = QuakeEvent.all
13+++++++++kml = render_to_string :template => 'quakes/feed.kml',
14+++++++++++++:layout => false
15+++++++++send_kmz kml
16+++++end
17+end

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:

$QUAKES/quakekml/app/views/quakes/start.kml

 1 +<?xml version="1.0" encoding="UTF-8"?>
 2 +<kml
+++++xmlns="http://www.opengis.net/kml/2.2"
+++++xmlns:gx="http://www.google.com/kml/ext/2.2"
+++++xmlns:kml="http://www.opengis.net/kml/2.2"
+++++xmlns:atom="http://www.w3.org/2005/Atom">
 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.

$QUAKES/quakekml/app/views/quakes/feed.kml

1 <?xml version="1.0" encoding="UTF-8"?>
2 <kml xmlns="http://www.opengis.net/kml/2.2"
+++++xmlns:gx="http://www.google.com/kml/ext/2.2"
+++++xmlns:kml="http://www.opengis.net/kml/2.2"
+++++xmlns:atom="http://www.w3.org/2005/Atom">
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, we use this one:

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:

$QUAKES/quakekml/app/views/quakes/_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 %>,<%= quake_event.lat %>,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:

$QUAKES/quakekml/app/views/quakes/_quake_description.html.erb

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(quake_event.lat, :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.