DOWNLOAD Oracle Database (Enterprise Edition install option) Sample Code

TAGS spatial, ajax, webdev, All

Create a Map Mashup with Oracle Spatial

Learn how to mash-up any Oracle Spatial-administered data and give your end users the same enhanced mapping GUIs offered by Yahoo and Google.

Published March 2007

With leading internet portals offering extremely detailed mapping services, end users now expect a lot when encountering a digital map—and with it, the bar for developing these types of applications has raised considerably.

Fortunately, many of these mapping providers are now empowering developers to reuse the same visual rendering capabilities through Web services, a process which has led to the term "mashup"—an interface comprising application data leveraged with Web services. As you'll see, a series of Oracle products—namely, Oracle Spatial (a feature of Oracle Database Enterprise Edition) and Oracle Application Server 10g—provide the ideal building blocks for creating sophisticated Web-enabled map GUIs or map mashups.

Oracle Spatial Foundations

The very nature of spatial data creates a unique need to store and manipulate this information in its original form. Just as an Oracle database has the capacity to store binary files via a BLOB, Oracle Spatial allows organizations to maintain the intrinsic value of geographical data natively.

Because data in most geographical applications entails fulfilling specific business logic requirements, such as locating the shortest route to a particular destination or calculating a delimiting area between certain coordinates, it is pivotal to maintain the necessary properties to perform such calculations or queries with the utmost accuracy. To this end, Oracle Spatial offers a series of methods, operators, functions, and procedures for just this purpose.

Table 1 lists a series of elements pertaining to Oracle Spatial.

This table contains only a fraction of the many functionalities available in Oracle Spatial, but it should be sufficient to illustrate the purpose of storing spatial data natively and get you through creating your own mashup application that uses some of these same features.

Having taken a look at what Oracle Spatial has to offer in terms of archiving geographical data, let's switch gears and explore what it is that mapping services around the internet can do to leverage such information.

Web-Based Mapping Services: Google, Yahoo, and MSN

Until recently, most map applications required you to store hundreds or thousands of pixelated images to be aggregated in an on-demand fashion for end users to see—that is to say, each movement in a map application required a complete client-server round-trip to generate a newly minted image, a process that not only resulted in a very poor user experience but also required a tremendous amount of server-side resources to stitch together the appropriate map pixels requested by a user.

As a value-added service, search portals such as Google, Yahoo, and MSN have made significant investments in developing a new generation of map applications. Behind these efforts are two approaches that are relatively new to development circles: Ajax and Web services.

What Ajax brings to these mapping applications is an extremely responsive browsing experience. While the underlying need to make client-server round-trips to update a map image still applies, the manner in which these updates are done is extremely efficient. Ajax allows for what is otherwise known as "out-of-band" calls, a fancy name for updating a Web page with newly acquired server-side data without the user experiencing a complete screen refresh. In a map's case, the net effect is a gradual loading of pixels based on a user's requests.

On the other hand, this functionality would not be of much use to us if it were exclusively bound to a portal's site, but here is where Web services come into the picture. Through Web services, it's possible to tap these Ajax-enabled mapping interfaces and mash them up with your own application data.

Now let's take a look at the technical architecture behind a mashup.

Mashup Architectures: One, Two, and Three Tiers

There can be various layers to a mashup application, but the end result in all variations unequivocally contains Web services API calls intertwined with your own data. In its simplest form, a one-tier mashup is a static page with a series of Web services API calls that are later rendered on a user's screen.

In a two-tiered design, although the application's display is still dictated by Web services API calls, the composition and order of such calls is done dynamically with user input—via a standard server-side application such as a JSP or servlet. Similarly, a three- tiered mashup consists of using this same server-side approach but enriching the programming sequence with information extracted from a database. (See Figure 1.)

Figure 1 Three-tiered mashup architecture

As you can see, a fundamental step to creating an effective map mashup is knowing the ins and outs of the Web services API that will eventually be rendered on a user's screen. Not to underestimate the process of extracting and preparing the geographical data to be plotted in a mashup application, but the end result is a reflection of how well you combine and understand the behaviors of each service call.

Now that we have covered the basics, let's get started with the real thing.

Your First Mashup: Ajax-Enabled Maps with Oracle-Administered Data

Our map mashup will be designed to display the U.S. offices of our fictional organization, ACME Corp. The application will allow users to make an initial selection and later observe the nearest offices to this first location. All map points will be stored in an Oracle database, with distance calculations made via Oracle Spatial functions and extraction done through a Java application. The latter will also be enabled to use Ajax type requests such as those used by mapping Web services.

Accompanying your Java application will also be a series of JavaScript functions, each having all the necessary Web services API calls to display your map mashup using either Google Maps or Yahoo Maps. Figures 2 and 3 illustrate a snapshot of your map mashup using both Google and Yahoo mapping Web services, respectively.

Figure 2 Map mashup using Google Maps

Figure 3 Map mashup using Yahoo! Maps

Having taken a peek at the final outcome, let's get a feel for the actual database schema used for your mashup and the application code used for accessing such information.

The initial mapping data will be imported from a flat file, which will first be placed in a temporary table—to be transformed into an Oracle Spatial format—and finally placed in the main data table using an INSERT INTO statement. (See sample code zip.) Listing 1 contains the most relevant sections.

Listing 1 Database schema structures for mashup

-- Flat file format
Chicago|IL|41.86|-87.71
Houston|TX|29.84|-95.36
Dallas|TX|32.73|-96.855
Miami|FL|25.72|-80.24

-- Main data table 
CREATE TABLE offices (
  city      VARCHAR2 (30),
  state     VARCHAR2 (30),
  location  SDO_GEOMETRY
);

-- Insert statement from office_load table
INSERT INTO offices
  SELECT city,
         state,
         sdo_geometry(2001,8307,SDO_POINT_TYPE(longitude,latitude,null),
                      null,null)
  FROM offices_load;

Notice that the flat file is in the form CITY|STATE|LONGITUDE|LATITUDE and that your main table uses the SDO_GEOMETRY data format. In passing your data from the flat file state to the main table, we need to construct an SDO_GEOMETRY structure, which is exactly the purpose of the last INSERT statement.

This last code snippet selects the map point data from an intermediary table onto which the flat file was loaded—offices_load—and inserts its contents into the main offices table. The only parameters that will probably seem strange in this INSERT statement are the 2001 & 8307 codes, along with the various nulls next to SDO_GEOMETRY. The first two numbers are codes used by Oracle Spatial to specify the type of data being inserted. In this case, 2001 indicates a two-dimensional coordinate and 8307 corresponds to WGS 84 (World Geodetic System) format, values which correspond naturally to our longitude/latitude data, while the various null statements are simply options not used by our application data.

Additional steps for preparing your mashup data include updating the default USER_SDO_GEOM_METADATA table to include our newly added offices table and creating a spatial index for this same table. As mentioned previously, the corresponding code can be obtained in the Resources section.

Once your data model is in place, you can start making spatial queries from your Java application. Listing 2 shows a spatial query like the one embedded in the Java application:

Listing 2

# Read about fixtures at http://ar.rubyonrails.org/classes/Fixtures.html
SELECT  city,
        state,
        v.Y latitude,
        v.X longitude,
        SDO_NN_DISTANCE (1) distance
        FROM
        OFFICES O,
        TABLE(SDO_UTIL.GETVERTICES(O.location)) v
 WHERE
  SDO_NN(
                        O.location,
                        (SELECT location FROM offices WHERE city = 'Houston'),
                        'sdo_num_res=6 unit=mile',1 ) = 'TRUE'
  ORDER by DISTANCE;

-- Results 
CITY            STATE         X           Y          DISTANCE
-------------- ------------   ---------   ---------- ----------
Houston          TX           29.84       -95.36              0
Dallas           TX           32.73       -96.855    217.859943
Topeka           KS           39.02       -95.71     633.08558
Atlanta          GA           33.76       -84.375    700.036941
Albuquerque      NM           35.1        -106.16    726.916231
Omaha            NE           41.27       -95.97     788.754057

The query in Listing 2 obtains the five nearest offices to the city of Houston, including the city/state pair, longitude/latitude coordinates, and distance in miles to the host office. As far as the query embedded in our Java application, it will be coded to receive both the host city and the number of offices to be plotted, from values selected in HTML lists presented to an end user.

These selection lists, while written in plain HTML markup, will be wired to perform Ajax type requests to our Java application. Listing 3 contains one of the aforementioned HTML lists and the JavaScript functions used to perform such calls.

Listing 3

<script type="text/javascript" src="prototype.js"></script>
<script type="text/javascript">
// Method invoked when user changes letter range
function updateOffices()        {
  // Declare variables for HTML select lists
  var city;
  var nearestNumber;
  // Browser detection IE
  var ie = (document.all) ? true : false;

  // Validate current selection list and assign based on browser
  if(ie) {
    // Assign values
    city = this.city.options[this.city.selectedIndex].text;
    nearestNumber = this.nearestNumber.options[this.nearestNumber.selectedIndex].text;

   } else {
    // Firefox-Mozilla
    // Assign values
    city = $F('city');
    nearestNumber = $F('nearestNumber');
  } // end assignment

  // Define URL for obtaining results
  var url = 'oracle_mashup.jsp';
  // Prepare parameters to send
  var pars = 'city=' + city + '&nearestNumber=' + nearestNumber;
  
  // Make web service Ajax request via prototype helper,
  // upon response, call displayOffices method
  new Ajax.Request( url, { method: 'get', parameters: pars, onComplete: displayOffices});
}
</script> 
<select id="city" onchange="updateOffices()">
   <option>San Diego  </option>
   <option>Los Angeles </option>
   <option>San Francisco </option>
   <option>Seattle </option>
   <option>Chicago </option>
   <option>Houston </option>
   <option>Dallas </option>
   <option>Miami </option>
   <option>New York </option>
</select>

Let's start at the bottom of Listing 3, where we have the HTML list. Each option in this list corresponds to cities archived in your database. More importantly, however, notice the JavaScript event onchange associated to the list. This indicates that any selection change should trigger the JavaScript function updateOffices().

If you move to the top, you will see the start of the updateOffices() method. Inside this particular method, the first step is extracting the variables selected in the HTML lists,assigning each value to the variables city and nearestNumber, respectively. Additionally, you define a URL variable with the value oracle_mashup.jsp, which would correspond to the actual deployment address for your Java application.

Armed with these input variables, you are ready to perform an out-of-band call to obtain a list of cities from our Java application, a process that falls to the last line:

new 
Ajax.Request( url, { method: 'get', parameters: pars, onComplete: displayOffices}); 

The Ajax.Request statement is a helper method included in Prototype—a framework for Ajax designs—the latter of which was imported as a JavaScript library at the top of the page.

The input for Ajax.Request includes a URL for indicating the request address; pars, which includes the desired host city and number of nearest offices; and displayOffices for defining the JavaScript method to be executed once the out-of-band call completes. The important thing to realize about this last process is that the method called upon completion—displayOffices—will receive a fresh set of map points each time a user modifies an HTML list, which in turn will make it the preferred choice for including all our mapping Web services calls.

Now that you understand how and where each map point will come from, let's take the final step—integrating each data set with Web services API calls from Google Maps and Yahoo Maps.

Integrating with Google Maps. The first thing you will need for working with the Google Maps API is an access key, which you can obtain free of charge from the following address: www.google.com/apis/maps/signup.html. The access key is an alphanumeric string that needs to be placed in a JavaScript statement in your application. If you are following along using the sample code, substitute this key accordingly, as each key is created to operate on a unique Web site.

Like any other programming API, Google Maps has many classes and functions that require extensive time to master and that we won't even attempt to explain here, so bear in mind that we will use only the minimum to plot our map points. That said, take a look at Listings 4 and 5, which show the displayOffices function version for generating a Google Maps interface and an example of its input, respectively. Note that this last input corresponds to the payload received from the out-of-band call mentioned earlier.

Listing 4 Function using Google Maps API for GUI rendition

function displayOffices(request)      {
   // Get office values
   officesToDisplay = request.responseText;
   // Split offices to display and place in array
   var officesSplit = officesToDisplay.split("[");

   if (GBrowserIsCompatible()) {
        var map = new GMap2(document.getElementById("map"));
        // Place center of the map near middle latitude/longitude for U.S
        // Zoom level 4
        map.setCenter(new GLatLng(37.50,-96.94), 4);

       // Loop through office array
        for (i=1; i < officesSplit.length; ++i) {
           // Split once more to get city, state, latitude/longitude, distance
           var officeData = officesSplit[i].split(",");
           var cityName =  officeData[0];
           var stateName =  officeData[1];
           var latitude =  officeData[2];
           var longitude =  officeData[3];           
           var distance =  officeData[4].replace("]","");

           var point = new GLatLng(latitude,longitude);
           map.addOverlay(createMarker(point,cityName,stateName,distance));

       } // end for office array


      } // end if GBrowserIsCompatible

  } // end displayOffices

 // Creates a marker at the given point with the given number label
   function createMarker(point, cityName,stateName,distance) {
    // Check for city origin if distance 0
    if(distance == 0){
      var baseIcon = new GIcon();
      baseIcon.shadow = "http://maps.google.com/mapfiles/kml/pal2/icon10s.png";
      baseIcon.iconSize=new GSize(32,32);
      baseIcon.shadowSize=new GSize(56,32);
      baseIcon.iconAnchor=new GPoint(16,32);
      baseIcon.infoWindowAnchor=new GPoint(16,0);
      var icon = new GIcon(baseIcon);
      icon.image = "http://maps.google.com/mapfiles/kml/pal2/icon10.png";
      var marker = new GMarker(point,icon);
      GEvent.addListener(marker, "click", function() {
      marker.openInfoWindowHtml("" + cityName + "," + stateName + "
");
      });
      return marker;
    } else {
      var marker = new GMarker(point);
      GEvent.addListener(marker, "click", function() {
      marker.openInfoWindowHtml("" + cityName + "," + stateName + "
 Distance to selected office: " + distance + " miles.");
    });
     return marker;
    }

  }

Listing 5 Out-of-band payload received from Java application

[Dallas,TX,32.73,-96.855,0][Houston,TX,29.84,-95.36,217][Topeka,KS,39.02,95.71,438]
[Albuquerque,NM,35.1,-106.16,558][Omaha,NE,41.27,-95.97,590]
[Denver,CO,39.75,104.98,662] 

The displayOffices function starts by splitting its input and placing these values in a separate array. We do this because of the incoming payload format, which is illustrated in Listing 5. Here once again, although you have total control for using any possible payload structure in your Ajax/Java application response, this format choice was made for simplicity reasons. You could have easily used a standardized format such as XML or JSON (JavaScript Object Notation), but this would have required additional code to process, hence the choice for simple brackets and commas.

With each map point split and placed in a JavaScript array, you will turn to specific Google Maps functions. First, check if the browser environment will be capable of displaying Google Maps with the GBrowserCompatible object. Once this is confirmed, create a GMap object to be displayed in the HTML <div> id by name map. If you look closely at the HTML application page, you will notice the declaration <div id='map'></div>, which is where the map GUI will be rendered because of these instructions.

Next, we assign the center and zoom level for this map object, assigning a longitude/latitude coordinate to approximately the middle of the U.S with a zoom level of 4, netting us a complete view of North America, all of which is followed by a loop on our map point array.

On each iteration of the aforementioned loop, we again split each value into separate variables for city, state, longitude, latitude, and distance, followed by an invocation of the auxiliary function createMarker with these values. This last function also contains a series of Google Maps API objects, which achieve the following:

• Generate a text window with the city, state, and distance to the selected office for each map point
• Create a custom icon to be displayed on the host city
• Allow a click on a map point to reopen its text window

That does it, as far as using the Google Maps API is concerned. Recall that Figure 2 shows a rendition of this particular code, and also that you can easily move around the generated map by simply dragging your mouse pointer, with each movement gradually loading the required pixels via Ajax calls from Google servers.

Integrating with Yahoo Maps

Using the Yahoo Maps API is strikingly similar to using the Google Maps API. First, you will need to obtain a freely distributed access key here, which you need to insert into your own mashup in a JavaScript statement.

Given our design, the only thing we need to do then is modify the JavaScript displayOffices function to use the Yahoo Maps API. Listing 6 illustrates the Yahoo Maps version for displayOffices.

Listing 6 Function using Yahoo Maps API for GUI rendition

function displayOffices(request)      {
   // Get office values
   officesToDisplay = request.responseText;
   // Split offices to display and place in array
   var officesSplit = officesToDisplay.split("[");

   // Place center of the map near middle latitude/longitude for U.S
   var usCenter = new YGeoPoint(37.50,-96.94);
   // Create a map object
   var map = new YMap(document.getElementById('map'));
   // Display the map centered to Zoom 14
   map.drawZoomAndCenter(usCenter, 14);

   // Loop through office array
    for (i=1; i < officesSplit.length; ++i) {
        // Split once more to get city, state, latitude/longitude, distance
        var officeData = officesSplit[i].split(",");
        var cityName =  officeData[0];
        var stateName =  officeData[1];
        var latitude =  officeData[2];
        var longitude =  officeData[3];
        var distance =  officeData[4].replace("]","");

        var point = new YGeoPoint(latitude,longitude);
        map.addOverlay(createMarker(point,cityName,stateName,distance));

      } // end for office array

  } // end displayOffices

Since we are dealing with the same function and it receives the same type of payload illustrated in Listing 5, the logical sequence is also in line with the one used in the Google Maps version. The only discrepancies you will find are regarding the API syntax. For example, Google uses the GMap2 object to define a top-level map, but our Yahoo version uses YMap. Similarly, Yahoo uses the YGeoPoint object to define a map point, but Google opts for GMarker.

Due to these similarities, we won't go through the logical sequence again, but recall that Figure 3 displays the GUI for this code. In a similar fashion to Google Maps, you can also navigate around the generated map using your mouse pointer, with each movement gradually loading the required pixels via Ajax calls from Yahoo servers.

Conclusion

Although geographical information systems are still built around the same raw data as their yesteryear counterparts, the most recent generation has made considerable advances in terms of graphical interfaces, and what's more, many of them have made their way to the general public free of charge via the Web.

But as it turns out, enterprise developments of the same kind don't have to create developments from scratch to play catch-up with these more-sophisticated implementations— as is generally the case in enterprise software. As you have seen, using Web services it is possible to mashup any Oracle Spatial-administered data using Oracle Application Server and offer your end users the same enhanced mapping GUIs offered by major internet portals.