Any database application that contains location data, such as addresses or geographic coordinates, may benefit from displaying this data on a map. It may be of further benefit to allow users to interact with this data by associating it with other data or even performing geometric calculations against it.

In this chapter, you will explore some options to free the geographic data from its confines of the database tables and render it in useful ways on interactive maps. To accomplish this, you will look at a number of tools and techniques available to you in APEX, the Oracle Database, and some publicly available APIs.

You will learn how to turn address data into geographic coordinates and how to use these coordinates to display addresses on a map. Further, you will take a look at various mapping APIs and how to use Oracle’s powerful spatial features to find relationships between geographic data points.

Geocoding

Geocoding is the process of turning addresses from plain text into coordinates. To do this, you will make use of some publicly available web services that do the heavy lifting for you. You can simply call such a web service with an address, and the service will return a cleaned-up version of this address, along with other data elements, including coordinates. You can then store these coordinates in the database and visualize them on a map.

Coordinates

The coordinates used for mapping are simply numeric values for latitude and longitude. There are a number of ways of expressing coordinates. Two notations are worth noting.

• DMS: Degree Minutes Seconds
• DD: Decimal Degrees

Oracle’s APIs use DD notation. Latitude values range from -90 to 90, and longitude values range from -180 to 180; both can go up to 15 decimal places. A four-decimal coordinate is accurate to about 11 meters, and a five-decimal coordinate is accurate to about 1 meter. The accuracy can vary with the position on the globe and the coordinate system being used (see “SDO_GEOMETRY” later in the chapter).

Web Services

There are a number of available web services that provide geographic data. They can take various types of queries as input and return detailed geographic data in the form of XML or JSON. Depending on which service is being used, the input can range from a simple address or location to an actual SQL query, as in the case of Yahoo. Let’s take a look at some of these services.

Yahoo Web Services

Yahoo provides a public web service for geocoding. As input, the user provides a query against one of the available Yahoo tables. The geocoding table at Yahoo is called geo.placefinder. The following URL calls the Yahoo web service and passes it the query against the geo.placefinder table. The web service then responds with the query results in XML format.

https://query.yahooapis.com/v1/public/yql?q=select * from geo.placefinder where text="1400 6th Ave, Seattle, WA 98101"

The default output is XML. Here’s an example:

<query xmlns:yahoo="http://www.yahooapis.com/v1/base.rng" yahoo:count="1" yahoo:lang="en-US">
    <results>
        <Result>
      <quality>87</quality>
      <addressMatchType>POINT_ADDRESS</addressMatchType>
      <latitude>47.610378</latitude>
      <longitude>-122.333832</longitude>
      <offsetlat>47.610512</offsetlat>
      <offsetlon>-122.333511</offsetlon>
      <radius>400</radius>
      <name/>
      <line1>1400 6th Ave</line1>
      <line2>Seattle, WA 98101-2318</line2>
      <line3/>
      <line4>United States</line4>
      <house>1400</house>
      <street>6th Ave</street>

To receive a JSON result, add the format parameter.

https://query.yahooapis.com/v1/public/yql?format=json&q=select * from geo.placefinder where text="1400 6th Ave, Seattle, WA 98101"

For further usage details, see the following:

 https://developer.yahoo.com/yql/guide/usage_info_limits.html

Yahoo also provides a developer console that you can use to test and explore the available services. You can find that console at https://developer.yahoo.com/yql/console.

Google Web Services

Google’s geocoding web service works similarly to that of Yahoo, except that it doesn’t use a query syntax. For example, here is a URL invoking the web service on an address in Seattle:

http://maps.googleapis.com/maps/api/geocode/xml?sensor=false&address=1400 6th Ave, Seattle, WA 98101

As you can see by the URL path, the result would be returned in XML. To get a JSON result, simply substitute json for xml in the path.

The web service call responded with the following XML:

<GeocodeResponse>
<status>OK</status>
        <result>
      <type>street_address</type>
      <formatted_address>1400 6th Avenue, Seattle, WA 98101, USA</formatted_address>
      <address_component>
      <long_name>1400</long_name>
      <short_name>1400</short_name>
      <type>street_number</type>
      </address_component>
      <address_component>
      <long_name>6th Avenue</long_name>
      <short_name>6th Ave</short_name>
      <type>route</type>
      </address_component>
      <address_component>
      <long_name>Pike Pine Retail Core</long_name>
      <short_name>Pike Pine Retail Core</short_name>
      <type>neighborhood</type>
      <type>political</type>
      </address_component>
      <address_component>
      <long_name>Seattle</long_name>
      <short_name>Seattle</short_name>
      <type>locality</type>
      <type>political</type>
      </address_component>

A reverse geocoding URL may look like this. Only the latlng parameter is needed.

http://maps.googleapis.com/maps/api/geocode/xml?latlng= 48.858012, 2.294746

The reverse geocoding call responded with a complete address in XML format.

<GeocodeResponse>
<status>OK</status>
        <result>
      <type>street_address</type>
      <formatted_address>8 Avenue Gustave Eiffel, 75007 Paris, France</formatted_address>
      <address_component>

What Are XML and JSON?

Extensible Markup Language (XML) is a plain-text markup language to describe data. It looks a lot like HTML, but the tags describe data field names, which you get to define yourself. The actual data exists between the opening and closing tags, as in <first_name>Christoph</first_name>.

JavaScript Object Notation (JSON) is a lightweight, plain-text data format, similar to XML. It is a way of organizing data in plain text so that JavaScript can easily process it and so that humans can still easily read it. JSON consists of objects, which are mainly name-value pairs enclosed by curly brackets ({ }), and lists of values, which function as arrays and are enclosed with square brackets ([ ]). JSON can get pretty complex when you start nesting objects and arrays. But for these purposes, JSON is quite simple.

The following example is a list of gas stations, each with a number of attributes (ID, brand, latitude, longitude). A representation of this in JSON looks something like this:

{
   "stations":[
      {
         "id":"1",
         "brand":"Shell",
         "lat":32.343214,
         "lng":-97.398834
      },
      {
         "id":"2",
         "brand":"Valero",
         "lat":32.15534,
         "lng":-97.19984
      },
      {
         "id":"3",
         "brand":"Exxon",
         "lat":33.92234,
         "lng":-94.88223
      }
   ]
}

Here you have an object named stations, containing an array of name-value pairs. You are going to generate this JSON string with PL/SQL in your AJAX callback and then pass it to your JavaScript function so that it can be parsed and the results rendered on the map.

Processing XML/JSON Results

Once you have geocoded results, you can process those results in a number of ways. If you receive XML or JSON results, you may want to parse the data and store it. If you use the Oracle Elocation Geocoder APEX plugin, from the Sample Geolocation Showcase sample application, you can simply work off the collection returned by that plugin. The data contained in the collection is already parsed into the address components (for example, street, city, state, and so on).

To decide whether you want to process XML or JSON, you should consider first where the data will primarily be used. If you plan to directly display it on a map, you may want to consider JSON since the map APIs are written in JavaScript, where JSON is easily processed.

If you plan to work with the results in PL/SQL and you are working in Oracle 11g or older, XML may be the better choice because JSON processing requires the help of a third-party package. To find out more, see https://ruepprich.wordpress.com/2013/09/17/geocoding-with-pljson/.

Oracle 12c provides native JSON processing and makes this the clear choice because you can easily use the results in JavaScript and PL/SQL.

If you do not need to process the geocoding results in JavaScript, the Oracle Elocation Geocoder plugin is a good choice since it directly populates an APEX collection, which allows for easy PL/SQL processing.

Oracle Web Services

Oracle also provides geocoding web services. Using them with Application Express is quite easy because Oracle already has a geocoding plugin. That plugin is the Elocation Geocoder plugin used in the sample application Geolocation Showcase of APEX version 4.2.5 and higher. This plugin uses Oracle’s own geocoding service. To use this and other plugins, simply export it from the sample application and import it into your own.

The plugin provides a text field for input and a submit button. On submit, the plugin calls Oracle’s web services and populates a collection with the results, which can then be easily displayed and processed in APEX.

The following is the process for creating a geocoding page with the Oracle plugin:

1. Create a page with a static region (HTML region in versions 4 and earlier).
2. Create search and country text fields.
3. Create a search button.
4. Add a classic report region.
5. Add a dynamic action to call the geolocation service.
6. Add a dynamic action to respond to the completed service call.

The following subsections describe each step in more detail.

Creating a Page

The first step is to create a page with a static region. This is just a basic page, and the static region should be empty at this point in the process.

Creating Search and Country Fields

Create a basic text field called Pn_SEARCH with a source type of Null. The country field (Pn_COUNTRY) is required by the plugin and contains the country abbreviation. For this example, you will just use the hard-coded value of USA, but you can always add an LOV with a list of countries and their respective codes.

Adding a Classic Report

Create a classic report region named Results using a query based on the collection that will be populated by the geolocation dynamic action. The following query maps the generic collection columns to recognizable column names. The values of the columns are determined by the plugin.

select
     nvl(c001, ’N/A’) as street,
     c002 as house_number,
     c003 as postal_code,
     c004 as settlement,
     c005 as builtup_area,
     nvl(c006,c005) as municipality,
     case
       when c011  = ’US’ then c007
       else initcap(c007)
     end as order1_area,
     c008 as side,
     c009 as error_message,
     c010 as match_vector,
     n001 as sequence,
     n002 as longitude,
     n003 as latitude,
     n004 as edge_id
from apex_collections where collection_name = ’GEOCODER_RESULTS’
and n002 is not null and n003 is not null
order by seq_id;

Creating a Search Button

You also need a button called GEOCODE that executes the search. The button uses the action Defined by Dynamic Action. This action fires on a click of this button and executes Oracle’s geocoding services. Be sure to specify a collection name matching that in the query from the preceding section. In this case, that collection name should be specified as GEOCODER_RESULTS. Figure 12-1 shows the definition of the dynamic action invoking Oracle’s geocoding service.

Add a True action that passes the search criteria to the plugin, as shown in Figure 12-2.

Adding a Dynamic Action to Respond

This action fires after the previous dynamic action (Oracle Elocation Geocoder [Plug-In]) executes successfully and refreshes the report region to show the results. Figure 12-3 shows the settings of the dynamic action for the geocoding response.

Now you add a True action to refresh the report region, as shown in Figure 12-4.

Now you can enter an address and click the Geocode button, and the results report will display the geocoding results as shown in Figure 12-5. Note that this example is for U.S. addresses. To geocode addresses in other countries, make sure to change the Country field to an English country name, such as Germany or Canada.

Choosing a Geocoding Service

Of course, there are more web services available, but which one should you choose? I have compared the results of Yahoo, Google, and Oracle and have found that each one returns almost the same results as far as latitude and longitude are concerned.

The main differences are in the additional data provided, such as cleaned-up addresses, and the way address elements are split up. Of course, you will have to review the terms and conditions of each service to make sure you are in compliance. You may also test the response times of each service because they may differ depending on your location, as well as the accuracy of some of your sample data.

The Mapping API

You can take the coordinates from your geocoding efforts and plot them onto a map. A map consists of map tiles and layers. Map tiles are images that show the geographic features on a map, such as roads, rivers, land, and water masses. Most mapping APIs will allow you to choose from different map tiles, such as road maps, satellite imagery, topographical features, and so on. These map tiles are provided by a tile server web service. This web service can be a proprietary one, as in the case of Google and Yahoo, or it can be a third-party provider, as in the case of Leaflet maps. When passing a coordinate pair and a zoom factor to the API, the map tiles for that location, and the surrounding area, will be provided by the tile server.

To draw something onto a map, you use layers. Layers contain elements such as pins, polygons, text (other than that included on the map tiles), and so on. Through the use of JavaScript calls, you can tell the map where and how to draw a layer. Layers typically allow further formatting, such as colors and size, and even can be extended with event handlers to allow them to react to mouse clicks and hovers.

You can display single coordinate pairs, which I will call points from here on, with a map marker or pin. You can also use these points and draw a circle around them. More complex shapes, such as lines and polygons, require multiple points.

Any marker or shape you draw on top of a map exists on a separate layer that the mapping APIs render. These layers can then be shown, hidden, altered, or removed. Depending on the API used, these shapes can also have additional attributes. Markers, for example, can use image files as their representations. Lines and polygons can have attributes such as line thickness, color, and opacity. The layers can also have JavaScript listeners attached to them that respond to clicks and hovers.

In this chapter, you will use the open source JavaScript library named Leaflet (www.leafletjs.com). Other APIs such as Google maps (https://developers.google.com/maps/) or Yahoo maps (https://developer.yahoo.com/maps/) work similarly and differ mainly in syntax. To get the full range of the capabilities for each API, refer to the respective online documentation. Also check their usage terms. I found the Leaflet API to be more concise than the others, and it works well on mobile devices.

Let’s take a closer look at how mapping APIs work. The following are the basic elements needed to render a map on an APEX page:

• Inclusion of the API libraries (JavaScript/CSS)
• A region containing a div with a specific ID and a minimum height
• Custom JavaScript using the API library to render the map inside the div

Including the API Libraries

First you must include these libraries in your application. You can download these libraries and store them in the application’s static files or on your server, or you can refer to their URLs on the Web. When choosing the latter, keep in mind that the libraries may change over time and affect your application in unexpected ways.

In the example shown in Figure 12-6, I downloaded the Leaflet JavaScript library and saved it to the static files of the application, and then I included it by making a URL reference to it in the page’s JavaScript attribute.

Note that APEX 5 now allows you to save static files under different directories. To keep my static files organized, I saved my JavaScript files in the js directory and the CSS files in the css directory.

This particular API also requires a CSS library, which I obtained from its website and stored in the application static files. I then included the CSS library by adding the URL into the CSS File URL attribute of the page, as shown in Figure 12-7.

Creating a div to Hold the Map

APEX regions of type Static Content are similar to blank HTML pages and allow you to add HTML to the region source. You will create such a region to simply hold a div container with a unique ID. The map JavaScript will then use this ID to render the map inside the div tags. Add the following syntax to the region’s source:

<div id="mapRegion" style="width:100%;height:430px;"></div>

Figure 12-8 shows the region’s definition in APEX. You can see the preceding code in the Region Source field at the very bottom of the image.

Writing JavaScript to Render the Map

Let’s add the following JavaScript code to the page’s JavaScript Execute when Page Loads attribute. When the page runs, the JavaScript will automatically be executed to render the map in the div created in the previous section.

var tileLayer =
      new L.tileLayer(’http://otile{s}.mqcdn.com/tiles/1.0.0/map/{z}/{x}/{y}.jpeg’
          , {
             attribution: ’Tiles Courtesy of MapQuest’
            ,subdomains: ’1234’
            });

var latlng = new L.LatLng(47.610871,-122.333451);

var map = new L.Map(’mapRegion’
            , { center: latlng
               ,zoom: 8
               ,layers: [tileLayer]
              });

With a few lines of code, you are able to draw the map. First you declare the variable tileLayer, which tells the API from where to pull the map tiles. In this case, you get the tiles from MapQuest, as specified in the URL. The URL contains four variables. The included JavaScript library will provide their values from the data you provide when you invoke to map. The four variables are as follows:

• {s}: Subdomain (otile1, otile2, otile3, otile4 as specified by the provider)
• {z}: Zoom factor (determines how close you are zoomed into the map)
• {x}: X coordinate (center of the map)
• {y}: Y coordinate (center of the map)

The curly brackets after the URL provide some options for the tile layer. The first is the attribution, which prints some text on the bottom right of the map. Since MapQuest was kind enough to provide the service for obtaining tiles, you attribute that fact in the attribution option.

The subdomains option provides the values for the {s} variable to help the provider to offload requests to different servers. The latlng variable creates and stores a LatLng object that holds a coordinate pair (x and y).

Finally, the map variable creates and stores a new Map object. The first attribute of the object is the ID of the div where you want to render the map. The following options provide details about the map itself: its center with X and Y coordinates, the zoom factor, and the source for the tiles.

By simply declaring the map variable, the map is then rendered inside the div on the page. See Figure 12-9 for an example of how the map appears.

Adding Layers

Now that you have a basic map, let’s make it a bit more interesting by adding some layers. First you’ll add a marker that shows a pop-up bubble when clicked, and then you’ll add some geometric figures.

Adding a Marker

A marker is a special layer object that represents a map pin. To add one, you just need to declare the marker object and give it a coordinate pair and some data about how it should look. In this example, you’ll include a custom image for the marker, rather than the standard blue pin provided by the API.

The following is the JavaScript to add a marker:

var myIcon = L.icon({
    iconUrl: ’#APP_IMAGES#img/marina-2-red.png’,
    iconAnchor: [22, 94],
    popupAnchor: [-6, -85]
});

var marker = L.marker(latlng,{icon: myIcon});

marker.bindPopup(’Welcome to Seattle!’);

marker.addTo(map);

You first create an icon that you will use as the custom marker image. The iconUrl option points to an image I included in the application’s static files. The iconAnchor is the coordinates of the “tip” of the icon calculated from the top left. This is useful in case the icon is of a nonstandard shape or it simply looks odd in its default position. You may have to tweak these numbers by trial and error. For custom map icons, see https://mapicons.mapsmarker.com/.

The popupAnchor gives the position of the pop-up bubble.

Now you create the actual marker, where you tell it its position (latlng) and what it should look like. For the marker to react to a click, you add the bindPopup method to the marker. The method accepts standard HTML that allows further styling.

To make the marker appear on the map, invoke the addTo method and pass the map variable to it. You should see results like in Figure 12-10.

Adding Many Markers

If you have a large number of markers to add to the map, you can make use of JSON. You can store all the coordinates for the markers in a JSON string. Then you can use JavaScript to loop through the JSON object and render each marker.

You first fetch the data from the database and store it in a hidden page item (P320_JSON). You do that using a Before Header PL/SQL page process. Here is what the code looks like:

declare
    l_json VARCHAR2(32000);
    l_i PLS_INTEGER;
begin
    -- Begin the JSON string
    l_json := ’{"stations": [’;

    -- Start a counter to help with formatting
    l_i := 0;

    -- Fetch data from our database table and begin a loop
    for c1 in (select id, name, brand, lat, lng from gas_stations) loop

        -- For loop iterations > 0 add a comma to the end of the string
        if l_i > 0 then
            l_json := l_json || ’,’;
        end if;

        -- Build the JSON string
        -- Use apex_escape.html() to escape special characters
        l_json := l_json || ’{"id": "’      || c1.ID
                         || ’", "name": "’  || apex_escape.html(c1.NAME)
                         || ’", "brand": "’ || apex_escape.html(c1.BRAND)
                         || ’", "lat": ’    || c1.LAT
                         || ’, "lng": ’     || c1.LNG
                         || ’}’;

        -- Increment the counter
        l_i := l_i + 1;

    end loop;

    -- Finalize the JSON string
    l_json := l_json || ’]}’;

    -- Store the JSON string in a page item
    :P320_JSON := l_json;

end;

Next you add a new JavaScript function that loops through the JSON and renders each marker, along with its pop-up bubble, on the map. You also add a function that allows you to remove the markers by clicking a button. For this example, I have also saved a new image (fillingstation.png) to the img directory in the static files. I also created a hidden page item (P320_JSON) to store the JSON from the JavaScript. The complete code now looks as follows:

//Tile source
var tileLayer =
    new L.tileLayer(’http://otile{s}.mqcdn.com/tiles/1.0.0/map/{z}/{x}/{y}.jpeg’
        , {
           attribution: ’Tiles Courtesy of MapQuest’
          ,subdomains: ’1234’
          });

//Initial map center
var latlng = new L.LatLng(32.935217,-97.084862);

//Map definition
var map = new L.Map(’mapRegion’
        , { center: latlng
           ,zoom: 11
           ,layers: [tileLayer]
          });

//Create a custom icon
var gasIcon = new L.icon({iconUrl: ’#APP_IMAGES#img/fillingstation.png’
                        ,iconAnchor: [12,41]
                       });

//Create an offset for the popup bubble, 3 points to the right
//and 25 points up.
var popupOptions = {
  offset:  new L.Point(3, -25)
};

//Load the JSON from the page item into a json variable
var json = JSON.parse($v(’P320_JSON’));

//Parse out the stations array and save it in gs (gas stations)
var gs = json.stations;

//Add additional variables
var marker, name, text, id, lat, lng;
var Markers = [];

//Loop through gs array
for (var i = 0; i < gs.length; i++) {

    //Set ID (primary key of gas stations)
    id   = gs[i].id;

    //Set latituted and longitude
    lat  = Number(gs[i].lat);
    lng  = Number(gs[i].lng);

    //Set name and format text for popup
    name = gs[i].name;
    text = ’<span class="gasStationName">’ + "Station " + gs[i].id + "<hr>" + name + ’</span>’;

    //Create a marker and bind popup bubble
    marker = L.marker([lat,lng],{icon: gasIcon});
    marker.bindPopup(text,popupOptions);

    //Attach custom properties:
    marker.id = id;
    marker.name = name;

    //Add marker to global array so we can remove it later
    Markers.push(marker);

    //Add marker to the map
    marker.addTo(map);
}

//Create function to remove markers
window.removeMarkers = function() {
    //Loop through markers
    Markers.forEach (function(e) {
        map.removeLayer(e);
    });
    Markers = [];
}

The function removeMarkers() is created in the windows namespace so that it can be called from a button on your page. Create a button labeled Remove Markers and define its behavior to redirect to a URL that executes a JavaScript function call, as shown in Figure 12-11.

Note that you are using a trick here to remove the markers from the map. To know which layer to remove, you simply store each marker in an array called Markers. In the removeMarkers() function, you simply loop through the array and delete it. There’s one more thing: Notice how you use the gas station ID as the array index (Markers[gs[i].id] = marker;). This means that if the first gas station ID is 10, the array will not have anything in it for indexes 0 to 9. Therefore, as you loop through the array from zero to Markers.length, you need to check whether an array element is defined (Markers[idx] !== undefined ) before you remove the marker from the map.

Figure 12-12 shows the multiple markers, with their custom icons and pop-up bubble. The data for the marker coordinates and gas station names came from the JSON object, which was generated with PL/SQL.

Adding a Polygon

Rendering polygons on a map is similar to rendering markers. The main difference is that multiple points are specified for a polygon rather than a single point for a marker. The mapping API will take care of the rest by connecting the points with lines and optionally filling the resulting shape with a color. A polygon can also be bound to a listener that can react to clicks and hovers.

Let’s add a polygon to the map. Like the previous marker object, Leaflet also provides a polygon object, which accepts an array of points. Leaflet will then connect the points with straight lines to draw the polygon. The following JavaScript code demonstrates how to create a polygon. Place it after the map definition.

var corners = [ [48.108239, -123.326049]
               ,[48.108239, -121.848388]
               ,[47.227851, -121.870360]
               ,[47.227851, -123.326049]
              ];

  var polygon = L.polygon(corners,{color: ’red’,fillColor: ’red’});

  polygon.bindPopup("I’m a polygon!");

  polygon.addTo(map);

In this example, you create an array of four coordinate points and simply pass it to the polygon. In addition, you add a couple of color options. You also bind a pop-up to the polygon, which will display a message when the polygon is clicked. Finally, you add the polygon to the map so that it gets rendered, as shown in Figure 12-13.

Since the polygon is drawn in order of the point array, it is important to make sure that the order is correct; otherwise, the polygon may render in unexpected ways. Figure 12-14 shows what can happen if you get the ordering wrong.

Interacting with the Database

Displaying geographic data on maps in APEX is powerful stuff. But let’s kick it up a notch and see how you can dynamically interact with the database. You may dynamically show markers on a map depending on report results, update page elements, or fetch data as map items are clicked. To get this level of interactivity, you’ll make use of the on-demand processes (AJAX calls).

Showing Query Results on a Map

In the next example, you have a searchable report that returns data about gas stations. You could search for a particular brand of gas station or try to find the gas stations within a ZIP code. Either way, you not only want the search result in the report but also want to see the markers for each gas station on the map.

To do this, you need to pass the coordinates to the map, along with some additional data, such as gas station brand, name, and address, so that you can include it in a pop-up window as the marker is clicked. The best way to do this is to capture the data inside a JSON array, pass it into the JavaScript code, and run the functions to render the map. Since the GAS_STATIONS table contains all the necessary data, including the coordinates, you could use a dynamic action to read the report rows and generate a JSON array from them. Or you could simply rerun the query in the background with an AJAX callback and directly generate the JSON there. The second option may be better for those cases where there are lots of results and the report will consist of multiple pages.

You will store the JSON in a CLOB column of a collection to make use of a convenient feature in the APEX JavaScript API that allows you to read the collection in JavaScript. The code calls the AJAX callback and then uses a callback function to render the results on the map, once the results have been completely returned.

The process flow looks like Figure 12-15.

Rather than laying out the entire example here, you’ll look at some of the trickier elements needed to do the work. Let’s begin with fetching the gas station data on demand, in other words, not directly on page load but rather when a button is clicked. Fetching on demand gives you a bit more flexibility by potentially allowing you to include user input in your data fetch process.

The following JavasScript function invokes the AJAX callback PL/SQL process on the page, passes a variable to it, waits for the results, and then calls another function to draw the markers on the map. This example uses the page item P115_SEARCH, which is a text field into which the user can enter a search string.

window.fetchGasStationJSON = function() {
      apex.server.process("FETCH GAS STATIONS", {
          pageItems: "#P115_SEARCH"
      }, {
          success: function(a) {
              var b = new apex.ajax.clob(function() {
                  var a = p.readyState;
                  if (a == 1 || a == 2 || a == 2) ; else if (a == 4) {
                      gJSON = p.responseText;
                      showGasStations();
                  } else return false
              });
              b._get();
          },
          dataType: "text",
          loadingIndicator: "#P115_SEARCH"
      });
  };

This function, like any of the previous JavaScript functions, is placed in the JavaScript Function and Global Variable Declaration of the APEX page attributes. You define this function in the window scope so it can be called from the APEX page, for example from a button. The function calls the build APEX JavaScript API apex.server.process, which in turn calls the AJAX callback PL/SQL process named in the first parameter (FETCH GAS STATIONS). Following this parameter is a number of options. The first, pageItems, sets the specified items in session state. This allows the AJAX callback to read its value. The next option consists of three suboptions: success, dataType, and loadingIndicator. The success option specifies what to do after the AJAX callback call succeeds. Here you specify a callback function, which reads the CLOB value from the collection in the AJAX callback into a JavaScript variable (gJSON). This function has another callback function, which waits until the CLOB has been completely read, assigns the CLOB to the gJSON variable, and then calls the function (showGasStations()) to draw the markers on the map. The dataType option simply specifies the data type for the call, and the loadingIndicator specifies the page item next to which to display a spinner graphic while the function is executing.

Next you create the AJAX callback process in the page, as shown in Figure 12-16.

The following is the PL/SQL code for the FETCH GAS STATIONS AJAX callback. It creates a collection for the JSON result. It uses a cursor-for-loop to retrieve data from the GAS_STATIONS table where either the name or the brand matches the search term in P115_SEARCH and then stores the result in the collection.

declare
    l_json clob;
    l_idx PLS_INTEGER;
    l_station clob;
begin

    -- Set up collection:
    -- If it exists, delete it.
    if apex_collection.collection_exists(p_collection_name=>’CLOB_CONTENT’)
    then
      apex_collection.delete_collection(p_collection_name=>’CLOB_CONTENT’);
    end if;

    -- Create a new collection.
    apex_collection.create_or_truncate_collection(p_collection_name=>’CLOB_CONTENT’);

    -- Create a temporary CLOB
    dbms_lob.createtemporary( l_json, false, dbms_lob.SESSION );

    -- Setup JSON beginning with the object stations
    l_json := ’{"stations": [’;

    -- l_idx is used to put the first comma in the right place
    l_idx := 0;

    -- Fetch the gas station data into cursor c1
    for c1 in (select id, name, brand, lat, lng
                from gas_stations
               where (
                      instr(upper("NAME"),upper(nvl(:P115_SEARCH,"NAME"))) > 0  or
                      instr(upper("BRAND"),upper(nvl(:P115_SEARCH,"BRAND"))) > 0
                     )
              ) loop

        -- Add a trailing comma to JSON after first loop iteration
        if l_idx > 0 then
            l_json := l_json || ’,’;
        end if;

        -- Create array element for each gas station
        l_station :=       ’{"id": "’       || c1.id
                         || ’", "name": "’  || c1.name
                         || ’", "brand": "’ || c1.brand
                         || ’", "lat": "’   || c1.lat
                         || ’", "lng": "’   || c1.lng;

        l_json := l_json || l_station || ’"}’;
        l_idx := l_idx + 1;
    end loop;

    -- Terminate the JSON
    l_json := l_json || ’]}’;

    -- Send JSON to clob
    apex_collection.add_member(p_collection_name => ’CLOB_CONTENT’
                                                   ,p_clob001               => l_json);

    -- Pass a success message back to the calling JavaScript function
    htp.p(’Success. JSON length: ’ || length(l_json));
end;

In this example, you use yet another trick: since the generated JSON can be quite large, you store it inside a CLOB. To pass this CLOB back to your JavaScript function, you store the CLOB in a collection, where the built-in apex.ajax.clob function can pick it up.

To build the JSON string, you use a cursor-for-loop to iterate through the wanted rows in the table and simply build the JSON string, with its brackets, commas, and quotes. The htp.p call at the end sends a success message to the calling JavaScript and is mainly used for debugging.

At this point, you have all the basic elements you need to display a map with all the gas stations that were queried in the report. You know how to do the following:

• Draw a map on the page
• Put a marker on the map
• Call a PL/SQL procedure from JavaScript
• Create a JSON object in PL/SQL
• Process JSON and draw the markers

Once the JSON is generated and passed to the APEX collection, the callback function in JavaScript can now parse the JSON and render the markers for each station on the map. The pseudocode looks something like this:

Loop through JSON
   Create a map marker.
   Assign  a popup to the marker with data from a gas station.
   Add the marker to an array, so that we can later remove it.
   Add the marker to the map
End loop

Here is the complete JavaScript source:

var tileLayer =
    new L.tileLayer(’http://otile{s}.mqcdn.com/tiles/1.0.0/map/{z}/{x}/{y}.jpeg’
        , {
           attribution: ’Tiles Courtesy of MapQuest’
          ,subdomains: ’1234’
          });

var latlng = new L.LatLng(32.935217,-97.084862);

var map = new L.Map(’mapRegion’
        , { center: latlng
           ,zoom: 11
           ,layers: [tileLayer]
          });

//Create a custom icon
var gasIcon = new L.icon({iconUrl: ’#APP_IMAGES#img/fillingstation.png’
                        ,iconAnchor: [12,41]
                       });

//Create an offset for the popup bubble, 3 points to the right and 25 points up.
var popupOptions = {
  offset:  new L.Point(3, -25)
};

function showGasStations() {
    //Parse JSON string in gJSON into json variable
    var json = JSON.parse(gJSON);

    //Parse out the stations array and save it in gs (gas stations)
    var gs = json.stations;

    //Add additional variables
    var marker, name, text, id, lat, lng;
    var Markers = [];

    //Loop through gs array
    for (var i = 0; i < gs.length; i++) {

        //Set ID (primary key of gas stations)
        id   = gs[i].id;

        //Set latituted and longitude
        lat  = Number(gs[i].lat);
        lng  = Number(gs[i].lng);

        //Set name and format text for popup
        name = gs[i].name;
        text = ’<span class="gasStationName">’ + "Station " + gs[i].id + "<hr>" + name + ’</span>’;

        //Create a marker and bind popup bubble
        marker = L.marker([lat,lng],{icon: gasIcon});
        marker.bindPopup(text,popupOptions);

        //Attach custom properties:
        marker.id = id;
        marker.name = name;

        //Add marker to global array so we can remove it later
        Markers[gs[i].id] = marker;

        //Add marker to the map
        marker.addTo(map);
    }
}

//Create function to remove markers
window.removeMarkers = function() {

    //Loop through markers
    for (idx = 0; idx < Markers.length; idx++) {

        //If marker index exists remove that marker’s layer from the map
        if (Markers[idx] !== undefined ){

          //Remove marker from the map
          map.removeLayer(Markers[idx]);

          //Remove marker from array
          Markers[idx] = 0;
        }
    }
}

window.fetchGasStationJSON = function() {
      apex.server.process("FETCH GAS STATIONS", {
          pageItems: "#P330_SEARCH"
      }, {
          success: function(a) {
              var b = new apex.ajax.clob(function() {
                  var a = p.readyState;
                  if (a == 1 || a == 2 || a == 2) ; else if (a == 4) {
                      gJSON = p.responseText;
                      showGasStations();
                  } else return false
              });
              b._get();
          },
          dataType: "text",
          loadingIndicator: "#P330_SEARCH"
      });
  };

Now define the global variable gJSON in the page attributes, as shown in Figure 12-17.

Then add a region button to call the fetchGasStations function. See Figure 12-18 for an example.

This should give you a good example of how to dynamically fetch geographic data from the database and display it on the map.

Spatial Math

Being able to display geographic data on a map is quite powerful; being able to analyze the relationships between geographic points is even more so. The Oracle Locator feature provides a number of features and services to make geographic calculations such as finding the distance between two points or calculating the area of a polygon.

Oracle Locator is a no-cost feature of Oracle Database 11g and 12c and is a subset of the for-cost option Oracle Spatial. Some of the capabilities that were available only in 11g Spatial at additional cost have been included in 12c at no additional cost.

You already encountered the geographic data type sdo_geometry earlier in the chapter. To do any of the spatial calculations, you not only need that data type but also a special spatial index and some spatial metadata.

SDO_GEOMETRY

The SDO_GEOMETRY data type contains various parameters that allow Oracle to make the right kind of spatial calculations. These calculations take into account the type of geometry (point, line, polygon) and the type of coordinate system that’s being used. When comparing geometries, you should always use the same coordinate system. Coordinate systems are identified by a spatial reference ID (SRID). In the examples, you will use SRID 4326, which is based on the World Geodetic System WGS84, a global coordinate system. The SDO_GEOMETRY is a built-in Oracle object type that stores the particulars for a geometric object.

This example creates an SDO_GEOMETRY of a single coordinate point:

SDO_GEOMETRY(
             2001  -- SDO_GTYPE: point
            ,4326  -- SDO_SRID (coordinate system)
            ,MDSYS.SDO_POINT_TYPE(lon, lat, NULL) -- POINT_TYPE
            ,NULL  -- SDO_ELEM_INFO_ARRAY
            ,NULL  -- SDO_ORDINATE_ARRAY
)

The SDO_GTYPE value defines the type of geometry (point, line, polygon). It is a “smart” number where the first digit represents the number of dimensions, the second the linear referencing system, and the last two the geometry type (line, polygon, and so on; see Oracle documentation http://bit.ly/1Dau4st. SRID is the coordinate system identifier as mentioned earlier. POINT_TYPE further defines the point’s location. SDO_ELEM_INFO_ARRAY and SDO_ORDINATE_ARRAY are used for geometries that consist of multiple points, such as lines or polygons.

SDO Metadata

Before being able to create a spatial index, you need to tell the database a few details about the table and column you want to index. The data dictionary table user_sdo_geom_metadata holds that information. Along with the table and column name, you need to specify an sdo_dim_array, which basically stores the minimum and maximum latitude and longitude you want to work with. Also, the SRID needs to be specified. The following example creates an entry in user_sdo_geom_metadata for the geom column in the gas_stations table. It further specifies that you want to use SRID 4326. The SDO_DIM_ARRAY specifies the size of the geometry grid you want to work with. In this case, that is the entire earth, which spans from -180 to 180 longitude and -90 to 90 latitude.

INSERT INTO user_sdo_geom_metadata
    (TABLE_NAME,
     COLUMN_NAME,
     DIMINFO,
     SRID)
  VALUES (
  ’GAS_STATIONS’,
  ’GEOM’,
  SDO_DIM_ARRAY(
        SDO_DIM_ELEMENT(’X’, -180, 180, 1),
        SDO_DIM_ELEMENT(’Y’,  -90,  90, 1)
     ),
  4326
);

Spatial Indexes

Once the spatial metadata is defined for the table and column, you can then create the index. Create it using the following statement:

CREATE INDEX gas_stations_geom_idx
   ON gas_stations(geom)
   INDEXTYPE IS MDSYS.SPATIAL_INDEX;

APEX_SPATIAL Package

The APEX_SPATIAL package provides you with an easy utility to create the metadata and the index in a single PL/SQL call. It is necessary when creating user_sdo_geom_meatadata records from within APEX. This example also creates a row in user_sdo_geom_metadata for the geom column in the gas_stations table.

begin
    apex_spatial.insert_geom_metadata (
           p_table_name        => ’GAS_STATIONS’
          ,p_column_name   => ’GEOM’
          ,p_diminfo             => SDO_DIM_ARRAY (
                                                   SDO_DIM_ELEMENT(’X’, -180, 180, 1),
                                                   SDO_DIM_ELEMENT(’Y’,  -90,  90, 1)
                                              )
          ,p_srid                    => 4326
          ,p_create_index_name => ’GAS_STATIONS_GEOM_IDX’
    );
end;

Now that all three pieces are in place (the sdo_geometry column, metadata, and index), you can do your spatial math. Let’s take a look at a few functions to make geographic calculations.

Calculating Distance

To calculate the distance between two points, use the SDO_GEOM.SDO_DISTANCE function. The function simply takes two points in the form of the sdo_geometry data type and returns the distance in the desired unit (miles, meters, kilometers, and so on). The following query shows how to calculate the distance in miles between gas stations with IDs 1 and 2:

SELECT ROUND(SDO_GEOM.SDO_DISTANCE (
                                     a.geom
                                    ,b.geom
                                    ,1
                                    ,’unit=mile’
                                   )
                             ,2) as miles
  FROM gas_stations a
             ,gas_stations b
 WHERE a.id = 1
      AND b.id = 2;

Search Within Distance

Another useful example is to show which gas stations are within 3 kilometers of a particular point. The following query compares the sdo_geometry attributes of all the gas stations to the given point (- 97.078686, 32.939251). If the distance from the given point is 3 km or less, the query returns the string TRUE.

SELECT *
  FROM gas_stations g
 WHERE
  SDO_WITHIN_DISTANCE(
                 g.geom
               , MDSYS.SDO_GEOMETRY(
                        2001
                       , 4326
                       , MDSYS.SDO_POINT_TYPE(- 97.078686, 32.939251, NULL)
                       , NULL
                       , NULL
                 )
                ,’distance = 3 unit=km’
  ) = ’TRUE’

Calculate Area

The SDO_AREA function can be used to calculate the area of a polygon. It takes an SDO_GEOMETRY object as its argument. The SDO_GEOMETRY object should be set as a two-dimensional object with an SDO_ORDINATE_ARRAY of coordinate points that describe the outer points of the polygon.

select SDO_GEOM.SDO_AREA(
               SDO_GEOMETRY(
                      2003  -- GTYPE: two-dimensional polygon
                     ,4326  -- SRID
                     ,NULL  -- POINT TYPE
                     ,SDO_ELEM_INFO_ARRAY(1,1003,1)
                     ,SDO_ORDINATE_ARRAY(3,3, 6,3, 6,5, 4,5, 3,3)
                 )
         ,1) AREA
  from dual;

Third-Party Data

At some point you may want to consider receiving more complex geographic data, such as ZIP code, census, or neighborhood boundaries from a third-party provider. These data sets can be quite large because they may consist of tens of thousands of coordinate points. For example, a ZIP code boundary would be displayed as a polygon layer on top of a map as shown in Figure 12-19. Since ZIP code boundaries may be intricate, they can contain a large number of points.

While there are multiple formats in which geometry data can be communicated, one that is fairly easy to deal with in the Oracle Database is Well Known Text (WKT).

WKT is a plain-text markup language for representing vector geometries, such as points, lines, and polygons. The example in Figure 12-20 shows how multiple coordinate pairs make up polygons and multipolygons.

What makes WKT easy to deal with in Oracle is the fact that Oracle already has a built-in function to parse WKT and convert it into the sdo_geometry data type. The function SDO_UTIL.FROM_WKTGEOMETRY will take the WKT string and convert it into a SDO_GEOMTERY data type.

A simple conversion technique would be to build a table with a WKT column (CLOB) and a SDO_GEOMETRY column. Then simply run an update statement to make the conversion. Here’s an example:

UPDATE zip_code_table
        SET geom = sdo_util.from_wktgeometry(wkt_column);

It is important to then update the sdo_geometry column and specify its spatial reference ID:

UPDATE zip_code_table a
         SET a.geom.sdo_srid = 4326;

Now the table is ready for spatial calculations.

Oracle also contains conversion functions for WKB and GML. WKB is the binary format of WKT. GMS is the Geography Markup Language. When dealing with third-party data, using any of these three data formats will serve you well because the data conversion can be made with Oracle’s built-in functionality.

Summary

Including interactive maps and perfoming spatial calculations on geographic data in an APEX application can be quite powerful. Just like charts and graphs allow a user to comprehend numeric data more efficiently, maps remove the abstraction of addresses and coordinate points and allow the user to better understand spatial relationships.

You explored how to convert address data into coordinates through web services and then utilized JavaScript mapping APIs to display that data on maps. You also learned how to customize the look of map markers and how to add polygon overlays to maps. By utilizing Oracle’s built-in spatial objects and packaged programs, you were able to begin drawing spatial relationships between data points. Finally, you looked at how you can utilize third-party data in your own projects.

It is important to note that the mapping JavaScript APIs are continually being developed and enhanced. Many provide further functionality through plugins. By looking at the available documentation online, you may discover that some of these enhancements may be suitable for your projects.

Oracle’s spatial option offers many more features and are quite performant when it comes to crunching large amounts of geographic data.

Explore the Sample Geolocoation Showcase application included in APEX 4.2.5 and higher and the various mapping APIs for some more usage ideas.

You can download the gas stations sample objects and data from Apress.