 | Level: Intermediate Marty Lurie (lurie@us.ibm.com), Information Technology Specialist, IBM
Aron Y. Lurie (aron.lurie@gmail.com), 10th grader/webmaster, Newton South High School
28 Jun 2007 Google's new Maps API Version 2 allows for more advanced mashup applications. In this article, you'll graduate from the basic mashup development explained in Part 1 of this series and see how to use the DB2® REC2XML function to eliminate lines of code from the PHP: Hypertext Preprocessor (PHP) scripts. You'll learn how to implement this solution on either DB2 or Informix® Dynamic Server (IDS), upgrade your current map from Maps API Version 1 to Version 2, and learn how to implement useful features in the new API.
Why map?
In this age of information, the Internet has done a good job of developing
applications to deal with the magnitude of information available and effectively display it. When dealing with
geographical data, the best way to represent it is using a graphic. This type of visualization has existed on computers for years in the form of geographic information systems (GIS) and Web sites such as Mapquest. However, Google Maps
revolutionized the model for online mapping with its easy-to-use interface, making it easier for users to find information quickly. With Google Maps, you can drag and
drop the map and have it update in real time -- without reloading the Web page.
Today, other mapping sites (including Mapquest, Yahoo!, and Microsoft) follow the
model that Google Maps created.
Why this article?
While there are thousands of other Web sites that talk about Google Maps, this article
is focused on how to use Google Maps with DB2 or Informix. Also, if you were to search for "google maps api php"
in Google, there are over 30 million results. And, Part 1 of this series is ranked in the top 10.
The application
There is no limit to what you can make with the Google Maps application programming interface (API), aside from
some restrictions in the
Terms of Use. Thousands of sites have used Google Maps in different ways to convey
different information to varied users. These Google Maps-based pages are known as
"mashups." In this article, we'll walk through how to make a simple example mashup, which
uses data from the US Census Bureau.
Figure 1. Page when first loaded
At the top, there is a form for the user to input a zip code. The map is below it,
and to the right is a display of relevant information.
Figure 2. Page after searching for zip code 02222
When you search for a zip code, the map zooms and centers on the location of the
zip code. It also creates pushpin markers to show the searched zip code and others in the
area. The pushpin highlighted with the asterisk (*) is the one for which you searched. A vareity
of colored markers is used to give you, at first glance, a general idea of
population density in each zip code.
The data
For this application, the data for the zip codes comes from the US Census Bureau, which
provides this and other geographic information freely online. As far as geographic data goes,
it is a simple schema.
Listing 1. Application's data schema
Column name Type
state char(2)
zip char(5)
longitude decimal(16)
latitude decimal(16)
population integer
housingunits integer
sqmeters int8 |
Google Maps API Version 2, JavaScript, and AJAX
Google's Maps API is the toolkit that allows anyone
to put their own map online. The original (Version 1) was released a few years ago, and Version 2 was
made available on April 3, 2006. The upgrade was intended to redesign fundamental parts of the API, making it a smaller download with improved functionality. Google designed the upgrade to
be 99% backwards compatible, but some incompatibilities do exist.
In order to help users switch over, Google has provided an upgrade guide with a list of incompatibilites
between the two versions. Below are some of the highlights (method names, constant names, and so on). The
full API Upgrade Guide
can be found online.
- Zoom levels: In the old API, zoom level 0 was the closest zoom. Now, it is the farthest zoom.
To convert between the zooms, try this trick:
newZoom = 17 - oldZoom
- Most method names have been simplified, for example
getZoomLevel() is now
getZoom(), and getBoundsLatLang() is now
getBounds().
- The names of the map types have changed slightly. For instance,
G_HYBRID_TYPE is now
G_HYBRID_MAP.
AJAX
AJAX, which stands for Asynchronous JavaScript and XML, is one of the
main components behind Google Maps and how the application works. While most dynamic Web sites require
the user to reload the browser and page in order to see new information, AJAX allows for the
information to be updated on the page without a browser reload. This is how the drag-and-drop
capability of the map comes to life -- when the map is dragged, the server sends the image for the
area you are requesting, and the map loads it immediately. For our application, AJAX is used to
load the zip code data automatically -- no refresh required.
DB2 and Informix
The IBM site describes DB2 as a "database management system that delivers a flexible and
cost-effective database platform to build robust on demand business applications" and describes
Informix as a database that "delivers superior application performance for transaction-intensive
environments."
The main DB2 feature that the mashup uses is REC2XML. It allows for a query that returns
formatted XML, based on a regular table (in our case, with our Census data and schema). The
corresponding function in Informix is called genxml, and you can see examples of both in the
code samples provided.
Architecture summary
Figure 3. Architecture diagram
This diagram shows the interactions of the pieces required to create and run the application.
The first request originates at the browser, where you would type in your zip code. Using AJAX,
the zip is sent to the Apache server, where PHP and Open Database Connectivity (ODBC) create a
query and execute it. PHP then creates formatted XML with the data from either DB2 or Informix, which is
recieved by AJAX and parsed with JavaScript. When the JavaScript has the information, it can
then call the API to load the map specific to that location. The process is then repeated,
using a different PHP and AJAX script to load the pushpins.
With DB2's REC2XML, this process is slightly altered, because the PHP no longer needs to
create the formatted XML, it simply needs to relay it to the client-side.
PHP and ODBC
PHP is a commonly used Web scripting language.
In the application, PHP is essentially a middle-man who runs data between the database and
the browser. ODBC is what allows the PHP to talk to the databases.
ODBC can be configured to work with either Informix or DB2, and in the
Resources section of this article, you'll find tutorials on how to set that up.
Code samples
In our application, there are three scripts:
- The page you, the user, sees with all the JavaScript™, AJAX, and API calls
- The first PHP script called, which returns longitude/latitude of the user's zip code
- The second PHP script called, which returns information about all zip codes inside of
the map you would see
You can download all of the scripts at the bottom of the article, so we will examine the
code most vital to the new API and REC2XML (DB2) and genxml (Informix).
Listing 2. The code
that updates the map
var maxZoom=12;// this is the farthest out they can zoom while still seeing markers.
//event listener for movement takes care of refreshing
GEvent.addListener(map, 'moveend', function() {
var center = map.getCenterLatLng();
var latLngStr = "Long/Lat of map center: ";
latLngStr += '(' + Math.round(center.y*10000)/10000 + ', '
+ Math.round(center.x*10000)/10000 + ')';
document.getElementById("longlat").innerHTML = latLngStr;
var zoomstring="Zoom Level: ";
zoomstring +=map.getZoom();
var divZoom = document.getElementById("zoomlev").innerHTML = zoomstring;
if (map.getZoom() >= maxZoom)
getZips();
else
{
document.getElementById("searchstatus").innerHTML = "";
document.getElementById("ziplist").innerHTML = "";
document.getElementById("zipstatus").innerHTML =
"<b>Zoom in closer to see the zip code list</b>";
map.clearOverlays();
}
});
|
This block of code defines a GEvent.addListener
which
listens for any map movement (drag, drop, zoom, pan, map type change) and defines the
appropriate action to take should it occur. One part of the code defines whether or not the
zip codes should be updated, based on the zoom level of the map. Because the new API
flipped the direction of the zoom increase/decrease, all the constants and math were changed
to reflect this. So, while the maxZoom used to be 6, it is now 12. Also, the Zoom must be more
than the maxZoom, instead of less.
Listing 3. DB2 REC2XML
example call
select
rec2xml(1.0, 'COLATTVAL',' Zip', zip, state, longitude, latitude, population, sqmeters)
from census_data
where longitude > -71.2928581237793 and longitude < -71.1723518371582
and latitude > 42.291532494305976 and latitude < 42.35511892873107
fetch first 200 rows only |
This is an example query that invokes the REC2XML function. You'll easily notice that
it is very similar to a regular select query, the only difference being the
rec2xml() and a few extra parameters for the function.
This call returns the following:
Listing 4. DB2 REC2XML
example output
<Zip>
<column name="ZIP">02458</column>
<column name="STATE">MA</column>
<column name="LONGITUDE">-0071.188092</column>
<column name="LATITUDE">0042.354727</column>
<column name="POPULATION">12150</column>
<column name="SQMETERS">4872457</column>
</Zip>
<Zip>
<column name="ZIP">02459</column>
<column name="STATE">MA</column>
<column name="LONGITUDE">-0071.193009</column>
<column name="LATITUDE">0042.321197</column>
<column name="POPULATION">17856</column>
<column name="SQMETERS">12598371</column>
</Zip>
<Zip>
<column name="ZIP">02460</column>
<column name="STATE">MA</column>
<column name="LONGITUDE">-0071.209073</column>
<column name="LATITUDE">0042.352996</column>
<column name="POPULATION">9143</column>
<column name="SQMETERS">3700602</column>
</Zip>
... |
These are only three of the zip codes that were returned in XML by the REC2XML call.
This XML is formatted a little differently than how the PHP we wrote would have formatted it.
Also, you can see that there is no root document tag, nor does it define itself as XML version
1.0 at the top. So, the PHP still has to add those two things while relaying the code.
The Informix function, genxml, uses a slightly different query syntax, and returns code
more similar to the PHP scripts used in the first implementation.
Listing 5. Informix
genxml example query
execute function genxmlquery("Zip",
"select * from census_data where ... "); |
This is the basic syntax for the genxml function. However, unlike the REC2XML, the whole select
query and clauses are inside the genxmlquery() function. Also, this returns more properly formatted
XML, with a root result set tag.
JMeter speed comparison
JMeter is a neat tool provided by Apache Jakarta (see the Resources section).
For this application, we can use it to test the speeds of the databases by running hundreds of
requests on their respective PHP scripts, and measuring how long it takes for the data to come
back. To do this, add two HTTP Request elements to a Thread Group, and configure them so they
point at the returnMarkers files, with the same longitude/latitude
variables. Also, a Listener element is required to tabulate the data.
Figure 4. JMeter HTTP request screen
Using a Summary Report Listener, and setting the Thread Group to 10 users over 10 seconds
sending 25 requests each, the table below shows the results from Informix:
Table 1. Data results from JMeter speed tests
| Database | Avg. Response (ms) | Min (ms) | Max (ms) | KB/sec |
|---|
|
Informix
| 109 ms | 15 ms | 2031 ms | 25.75 |
|---|
More with Google API Version 2
Google enhanced more with the new API than just speed and download size. They also added
some new and useful features, which, although not covered in this article,
can be pretty useful in certain applications. Some of the new features include:
- Custom map controls: In addition to Google's Map Controls, it is now possible to create
custom controls using Googles GControl class.
- Marker manager: With this system, any markers that are defined in its power are
controlled effectively. It will display and hide markers as necessary, as well as "cluster"
markers at higher zoom levels, in effect reducing strain on the user's system.
- Driving directions: The API allows for up to 10,000 driving direction requests a day, and they are the same directions that would be given at their own maps site.
- Additional zoom levels: Two new levels of zoom have been added to the API, for high
resolution areas such as New York, Boston, LA, and so on.
What's next?
The code for this application, on DB2 and Informix, is provided below. You can use that as a
starting point for your application, or start from scratch using this article and many other
tutorials out there. What you can make with this application is nearly unlimited.
Downloads | Description | Name | Size | Download method |
|---|
| Google Maps with Informix | V2_Ifx.zip | 34KB | HTTP |
|---|
| Google Maps with DB2s REC2XML | V2_DB2REC2XML.zip | 35KB | HTTP |
|---|
Resources Learn
Get products and technologies
-
Download a free trial version of DB2 Enterprise 9.
-
Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications.
-
Download a free trial version of Informix Dynamic Server.
- Download
IBM product evaluation versions
and get your hands on application development tools and middleware products from
DB2, Lotus®, Rational®, Tivoli®, and
WebSphere®.
Discuss
About the authors | 
| | Marty Lurie started his computer career generating chads while attempting to write Fortran on an IBM 1130. His day job is in Systems Engineering for IBM's Software Division, but if pressed he will admit he mostly plays with computers. His favorite program is the one he wrote to connect his Nordic Track to his laptop (the laptop lost two pounds, and lowered its cholesterol by 20 percent). Marty is an IBM-certified WebSphere Administrator, IBM-certified DB2 DBA, IBM-certified Business Intelligence Solutions Professional, Linux+ Certified Administrator, Informix-certified Professional, and IBM-certified SOA Solution Designer. |
| 
| | Aron was completing his sophomore year in high school while writing this article. He has had a Web development business for 5 years, and since 4th grade has been teaching himself new languages. He is currently webmaster for his school newspaper, and was webmaster for Hebrew College's prozdor for over 3 years. In his free time, he is a member of Newton South's Ski Racing Team. Unfortunately, he has not yet found a way to combine computers with skiing. |
Rate this page
| |
