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:
Now create a narrative view:
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:
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
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.
ReplyDeleteThe 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.
Good Tip!
ReplyDeleteThanks for sharing!
regards
John
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).
ReplyDeleteI 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?
ReplyDeleteI 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
Eric,
ReplyDeleteWhat kind of data is in your second and third column?
regards
John
This comment has been removed by the author.
ReplyDeleteHello...
ReplyDeleteI 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???
@dimitris,
ReplyDeleteThanks for sharing!
Regards
John
John --
ReplyDeleteI 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
@Ben,
ReplyDeleteIf 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
Hey John!
ReplyDeleteThanks 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!
Hi John,
ReplyDeletemy 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
@Jose,
ReplyDeletewhich type of browser are you using?
regards John
//////
ReplyDelete@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...
Did you investigate why? Does it work in Chrome?
ReplyDeleteJohn
Hi,thanks for your quick reply.
ReplyDeleteI'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???
Thanks for sharing the code.
ReplyDeleteIt 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?
I have posted an update the Google Map Integration with OBIEE here,
ReplyDeletehttp://www.artofbi.com/index.php/2009/07/obiee-and-google-maps-integration/
Hi,
ReplyDeleteHas 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?
To all the problems with IE explorer have been solved:
ReplyDeletehttp://obiee101.blogspot.com/2009/07/obiee-google-maps-alternative-ending.html
Hi,
ReplyDeleteI 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.
@prathibha,
ReplyDeleteThe problem might be that you are sending to many requests at the same time to GOOGLE.
regards
john
@John
ReplyDeleteMy 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?
@prathibha,
ReplyDeleteGoogle 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
How can I implement Google Maps on Reports with Drill Down
ReplyDeleteHelp please ...
Himy name is Daniel and I am from México How can I implement Google Maps on Reports with Drill Down
ReplyDeleteHelp please ...
@Daniel,
ReplyDeleteCan 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
I feel much appreciate your help
ReplyDeletethis 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 ');
I Daniel
ReplyDeletemy question was about drill down (July 29, 2009 9:15 PM)
and to publish my report fictitious immigrant (August 21, 2009 9:27 PM)
@Anonymous,
ReplyDeleteTry using oracle spatial for this problem or wait a bit for obiee 11g
regards
John.
Hi John,
ReplyDeleteYou 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
@Jeffrey,
ReplyDeleteGoogle maps is only good for demo purposes, try Oracle spatial instead or wait fot OBIEE 11G
regards
John
Below is the code for Maps V3.0
ReplyDelete(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 "<"
@ramachandrarao
ReplyDeleteThanks for sharing, I will investigate 3.0 as soon as I have a spare moment!
Regards
All,
ReplyDeleteI'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
All,
ReplyDeleteI 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
@Jonathan:
ReplyDeleteGoogle 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
Hallo, please help me with following issue:
ReplyDeleteI 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
Michael,
ReplyDeleteI 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 ','.
Jonathan,
ReplyDeletemany 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
Hi I am looking for drill down report on Map from OBI through Mapviewer
ReplyDeleteCan 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
@Manish
ReplyDeletehttp://tinyurl.com/28ordoz
Hello!
ReplyDeleteI'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!
Hi John,
ReplyDeleteI 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).