You are currently on IBM Systems Media’s archival website. Click here to view our new website.


Show Geographic Data With Web Query for i


All businesses create reports and traditionally, report output was text only. Now with IBM DB2 Web Query for i, you can also represent data with several new HTML5 venues, including maps. This article will take a look at some examples where Web Query represents data utilizing maps and geographic technology.

When mobile applications ask to use a person’s location, the phone uses cell towers, GPS or WiFi to find that information.

If you had a database with geographic data, such as a Country name field, in each record, you could query the database, summarizing (SUM and GROUP BY) Revenue by Country, to present a map of Revenue by Country in a couple ways.

Web Query ships with the mapping ability to present “proportional maps” or “heat maps”. In my Country example, for a proportional map, a circle is placed on the map for each Country, sizing each circle to represent the numeric value being plotted. In a heat map, the various Countries get differing colors based on the numeric value magnitude. See Figure 1 and Figure 2.

Three features are key to utilizing this mapping technology; the map, measure and geographic role. In my example, Revenue dollars is the measure and Country is the field with a geographic role, to be plotted on a map. First start Web Query’s InfoAssist tool. Get a new chart. Then pick HTML5 as Output Type and Map as the Type of Chart. See HTML5 and Map selected at either end of the Web Query menu bar in Technote to assist you with this process and contains specific IFS paths.

The Technote lists steps to modify or enable maps that are shipped but not necessarily enabled in Web Query. You should not modify anything in the ProdData folder but you can make changes in UserData. ProdData is liable to be overwritten by subsequent releases, upgrades or hot fixes. Before making any changes to your Web Query configuration in UserData, read the IBM-supplied information and make backup copies of anything you’re changing.

After making changes and restarting Web Query, the New Hampshire map was visible to select. This got me interested in the actual contents of a map data file, which includes latitude and longitude information. I also discovered that it contained the 10 counties of New Hampshire. See Figure 4.

To create a report with maps of NH data, I must provide a character field with County names plus a numeric measure for each county. I didn’t have a database with Revenue by County, but I did have the IBM sample data, which contains revenue by Plant and Sales Representative. Using the option to create my own character field, I created a field to redefine existing data fields, assigning hardcoded County names, via the Web Query DEFINE function. See Figure 5.

Rick Flagler is an information technology consultant, teacher and mentor.

Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.



2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Untangling Web Query

How metadata can reduce query and report complexity

Protection From Slow Queries

Introducing DB2 for i Adaptive Query Processing

Intelligent Queries

DB2 for i Learning Statistics Engine works smarter

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters