| Business Intelligence
Use Location Information in Enterprise Reporting
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:
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:
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):
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):
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 createda process that is outside our scope here. (See the Discoverer Administrator's Guide for more information.)
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."
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 mapthus 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:
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):
After creating the customers and warehouses themes, add them to our new WAREHOUSES_AND_CUSTOMERS base map (Figure 11):
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):
Notice that the mapclient.jsp sample application also displays the issuing request made to the MapViewer serverthis 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).
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); %>
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):
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.
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).
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).
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).
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).
The layout for our report enables us to dynamically drill into information based on the Warehouse ID and Account Manager ID (Figure 21).
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)
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).
At last, both end users and developers are able to view the location information in an informative and user-friendly way (Figure 24).
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 .