I  think it is quite safe to say that I do not need to explain the virtues of spatial analytics.  In our previous posts we have discussed several ways use spatial analytics with OBIEE. In this post I will discuss how to develop a new custom point of interest layer (POI Layer) within Oracle spatial so that it can be used with OBIEE. 

POI layers can be used to capture a set of points such as retail locations, shops etc. for use with Oracle spatial. Similar to spatial layers, POI layers are rendered on top of the base map (e.g. google maps or bing maps) and can be linked with columns in OBIEE for geo spatial analysis.

Pre-requisites

• Oracle Map Builder
• Oracle Map Viewer (comes deployed with WebLogic)
• Oracle BI EE v11.1.1.6 or higher
• Oracle Database v11.2 or higher
• POI geospatial data (latitude / longitude)
• POI dimension is already available in your Data Warehouse system

This demo is done with Oracle BI EE v11.1.1.9, Oracle Map Builder 12.2.1.0.0. The screenshots which are captured by older version can be a little bit different but all steps are quite similar.

This section describes the steps to create geospatial data which we can import the shape file or create manually using excel file (that contains the geometry information i.e. latitude / longitude. If you are using an ESRI shapefile please use the steps defined in the earlier blog on map builder. 

Below is sample script to create new geometry table for retailer. We need to ensure that there is one column which the data type is MDSYS.SDO_GEOMETRY.

You can SQL Loader or other tools to import your geospatial data to the new table in geometry schema. This new table stores all geospatial data for the layers such as Retailer, Services, Youth hangout, Administrative, Industrial locations, Public spaces, Shopping mall, market, Transit points, Public spaces, Tourist locations. 

Once loaded into the oracle database it can be viewed as below 

Once data is loaded now we need to update the GEOMETRY column in the table. This is a very simple SQL statement to update the GEOMETRY based on the POI_LAT and POI_LONG columns from the table. Couple of points to note 

update DIALOG_POI_RETAILER set GEOMETRY = SDO_CS.TRANSFORM(
     mdsys.sdo_geometry(
     2001,  -- two-dimensional polygon
     4326,  -- SRID of google maps since we are going to render on google maps
     mdsys.sdo_point_type(POI_LONG,POI_LAT,NULL),
     NULL, -- used for polygons
     NULL  -- used for polygons
     ),
     'USE_SPHERICAL', -- asks oracle to use the spherical SRID instead of ellipsoidal 
     3785 -- SRID for oracle 
);
commit;

Before we can use this POI layter we need to let Oracle know that the newly added table has a geometry column so that it can be used in map builder eventually to add a layer. This is done by adding the entry in the USER_SDO_GEOM_METADATA table. 

insert into USER_SDO_GEOM_METADATA values ('DIALOG_POI_RETAILER', 'GEOMETRY', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X', -20037508, 20037508, .00000005), MDSYS.SDO_DIM_ELEMENT('Y', -20037508, 20037508, .00000005)), 3785);

Now the last step is to create the spatial index so that it can be queried by Mapviewer 

create index POI_RETAILER_IDX on DIALOG_POI_RETAILER(GEOMETRY) indextype is mdsys.spatial_index;

With all of these steps done now we can go ahead and add this as a layer in map builder as defined in an earlier post.  With the layer defined we should be able to render the layer successfully in Mapbuilder as shown below. 

Related blogs 

Geospatial Analytics in OBIEE Basics

Shapefiles and map builder

Role of Mapviewer