Lately I have been asked to prepare some proof-of-concepts for our clients that use spatial data and visualisations in Oracle Business Intelligence (OBI) dashboards and reports.
In general there are demos on internet showing how you can use a map view in an analysis. But how do you actually get there, it is never shown. So I decided to share this with whomever is interested and here it is.
Preparation and deployment of spatial data in order to be used in OBI visualisations is a 5 step process:
But before kicking off, let me set the scene first. Let's assume I have a dashboard already prepared for my analysis.
The dashboard above show sales data from "my Japan based" company by sales regions, chiho regions and prefectures. So you can drill to lower level, colour and display information on the map and possibility to navigate to other dashboard pages, etc.
For my demo here, the scenario requires me to prepare spatial data for all three hierarchy levels: sales region, chiho and prefecture. Let's say I got spatial data only for the lowest level of the three, for prefectures.
This information is stored in MAP_PREFECTURES table, which has the following structure:
MAP_PREFECTURE contains my hierarchy:
If we perform select * from MAP_PREFECTURE, this would be the output:
Observe that GEOMETRY does not reveal its content. But don't worry, in SQL Developer, you can simply double click on the column value and window with the following information will be shown:
MDSYS.SDO_GEOMETRY(
In general there are demos on internet showing how you can use a map view in an analysis. But how do you actually get there, it is never shown. So I decided to share this with whomever is interested and here it is.
Preparation and deployment of spatial data in order to be used in OBI visualisations is a 5 step process:
- Prepare data in Oracle database
- Configure and create layers in Oracle MapViewer
- Create styles and geometry themes in Oracle Map Builder
- Import and configure layers and background maps into OBI
- Add new map views into analyses
Setting the scene
In this post I will focus on preparing data in Oracle database. I will use Oracle Spatial Data Option for creation of required spatial objects. As you will see, there are some new spatial data object types involved, but in general this is not a rocket science.But before kicking off, let me set the scene first. Let's assume I have a dashboard already prepared for my analysis.
The idea of this exercise is to replace left-upper table view with a new map view, something like this:
The dashboard above show sales data from "my Japan based" company by sales regions, chiho regions and prefectures. So you can drill to lower level, colour and display information on the map and possibility to navigate to other dashboard pages, etc.
For my demo here, the scenario requires me to prepare spatial data for all three hierarchy levels: sales region, chiho and prefecture. Let's say I got spatial data only for the lowest level of the three, for prefectures.
This information is stored in MAP_PREFECTURES table, which has the following structure:
MAP_PREFECTURE contains my hierarchy:
- Country
- Sales region
- Chiho
- Prefecture
If we perform select * from MAP_PREFECTURE, this would be the output:
Observe that GEOMETRY does not reveal its content. But don't worry, in SQL Developer, you can simply double click on the column value and window with the following information will be shown:
MDSYS.SDO_GEOMETRY(
2003,8307,NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(
139.953704833984,37.1512184143066, …,
139.964492797852,37.1502304077148))
The first argument describes SDO_TYPE. In this case value 2003 stands for "polygon". Basically it is expected that prefectures are displayed as regions on the map, so it should be in the shape of some polygon.
The second argument defines coordinate system which is used in my map, it specifies Spatial Reference system ID (SRID). In our case we use coordinate system with ID 8307. That is, the geometries are defined as using the coordinate system whose SRID is 8307 and whose well-known name is "Longitude / Latitude (WGS 84)". This is probably the most widely used coordinate system, and it is the one used for global positioning system (GPS) devices.
The third attribute has value NULL. This attribute defines SDO_POINT_TYPE, which I don't need as we on my map I will draw polygons and not just points.
The last two arguments of MDSYS.SDO_GEOMETRY specify geometry in more detail.
The first of the two defines the polygon itself, so for example, the first value 1 defines starting offset, value 1003 defines a simple element. The last value is again 1, which means that my polygon has got vertices that are linked by straight lines.
The second detailed attribute hold coordinates of my polygon. Coordinates are represented as an array of pairs (x,y) that stores the coordinate values that make up the boundary of a spatial object.
With this I have set the scene for the first next step, which is prepare tables for upper levels of hierarchy to be used in my analysis.
The first argument describes SDO_TYPE. In this case value 2003 stands for "polygon". Basically it is expected that prefectures are displayed as regions on the map, so it should be in the shape of some polygon.
The second argument defines coordinate system which is used in my map, it specifies Spatial Reference system ID (SRID). In our case we use coordinate system with ID 8307. That is, the geometries are defined as using the coordinate system whose SRID is 8307 and whose well-known name is "Longitude / Latitude (WGS 84)". This is probably the most widely used coordinate system, and it is the one used for global positioning system (GPS) devices.
The third attribute has value NULL. This attribute defines SDO_POINT_TYPE, which I don't need as we on my map I will draw polygons and not just points.
The last two arguments of MDSYS.SDO_GEOMETRY specify geometry in more detail.
The first of the two defines the polygon itself, so for example, the first value 1 defines starting offset, value 1003 defines a simple element. The last value is again 1, which means that my polygon has got vertices that are linked by straight lines.
The second detailed attribute hold coordinates of my polygon. Coordinates are represented as an array of pairs (x,y) that stores the coordinate values that make up the boundary of a spatial object.
With this I have set the scene for the first next step, which is prepare tables for upper levels of hierarchy to be used in my analysis.