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