Integrating Google Maps With Oracle

Mashing Google Ma Maps wi with Or Oracle Da Database

http://ww w.oracle.com/technology/pub/articles/schalk-googlemaps.html?..

Developer: Java

Mashing Google Maps with Oracle Database

DOWNLOAD

Oracle Database Oracle JDeveloper Sample Sample JDeveloper Proje ct

by Chris Schalk

Easily build a mashup application that integrates Oracle data with the Google Maps API using Oracle XML DB

TAGS

java, google, All

Published August August 2007 As you may know, Oracle has had the ability to generate XML on the fly from the database for a number of years. In addition to generating XML from an Oracle database, it is relatively trivial to publish the generated data XML on the Web using a variety of languages such as with Java Servlets and J DBC. What you possibly haven't seen yet is how easy it is to take dynamically generated XML data from an Oracle database and mash it together with Web page using the Google Maps API. For example, if you have an Oracle database filled with interesting geo-related data, you can very easily integrate this data with Google's JavaScript Maps API. In this article, I'll demonstrate how to build a mashup application that integrates data from an Oracle database with the Google Maps API using Oracle's XML DB feature feature and Java (wi th Oracle Oracle JDeveloper 10 g ; sample Project file here). here ).

Introducing Introducing the Worldwide Worldwide ACME Hotel Hot el Locator Application In order to get a feel for the types of applications possible when combining the power of an Oracle database with the Google Maps API, here is a simple application that allows you to query for global hotel locations and see the results on a Google Map. When you first visit the application's Web page, you see a Google Map along with a simple form at the top that allows you to enter query parameters.


http://www.oracle.com/technology/pub/articles/schalk-googlemaps.html?..

Figure 1: The ACME Global Hotel Locator

As you can see in Figure 1, a query criteria of "Beach access", "under 400$" and in the "Americas" region was specified and the "Find Hotels" button was clicked. This returned a set of hotel results in Miami, Acapulco, and Rio de Janeiro. Clicking on the marker reveals the hotel details in an pop-up window. As you can also see, the hotel rating is 4 stars and the average price at this hotel is about $293. A "Book it!" link is also displayed allowi ng you to easily jump to the hotel's web si te in order to reserve your room. To get a feel for the dynamic nature of the application, let's say you wanted to search for an ACME hotel that has a pool yet was under $400 and was located in Europe. This sear ch would reveal the following results:



Figure 2: A price conscious hotel search in Europe

As you can see here, a lovely, yet affordable hotel in the heart of Rome with a pool would appear in the query results. Another cost conscious query in Europe this time without specifying a pool but under $100 yields a 3-star London Express hotel in the heart of London.

Figure 3: A 3 star hotel in London under $100. Only in a demo!

As you can see in this example, a simple switch to the "Map" option on the Google Maps window allows you to see exactly where in the city the hotel is located. Additional queries yields many interesting combinations of hotels across the world.



Figure 4: Various results from the ACME Hotel Locator App

One of the cool factors with this application is that it's surprisingly ease of use, which is true with pretty much any Google Maps application.

Building the ACME Hotel Locator Application Before jumping into the code behind this application, let's take a quick at the architecture. As you can see in Figure 5, the application's architecture consists of an Oracle database providing the actual data behind the application, an application server that runs a Java servlet that communicates with the database and retrieves the data in XML format using Oracle XML DB.

Figure 5: The Architecture of the ACME Hotel Application



The actual Java servlet (XmlServlet) makes use of a special class, XmlGenerator, that issues a query using JDBC and retrieves an XML stream of hotel data. This data is then returned to the servlet which in turn sends it to the html Web client ( acme_hotels.html), which uses the Google Maps API to display the data. The actual Map images, or tiles, specifically are provided by Google. That's essentially the architecture in a nutshell. Let's take a look at the code in more detail.

The Database Table - Hotels For this simple application, all that is really needed is a single table, "hotels", which contains a series of columns that contain all of the pertinent information for each hotel. Here is the script that creates the hotels table.

REM Create a Geocoded Hotels Table CREATE TABLE HOTELS ( ID NUMBER NOT NULL, NAME VARCHAR2(500) NOT NULL, DESCRIPTION VARCHAR2(500), THUMB_IMG_URL VARCHAR2(500), WEB_ADDRESS VARCHAR2(500), ADDRESS VARCHAR2(1000), CITY VARCHAR2(500), POSTAL_CODE VARCHAR2(500), STATE VARCHAR2(500), COUNTRY VARCHAR2(500), REGION VARCHAR2(100), STARS NUMBER, BEACH VARCHAR2(1), AVE_PRICE_USD DOUBLE PRECISION, POOL VARCHAR2(1), LATITUDE DOUBLE PRECISION, LONGITUDE DOUBLE PRECISION ); ALTER TABLE HOTELS ADD CONSTRAINT HOTELS_PK PRIMARY KEY ( ID )

ENABLE;

Besides the obvious columns such as name, description, and so on, notice the columns latitude and longitude. This is the exact geographic location of each hotel, which are provided to the Google Maps API in order locate the hotels on the map. Using the ' Spatial' option or basic table columns. An alternative way to store geographic information in an Oracle database is to use the Oracle Spatial option. With the Spatial option, instead of loading just latitude and longitude coordinates into their own columns, you would load them into an object called SDO_GEOMETRY. This object demonstrates the power of Oracle Spatial; it can actually support a lot more geographic information than just geographic points. These include lines, curves, polygons, multilines,multipolygons and so on. For the ACME Hotel application however, all that is r eally needed is just latitude and longitude coordinates for now, so two do uble precision columns will suffice. It would be a trivial process though to upgrade this application to using the Spatial option and would require only minor updates to the table and the SQL queries to insert and retrieve the geographic data.

Having reviewed the database structure, let's revi ew the Java cod e that runs on the application server and queries the data.

Querying Data in an XML Format In order to r etrieve the data in the hotels table, the ACME application uses a Java ser vlet running in Oracle Containers for Java (OC4J), the enterprise Java e ngine inside Oracle Application Server. The Java servlet, XmlServlet, itself merely acts as an intermediary between the Web client and the code that queries the database. As the end user clicks on the "Find Hotels" button, an Ajax call is made to the servlet requesting the geographic hotel data in XML from the database. How does this work? The servlet processes the query arguments sent from the Web client and then passes it to another Java class, XmlGenerator, whose role is to connect to the database using JDBC and return a stream of XML data based on the query sent by the servlet. The XML content is automatically generated using Oracle XML DB. Here is closer look at the architecture of the applications where you can see how the Web client communicates with the XmlServlet which in turn uses the XmlGenerator class to extract the hotels data in XML.



Figure 6: The Acme Hotel application architecture exposed

As you can see, the Web client makes a call to the servlet using the Google Maps API GDownloadUrl( ) function with parameters such as whether a pool or beach access is required or the requested price r ange. Incidentally, GDownloadUrl( ) uses XMLHttpRequest under the covers to issue the Ajax request. This function serves a s a convenience function as it also does browser compatibility checks as well as error processing, which you would have to write in your own code if using XMLHttpRequest natively. Let's examine the code in the Java servlet that processes this request.

The Java S ervlet: XmlServlet The Java servlet, XmlServlet, is a simple servlet that responds to the Ajax Http request for hotel data and then uses another Java class, XmlGenerator, to query the database using the XML DB option. Let's examine the code a bit closer.

import import import import

java.io.IOException; java.io.PrintWriter; javax.servlet.*; javax.servlet.http.*;

import com.xml.XmlGenerator; public class XmlServlet extends HttpServlet { private static final String CONTENT_TYPE = "text/xml"; public void init(ServletConfig config) throws ServletException { super.init(config); } public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType(CONTENT_TYPE); PrintWriter out = response.getWriter(); XmlGenerator xgen = new XmlGenerator(createWhereClause(request)); // Return XML data as an Http Response String xmlResponse = xgen.getXmlResponse(); out.println(xmlResponse); out.close(); } public String createWhereClause(HttpServletRequest request) {



// Create where clause based on request arguments ... return wc; } } Aside from being a pretty typical Java servlet, you'll notice that the CONTENT_TYPE is is set to "text/xml". This is required as the servlet will be responding purely in XML, as opposed to HTML. Further on in the code, you see the doGet method you see that the class XmlGenerator is instantiated with the returned results which are assembled in a local method, createWhereClause( ). Instead of showing every line of this method, it's sufficient to know that it si mply transfor ms the HttpRequest ar guments from a form of:

"?pool=yes&beach=yes&price=400" into a whereclause String that can be appended to the database query, such as:

"WHERE BEACH = 'Y' AND POOL = 'Y' AND AVE_PRICE_USD < 400"

The XmlGenerator Class The code for the XmlGenerator class is as follows:

import import import import

java.sql.Connection; java.sql.DriverManager; java.sql.SQLException; oracle.xml.sql.query.OracleXMLQuery;

public class XmlGenerator { public String xmlResponse; // Customize as needed private static String jdbcURL = "jdbc:oracle:thin:@your-host:1521:orcl"; private static String user = "geo"; private static String passwd = "geo"; public XmlGenerator() { } public XmlGenerator(String wc) { String tabName = "hotels"; Connection conn = null; try { DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); conn = DriverManager.getConnection(jdbcURL, user, passwd); } catch (SQLException e) { e.printStackTrace(); } String strqry = "select name, description, thumb_img_url, web_address, address, city, postal_code, state, country, region" + "stars, beach, ave_price_usd, pool, latitude, longitude from " + tabName + wc; OracleXMLQuery qry = new OracleXMLQuery(conn, strqry); // Structure the generated XML document qry.setRowIdAttrName(null); qry.setMaxRows(50); qry.setRowsetTag("hotels"); // set the root document tag qry.setRowTag("hotelinfo"); // sets the row separator tag // Get the XML document in string format String xmlString = qry.getXMLString(); setXmlResponse(xmlString); } public void setXmlResponse(String xmlResponse) { this.xmlResponse = xmlResponse; }



public String getXmlResponse() { return xmlResponse; } } The code in the XmlGenerator class is fairly standard JDBC code that uses the XML DB option. Notice that in addition to the standard JDBC packages the XML DB package , oracle.xml.sql.query.OracleXMLQuery, is also imported. This class follows as standard Java Beans methodology and has a single property: XmlResponse with its getter and setter methods at the end of the class. Incidentally this property will contain the XML response retrieved from the database. After setting up the JDBC connection, the key piece of code is:

OracleXMLQuery qry = new OracleXMLQuery(conn, strqry); This creates a new OracleXMLQuery object with the connection and query string specified. Once created, several other settings can be applied including the RowsetTag, which assigns "hotels" as the root element for the XML data. The RowTag is set to "hotelinfo" and serves as the repeating element for each returned record. The resulting XML data has the following format:

ACME Luxury Acapulco The ACME Luxury Acapulco is a luxurious beautiful beach front with all the amenities that a 4 star hotel is expected to have. images/acapulco-sm.jpg http://acmeluxuryhotelacapulco-bogus.com
2322 La Sienna
Acapulco 38432 MX 4 Y 375.95 Y 16.850548 -99.920654 ACME Luxury San Francisco ... ... ... ... Now that we've reviewed the Java servlet and its XML generation code, let's turn to the HTML Web client, which has the job of displaying a Google Map along with processing the form values and issuing the Ajax request to the servlet.

The HTML Web Client: acme_hotels.html Recall that the client Web page essentially contains just an HTML form and a Google Map. The form contains checkboxes, a dropdown list, and a submit button. The Google Map starts out in a neutral state with a view of the entire world. Before examining the form code, let's examine the small bit of JavaScript code to render the initial Google Map.

// Initialize map - called when page is loaded function initMap(){ if (GBrowserIsCompatible()) { map = new GMap2(document.getElementById("map")); map.setCenter(new GLatLng(2, -55), 1); map.enableScrollWheelZoom(); map.setMapType(G_HYBRID_TYPE); map.addControl(new GLargeMapControl()); map.addControl(new GMapTypeControl()); } }



As you can see, this code is encapsulated in a function called initMap( ) also happens to specified to be executed when the page loads using:

; Incidentally the GUnload( ) function is a Google Maps provided function that is used to clean up any in memory objects when the user navigates away from the page. As you can see in the initMap( ) function, a new GMap2 object is created. The argument to its constructor is the id of an existing HTML
tag that is defined lower in the body of the page as:

...
As you can see, the map's dimensions are defined in the container DIV's style attribute. Returning back to the initMap( ), the rest of lines are merely the options for the map. For this map the center is set to a latitude of 2 and longitude of -55 and the mousewheel zoom option is enabled. The initial map type is G_HYBRID_TYPE, which mashes both satellite images with geographic data. Finally, two controls are added to the map; they are GLargeMapControl - which is a large version of the zoom control, and a GMapTypeControl, which allows the user to toggle between the different map types ( Map, Satellite, Hybrid, Traffic ...). Once the map is initialized, it will render a generic map and the Web page will wait for further input. At this point users can enter search criteria in the form: Has pool:

Beach access:

Price:

anyprice

Region:

anyregion

FindHotels

which is positioned above the map. The code for this form is straight forward:

Has pool: Beach access: Price: Region:

The form consists of four different input values; pool, beach, price , and region, and each of these input fields has a corresponding JavaScript function attached to it and will be executed when either the checkboxes are checked or when the select menus are changed. Here's an example of one of the input field listener functions, togglepool( ).

function togglePool() { if (document.getElementById('pool').checked) { haspool = true; } else { haspool = false; } } The togglePool( ) function's job is to check whether the pool input field is either checked or not checked and update a corresponding boolean global variable, haspool, accordingly. For the drop down select menus, the code simply applies the value of the select menu to a global variable for the hotel pri ce, hotelprice.



function togglePrice() { hotelprice = document.getElementById('price').value; } Launching a query. Once a user has specified a query criteria by setting the input fields, he/she can then execute a query by clicking on the "Find Hotels" button which launches executes the showHotels( ) function. This function clears any previous maps using clearMap( ) and calls loadHotelLocations( ) .

function showHotels(){ clearMap(); loadHotelLocations(); } The clearMap( ) function uses the Google Maps API call, map.clearOverlays( ) , to clear out any existing map overlays. As you'll r eview shortly, Google Maps overlays are how you add extra visual content on top of a map, such as drawing a line or adding a popup window, which is essentially what this application does. The loadHotelLocations( ) function is the real workhorse of the application. It assembles the query parameters specified on the form and then makes the Ajax call using the Google Maps API call, GDownloadUrl( ) to download an XML stream containing the corresponding hotel information based on the query. Here is the entire function: function loadHotelLocations() { var ajaxParms = generateRequestArgs(); // Make Ajax request to get Hotel data using Google Maps GDownloardUrl() GDownloadUrl("/ACME-Hotel-Locator/xmlservlet" + ajaxParms, function(data, responseCode) { var xml = GXml.parse(data); var markers = xml.documentElement.getElementsByTagName("hotelinfo"); if (markers.length == 0){ alert("Your query returned no results. Please broaden your search criteria and try again."); } else { for (var i = 0; i < markers.length; i++) { var point = new GLatLng(parseFloat(markers[i].getElementsByTagName("LATITUDE")[0].firstChild.nodeValue), parseFloat(markers[i].getElementsByTagName("LONGITUDE")[0].firstChild.nodeValue)); var var var var var var var

hotelName = markers[i].getElementsByTagName("NAME")[0].firstChild.nodeValue; hotelDescription = markers[i].getElementsByTagName("DESCRIPTION")[0].firstChild.nodeValue; hotelStars = markers[i].getElementsByTagName("STARS")[0].firstChild.nodeValue; hotelImg = markers[i].getElementsByTagName("THUMB_IMG_URL")[0].firstChild.nodeValue; hotelWebAddress = markers[i].getElements ByTagName("WEB_ADDRESS" )[0].firstChild.nodeValu e; hotelStars = markers[i].getElementsByTagName("STARS")[0].firstChild.nodeValue; avgPrice = markers[i].getElementsByTagName("AVE_PRICE_USD")[0].firstChild.nodeValue;

// Generate rating stars HTML DIV var ratingHtml = generateRatingHtml(hotelStars); point.name = "
" + hotelName + "

" + ratingHtml + "
Ave. Price: $"+ avgPrice + " (USD)
Book it!
" + hotelDescription + "

"; mapMarkers.push(createHotelMarker(point)); } showMap(); } }); }

The first bit of code to notice is the call to a function generateRe questArgs( ) . This function constructs a query string and returns it in the form of "?pool=true&beach=true&price=400®ion=europe" and is based on the values of the JavaScript global variables pool, beach, and so on. This is assigned to a string, ajaxParms, which is then appended to the first argument of GDownloadUrl( ) , which is a URL pointing to the Java Servlet, "/ACME-Hotel-Locator/xmlservlet ", that will return the XML data:

GDownloadUrl("/ACME-Hotel-Locator/xmlservlet" + ajaxParms , function(data, responseCode) {...} ); The second argument in the GDownloadUrl( ) call is a callback function whose role is to pr ocess the incoming data re ceived from the Ajax request. As you can see in the second argument/callback function, the first parameter is the actual XML data returned from the query. In the body of the callback function, we call GXml.parse(data), to parse the incoming XML data and make it available as a Document Object Model (DOM) object so JavaScript can be used to iterate through the values and manage the data.

var xml = GXml.parse(data); var markers = xml.documentElement.getElementsByTagName("hotelinfo");



After establishing a handle to the DOM object representing the XML data, a variable named markers is assigned the returned value from, getElementsByTagName ( ) which is an array of elements corresponding to the parent tag "hotelinfo". If there is data retrieved from the query, the code will iterate through the different elements and add points (markers) to the map for each corresponding hotel. Here is the looping code again that does this: for (var i = 0; i < markers.length; i++) { var point = new GLatLng(parseFloat(markers[i].getElementsByTagName ("LATITUDE")[0].firstChild.nodeValue), parseFloat(markers[i].getElementsByTagName("LONGITUDE")[0].firstChild.nodeValue)); var hotelName = markers[i].getElementsByTagName("NAME")[0].firstChild.nodeValue; var hotelDescription = markers[i].getElementsByTagName("DESCRIPTION")[0].firstChild.nodeValue; var hotelStars = markers[i].getElementsByTagName("STARS")[0].firstChild.nodeValue; ... // Generate rating stars HTML DIV var ratingHtml = generateRatingHtml(hotelStars); point.name = "
" + hotelName + "

" + ratingHtml + "
Ave. Price: $"+ avgPrice + " (USD)
Book it!
" + hotelDescription + "

"; mapMarkers.push(createHotelMarker(point)); }

Upon closer inspection, you see that a new point object is created using the Google Maps GLatLng class constructor which takes a latitude and longitude values as arguments. These values are extracted from the markers array using the DOM getElementsByTagName call:

markers[i].getElementsByTagName("LATITUDE")[0].firstChild.nodeValue This value is also parsed into a JavaScript floating point number using the native JavaScript parseFloat( ) before being sent as an argument to the GLatLng constructor. Once the new GLatLng point has been created, the remaining step is to add the additional hotel information to the point's name field. This is done by extracting the other fields such as name , description and so on from the XML stream using the same DOM method and then concatenating them all together in HTML to the name field. You may also notice the ratingHtml value is generated using an additional function, generateRatingHtml( ) , which takes the number of stars returned from the XML stream and generates a small portion of HTML that has graphical stars.

function generateRatingHtml(stars){ var starsHtml = "
"; for (i=0; i<5; i++){ if (i < stars ) starsHtml += ""; else starsHtml += ""; } starsHtml += "
"; return starsHtml; } Returning back to the looping code, once the entire point.name field has been filled with a hotel's information, a new Google Maps marker (GMarker) is created from the new point using the function createHote lMarker( ) and pushed onto a global mapMarkers array. Incidentally the createHotelMarker( ) function is one of the most important functions in the application in that it creates a new marker (GMarker) based on the point object provided, and establishes a "click" listener that listens for mouse cl ick events on the marker. (Note: creating your own custom markers with a different icon is relatively easy as well, but is beyond the scope of this article.)

function createHotelMarker(point) { var marker = new GMarker(point); GEvent.addListener(marker, "click", function() { var opts = {pixelOffset:new GSize(32,5), maxWidth:280}; marker.openInfoWindowHtml( point.name, opts); }); return marker; } For every mouse click on a marker, a new window ( GInfoWindow) is popped up using marker.openInfoWindowHtml( ) . Notice in the call to openInfoWindowHtml( ) , the first argument is the point.name property that contains the HTML content for a hotel. The second argument is just an options argument that specifies that the window should be no larger than 280 pixels wide and that the window should appear offset from the



marker horizontally by 32 pixels and vertically by 5 pixels. After the global marker array, mapMarkers, is filled with all the hotel information, it can then used to display the hotel points on the map using the function showMap( ) . Here is the code for showMap( ):

function showMap(){ // Find boundary points of hotel location var bounds = new GLatLngBounds(); for (var i=0;i < mapMarkers .length;i++) { map.addOverlay(mapMarkers[i]); bounds.extend(mapMarkers[i].getPoint()); } // Reset center and zoom level based on queried hotel locations map.setCenter(bounds.getCenter()); map.setZoom(map.getBoundsZoomLevel(bounds)); } The function showMap( ) function loops through the mapMarkers array and adds them to the map (as overlays). It also determines the outer bounds of the markers so the map can re center and zoom appropriately.

Conclusion As you can see, the building of this application was relatively straight-forward. The two Java clas ses, XMLGenerator and XMLServlet, perform the tasks of responding to the Ajax request from the Web page client and then transforming it into a database query. The results are then streamed back over HTTP in an XML stream by XMLServlet allowing the Google Maps application in the Web page to easily parse the XML and display it on the map. That's it! The generation of the XML data was very easy thanks to Oracle XML DB. Creating the Java classes as well as the HTML Web page client was also easily created in Oracle JDeveloper. Chris Schalk is a Google Developer Advocate and works to promote Google's Ajax APIs and technologies. Before joining Google, Chris was a Principal Product Manager and technology evangelist at Oracle in the Java development tools group. Chris also recently co-authored the book JavaServer Faces, The Complete Reference (McGraw-Hill/Osborne).

	" + ratingHtml + " Ave. Price: $"+ avgPrice + " (USD) Book it!
" + hotelDescription + "

Integrating Google Maps With Oracle

Recommend Documents