 | Level: Introductory Aron Y. Lurie (aron.lurie@gmail.com), 9th Grader/Webmaster, Newton South High School/Hebrew College
Marty Lurie (lurie@us.ibm.com), Information Technology Specialist, IBM
02 Mar 2006 Google Maps API along with DB2® or Informix®, PHP, JavaScript, and XML let you create an easy-to-use map with your data on it. Pan to your zip code to see area-specific data. Use custom icons, change the map type, create a sidebar, and use event handlers.
Mapping
The recent explosion of information has spawned many new technologies that help digest all these gigabytes.
Most humans do best with graphical representations when trying to absorb lots of information.
Geographic data, in this case from the US Census, can be visualized using Google Maps,
instead of traditional geographical information
systems (GIS). Why use this article when there are over 69 million hits for the search words "google maps"?
This article focuses on using PHP, Informix, DB2, and Linux. Also, the code provided here has the clarity that
only a 14-year-old programmer can provide. (See "Resources" for more articles written by this author.)
The example in the article is based on census data.
The search screen provides a form to enter a zip code. The results page shows population density
for the zip code selected and adjacent zip codes with color-coded "push pins." The map can be moved with a left-click, drag mouse
action, and the scale can be changed with the zoom control in the upper left corner of the map.
Listing 1 shows some samples after
some cleanup and data scrubbing. The database schema is shown in Listing 2.
Listing 1. Sample data
state AL
zip 35004
longitude -86.502492000000
latitude 33.6063790000000
population 6998
housingunits 2815
sqmeters 49387881
state ME
zip 03901
longitude -70.845590000000
latitude 43.2901600000000
population 6338
housingunits 2406
sqmeters 96091016
state WA
zip 98001
longitude -122.26608100000
latitude 47.3037220000000
population 25771
housingunits 9158
sqmeters 46475168
|
Listing 2. Database schema
Column name Type
state char(2)
zip char(5)
longitude decimal(16)
latitude decimal(16)
population integer
housingunits integer
sqmeters int8
|
Figure 1 illustrates the search page, which is the first page encountered by the user.
Figure 1. Page when first loaded
After the user enters a zip code, the results are displayed, as shown in Figure 2. The map has color-coded indicators for
population density. The text display on the right shows the zip codes displayed on the map. The zoom controls in
the upper left hand corner change the scale of the map. The info window shows the details of the
02222 zip code.
Figure 2. Search results page for zip code 02222
Architecture Overview
So how do you get from a database table to the search and display screens in Figures 1 and 2?
The combination of data sources and the Google Map API is referred to as a "mashup" (Wikipedia.org) or
Web application hybrid. Figure 3 illustrates the server architecture.
The really interesting concept here is the integration, at the browser, of different data
sources. Limiting the volume of data that is combined on the client browser is critical to the
success of this approach. As soon as the client gets too greedy for large quantities of data,
network limitations and
client processor speed will frustrate users.
Figure 3. Annotated mapping architecture
The architecture is quite simple. A request originates from the browser to the Apache Web server. The PHP page requested includes
HTML and JavaScript. The JavaScript calls the Google Map server, and the combined page is rendered (as shown in Figure 1). When a
zip code is entered into the search field, a request is passed to the Web server, which uses a PHP ODBC connection to the backend Informix database.
The code samples in this article show how to use Informix. See the "DB2 and open source: Web polling with DB2, PHP, and Linux" (developerWorks, August 2004) to learn how to configure DB2 to work with PHP.
The data required for the page is retrieved from the database. The push pin color scheme shows population densities based
on the color spectrum. Population ranges were selected to get a similar quantity of occurrences for each distribution.
Please see the
discussion of the code and the comments in the code provided in this article for additional information about how the architecture works.
The components
Ajax/JavaScript and Google API
The Google API, JavaScript, and Ajax make up a majority of the application. The Google Maps API requires a key specific to one directory of one Web site.
To get a key, you must go to www.google.com/apis/maps and click on "Sign up for a Google Maps API key."
The API comes with many useful functions, including the Ajax HttpRequest class and event handler controls. Google provides an excellent documentation
for the api, which can be found at www.google.com/apis/maps/documentation/.
Ajax, however, is just a fancy acronym for the combination of JavaScript and XML. By calling the Ajax and parsing the HTML returned by the PHP, as you will see below, you can eliminate the need to refresh the user's browser.
While not all browsers will support the JavaScript and Google API, a large majority will, including Mozilla Firefox and Internet Explorer 6.
Also, some older browsers may not support some of the CSS used.
Apache, PHP, and ODBC
The mid-tier server provides the Web pages, run-time environment, and database access. The Apache server is well known and, according to the latest
statistics, is the server of choice for almost 70% of internet sites. Apache supports add-in modules, and our server is using mod_php to provide a PHP run-time environment. You can perform simple tests of php scripts by using the command php from the command line. When php code needs to
execute and be sent to the requesting browser, Apache and mod_php come into play.
The database access, ODBC, has suffered from performance stigma. Early releases of ODBC were slow. Implementations have improved
significantly, providing good performance. Using ODBC provides the greatest portability between systems. There are libraries to get native access
to DB2 or Informix, which are specific to the database client APIs.
Informix/DB2
From the IBM Web site:
-
DB2 family: Database management system that delivers a flexible and cost-effective database platform to build robust on demand business applications.
-
Informix family: Delivers superior application performance for transaction-intensive environments.
(Having worked with Informix for a number of years, I can testify that it also does a great job on data warehouse/decision support systems.)
The article "DB2 and open source: Web polling with DB2, PHP, and Linux" cited most examples from DB2, so
here are the examples from Informix. Please refer the other articles written by this author if you have any difficulty making
this example work with DB2 (see "Resources).
The code
Our code consists of three pages:
- The main page, which has the map and is coded in HTML/JavaScript
- A PHP script that returns longitude/latitude of a zip code as XML
- Another PHP script that returns data about the zip codes within the boundaries of the map as XML
Let's start by taking a look at the two components that the user never sees -- the PHP scripts, which are called by the JavaScript, interact with the database, and return XML:
Listing 3. PHP Code to retrieve longitude/latitude for a given zip code
<?php
header("Content-Type: text/xml");
echo"<?xml version=\"1.0\"?>";
$newloc=$_GET[location];
//connect to db2/informix database
//connect vars
$dbname="census";
$username="informix";
$password="useyourown";
// odbc_pconnect returns 0 if the connection attempt fails
// otherwise it returns a connection ID used by other ODBC functions
// echo ( "attempt connect.....\n " );
$conn = odbc_pconnect ( $dbname, $username, $password );
if ( $conn == 0 ) {
echo ( "Connection to database failed." );
//If connection failed, show what the error message was:
$sqlerror = odbc_errormsg ( $conn );
die($sqlerror);
}
$sql = "select longitude,latitude,zip from census_data where zip = '$newloc'";
$locResult=odbc_exec($conn,$sql);
$locData=odbc_fetch_object($locResult);
echo "\n<Results>";
if($locData->latitude !=""){
echo "\n\t<Zip>";
echo "\n\t\t<Latitude>$locData->latitude</Latitude>";
echo "\n\t\t<Longitude>$locData->longitude</Longitude>";
echo "\n\t</Zip>";}
echo "\n</Results>";
?>
|
You may notice that this code connects to an ODBC data source named census. This system was configured using Informix and unixODBC. A good tutorial on how to set it up can be found on the unixODBC Web site (see "Resources"). However, this script can be easily converted to DB2. "Connecting PHP applications to IBM DB2 Universal Database" (developerWorks, July 2001) discusses setting up DB2 to interact with PHP.
The output of the code in Listing 3 will return an elegantly formatted XML source, which will have the Latitude and Longitude tags within a Zip tag, within a Results tag. It will search the database based on a value in the URL. If it doesn't find a result, the Results tag will not have a Zip tag within it. You will also see the Results tag in a more practical role in the second PHP script.
Listing 4 is a sample output of the script, using 02222 (Boston, MA) as the zip code.
Listing 4. Sample output of the longitude/latitude script
<?xml version="1.0"?>
<Results>
<Zip>
<Latitude>42.367797</Latitude>
<Longitude>-71.062829</Longitude>
</Zip>
</Results>
|
The only time this script will be called is by the JavaScript in the main page. Because it returns in XML with the correct headers (set by the header() function), the XML can be parsed by the XML parser built into the Google Maps API. (You will see this later on, in Listing 6.)
Listing 5. PHP Code to retrieve data about zip codes within user's screen
<?php
header("Content-Type: text/xml");
echo"<?xml version=\"1.0\"?>";
//$newloc=$_GET[location];
//connect to db2/informix database
//connect vars
$dbname="census";
$username="informix";
$password="useyourown";
//odbc_pconnect returns 0 if the connection attempt fails
// otherwise it returns a connection ID used by other ODBC functions
// echo ( "attempt connect.....\n " );
$conn = odbc_pconnect ( $dbname, $username, $password );
if ( $conn == 0 ) {
echo ( "Connection to database failed." );
//If connection failed, show what the error message was:
$sqlerror = odbc_errormsg ( $conn );
die($sqlerror);
}
$sql = "select * from census_data where longitude > '$_POST[left]'
and longitude < '$_POST[right]'
and latitude > '$_POST[bottom]'
and latitude <'$_POST[top]'";
$locResult=odbc_exec($conn,$sql);
echo "\n<Results>";
while($locData=odbc_fetch_object($locResult))
{
echo "\n\t<Zip>";
echo "\n\t\t<Code>$locData->zip</Code>";
echo "\n\t\t<State>$locData->state</State>";
echo "\n\t\t<Latitude>$locData->latitude</Latitude>";
echo "\n\t\t<Longitude>$locData->longitude</Longitude>";
echo "\n\t\t<Population>$locData->population</Population>";
echo "\n\t\t<Housing>$locData->housingunits</Housing>";
echo "\n\t\t<Area>$locData->sqmeters</Area>";
echo "\n\t</Zip>";
}
echo "\n</Results>";
?>
|
XML returned by this script is based loosely on the first script -- it will have the root tag, Results, like you saw in the first script. However more often than not, this script will return multiple Zip tags. For each line of data returned by the data source, the while loop will take in the line as an object, and spit out a Zip tag with all the data in the database (see Listing 1).
The most important part of this code is the SQL query -- in Listing 6, the Ajax will switch to POST, instead of GET (as seen in Listing 3), and send four variables: left, right, bottom, and top. These variables are defined as the borders, in longitude/latitude, of the visible map. By querying the database and asking for the zip codes within these exact borders, you eliminate using unneeded bandwidth, helping the server and also helping to cut back response time.
Three sections make up the code in Listing 6:
- CSS
- The <div> tags
- JavaScript and a small amount of PHP
The first section is a cascading stylesheet (css) embedded in the <head> tag. Its borders are defined by the <style type="text/css"> and </style> tags. The css sets up the classes that will be used in the <div> tags to create the page's look and feel. Without the css, there would be no color and a much looser, less organized page layout.
Within the body tag lies the next piece: the building block <div> tags, which play host to the layout, the information, and the search tools. Because of the way the JavaScript works, <div> tags work extremely well for easily updated pages.
The final section is the most important: the JavaScript. As mentioned, there are a few sections within the JavaScript:
- Search methods
- Methods to control the LOADING.. sign
- Methods that parse the XML and put markers on the map
- A few PHP
for loops that create the icons
- Methods that trigger when the search button is pressed or the form is "submitted"
Out of all of these, the methods where the most functionality is are the methods that parse the XML. They create a new GXmlHttp.create();, which is an HttpRequest class built into the Google Maps API. This is not only the heart of updating the maps, but it's also the heart of most Ajax applications. (However, not all Ajax applications use the Google Maps HttpRequest class.)
After the request is sent to the PHP file and the XML is recieved, the code goes through and looks for all the Zip tags. When it finds one, it will add a marker to the map. It also has to add an event listener to handle an event -- in this case, someone clicking on the marker. Like the HttpRequest, this event listener function is also built into the Google Maps API.
Another important part of the maps is the event listener initialized after all the methods that listens for map movement. It looks at the zoom level and, depending on whether or not the level is above or below 6 (to avoid too much bandwidth and slow response times), it will update the map and the sidebar or tell the user that he/she has zoomed out too far.
What next?
The possibilities for geographic data displays are limited only by your
imagination. The example for this article was based on US Census data.
The architecture and methods shown here can be applied to any data of
your choice. We hope you find this useful and would be interested in
feedback based on your experiences creating "mashups."
Download | Description | Name | Size | Download method |
|---|
| Sample code | googleAPI.zip | 35KB | FTP|HTTP |
|---|
Resources Learn
Get products and technologies
Discuss
About the authors | 
| | Aron Lurie was midway through 10th grade while writing the article. He has had a web development business for 5 years, and ever since 4th grade he has been teaching himself new languages. He is the Webmaster for his school newspaper, and has been Webmaster for his USY chapter and the Hebrew College. In his spare time, Aron is a member of the Newton South ski racing team. Unfortunately, he has not yet found a way to combine computers with skiing. |
| 
| | 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. |
Rate this page
| |
