An Oracle blog about BI Publisher

  • RTF
    August 12, 2008

Googlin' Maps

OK OK, enough of Google already!

This is the last for now I promise, Christoper and his blog have to take some of the blame. I was idly thinking about how to get Google map output into Publishers outputs when I read his article last night on google geocoding and plsql. The pl/sql was not so useful but the Google API that is callable via HTTP and can be made to return XML got me thinking. Thanks Chris!

The other piece to the 'puzzle' comes from Google Maps - they now have a static image API that can be called via HTTP. With both pieces and a little funking around and I can get Google Maps images into Publisher outputs.

To avoid using static XML output I thought I would create a Publisher report using the Geocode API. The url to call is:


That was simple enough, just create a report based on an HTTP(XML) feed.


The 'output', returned data format i.e. XML and 'key', developer key i.e. abcde, parameters are hard coded. The 'q' parameter is mapped to a user enterable address parameter
With this report defined Im now getting XML back from the API based on an address, its blisteringly fast and will find match(es) depending on whats entered. The XML returned takes the format:
<kml xmlns="http://earth.google.com/kml/2.0">
<name>500 Oracle Parkway</name>
<Placemark id="p1">
<address>500 Oracle Pkwy, Redwood City, CA 94065, USA</address>
<AddressDetails Accuracy="8" xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0">
<LocalityName>Redwood City</LocalityName>
<ThoroughfareName>500 Oracle Pkwy</ThoroughfareName>

Lots of info about the address I entered, seems simple enough to build the template. However, there are a couple of issues that we need to address.

Namespace - you'll see in the top of the XML the line
<kml xmlns="http://earth.google.com/kml/2.0">

that 'empty' namespace is a real pain. I think who ever developed the XML at Google was not paying attention. Unless we address it, nothing is going to appear when we run our template.
The following


will not render because of that namespace.
So we need to declare it in the template, but there is no namespace prefix in the declaration above. So, we have to create a dummy one in our template.


we then need to prefix all of our element names with 'x:'

- its a pain but there is no other way around it.

Coordinates -I did not spot this until I got to grips with the Google static map API - there is lots of documentation on the URL construct here. Heres the basics:


there are other bells and whistles but you can check that out.
Being a little ignorant of the geo data (longitude, latitude) I just plugged in my returned data '-122.262227,37.531028,0' into the URL. Sadly , it failed miserably!

The URL does not need the last value '0' and the other two need to be swapped! Strange that both APIs come from Google but the data is not compatible? To get the data into the right format we need some string manipulation. Heres the source data:


To get the values we need, we can use some native string manipulation functions :

<?substring-before(x:coordinates,',')?> ==> -122.262227

<?substring-before(substring-after(x:coordinates,','),',')?> ==> 37.531028

Now all we need to do is put a dummy image of the right size into our template and just as with the other Google Charts integration we use the url: function and construct the URL to call Google for a static map of the returned address we provided.









,'&key=|dev key|

You can get a dev key from Google - I was lazeee and hijacked their demo one.

We can now mount the template into the report and run it. The report calls Google to get the address geo data and then the template calls it again to get a map of the address(es) returned! Cool or no? If you're interested, you can get the report (xdo), RTF template, sample XML and final output here.

The Geo API is very fast, I passed part of my old address from when I was a kid in the UK '1 Park Drive, UK' it came back with a bunch of results very quickly.

Google ... done! I promise!

Join the discussion

Comments ( 3 )
  • Christopher Beck Tuesday, August 12, 2008
    What do you meant the PL/SQL was not so useful?!?!?!? That code was brilliant! Pure genius I tell you! :P
    Seriously, glad my post helped you out. :D
  • Paulo Tuesday, April 13, 2010
    does anyone know how to do exactly the same, but with Oracle Map Viewer?
  • Cathie Voigtlander Tuesday, July 27, 2010
    I always read the article on sites previous to I leave a review. If I have got little to say about the page topic, I do not have to leave any feedback. And I like to read someone else’s feedback on that post too. Because I'll find handy ideas from the professional comments. I might have posted “Amazing blog! Fabulous posts!” then I could well be spamming your blog. I as well develop a web log, one thing I'd do every single day when i signed up on my wordpress blog is go look at comments to my blogposts and physically approve or reject these remarks. Keywordluv not merely brings you more commenters. It improves your position in search engines due to you’re not only on countless “dofollow blog lists” but additionally on a number of those “keywordluv lists”. Cheers, Leannae
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.