Tuesday, March 17, 2009

OBIEE Google Maps Multiple Addresses

Based on a previous posting (http://obiee101.blogspot.com/2008/10/obiee-using-google-maps-q-style.html) I was asked if it's possible to add multiple addresses to a Google map. Actually this even simpler then having to create a separate map for each address.

Let's get our base table:

image

Now create a narrative view:

image

In the Prefix part put:

// Set the Google maps api key:
<script src="http://maps.google.com/maps?file=api&v=2&key=ABQIAAAAtgh-ZliBar5ci3sjZR_oGRSEtwgT_n0GADCjO95K9FWMY2XE2RQZwN8F1TggjSu117aG70pYMI0GfQ type="text/javascript"></script>

<script type="text/javascript">
// Declarations
var map = null;
var geocoder = null;
var marker = null;

function initialize() {
if (GBrowserIsCompatible()) {
// Create the map
map = new GMap2(document.getElementById("map_canvas"));
// Set the center without markers; 13 is the zoom level
map.setCenter(new GLatLng(37.4419, -122.1419), 13);
// Add the compass and zoom control
map.addControl(new GLargeMapControl());
// Add the Map type control
map.addControl(new GMapTypeControl());
// Get a new geocoder (needed to convert adresses to coordinates
geocoder = new GClientGeocoder();
// Get the ICON for the marker
icon0 = new GIcon();
icon0.image = "http://www.google.com/mapfiles/marker.png
icon0.shadow = "http://www.google.com/mapfiles/shadow50.png
icon0.iconSize = new GSize(20, 34);
icon0.shadowSize = new GSize(37, 34);
icon0.iconAnchor = new GPoint(9, 34);
icon0.infoWindowAnchor = new GPoint(9, 2);
icon0.infoShadowAnchor = new GPoint(18, 25);
// Get the Adresses
GetMapAdress ()
}
}

function showAddress(address,comment) {
// Coverts adresses to coordinates and set the marker on the chart
if (geocoder)
{
geocoder.getLatLng
(
address,
function(point)
{
if (!point)
{
alert(address + " not found");
}
else
{
map.setCenter(point, 13);
var marker = createMarker(point,icon0,comment);
map.addOverlay(marker);
// Opens the last marker
marker.openInfoWindowHtml(comment);
}
}
);
}
}
function createMarker(point, icon, popuphtml)
// Creates the marker
{
var popuphtml = "<div id=\"popup\">" + popuphtml + "<\/div>";
var marker = new GMarker(point, icon);
GEvent.addListener(marker, "click", function() {marker.openInfoWindowHtml(popuphtml);});
return marker;
}

function GetMapAdress ()
{

In the narrative part:

// From here it's build dynamicly in OBIEE
showAddress('@2', '@3');

In the Postfix part:

}

</script>
<body onload="initialize();return false" onunload="GUnload()">
<div id="map_canvas" style="width: 600px; height: 400px"></div>
</body>
</html>

No check if it works in the compound layout:

image

Till Next Time

Edit if you are using IE you migth want to look also here:

http://obiee101.blogspot.com/2009/07/obiee-google-maps-alternative-ending.html

44 comments:

Anonymous said...

I have found that if you try to plot a lot of addresses at once you will start getting geocode calls rejected from google as they only allow a certain rate of geocoding.

The way round it is to use something like:
setTimeout("addMarker('@2, @3, @4, @5', '@1', '@7', '@8')", @6 * 200);
which staggers these calls more evenly. Unfortunately it turns the population of the map into a bit of a slideshow.

John Minkjan said...

Good Tip!

Thanks for sharing!

regards
John

Anonymous said...

This is an awesome tutorial John! OBIEE can be transformed into GIS instantly :-). The only problem is that some companies/government have prohibitions(fears) of using external API (I know this is silly).

Eric steege said...

I cannot figuire out what I am doing wrong but I know the map is there but I cannot see it. Is there some color/font setting that needs to be changed in the OBIEE setup that would be required to make this work?

I put a header and footer on this so I know there is content in between but it is not visable.

I orginally was challenged with the google map call but I figured that out.

What else am I missing?

I copied the code exactly and then replaced the google map connection.

Any help is appreciated.

Eric

John Minkjan said...

Eric,

What kind of data is in your second and third column?

regards
John

eejimkos said...
This comment has been removed by the author.
eejimkos said...

Hello...
I finally succeed my porpose...
i manage with the following code(as urs)
//////////
prefix:
script src="http://maps.google.com/maps?file=api&v=2&oe=utf8&key=ABQIAAAAKKIyNTT8znjGXr8bgZHGEhRZwS6NK6efqIMWSf4oqB9fdz_vfBS_b0Do2aI6Lkm1vWGvwLhnR3JNCA"
type="text/javascript">
/script>


script type="text/javascript">


var map = null;
var geocoder = null;
var marker = null;

function initialize() {
setTimeout("addMarker('@2, @3)", 200000);
if (GBrowserIsCompatible()) {
// Create the map
map = new GMap2(document.getElementById("map_canvas"));
// Set the center without markers; 13 is the zoom level
map.setCenter(new GLatLng(37.4419, -122.1419), 11);
// Add the compass and zoom control
map.addControl(new GLargeMapControl());
// Add the Map type control
map.addControl(new GMapTypeControl());
// Get a new geocoder (needed to convert adresses to coordinates
geocoder = new GClientGeocoder();
// Get the ICON for the marker
icon0 = new GIcon();
icon0.image = "http://www.google.com/mapfiles/marker.png";
icon0.shadow = "http://www.google.com/mapfiles/shadow50.png";
icon0.iconSize = new GSize(20, 34);
icon0.shadowSize = new GSize(37, 34);
icon0.iconAnchor = new GPoint(9, 34);
icon0.infoWindowAnchor = new GPoint(9, 2);
icon0.infoShadowAnchor = new GPoint(18, 25);
// Get the Adresses
GetMapAdress ()
}
}

function showAddress(address,comment) {
// Coverts adresses to coordinates and set the marker on the chart
if (geocoder)
{
geocoder.getLatLng
(
address,
function(point)
{
if (!point)
{
alert(address + " not found");
}
else
{


map.setCenter(point, 13);
var marker = createMarker(point,icon0,'""<""center>""<""a href= "'+comment+'" target="_blank">Informations"<"/a>"<"/center>');
map.addOverlay(marker);
}
}
);
}
}
function createMarker(point, icon, popuphtml)

{
var popuphtml = ""<"div id=\"popup\">" + popuphtml + ""<"\/div>";
var marker = new GMarker(point, icon);
GEvent.addListener(marker, "click", function() {marker.openInfoWindowHtml(popuphtml);});
return marker;
}
function newwindow()
{
window.open('comment','jav','width=400,height=300,resizable=yes');
}

function GetMapAdress ()
{
/////////////
narative:
showAddress('@2', '@3');
/////////////////
postfix:
}

/script>
"<"body onload="initialize();return false" onunload="GUnload()">
"<"div id="map_canvas" style="width: 600px; height: 400px">"<"/div>
"<"/body>
"<"/html>
---------------------------
where when a user click on a spot,a new window opens with the suitable link(either any http,,link--or a link to oracle bi report).
My problem is that in firefox works just fine.
On Explorer 7 , when i am in the section of building the report(in narative one-and i try the pre-view dashboard button-->>all is ok.)BUT in my dashboard nothing happen-blank page-nothing....
any help???

John Minkjan said...

@dimitris,

Thanks for sharing!

Regards

John

Unknown said...

John --

I have read your other blogs and found them to be quite useful. I have integrated google maps into my OBIEE narrative but need to dynamically zoom the viewport so that when i reference @2, @3 for my addresses the script will auto zoom to fit. Is there an easy way to do this?

Thanks in advance,
Ben

John Minkjan said...

@Ben,

If you know the "spread" of the adress you could try to dynamicly set zoomlevel. If you already have the GEO coordinates of the adress you can maybe calculate it. Try the googlemaps discussion forum for more info.

regards john

Anonymous said...

Hey John!
Thanks for your tips, we find your work very helpful. Is it possible you could post something along the same lines that would take coordinates dynamically (@2,@3, etc) and create a polygon around a State's borders? Cheers!

Unknown said...

Hi John,

my name is Jose and I am from Spain. I have applied your idea to integrate Analytics and Google Maps and it's great. Thanks a lot for it, it's fantastic.

But... I have a problem and it would be great if you could help me.
When I design the request (both on Analytics 7.8 and OBIEE), I can only see the map on the Preview window (the popup window) but not on the Compound Layout and, of course, when I add the request to a dashboard. Then, a blank area appears instead of the map.

Do you have any idea why this is happenning?

Thanks a lot in advance and regards

John Minkjan said...

@Jose,

which type of browser are you using?

regards John

eejimkos said...

//////
@Jose,

which type of browser are you using?

regards John
///////////////
Hi,
John as i have mentioned before i have the exact same problem.In any version of Internet Explorer does not work.It works only in firefox...

John Minkjan said...

Did you investigate why? Does it work in Chrome?

John

eejimkos said...

Hi,thanks for your quick reply.
I've also check it to oracle forum and metalink3 with no result.
In firefox all is ok.
In Internet Explorer the map is visible only on the Preview window (the popup window) and not in dashboard.
--there is no compile error--
1.it doesnot show any error in the page...
2.i compile it as a simple html page , and everything is ok..i've search it a little bit,i believe that IE load the classes a little bit different and this confuse Bi??or opposite ?
In Chrome everything is ok...
Any help???

Unknown said...

Thanks for sharing the code.
It works well.
But when I tried with zip codes,it worked well only for US zip codes.
Is there any change in code for different countries?

Christian said...

I have posted an update the Google Map Integration with OBIEE here,
http://www.artofbi.com/index.php/2009/07/obiee-and-google-maps-integration/

Unknown said...

Hi,
Has anybody noticed that some data points(not same data points each time) go missing after every refresh of the map in obi.
Is there any solution for this?
Any changes required for the code?

John Minkjan said...

To all the problems with IE explorer have been solved:
http://obiee101.blogspot.com/2009/07/obiee-google-maps-alternative-ending.html

Unknown said...

Hi,
I have changed the post fix part of the code as mentioned in the link
http://obiee101.blogspot.com/2009/07/obiee-google-maps-alternative-ending.html
Still the same problem persists, some countries go missing each time map is refreshed.

John Minkjan said...

@prathibha,

The problem might be that you are sending to many requests at the same time to GOOGLE.

regards

john

Unknown said...

@John

My data has only 20 countries.
Among them 4 to 5 countries go missing after refresh.
Is there any limitation on the number of countries to be present in the data?

John Minkjan said...

@prathibha,

Google maps handles the request asynchronisly, which means you have to wait for the answer, if your internet connection is slower then the rendering by the presentation server you might "miss" some answer. Also a proxy server between the presentation server and your internet connection can cause problems.

Regards

John

Anonymous said...

How can I implement Google Maps on Reports with Drill Down
Help please ...

Anonymous said...

Himy name is Daniel and I am from México How can I implement Google Maps on Reports with Drill Down
Help please ...

John Minkjan said...

@Daniel,

Can you elaborate a bit on what kind of drill-down functionality you are looking for? If you want to drill-down on the map you will have to go for map viewer.

regards

John

Anonymous said...

I feel much appreciate your help

this is my table

COUNTRY --- TOTAL ---INMIGRANTS
United State 11,000 millions
Great Bretain 8,000 millions
Jhon David 12,000 millios


when you click in the U.S. over the next chart will show me and I locate the states on the map


COUNTRY---STATES---TOTAL INMIGRANTS
United State Missisipi 5,000 millions
United State New York 5,000 millions
United State Texas 1,000 millions
United State Arizona 1,000 millions




in the narrative I have the following
narrative: // From here it's build dynamicly in OBIEE

showAddress('@1','@1 @2 ');

Anonymous said...

I Daniel

my question was about drill down (July 29, 2009 9:15 PM)
and to publish my report fictitious immigrant (August 21, 2009 9:27 PM)

John Minkjan said...

@Anonymous,

Try using oracle spatial for this problem or wait a bit for obiee 11g

regards

John.

Unknown said...

Hi John,
You mentioned that Map Viewer would be required for drill down capability. We have a state map rendered with OBIEE. When the user clicks on one of the state counties, we want to drill down in the map to that county and have the related OBIEE charts update to display that county's information.

Is this map driven integration possible? Our GIS team is running ESRI and has OGC based layers. Can the drill downs remain in the same OBIEE window without having to create a separate tab/dashboard for the county?

thank you!
jeff

John Minkjan said...

@Jeffrey,

Google maps is only good for demo purposes, try Oracle spatial instead or wait fot OBIEE 11G

regards

John

Anonymous said...

Below is the code for Maps V3.0
(it doesn't need license key)

Narrative View
Prefix
/////////////////////////////
?!?script type="text/javascript" src="http://maps.google.com/maps/api/js?sensor=false">
?!?/script>
?!?script type="text/javascript">
var geocoder;
var map;
function initialize() {
geocoder = new google.maps.Geocoder();
var latlng = new google.maps.LatLng(40.086, -82.808);
var myOptions = {
zoom: 8,
center: latlng,
mapTypeId: google.maps.MapTypeId.ROADMAP
}
map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
GetMapAdress ();
}

function showAddress(address,comment) {
if (geocoder) {
geocoder.geocode( { 'address': address}, function(results, status) {
if (status == google.maps.GeocoderStatus.OK) {
map.setCenter(results[0].geometry.location);
var marker = new google.maps.Marker({
map: map,
position: results[0].geometry.location
});
var infowindow = new google.maps.InfoWindow({
content:comment
});
google.maps.event.addListener(marker, 'click', function() {
infowindow.open(map,marker);
});
} else {
alert("Geocode was not successful for the following reason: " + status);
}
});
}
}

function GetMapAdress (){

/////////////////////////////
In Narrative
// From here it's build dynamicly in OBIEE
showAddress('@2', '@3');

///////////////////////////
In Postfix

}
?!?/script>
?!?script src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.js" type="text/javascript">?!?/script>
?!?script type="text/javascript">
$(document).ready( function(){initialize();return false;});
?!?/script>

?!?body onunload="GUnload()">
?!?div id="map_canvas" style="width: 600px; height: 400px" > ?!?/div>
?!?/body>

-------------------
Replace ?!? with "<"

John Minkjan said...

@ramachandrarao

Thanks for sharing, I will investigate 3.0 as soon as I have a spare moment!

Regards

Unknown said...

All,

I've developed a small data mart representing information about my runs (time, distance, location, etc.). I have OBIEE set up as my BI Environment, and would like to integrate Google Maps into a few reports.

I have all latitudes and logitudes captured in a table at (3 second incriments - about every 20ft.)...

Can someone please give me a few pointers on how to call the lat / longs directly (rather than converting to address)?

Thanks,

Jonathan

Unknown said...

All,

I have semi-successfully integrated google maps to display lat/lon coordinates for a particular workout...Each coordinate has a unique LOCATION_ID, and I have filtered my report to return every 10 records (MOD).

My issues is that, OBIEE seems to drop several records when plotting spatially. For example, if I have 50 records in my table (and 50 unique coordinates) then OBIEE will only plot 20 on the map.

If I refresh my browser, clear the presentation cache, and re-run the request, OBIEE will generate a map with a different 20 coordinates.

Can anyone help to successfully plot all points?

Thanks

John Minkjan said...

@Jonathan:
Google maps handles the request asynchronisly, which means you have to wait for the answer, if your internet connection is slower then the rendering by the presentation server you might "miss" some answer. Also a proxy server between the presentation server and your internet connection can cause problems.


Regards

michal said...

Hallo, please help me with following issue:

I am using to display in map directly GPS coordinates
showAddress('@1, @2');
where @1, @2 stands for GPS Lat. and GPS Long.
@1, @2 are in form with decimal comma, e.g. 49,2345 and google map work only with

decimal point 49.2345.
Is there any way how to replace point instead of comma?
I have tried JAVA function replace

var Lat_a;
var Lat_b;
Lat_a = '@1'.replace( ',' , '.' );
Lat_b = '@2'.replace( ',' , '.' );
showAddress('Lat_a, Lat_b');

but this substitution is not working. Maybe some error in Java code???

Please help.
Thank in advance,
rgs,
Michal

Unknown said...

Michael,

I think your best bet here is to alter the lat and longs at in the semantic layer.

I would put logic on the lat / long attributes that would be the equivalent of :

select left(latitude,0,3) AS left lat,
left(latitude,4) as right lat,
left(longitude,0,3) AS left long, left(longitude,4) as right long from location_dim;

I would then create a new attribute base on the one you just modified:

Select CONCAT(CONCAT(left_lat, '.'), right_lat) as LATITUDE,
CONCAT(CONCAT(left_long, '.'), right_long) as LONGITUDE from location_dim;

This will take your latitudes to the following stages:

49,294 (lat) -98,384 (long)

49 (left_lat) , 294 (right_lat)
-98 (left_long), 384 (right_long)

49.294 (LATITUDE) -98.384 (LONGITUDE)

This will work for ya.

NOTE: Additional logic will be required, as the length of the left stings used in sql has been hard coded, and will need to be a variable that looks for the ','.

michal said...

Jonathan,
many thank for tip.

At the end I used this formula directly in OBI column.

REPLACE(CAST "BTS Latitude" * 0.000069444444444 AS char), ',', '.')
I had to retype number to char before use of REPLACE, RIGHT, LEFT functions.
GPS data are in multiplayed form GPS * 14400 and then i got the number.
Finaly its working.
Thanks again for quick response and turn me to the right way :-)
rgs,
Michal

Manish said...

Hi I am looking for drill down report on Map from OBI through Mapviewer
Can you please share the steps to do create a drill down report in Map from BI itself for ex.
User clicks on state (say California (CA)) which shows sales data for whole CA then on clicking CA further it will zoom and shows all counties of CA ,on clicking any of the counties of CA it will shows data of that county and after clickin further it will show all cities in that counties and so on for your reference please check this URL which defines how to create but not given detailed explanation,so requesting you to please explain it in detailed steps if possible.
URL:-http://www.clearpeaks.com/blog/oracle-bi-ee/visualizing-information-using-maps-in-oracle-business-intelligence

Anonymous said...

@Manish

http://tinyurl.com/28ordoz

Anonymous said...

Hello!
I'm from Russia!
Please help me!!!
Your blog is very interesting and important for me, because i'm working with OBIEE! Also, i use google maps API v3 in OBIEE and have some problems with google maps v3 in OBIEE. A man "ramachandrarao" wrote the comment about google map v3, it's helped me, but i don't know how use markercluster there! I know about markercluster.js but i don't know how use "markers.push(marker) and var markerClusterer = new MarkerClusterer(map, markers);"!
Help please! Where in code man "ramachandrarao" i must use those rows.
Thanks!

Anonymous said...

Hi John,
I am trying to use Ramchandra code for integrating google maps.I have only data for 3 rows.but still unsuccessful.i appreciate if you can help me.my lat and long(39.072,-76.790).