Business Intelligence

Use Location Information in Enterprise Reporting
by Justin Lokitz & Madeline Alameda

Learn how to create dynamic maps that make the relationships between attribute and location-based information a lot more visible

Download the JDeveloper Project Files for this article

Location information is everywhere; it can be found across all lines of business, all industries, and in virtually every organization and department in the world. However, most organizations regard location information in one of only two ways:

  • Location as an address — usually associated with an entity's name or ID, and used to support a business function (such as billing or mailing) or to separate certain records from other, similar records in a reporting environment. Examples include applications for sales and service organizations, as well as for human resources and marketing.
  • Location as a point on a map — used only to delineate a single point or location from other points, locations, and/or themes, and accessible only by geographic information system (GIS) users.


Although location information is essential for making both of the above systems work, it is seldom ultimately used to integrate data across these systems and provide actionable business intelligence to the organization.

It's not widely known, but with current technology, it is indeed possible to use location not only as an address or a point on a map, but as a way to reveal trends based on market share, population concentration, or complex location-based interactions. In this article, we will describe a technique for meeting that goal across all lines of business using the same enterprise reporting tools that are commonly used for relational data.

Gaining Intelligence from Your Location Information

There are at least three ways to store GIS information in a database: in a proprietary format (typically BLOB or Long Raw), as flat X, Y coordinates, or using an OpenGIS-compliant object type such as the Oracle Spatial SDO_GEOMETRY type.

In the first two scenarios, in order to pull location information out of the database and associate it with attribute information, developers must use third-party middleware or write programs. However, using Oracle's SDO_GEOMETRY object format to store and manage location data, developers can use standard SQL with GIS-functions to extract intelligence from location and attribute data.

Consider the following example from Oracle by Example: Performing Location-Based Analysis . Given the two tables Customers and Warehouses, we are storing data using several data types such as the obvious NUMBER, VARCHAR2, XMLTYPE, as well as user-defined types. However, you will also notice that there is a fifth type, MDSYS.SDO_GEOMETRY, which is a Spatial/Locator type used to store a customer's or warehouse's point location on the Earth's surface.

The Customers table has the following fields:

                               
Column                 
                              
Data Type
customer_id           NUMBER(6)
cust_first_name       VARCHAR2(20)
cust_last_name        VARCHAR2(20)
cust_address          cust_address_typ
phone_numbers         phone_list_typ
nls_language          VARCHAR2(3)
nls_territory         VARCHAR2(30)
credit_limit          NUMBER(9,2)
cust_email            VARCHAR2(30)
account_mgr_id        NUMBER(6)
cust_geo_location     MDSYS.SDO_GEOMETRY 
                            


The Warehouses table has the following fields:

                               
Column                 
                              
Data Type
warehouse_id          NUMBER(3)
warehouse_spec        SYS.XMLTYPE
warehouse_name        VARCHAR2(35)
location_id           NUMBER(4)
wh_geo_location       MDSYS.SDO_GEOMETRY
                            


Taking a closer look at the cust_geo_location column from the Customers table, we see that it is described by the following:

CUST_GEO_LOCATION(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)


From this description, we can determine several characteristics of the data being stored:

  • SDO_GTYPE describes the type of geometry (point, line string, or polygon)
  • SDO_SRID is used to identify the coordinate system
  • SDO_POINT describes the latitude and longitude
  • SDO_ELEM_INFO describes how to use the numbers stored in the SDO_ORDINATES section
  • SDO_ORDINATES describes the coordinate values that make up the boundary of a spatial object.


Examining a single column's data ( SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(6.1667, 46.2, NULL), NULL, NULL) ), we see that for point data, only the first three SDO_GEOMETRY attributes are used. Storing the location data in this manner allows for flexibility as well as access via standard access methods such as SQL, JDBC, and ODBC.

We can now query the location and attribute information in the Customers table using SQL*Plus with Spatial/Locator operators. Consider the following example:

SELECT a.cust_last_name
 FROM customers a
 WHERE
   SDO_WITHIN_DISTANCE(a.cust_geo_location, mdsys.sdo_geometry (2001, 8307,
                              mdsys.sdo_point_type (6.1667, 46.2, null), 
                              null, null),
                           'distance=20000 unit=meter') = 'TRUE';


If we examine this query (Figure 1), we are selecting the last name of each customer within 20,000 meters (20 Km) of a point on Earth (6.1667, 46.2):

figure 1
Figure 1: Query to find customers within 20,000 meters


The preceding simple query is interesting, but what we really want to extract from the location and attribute information is a result set that tells us how our customers might interact with our warehouses. Therefore, the following SQL query finds the names of the ten closest customers to the warehouse where warehouse_id=2 , and exactly how far customers live from the given warehouse (in meters):

select /*+ordered*/ 
         c.customer_id, 
         c.cust_first_name, 
         c.cust_last_name,
         sdo_nn_distance (1) distance
from warehouses w, 
         customers c
where w.warehouse_id = 2
and sdo_nn (c.cust_geo_location, w.wh_geo_location, 'sdo_num_res=5', 1) = 'TRUE'
order by distance;


Again, examine the individual parts of this query (Figure 2):

figure 2
Figure 2: Query to find 10 closest customers to warehouse


The above queries showed how to retrieve location and attribute data from the database and even to find relationships with the data. Next, we will examine how we can use these queries in an enterprise reporting system (ERS), thereby empowering end users to see the information for themselves.

Using Location Information in an ERS

Now that we can find relationships between location and attribute data stored in different tables by using standard SQL, we will examine ways to deploy this functionality in ERSs. Most such systems support standard SQL to return result sets. Hence, we should now be able to use the SQL from the previous section with any such tool. In this case, we'll integrate SQL containing GIS result sets into Oracle Application Server Discoverer.

In the end user's view in Discoverer, relationships and other attributes in metadata are abstracted by the Discoverer Administrator. After connecting to the Oracle Database instance where our location information is stored, we must first build the Business Area (Figures 3 —6). This operation assumes that the Discoverer End User Layer (EUL) has already been created—a process that is outside our scope here. (See the Discoverer Administrator's Guide for more information.)

figure 3
Figure 3: Create a Business Area.


figure 4
Figure 4: Select the user schema of the tables to access (eg, user OE).


figure 5
Figure 5: Select the tables you want in the Business Area.


figure 6
Figure 6: Give the Business Area a name (eg, "10 Closest Customers"), and click "Finish."


After creating a Business Area, we must define how our users will call the location data in our OE schema tables (Customers and Warehouses). To do that, we create a custom folder that will contain the SQL statement from the previous section, included in the following query:

select /*+ordered*/ c.customer_id, 
         c.cust_first_name, 
         c.cust_last_name,
         c.account_mgr_id,
         w.warehouse_id, 
         sdo_nn_distance (1) distance
from warehouses w, 
         customers c
where sdo_nn (c.cust_geo_location, w.wh_geo_location, 'sdo_num_res=10', 1) = 'TRUE'
order by distance;


Notice that we have added w.warehouse_id and c.account_mgr_id to the body of the query. This addition will let us run the query dynamically from Discoverer based on any warehouse or account manager. Figure 7 shows this query in the custom folder, which we'll call "Distance."

figure 7
Figure 7: Inserting a custom folder to hold and validate our GIS SQL statement


We now have a Discoverer Business Area and a folder named Distance that includes attribute and location data. At this point we can deploy this Business Area so that our end users can use the data in reports, or we can go a step further and provide them with a secondary set of information: a dynamic, intelligent map.

Using Dynamic Maps

Probably the most powerful way to "see" location information is via a map. In the previous examples we extracted location and attribute data using standard query and reporting techniques; however, in many cases geographical features are only distinguishable when they are viewed on a map. Because we have a large dataset and many end users who need to see the data, we will provide an enterprise reporting front end that lets end users drill into the details of any given report, which will in turn link to a dynamic, web-based map—thus presenting end users with all the details in tabular and map form.

Many GIS and cartographic tools support development of such maps. In this demo, we will use Oracle Application Server MapViewer, a J2EE service for rendering maps using location data managed by Oracle Spatial/Locator. Because MapViewer communicates with clients via XML, it works on the principle that anything that can issue a request and receive a response in XML can essentially become a MapViewer client. Furthermore, because MapViewer is a J2EE service, it comes with a Java API as well as a JSP tag library.

Like other map-rendering tools, MapViewer uses the concepts of styles (colors, markers, lines, areas, text, symbology, and advanced styles) and themes (sometimes called layers) to create dynamic maps. The definitions of these attributes, as well as actual map definitions, are stored as XML in the database along with the location information.

To create attributes and/or map definitions, we can issue standard SQL inserts/updates to the database such as:

SQL> insert into USER_SDO_STYLES values(
      'V.PIECHART1', 'ADVANCED', null, 
      '<?xml version="1.0" ?>
      <AdvancedStyle>
              <PieChartStyle pieradius="10">
                      <PieSlice name="A"    color="#ffff00" />
                      <PieSlice name="B"    color="#000000" />
                      <PieSlice name="H"    color="#ff00ff" />
                      <PieSlice name="I"    color="#0000ff" />
                      <PieSlice name="W"    color="#ffffff" />
              </PieChartStyle>
      </AdvancedStyle>',  null, null);


Or, we can use the supplied Map Definition tool (Figure 8), which does most of the for us:

figure 8
Figure 8: Map Definition Tool


The standard MapViewer download or deployment included with Oracle Application Server has a set of styles, themes, base maps, and sample code. However, for our example, we will need to copy one base map and add two themes as well. Because we have both SQL and tool access to the map definitions in the database, the easiest way to copy the definition of a map is to issue a SQL statement like the following. (Before doing this, examine the MapViewer-related views user_sdo_styles, user_sdo_themes, and user_sdo_maps for a better understanding of how they work.)

SQL> insert into user_sdo_maps
  2  values('WAREHOUSES_AND_CUSTOMERS', 'customers and warehouses',
  3  (select definition from user_sdo_maps where name='DENSITY_MAP'));

1 row created.

SQL> commit;

Commit complete.


We now have a new base map named WAREHOUSES_AND_CUSTOMERS to which we will add some themes using the Map Definition tool. First, change to the directory containing mapdef.jar, and issue the following command to connect to the Oracle Database instance that is storing our map definitions:

java -classpath mapdef.jar;d:\oracle\ora92\jdbc\lib\classes12.jar - Dhost="localhost" -
Dsid="orcl" -Dport="1521" oracle.eLocation.console.GeneralManager


Next, create two themes, CUSTOMERS and WAREHOUSES (Figures 9 and 10):

figure 9
Figure 9: Creating the CUSTOMERS theme


figure 10
Figure 10: Creating the WAREHOUSES theme


After creating the customers and warehouses themes, add them to our new WAREHOUSES_AND_CUSTOMERS base map (Figure 11):

figure 11
Figure 11: Creating the WAREHOUSES_AND_CUSTOMERS


Notice the Min Scale and Max Scale elements. These elements are used to define which themes appear on a given map, depending on the zoom level for a request.

After updating the map definition (via the Update button), we can begin to use them to build dynamic maps based on client requests. An easy way to test that our map is working is to plug this new map definition name, WAREHOUSES_AND_CUSTOMERS, into one of our demo MapViewer applications. To do that, navigate to our MapViewer simple map client (mapclient.jsp at the MapViewer URL) and enter the necessary values (Figure 12):

figure 12
Figure 12: Demo map client


Notice that the mapclient.jsp sample application also displays the issuing request made to the MapViewer server—this is the same XML request that can be made by any client or development language. If all definition and connection values are correct, a map (or a URL to the map image) will be built upon request.

For our example, we will use the demo/tagmap.jsp file that is supplied with MapViewer. After running the supplied tagmap.jsp, we can link out to its source code and begin to modify it so that it fits our BI query model. Modifying and testing JSPs can be an easy task when using the right tools. In this demonstration, we'll use Oracle JDeveloper 10g and the associated Oracle Application Server 10g MapViewer JSP Tag Libraries. (Download the JDeveloper Project Files here .)

To create the map dynamically based on requests for data in the cust_geo_location column, modify tagmap.jsp (which we have renamed oemap.jsp) to include methods that open a database connection, execute a query, and retrieve a result set. Usually you would use JDBC or similar technologies to perform database actions. However, because Oracle Application Server MapViewer already uses JDBC to obtain the map definitions from the database, we can use the supplied MapViewer Java API to make all the connections for us: all we have to worry about is writing the query.

To access our Customers table, execute a query and supply latitude and longitude variables to be used in our map, and add the following code to oemap.jsp. Note the custid JSP parameter: used in the SQL query, this parameter filters out the result set and thus creates a dynamic map based on a single customer ID:

String mvURL = "http://www.foo.com:8888/mapviewer/omserver";
MapViewer mv = new MapViewer(mvURL);
String custid = request.getParameter("custid");
String latitude = "";
String longitude = "";
String sqlQuery = "SELECT c.cust_first_name, c.cust_last_name, t.X, t.Y FROM   customers c, 
TABLE(SDO_UTIL.GETVERTICES(c.cust_geo_location)) t where c.customer_id ="+custid;
String[][] qryRes = mv.doQuery("mvdemo",sqlQuery);


After performing the query operations and obtaining a result set, print the results and set the latitude and longitude parameters accordingly (Figure 13).

figure 13
Figure 13: Adding variables, query operations and printing results.


After executing the query and setting the latitude and longitude variables, use the following code to set up MapViewer tags and a special JDBC theme (used to print a red star at the center of the map; see Figure 14):

<mv:init url="<%=mvURL%>"datasource="mvdemo" id="mvHandle"/>
    
<mv:setParam title="Customer/Warehouse Map" bgcolor="#ffffff" width="500" height="375" antialiasing="true" />

<mv:importBaseMap name="WAREHOUSES_AND_CUSTOMERS"/>
    
<mv:setParam centerX="<%= latitude %>" centerY="<%= longitude %>" size="3"/>


Dynamically add the theme to locate and identify a single point on the map with a red star:

<%
mvHandle.addJDBCTheme("mvdemo", "centerPoint","SELECT mdsys.sdo_geometry(2001, 8307, 
mdsys.sdo_point_type("+ latitude +","+ longitude +", null), null, null)  geom FROM dual","geom", 
null, "M.STAR", null, null, true);  
%>


figure 14
Figure 14: MapViewer tags and JDBC theme


After making a few minor changes to the map code, test deploying the map from JDeveloper, set the custid JSP parameter, and get the following result (Figure 15):

figure 15
Figure 15: Newly created dynamic customer map


When the map looks and performs as intended, deploy the map to an Oracle Application Server instance.

We are finally ready to link our Discoverer report to our dynamic customer map. To do that, go back into the Discoverer Administrator Tool and create a calculation column on the Distance folder. This column will hold the URL to link out to the dynamic map.

With the cursor on the Distance folder, click the "New Calculation" icon (Figure 16). This new Calculation column is given the name MAP URL.

figure 16
Figure 16: New Calculation icon


In the Calculation section of this wizard, enter the URL for the map and concatenate the Discoverer Customer Id field at the end of the URL, such that for each record returned to the Discoverer Report, there will be a different custid JSP parameter (Figure 17).

figure 17
Figure 17: Entering URL and concatenated Customer Id to calculation field. (URL reads: http://www.foo.com:8888/OEMaps/oemap.jsp?custid='||Customer Id)


Now that a dynamic URL is associated with each record to be returned, make certain that when a user clicks on the URL, a new map window will open displaying the dynamic map. To do this, double-click the new MAP URL calculation, which takes us to the properties for this column. Under the Content Type property, select "File" (Figure 18).

figure 18
Figure 18: Changing the Content Type to "File"


We're now ready to set up our Discoverer report and test our dynamic map. Discoverer Desktop and Discoverer Plus are query tools that enable end users to log into the database and create dynamic reports based on the information set up by the Discover Administrator. Therefore, log in to the Discoverer Desktop as an end user, and begin the process of creating a new report to be displayed as a Page Detail Table (Figure 19).

figure 19
Figure 19: Creating new Discoverer report


Because we connect to the database and have been granted the proper permissions, we can decide which fields will be important for our report. In this case, choose all the fields in the Distance folder (Figure 20).

figure 20
Figure 20: Report fields


The layout for our report enables us to dynamically drill into information based on the Warehouse ID and Account Manager ID (Figure 21).

figure 21
Figure 21: Report layout


To provide a different sorting than the default, create a group sort for the Distance column to present information based on how far customers are from a particular warehouse — from closest to farthest in meters (Figure 22)

figure 22
Figure 22: Create a sort.


After completing the process of creating a report using the Discoverer wizards, we can run the report from Discoverer Desktop, Discoverer Plus, or Discoverer Viewer. Notice the dynamic URL created for each row of the report (Figure 23).

figure 23
Figure 23: Running report from Discoverer Plus


At last, both end users and developers are able to view the location information in an informative and user-friendly way (Figure 24).

figure 24
Figure 24: Seeing is believing.


The Ubiquity of Location Information

Whether you are processing customer orders at a warehouse or looking at population concentrations on a map, location drives all transactions and is at all times associated with non-location attribute information. With dynamic maps, finding the relationships between attribute and location-based information is a lot easier.

For more information about Oracle Application Server MapViewer, see otn.oracle.com/products/mapviewer/index.html .

For detailed information about using SDO_GEOMETRY data, see the Oracle Spatial User's Guide and Reference at download-west.oracle.com/docs/cd/B13789_01/appdev.101/b10826/toc.htm .


Justin Lokitzis a Senior Sales Consultant at Oracle Corporation specializing in GIS and J2EE development. Madeline Alameda is a Staff Sales Consultant at Oracle Corporation specializing in Business Intelligence and Data Warehousing.

Please rate this document:

Excellent Good Average Below Average Poor




Send us your comments