Data Analytics Blog: December 2009

Since we have started to integrate Google maps into an OBIEE application, the users have requested the ability to look at a Map of the US and see concentrations of points on a map. They wanted to see at a national level where the companies they were interested in were located. They also wanted the ability to zoom in and see exactly where each facility was located. The solution we chose to meet the requirement was the Google maps and the Marker Cluster API found here: Gmaps utility library

The way the code works as described by the creator: “MarkerClusterer collects markers into different clusters and displays the number of markers in each cluster with a label, creating new clusters as the map zoom level changes. The clustering algorithm is simple; for each new marker it sees, it either puts it inside a pre-existing cluster, or it creates a new cluster if the marker doesn’t lie within the bounds of any current cluster.”

So for our project we took a set of about 10,000 facility locations and attempted to map them but found the first limitation of JavaScript being that most clients will freeze if you plot over 6,000 or so points. So we gave the users the ability to filter the data on several criteria to limit the number of points in the map and placed a governor on the number of points by setting max rows to 3000. Below is the top zoom level view of the data set we started with:

You will notice that the default clustering algorithm has 3 colors representing clusters of more than 100 (Red), 10 to 99 (Yellow) and 2 to 9 (Blue). A single location will be represented with a tear drop icon. So now instead of seeing 3,000 separate points there are about 30 clusters on the map. The eye is quickly drawn to the northeast with its multiple red clusters. Now to meet the requirements a user can zoom in anywhere on the map and the clusters re-calculate.

So we have seen how the code works, below is the steps used to implement this on our project.

First we geocoded our addresses and stored latitudes and longitudes for each data point we wanted to map. If you do not geocode the addresses you really need to work with less points (less than 1000) and the results will take much longer.
Then we created a query that has the company facility name and internal company number (used for navigation), lat and long, and a piece of data called rank for displaying on the single points in the map. The RSUM(1) is used for displaying if more that 3000 rows could be displayed in the map.

The heavy lifting is done in a narrative view using JavaScript, the Google maps API, and the MarkerCluster API

Get a Google Maps API Key from: http://code.google.com/apis/maps/signup.html - Google maps API uses a key generated based on the IP and name from the calling server, so be sure you are on your web server when obtaining this. Get a key based on your own OBIEE server address.
Create a narrative view and make sure you mark the contains HTML Markup checkbox

In the Prefix box add the following code: (See the red text comments that explain parts of the code)

Prefix Text:

[−]

<html>
<head>
<script src="http://maps.google.com/maps?file=api&v=2.x&client=gme-dotdc&sensor=false" type="text/javascript"></script> //GOOGLE API
<script type="text/javascript">
var map1 = null;
var count = 1;
var point1 = null;
var marker;
var markers = [];
function initialize1(comp_facility, lat, long, row_wid, cnt, rank)
{

if (count == 1)
{
if (GBrowserIsCompatible())
{
map1 = new GMap2(document.getElementById(1),{size:new GSize(900,600)});
map1.setCenter(new GLatLng(39.5, -98.35), 4); //Center Map on US
map1.addControl(new GLargeMapControl());
map1.addControl(new GMapTypeControl());
markerClusterer = new MarkerClusterer(map1);
count = 0;
alert("Due to the large volume of Data this Report may take upto 5 Min to display the Map. Please do not close the browser."); //A Warning to users that the map generation may take time
}
}
point1 = new GLatLng(lat, long); // Creates the data point to display
// marker = new GMarker(point1);
marker = createMarker(point1, row_wid, comp_facility, rank); // Text to display on popup
markers.push(marker); // Type of marker for a single location

if (cnt == 1 )
{
var markerCluster = new MarkerClusterer(map1, markers);
}

}

function createMarker(point, int_num,cmp_facility, cmp_rank)
{

var marker = new GMarker(point);
GEvent.addListener(marker, "click", function() {marker.openInfoWindowHtml("<a href = http://www.yoururlhere.com/analytics/saw.dll?Dashboard&PortalPath=/shared/Safety%20Maps/_portal/Safety%20Maps&Page=Navigation&Action=Navigate&col1=Company.%22Internal%20Company%20Number%22&val1="+ int_num + " target=\"_blank\">" + cmp_facility +"<br/><center>" + "Company Rank: " + cmp_rank + "</center></a>");});
return marker; //On click event will take user to a OBIEE dashboard
}

function MarkerClusterer(map, opt_markers, opt_opts) {
// private members
var clusters_ = [];
var map_ = map;
var maxZoom_ = null;
var me_ = this;
var gridSize_ = 60;
var sizes = [53, 56, 66, 78, 90];
var styles_ = [];
var leftMarkers_ = [];
var mcfn_ = null;

var i = 0;
for (i = 1; i <= 5; ++i) {
styles_.push({
'url': "http://gmaps-utility-library.googlecode.com/svn/trunk/markerclusterer/images/m" + i + ".png", //location of the cluster graphics
'height': sizes[i - 1],
'width': sizes[i - 1]
});
} //code for generating the clusters

if (typeof opt_opts === "object" && opt_opts !== null) {
if (typeof opt_opts.gridSize === "number" && opt_opts.gridSize > 0) {
gridSize_ = opt_opts.gridSize;
}
if (typeof opt_opts.maxZoom === "number") {
maxZoom_ = opt_opts.maxZoom;
}
if (typeof opt_opts.styles === "object" && opt_opts.styles !== null && opt_opts.styles.length !== 0) {
styles_ = opt_opts.styles;
}
}

function addLeftMarkers_() {
if (leftMarkers_.length === 0) {
return;
}
var leftMarkers = [];
for (i = 0; i < leftMarkers_.length; ++i) {
me_.addMarker(leftMarkers_[i], true, null, null, true);
}
leftMarkers_ = leftMarkers;
}

this.getStyles_ = function () {
return styles_;
};

this.clearMarkers = function () {
for (var i = 0; i < clusters_.length; ++i) {
if (typeof clusters_[i] !== "undefined" && clusters_[i] !== null) {
clusters_[i].clearMarkers();
}
}
clusters_ = [];
leftMarkers_ = [];
GEvent.removeListener(mcfn_);
};

function isMarkerInViewport_(marker) {
return map_.getBounds().containsLatLng(marker.getLatLng());
}

function reAddMarkers_(markers) {
var len = markers.length;
var clusters = [];
for (var i = len - 1; i >= 0; --i) {
me_.addMarker(markers[i].marker, true, markers[i].isAdded, clusters, true);
}
addLeftMarkers_();
}

this.addMarker = function (marker, opt_isNodraw, opt_isAdded, opt_clusters, opt_isNoCheck) {
if (opt_isNoCheck !== true) {
if (!isMarkerInViewport_(marker)) {
leftMarkers_.push(marker);
return;
}
}

var isAdded = opt_isAdded;
var clusters = opt_clusters;
var pos = map_.fromLatLngToDivPixel(marker.getLatLng());

if (typeof isAdded !== "boolean") {
isAdded = false;
}
if (typeof clusters !== "object" || clusters === null) {
clusters = clusters_;
}

var length = clusters.length;
var cluster = null;
for (var i = length - 1; i >= 0; i--) {
cluster = clusters[i];
var center = cluster.getCenter();
if (center === null) {
continue;
}
center = map_.fromLatLngToDivPixel(center);

// Found a cluster which contains the marker.
if (pos.x >= center.x - gridSize_ && pos.x <= center.x + gridSize_ &&
pos.y >= center.y - gridSize_ && pos.y <= center.y + gridSize_) {
cluster.addMarker({
'isAdded': isAdded,
'marker': marker
});
if (!opt_isNodraw) {
cluster.redraw_();
}
return;
}
}

// No cluster contain the marker, create a new cluster.
cluster = new Cluster(this, map);
cluster.addMarker({
'isAdded': isAdded,
'marker': marker
});
if (!opt_isNodraw) {
cluster.redraw_();
}

// Add this cluster both in clusters provided and clusters_
clusters.push(cluster);
if (clusters !== clusters_) {
clusters_.push(cluster);
}
};

this.removeMarker = function (marker) {
for (var i = 0; i < clusters_.length; ++i) {
if (clusters_[i].remove(marker)) {
clusters_[i].redraw_();
return;
}
}
};

this.redraw_ = function () {
var clusters = this.getClustersInViewport_();
for (var i = 0; i < clusters.length; ++i) {
clusters[i].redraw_(true);
}
};

this.getClustersInViewport_ = function () {
var clusters = [];
var curBounds = map_.getBounds();
for (var i = 0; i < clusters_.length; i ++) {
if (clusters_[i].isInBounds(curBounds)) {
clusters.push(clusters_[i]);
}
}
return clusters;
};

this.getMaxZoom_ = function () {
return maxZoom_;
};

this.getMap_ = function () {
return map_;
};

this.getGridSize_ = function () {
return gridSize_;
};

this.getTotalMarkers = function () {
var result = 0;
for (var i = 0; i < clusters_.length; ++i) {
result += clusters_[i].getTotalMarkers();
}
return result;
};

this.getTotalClusters = function () {
return clusters_.length;
};

this.resetViewport = function () {
var clusters = this.getClustersInViewport_();
var tmpMarkers = [];
var removed = 0;

for (var i = 0; i < clusters.length; ++i) {
var cluster = clusters[i];
var oldZoom = cluster.getCurrentZoom();
if (oldZoom === null) {
continue;
}
var curZoom = map_.getZoom();
if (curZoom !== oldZoom) {

// If the cluster zoom level changed then destroy the cluster
// and collect its markers.
var mks = cluster.getMarkers();
for (var j = 0; j < mks.length; ++j) {
var newMarker = {
'isAdded': false,
'marker': mks[j].marker
};
tmpMarkers.push(newMarker);
}
cluster.clearMarkers();
removed++;
for (j = 0; j < clusters_.length; ++j) {
if (cluster === clusters_[j]) {
clusters_.splice(j, 1);
}
}
}
}

// Add the markers collected into marker cluster to reset
reAddMarkers_(tmpMarkers);
this.redraw_();
};

this.addMarkers = function (markers) {
for (var i = 0; i < markers.length; ++i) {
this.addMarker(markers[i], true);
}
this.redraw_();
};

// initialize
if (typeof opt_markers === "object" && opt_markers !== null) {
this.addMarkers(opt_markers);
}

// when map move end, regroup.
mcfn_ = GEvent.addListener(map_, "moveend", function () {
me_.resetViewport();
});
}

function Cluster(markerClusterer) {
var center_ = null;
var markers_ = [];
var markerClusterer_ = markerClusterer;
var map_ = markerClusterer.getMap_();
var clusterMarker_ = null;
var zoom_ = map_.getZoom();

this.getMarkers = function () {
return markers_;
};

this.isInBounds = function (bounds) {
if (center_ === null) {
return false;
}

if (!bounds) {
bounds = map_.getBounds();
}
var sw = map_.fromLatLngToDivPixel(bounds.getSouthWest());
var ne = map_.fromLatLngToDivPixel(bounds.getNorthEast());

var centerxy = map_.fromLatLngToDivPixel(center_);
var inViewport = true;
var gridSize = markerClusterer.getGridSize_();
if (zoom_ !== map_.getZoom()) {
var dl = map_.getZoom() - zoom_;
gridSize = Math.pow(2, dl) * gridSize;
}
if (ne.x !== sw.x && (centerxy.x + gridSize < sw.x || centerxy.x - gridSize > ne.x)) {
inViewport = false;
}
if (inViewport && (centerxy.y + gridSize < ne.y || centerxy.y - gridSize > sw.y)) {
inViewport = false;
}
return inViewport;
};

this.getCenter = function () {
return center_;
};

this.addMarker = function (marker) {
if (center_ === null) {
/*var pos = marker['marker'].getLatLng();
pos = map.fromLatLngToContainerPixel(pos);
pos.x = parseInt(pos.x - pos.x % (GRIDWIDTH * 2) + GRIDWIDTH);
pos.y = parseInt(pos.y - pos.y % (GRIDWIDTH * 2) + GRIDWIDTH);
center = map.fromContainerPixelToLatLng(pos);*/
center_ = marker.marker.getLatLng();
}
markers_.push(marker);
};

this.removeMarker = function (marker) {
for (var i = 0; i < markers_.length; ++i) {
if (marker === markers_[i].marker) {
if (markers_[i].isAdded) {
map_.removeOverlay(markers_[i].marker);
}
markers_.splice(i, 1);
return true;
}
}
return false;
};

this.getCurrentZoom = function () {
return zoom_;
};

this.redraw_ = function (isForce) {
if (!isForce && !this.isInBounds()) {
return;
}

// Set cluster zoom level.
zoom_ = map_.getZoom();
var i = 0;
var mz = markerClusterer.getMaxZoom_();
if (mz === null) {
mz = map_.getCurrentMapType().getMaximumResolution();
}
if (zoom_ >= mz || this.getTotalMarkers() === 1) {

// If current zoom level is beyond the max zoom level or the cluster
// have only one marker, the marker(s) in cluster will be showed on map.
for (i = 0; i < markers_.length; ++i) {
if (markers_[i].isAdded) {
if (markers_[i].marker.isHidden()) {
markers_[i].marker.show();
}
} else {
map_.addOverlay(markers_[i].marker);
markers_[i].isAdded = true;
}
}
if (clusterMarker_ !== null) {
clusterMarker_.hide();
}
} else {
// Else add a cluster marker on map to show the number of markers in
// this cluster.
for (i = 0; i < markers_.length; ++i) {
if (markers_[i].isAdded && (!markers_[i].marker.isHidden())) {
markers_[i].marker.hide();
}
}
if (clusterMarker_ === null) {
clusterMarker_ = new ClusterMarker_(center_, this.getTotalMarkers(), markerClusterer_.getStyles_(), markerClusterer_.getGridSize_());
map_.addOverlay(clusterMarker_);
} else {
if (clusterMarker_.isHidden()) {
clusterMarker_.show();
}
clusterMarker_.redraw(true);
}
}
};

this.clearMarkers = function () {
if (clusterMarker_ !== null) {
map_.removeOverlay(clusterMarker_);
}
for (var i = 0; i < markers_.length; ++i) {
if (markers_[i].isAdded) {
map_.removeOverlay(markers_[i].marker);
}
}
markers_ = [];
};

this.getTotalMarkers = function () {
return markers_.length;
};
}

function ClusterMarker_(latlng, count, styles, padding) {
var index = 0;
var dv = count;
while (dv !== 0) {
dv = parseInt(dv / 10, 10);
index ++;
}

if (styles.length < index) {
index = styles.length;
}
this.url_ = styles[index - 1].url;
this.height_ = styles[index - 1].height;
this.width_ = styles[index - 1].width;
this.textColor_ = styles[index - 1].opt_textColor;
this.anchor_ = styles[index - 1].opt_anchor;
this.latlng_ = latlng;
this.index_ = index;
this.styles_ = styles;
this.text_ = count;
this.padding_ = padding;
}

ClusterMarker_.prototype = new GOverlay();

ClusterMarker_.prototype.initialize = function (map) {
this.map_ = map;
var div = document.createElement("div");
var latlng = this.latlng_;
var pos = map.fromLatLngToDivPixel(latlng);
pos.x -= parseInt(this.width_ / 2, 10);
pos.y -= parseInt(this.height_ / 2, 10);
var mstyle = "";
if (document.all) {
mstyle = 'filter:progid:DXImageTransform.Microsoft.AlphaImageLoader(sizingMethod=scale,src="' + this.url_ + '");';
} else {
mstyle = "background:url(" + this.url_ + ");";
}
if (typeof this.anchor_ === "object") {
if (typeof this.anchor_[0] === "number" && this.anchor_[0] > 0 && this.anchor_[0] < this.height_) {
mstyle += 'height:' + (this.height_ - this.anchor_[0]) + 'px;padding-top:' + this.anchor_[0] + 'px;';
} else {
mstyle += 'height:' + this.height_ + 'px;line-height:' + this.height_ + 'px;';
}
if (typeof this.anchor_[1] === "number" && this.anchor_[1] > 0 && this.anchor_[1] < this.width_) {
mstyle += 'width:' + (this.width_ - this.anchor_[1]) + 'px;padding-left:' + this.anchor_[1] + 'px;';
} else {
mstyle += 'width:' + this.width_ + 'px;text-align:center;';
}
} else {
mstyle += 'height:' + this.height_ + 'px;line-height:' + this.height_ + 'px;';
mstyle += 'width:' + this.width_ + 'px;text-align:center;';
}
var txtColor = this.textColor_ ? this.textColor_ : 'black';

div.style.cssText = mstyle + 'cursor:pointer;top:' + pos.y + "px;left:" +
pos.x + "px;color:" + txtColor + ";position:absolute;font-size:11px;" +
'font-family:Arial,sans-serif;font-weight:bold';
div.innerHTML = this.text_;
map.getPane(G_MAP_MAP_PANE).appendChild(div);
var padding = this.padding_;
GEvent.addDomListener(div, "click", function () {
var pos = map.fromLatLngToDivPixel(latlng);
var sw = new GPoint(pos.x - padding, pos.y + padding);
sw = map.fromDivPixelToLatLng(sw);
var ne = new GPoint(pos.x + padding, pos.y - padding);
ne = map.fromDivPixelToLatLng(ne);
var zoom = map.getBoundsZoomLevel(new GLatLngBounds(sw, ne), map.getSize());
map.setCenter(latlng, zoom);
});
this.div_ = div;
};

ClusterMarker_.prototype.remove = function () {
this.div_.parentNode.removeChild(this.div_);
};

ClusterMarker_.prototype.copy = function () {
return new ClusterMarker_(this.latlng_, this.index_, this.text_, this.styles_, this.padding_);
};

ClusterMarker_.prototype.redraw = function (force) {
if (!force) {
return;
}
var pos = this.map_.fromLatLngToDivPixel(this.latlng_);
pos.x -= parseInt(this.width_ / 2, 10);
pos.y -= parseInt(this.height_ / 2, 10);
this.div_.style.top = pos.y + "px";
this.div_.style.left = pos.x + "px";
};

ClusterMarker_.prototype.hide = function () {
this.div_.style.display = "none";
};

ClusterMarker_.prototype.show = function () {
this.div_.style.display = "";
};

ClusterMarker_.prototype.isHidden = function () {
return this.div_.style.display === "none";
}
</script>
</head>
<body>

Narrative text:

Narrative Text:

[−]

<div id="1" > <script type="text/javascript"> initialize1('@1','@3','@4','@5','@6','@2') </script>
</div>

Note the@ numbers are in the order of the function call in the prefix:

function initialize1(comp_facility, lat, long, row_wid, cnt, rank)

Finally in the PostFix add:

Postfix Text:

[−]

</body>
</html>

And set the max rows to a number less than 5000.

The next blog I will show how to use the single points on the map to drill down into another dashboard in OBIEE.

Data Analytics Blog

Friday, December 18, 2009

OBIEE: Seeding the Dashboards for End Users and Purging on Nightly Basis

Wednesday, December 2, 2009

Integrating OBIEE and Google Maps with the MarkerClusterer API