A Flashy Demo - Web Queries and KML

Every Manifold advocate should have at least one flashy demo that he or she uses to show off Manifold. While it is the deep power of Manifold doing things like topology overlays that impresses the GIS elite, to avoid putting to sleep those guests who may not have a deep appreciation of GIS it is important to keep the demo light and visually appealing.

 

Important: If you have installed 64-bit Manifold, do this example in 32-bit mode by launching Manifold System using the Manifold System (32-bit) shortcut. See the note on 64-bit Manifold at the end of this topic.

 

This examples shows a sequence of moves using Excel to grab dynamic data from the web, using it in Manifold and then grabbing part of that data and exporting to Google Earth. Fast and easy, it's a proven crowd pleaser.

 

Practice this routine. It's slow at first but after a few practice sessions can be accomplished with confidence in less than ten minutes. It goes so fast you'll spend more time explaining various wonderful things about Manifold than doing the demo.

 

This demo requires a web connection for the machine being used. In addition to Manifold we also need Microsoft Excel and Google Earth installed on the machine.

 

Step 1: Create a Web Query in Excel

 

Excel can be setup to automatically grab data from a web site using an Excel facility called web queries. Experts can create a web query in Excel by opening an Internet browser to the web site desired and then dragging and dropping the target data into an Excel session, using a special Paste As option. That's very flashy but easy to do wrong.

 

Another way to create a web query in Excel is the more traditional way using Excel menu commands shown in this topic. This is easier to remember, still flashy and virtually impossible to get wrong. For this example we will use the traditional way to create a web query that grabs data about recent earthquakes. Here's how:

 

images\eg_webq_01.gif

 

Launch Excel

 

images\eg_webq_02.gif

 

Choose Data - Import External Data - New Web Query

 

images\eg_webq_03.gif

 

The resulting New Web Query dialog launches like an Internet Browser. It opens to your home page so you might want to set your home page to something respectable before doing this for an audience.

 

In the Address box, enter the URL to the USGS web page that gives a list of recent earthquakes in the US greater than magnitude one. The URL happens to be...

 

http://earthquake.usgs.gov/eqcenter/recenteqsus/Quakes/quakes_all.php

 

...but that's not possible to remember. Most people will launch an Internet browser just ahead of the demo and then use their favorite search engine to search for recent US earthquakes USGS or some similar search terms. That will find the USGS earthquake page, which has links to pages giving lists of recent earthquakes in the US and worldwide.

 

In fact, some people like to start this demonstration with a web browser already on the web site showing the list of recent earthquakes saying something like, "Wouldn't it be great if we could show the data in this page on a map in our GIS system and have it automatically updated, all without any programming?" A good performer always dangles a bit of bait in front of his or her audience to get their attention.

 

Once you have the page in your Internet browser you can copy and paste it into the Address box of the New Web Query dialog and press the Go button.

 

images\eg_webq_04.gif

 

The earthquakes page can take tens of seconds to appear, because the USGS server is generating a table of recent quakes and sending it out to the page. This is your chance to distract the audience from the delay by talking about how this technique will work with any web page of locations, such as navigation buoy locations, locations of storms, locations of tracked animals or vehicles and so on.

 

When the page appears, the New Web Query dialog will place a yellow box with an arrow at the location of every table that is embedded in the page.

 

images\eg_webq_05.gif

 

To find the data of interest, scroll down on the page (scroll bars in the dialog are not visible in the screenshots shown) to get to the table of recent earthquakes. The table is marked with a yellow box with an arrow. Note that the table has the magnitude, time and latitude and longitude locations of the quakes. Those who have been following along in previous examples know it is a geocoded table.

 

images\eg_webq_06.gif

 

Press the yellow box with an arrow to select the table.

 

images\eg_webq_07.gif

 

When the table is selected it will be highlighted and the box next to it will turn green.

 

images\eg_webq_08.gif

 

Press the Import button on the New Web Query dialog.

 

images\eg_webq_09.gif

 

That launches the Import Data dialog. Press the Properties button.

 

images\eg_webq_10.gif

 

We are here in the External Data Range Properties dialog mainly for one very interesting box, the one we just checked that says Refresh every ... minutes. We've set the value to 30 minutes. What is immensely cool about this box is that we can tell Excel to go fetch data in background from the website so it always has current data in the spreadsheet. Our web query is like a robot in Excel that interrogates the USGS list of recent quakes every 30 minutes to get the most recent list.

 

In this case, we've told Excel to grab a table of recent earthquakes and to update the data from the web site every 30 minutes. That seems often enough for quakes. Press OK to exit the properties dialog and then OK to exit the Import Data dialog.

 

images\eg_webq_11.gif

 

Back in Excel we see that the spreadsheet now contains data from the web site. If we were to sit around for 30 minutes we could see the numbers change as the spreadsheet automatically updates itself from the web query. The table looks a bit messy, with missing values and a first column that looks like junk, but Manifold will digest this nonetheless.

 

Save the Excel spreadsheet to a file called usquakes.xls in some convenient folder.

 

Step 2: Create a Linked Drawing in Manifold

 

Launch Manifold and choose File - Link - Drawing. This immediately launches the Data Source dialog to allow choosing a data source from which the drawing should be linked.

 

images\eg_webq_12a.gif

 

We click the Add Data Source button to add a data source for our desired .xls file.

 

images\eg_webq_12.gif

 

In the Connect To dialog choose XLS Files in the Files of type box and navigate over to the usquakes.xls file and open it.

 

images\eg_webq_13b.gif

 

This adds a new data source, but the default name used is not particularly helpful. We will give it a more memorable name.

 

images\eg_webq_13a.gif

 

In the Name box we enter a name that will help us remember what this data source is for. Press OK.

 

images\eg_webq_13.gif

 

In the resulting Link Drawing dialog Manifold has already examined the .xls file and has pre-loaded the dialog with sensible choices. Manifold knows that LON likely means the longitude field and LAT the latitude field so these come pre-loaded with the right field choices. We'll select all the fields except the first F1 column which is just empty junk at the beginning of the Excel table.

 

Pausing a moment to enjoy the priceless value of a demo where most dialogs work fine using default values, we press OK.

 

images\eg_webq_14.gif

 

The result is that an ensemble of new components appear in the project. When giving this demo to an audience of very sophisticated GIS people who understand something about spatial SQL, we might take a few minutes to discuss how Manifold works with geocoded tables, using the information in topics like the Creating Drawings from Geocoded Tables topic. However, tread lightly on such matters when beginners are in the audience.

 

The new components created do have ugly names because of the naming conventions within spreadsheets, but this is an opportunity to do something with Manifold that even a novice will enjoy: we will rename a file.

 

images\eg_webq_15.gif

 

Right click onto the linked drawing and choose Rename. Rename it to US Quakes so the audience can see it automatically renames dependent components and that doing things in Manifold is really easy, using clicks and context menus as one would expect in Windows.

 

images\eg_webq_16.gif

 

When we click open the drawing the GIS people are impressed to see a drawing created on the fly from geocoded table in the XLS, but less technical people may be bored by the lack of pretty colors or lack of a map. Let's add pretty colors first and then later on (whet their appetites) we'll add a map.

 

Step 3: Format the Drawing

 

images\eg_webq_17.gif

 

Click into the background color well for points in the formatting toolbar and choose Theme.... In the Format dialog use the MAG field with Natural Breaks and the Spectrum palette. We like to change the first color in the palette to a darker purple. Doing so shows how easy the dialog is to operate. Press OK.

 

After doing this demo once or twice most people cannot resist showing off different palettes while clicking the Preview box on and off so that people can see how interactive Manifold is. Cheap and flashy, perhaps, but fun and impressive as a matter of detail.

 

images\eg_webq_18.gif

 

The result shows earthquake points colored by their magnitude, with more dangerous quakes colored more towards the red end of the spectrum. Someone in the audience will almost inevitably ask if points could be made larger for more powerful quakes.

 

images\eg_webq_19.gif

 

Click into the size well for points and click Theme... In the Format dialog choose a size range for points similar to that shown above. Sometimes it is fun to turn on the Preview box and ask for audience feedback as different sizes are tried. Getting the audience involved is Rule Number One for performers.

 

images\eg_webq_20.gif

 

That's better. Now we see larger quakes with larger points. By now the audience is clamoring to see the points on a map. They've probably asked for this before but have been put off by the demonstrator. Making the audience ask for more is Rule Number Two for a performer.

 

Step 4: Create a Map

 

images\eg_webq_21.gif

 

Import the World_eg sample drawing from the Manifold download site. The drawing is shown above, but the steps to import it are not shown in this example as the elementary importing of drawings is covered in many other places.

 

images\eg_webq_22.gif

 

Right click on the World_eg Drawing in the project pane and choose Create - Map. In the Create Map dialog check both drawing boxes to participate in the map and press OK.

 

images\eg_webq_23.gif

 

Open the map to oohs and aahs as people see where the quakes are on a real map. Pan and zoom a bit. Note that using the USGS site ends up creating at least one point at the 0,0 origin of latitude and longitude (by Africa) because the first row in the table after the column names does not have lat/lon values. You can promise the audience that later you'll show them an expert Manifold trick that can be used to whip such deficient data into shape. For now, we ignore it.

 

Step 5: Create a KML File

 

Everyone likes to share the fruits of their work, so you can set up the next step by telling the audience you'll show them a fast and easy way of communicating the data you have to others. We can do that by creating a PDF (a variation of this demo) from a print layout, or use the following technique of creating a Google Earth KML file that can be emailed to colleagues.

 

Note: Drawings ideally should be in Latitude / Longitude projection before export to KML or KMZ. Our drawing already is in Latitude / Longitude projection. See the note at the end of this topic for discussion of projections and KML.

 

images\eg_webq_24.gif

 

Zoom into Southern California where there are always plenty of small earthquakes and select some of them (make sure you are on the quakes layer in the map).

 

images\eg_webq_25.gif

 

With the quakes selected, choose Edit - Copy to copy those points.

 

images\eg_webq_26.gif

 

Right click onto a blank spot in the project pane and choose Paste. A new drawing appears, helpfully named in a like fashion to its parent. GIS people in the audience with Windows skills know they are seeing something very interesting here, as this sort of thing is not done with legacy GIS systems.

 

images\eg_webq_27.gif

 

Open the drawing and people will see that the formatting is retained. In the above illustration we've taken one step more (not shown) and have changed the thematic formatting for point size to increase the point sizes a value or two, say, from 2 to 4 and so on, so that the points appear larger. We have a reason for doing that, as will soon be clear. We've also renamed the drawing to SoCalQuakes.

 

images\eg_webq_28.gif

 

With the SoCalQuakes drawing open, we choose File - Export - Drawing. In the Export Drawing dialog we choose KML / KMZ Files in the Save as type box and we provide the filename socalquakes.kml as the File name. Press Save.

 

images\eg_webq_29.gif

 

The Export KML File dialog pops open to ask us which fields we would like to use for Name and Description. We'll use the MAG field giving magnitudes for the Name and the date and time field for the Description. Press OK.

 

What we've now created is a file called socalquakes.kml that is a "document" file for Google Earth. We can email it to someone and if they have Google Earth installed on their machine when they double-click the socalquakes.kml to open it, Google Earth will launch and display the contents of the file.

 

Step 6: Display KML File in Google Earth

 

Switch out of Manifold and into Windows Explorer and browse to the folder where the socalquakes.kml was stored. Double-click the socalquakes.kml file to display it in Google Earth. Google Earth will launch automatically and will zoom in from space to show our earthquake points in southern California.

 

images\eg_webq_30.gif

 

The MAG field we used for Name will result in the points in Google Earth being labeled with the magnitude of the earthquake represented by each point. The points are still colored by the strength of the quake because that's how they were sent out by Manifold. The points are large and obvious against the clutter of the photographic background because we enlarged point size slightly before exporting the drawing.

 

images\eg_webq_31.gif

 

If we look at the Google Earth information panes we can see that individual points have a description of sorts which is the date and time field. This is not the most powerful interface for handling data information, but then Google Earth is about visual interest, and lots of it, and not about manipulating or displaying data.

 

Step 7: Adult Talk about Spatial Queries

 

Google Earth is fun for all ages, but some folks with GIS expertise might be interested in just how you propose to use spatial magic to get rid of that spurious point near Africa. That's a chance to talk about how the drawing created from the XLS was actually created by an intermediate query from the linked table in the drawing.

 

images\eg_webq_35.gif

 

You can open the query so that people with some familiarity with SQL can see how it works.

 

images\eg_webq_36.gif

 

The next step is to add just one line at the bottom, WHERE [MAG] > 0, which adds a qualifier to do all that spatial query stuff only for cases where the magnitude of a quake is greater than zero. Spurious lines in the table that don't have data for latitude and longitude won't have earthquake magnitudes greater than zero either, so this is a quick and dirty way of accomplishing the task.

 

After modifying the query in the above manner you'll have to Run the query and then Relink the drawing back to the query. Practice this a few times before doing a live demo so you feel confident with the operation and show that confidence in your demo.

 

Operating the Relink dialog shows people how the drawing is linked to the query, and GIS people with computer science expertise are never failed to be impressed how the system can hang together so well that after the Relink the formatting is still intact.

 

images\eg_webq_37.gif

 

The result is a map of earthquakes with no spurious points near Africa.

 

Projections and KML Format

 

Google KML and KMZ formats support Latitude / Longitude projection only. Drawings to be exported to KML or KMZ can be in any projection and will be re-projected on the fly into Latitude / Longitude during export; however, to avoid a potentially slow re-projection process it is wise to explicitly re-project drawings into Latitude / Longitude before export. This not only avoids a slow export, it also provides an opportunity to catch errors before export if the re-projection results in a strange display as might happen if the initial projection was not correctly assigned, say, after importing projected data from some format that does not store projection information.

 

Unlike drawings, images must be already in Latitude / Longitude projection to be exported to KML or KMZ.

 

Notes

 

Some demonstrators like this demo so much they go on to create a web site in Manifold IMS. this is especially interesting because as Excel refreshes itself with recent earthquake data Manifold IMS can also be told to refresh itself at a specified interval. This is a fast and easy way of creating a web site showing current earthquake data, with less than 15 minutes required for the entire process. If you add IMS to the demo, practice creating the web site on the target machine so that something dumb, like forgetting a Windows permission, won't trip you up in the live demo.

 

In addition to using export to Google Earth, many demonstrators will create a linked image in the Manifold map, taking imagery from TerraServer or other image server. That shows how Manifold can be used to bring together data from different web sources within the same project. For example, some people bring the World_eg background into the map as an image linked from a Manifold Image Server. If you use that idea in a demo, work it out in advance so that you create a map from the linked drawing (which cannot be re-projected) and then show earthquake points on that.

 

Once pasted, the points copied from southern California will create a static drawing that has no link back to the original XLS. That's OK, as it illustrates how Manifold can grab data and move it from remote to local form.

 

This demo example was inspired by the magnificent presentations given by Manifold expert Dr. Arthur Lembo, who has used web queries together with Manifold at Cornell University to create rapid response web sites to help with disaster relief.

 

Important Note when Using 64-bit Manifold Editions

 

Due to a lack of required Microsoft facilities in 64-bit Windows systems, Manifold in 64-bit mode cannot export, import, export or link to DB, HTML, MDB, XLS or WKx format files. This includes no access to the MDB parts of Manifold MFD and MapInfo TAB imports. The workaround for importing or exporting such files is to launch Manifold in 32-bit mode by using the Manifold System (32-bit) shortcut, perform the export from or import into a .map project file and then re-launch Manifold in 64-bit mode using the Manifold System (64-bit) shortcut. Linking is more complex: the data must be in some format usable in 64-bit mode within 64-bit Windows systems, such as a SQL Server database. Alternatively, the data can be kept within a Manifold .map project file and linked using the Manifold ODBC driver.

Although Microsoft is slowly moving forward towards 64-bit operation of Office and the JET components used by Access and Excel, at the present writing (2010) this is such a total kludge that while it is possible to get 64-bit access to Access or Excel it is just easier for most to continue using the 32-bit versions. See comments in the 32-bit and 64-bit Manifold Editions topic.

 

See Also

 

Linked Drawings

A Flashy Demo - Web Queries and KML

Exporting KML to Google Earth

Fun with Google Earth

Linked Images from Google Servers

Export Drawing - KML, KMZ

Export Image - KML, KMZ

Import Drawing - KML, KMZ