Monthly Archives: December 2015

Spatial Reporting in OBIEE

OBIEE allows you to create spatial reports. The idea is relatively simple. Instead of a tabular reports with rows and columns, we create a reports that are createad as a layer upon a map. So we have a map, with a layer of plotted values. When we have a report with a geographical component, such as turnover per town, we map create a nice overview of turnovers as realised in different town that are plotted on a map of a particular area.
An example:
You see a map of the Netherlands with some areas that correspond to municipalities. These areas are either yellow or purple, depending upon the value that correspond to the municipality.
How is this accomplished?
OBIEE allows the creation of such reports. It provides several maps (Bing/ Nokia and Elocation) that are provided with OBIEE. One could interpreted this as the first layer upon which we must plot our municipalities. We must firstly create an object that holds the geographical components. We need at least two attributes:
1: We need a geographical object
2: We need a name for that geographical object.

This can be stored as a record in Oracle. An example is given below:
Here we store a geographical object in field “Geometry”. If fact, we store a series of data on latitude, longitude that together create a polygon. This is stored in Oracle. We attach a name “NLD_GOUDA” to it.
Now we have an object with a name and geographical content. We can refer to “NLD_GOUDA” if we want to link it to that geographical object.
Such linkage is created here in another table where address keys are linked to the name of the geographical object. See:
This address key is used in this data base with customers that generate a certain turnover. If we add all turnover that are realised by customers that have an address key that is mentioned in the picture above, we have total turnover as realised in “NLD_GOUDA”.
It might well be that we are interested in the turnover that is generated in each of the addresses. This could be realised by storing the geographical objects with a name of each address in the database. Here (see record number 5), we store “NLD_GOUDA_2804XABLOEMENDAAL” with the geographical object. See picture below:
We now have several addresses within one municipality for which geographical objects are created. We may now link the addresses to the labels as shown below:
We now have two types of geographical objects:
1: municipalities for which we have geographical polygons
2: addresses within the municipalities for which we have other geographical objects.

The end result is shown below, where the individual addresses are displayed with a bubble and the municipality is shown in yellow:
We see one address “NLD_GOUDA_2804XABLOEMENDAAL” standing out.

Latitudes and longitudes in the Netherlands

Getting geographical data on Holland is not a trivial matter. It took me a bit of tenacity to get the data. Hence I thought being a good idea to store my findings. I might need these data in the future. The first file that I found on the internet is a file with longitudes/ latitudes of municipalities.. Then nice thing about this file is that municipalities are given with a polygon. It is possible to show the municipalities with their contours on a map. Later, I found another file with six digit postal codes. Each postal code represents about 10 different addresses. This file is stored here.
I use these data to display spatial information in Oracle. In Oracle such data must be stored in a special data type, SDO_Geometry. The import file on municipalities is stored stored here. The import file on postal code is provided here.