Integrating Mapping And Spatial Data Into Your Oracle Data Warehouse

Mark Rittman -

There's an interesting article currently running over on OTN entitled "Use Location Information in Enterprise Reporting". According to the introduction, the article helps you "Learn how to create dynamic maps that make the relationships between attribute and location-based information a lot more visible for business intelligence analysis."

The article's about two Oracle products that help you add GIS functionality to your data warehouse; the Spatial Option to the Enterprise Edition of the database, and the new Oracle Application Server MapViewer, a J2EE service for rendering maps using location data by provided by the Spatial Option. The Spatial Option lets you add geographic attributes to data in your database using an  OpenGIS-compliant object type called SDO_GEOMETRY, and comes with SQL operators that let us make comparisons between two map points. For example, a typical SQL query using given in the article that uses the Spatial Option would be:

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';

What makes this interesting is the new Oracle Application Server MapViewer, a J2EE application that comes with Application Server 10g (9.0.4) and displays data stored using the Spatial Option. The article comes with a JDeveloper project file that builds a map from spatial data in the database, and the displays the map using the Mapviewer application. The article then goes on to show how this map can be called from Oracle Discoverer 9iAS, allowing us to integrate mapping in to our Discoverer reporting solution.

If you're interested in finding out more about the Spatial Option and Mapviewer, there's an Oracle-by-Example tutorial available on OTN titled "Performing Location-Based Analysis". You can also find out more on the OTN Mapviewer homepage and in the Oracle Spatial User's Guide and Reference.