By Tim Dexter-Oracle on Aug 12, 2008
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"> <Response> <name>500 Oracle Parkway</name> <Status> <code>200</code> <request>geocode</request> </Status> <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"> <Country> <CountryNameCode>US</CountryNameCode> <AdministrativeArea> <AdministrativeAreaName>CA</AdministrativeAreaName> <Locality> <LocalityName>Redwood City</LocalityName> <Thoroughfare> <ThoroughfareName>500 Oracle Pkwy</ThoroughfareName> </Thoroughfare> <PostalCode> <PostalCodeNumber>94065</PostalCodeNumber> </PostalCode> </Locality> </AdministrativeArea> </Country> </AddressDetails> <Point> <coordinates>-122.262227,37.531028,0</coordinates> </Point> </Placemark> </Response> </kml>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:'
<?x:coordinates?>- 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:
<Point> <coordinates>-122.262227,37.531028,0</coordinates> </Point>
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.
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!