Maps in OBIEE Free and Easy – Part 2 – The Database


In the database geometry objects are stored in a special column type called SDO_GEOMETRY that is specifically for geometric data.  The database stores geometry objects, such as States, as an ordered set of points, which are then connected by line segments  by Map Viewer to render the shape.


The type, and its associated metadata views and functions, are provided by the MDSYS schema which is included with your database installation.  Although the SDO_GEOMETRY type is associated with the MDSYS schema it can be used by any database user to
create geometry columns in any schema.  The only requirement is that there must be a spatial index on the table that contains the geometry type and there must be an entry in the USER_SDO_GEOM_METADATA view that tells the database that a particular table and column contain geometric data in order for functions and rendering operations to work correctly.

The geometry type is analogous to an XML type in that it is stored as a column in a table but also has its own internal structure that can be addressed by various database functions provided specifically for it.


X/Y Grid

Using an example from the documentation, if we look at the polygon named cola_b rendered on an xy grid we can see that its definition should include the points (5,1, 8,1, 8,6, 5,7, 5,1).

If we look at the COLA_MARKETS table in the database and look at the row that contains cola_b we can see that  the SHAPE column (which is an SDO_GEOMETRY type) does indeed contain those points.  Checking to see we have an entry in the USER_SDO_GEOM_METADATA view and that we have a spatial index on the table we can create a map view of our COLA_MARKETS table right inside of SQL Developer.


COLA_MARKETS table in the database

Map based objects such as a US State are merely a more complex example of a polygon that have a reference to a projection system (SRID) for rendering the earth’s 3 dimensional surface on a 2 dimensional plane.  If we look at a stored geometry for Colorado we can see that the only difference between it and the simple polygon we created for cola_b above is a reference to an SRID code and the number of points (longitude and latitude in this case) needed to render it.

That covers the basics of what you need to know about geometry and the database for now.  The SDO_GEOMETRY column type is used to store geometry definitions in the database and the USER_SDO_GEOM_METADATA view gives the database the information it needs to create spatial indexes.

I’ll show you in another post that lines, points, circles, etc. can also be associated with and rendered on maps and it’s pretty easy to create and use these objects to add even more insight to your map visualizations.  Additionally, we’ll talk about several important and useful database procedures & functions that are provided for validating (and fixing if needed) your geometric data which is especially helpful when we import freely available shapefiles into our database to create our own maps for use inside of OBIEE.



Maps in OBIEE Free and Easy – Part 1 – The Basics


Many people mistakenly believe that implementing map views in Oracle Business Intelligence (OBI) is difficult, requires additional licensing on the Oracle database and requires them to pay for content from third party map providers.  Third party provided map data and Oracle Spatial and Graph (an additional option for the Enterprise Edition of the database) may offer significant value to your organization but neither of these options are necessary to create and use maps with OBI.  Country, state or province, county, city and even neighborhood level map data is freely available and easily found in a number of places.

Oracle Locator is included with all versions of the Oracle Database.  Oracle Map Viewer is included with OBI and the Map Builder and Map Editor tools included with Map Viewer make it possible (and dare I say easy) to import and customize maps.  Maps views are produced through an integration between the Oracle Database, Map Viewer and OBI but the hard part of the integration is already done for you so all that is really required to utilize the power of maps is an understanding of the components involved in the integration and how they interact with each other.

As with most things Oracle, what at first seems complicated is actually pretty simple once you understand what’s going on.  I can’t count the number of times when first learning something new with Oracle technology I have marveled at how over complicated it seems but after taking the time to understand it have been equally impressed with how simple they have made very complicated things.  Maps are like this.  At the heart of map views we are dynamically associating dollar or quantity type measures with geometric shapes and rendering those shapes using colors, styles and other visualizations in such a way as to enhance the meaning of the underlying data.   That’s a complicated task.  Thankfully all you really need to know about it is what geometric shapes (US States as an example) do you want to visualize and what attribute of that shape (State name for example) do you want to use to associate with your measures.  Everything else involved is just giving the various components involved what they need to do their part of the task.

MapViewer1Let’s take a look at a figure from the MapViewer documentation that helps to describe the architecture involved in producing maps and then we will discuss the important things to understand at each layer.  OBI is the “Client” application in our scenario so it passes map rendering requests to MapViewer which in turn interacts with the database to get the map definition information it needs to render a map and pass it back to OBI.

MapViewer (the “Middle Tier”) needs to know what map definition to use and how that definition is linked to OBI data.  This linkage is defined in the Map Administration page of OBI and ties a field (or fields) from a subject area to a map layer.  MapViewer is a J2EE application that comes pre-deployed with the included Weblogic instance that OBI is deployed on.  Although it is collocated with OBI it can also be deployed on a standalone server running Glassfish or Tomcat.

Oracle Database stores the geometric definition of shapes and what colors, line types and text styles to use when rendering them.  Additionally, various functions can be performed on geometric data such as distance calculations or merging several states into a territory.

So that gives us a basic understanding of the moving parts involved with creating Map views inside of OBI Answers.  OBI is a client application asking Oracle MapViewer to render an interactive map for it based on some business data.  Oracle MapViewer fetches the map definition and styles from the Oracle Database and uses that information to render a map that it passes back to OBI.


Sources of free map data


When implementing maps in Oracle Business Intelligence a lot of people may have the mistaken impression that they must purchase map data or use external map providers to create background maps or create themes for use with their business data.  In truth there are numerous free sources of shape files that can be used under open source licenses for your business.  These files are easily imported into your Oracle database using Oracle Map Builder.

Following is a list of free map data I have compiled and will continue to add to over time.

One fantastic source of United States shape files is the United States Census Bureau Tiger/Line Shapefiles.  They make available files from the state level down to the zip code as well as many features such as Landmarks and Roads.

Canada also has a Census Program and provides many files that can accessed via the Statistics Canada Geography page.

Another immense source of freely available and usable shape files is Natural Earth which is a “collaboration involving many volunteer NACIS members and cartographers around the globe”.