Update April 2017 wIth the launch of the Acterys Platform a more versatile and powerful platform is available that combines the power of transactional databases like SQL server with at least similar multi-dimensional features that Jedox offers.
Hi, hope you enjoy my first post. I am a Senior Consultant at Managility specialising in the ETL, report development and custom visualisations.
Recently I faced an interesting challenge at a Managility client who was looking to visualise sales employee performance within a specific Australian postal code area.
A quick and free option is using Google Map techniques integrated in a Jedox Web widget to enable very interesting insights.
So to start off, The end result we are aiming is depicted in the screenshot below:
The solution that I worked out is composed of the following components:
1. Google Geocoding API: This API usage was primarily to place the marker on the map requiring the latitude and longitude coordinate values mapping to the postal code in Australia. This API is responsible for translating the postal code value to the required coordinates for the map marker
2. Google Charts API: This API is used to draw/plot the sales person performance data in the google map marker information window. Using this API in the sample I have rendered a column chart but, it has to offer a wide variety of chart types with great detailing tweaks and settings offered in the API
3. Google Maps (Marker/Info window): This API sections have been used to place the marker and the associated Info window (containing the embedded chart)
4. Postal code area Highlight: Now, here’s the tricky bit… Which took me a while to figure out myself. For the postal code area to be highlight now a days Google by itself offers a highlight mechanism as illustrated below:
In the above check the red dotted line mapping the postal code boundary
Google Map Default Postal Code Boundary (AU, NSW 2008)
Now first line of thoughts made me to leverage what’s available with the google by default and I tried using the same in conjunction with rest of my task to be accomplished involving setting the marker with an info window and embed chart inside the same. But scanning the entire google maps API I was unable to figure out a way as to where I can combine all the pieces of the puzzle together.
But then again googling examples to find a methodology for the same I stumbled upon the following link which partially answered my problem (and being very close to my requirements):
Now only thing was to marry all together….
So investigating upon and with an aim to not to elongate the mystery of the solution, I found my solution with the use of KML files used in conjunction with google maps to custom draw shapes on the google maps driven by a custom directive files for the shapes termed as KML (Keyhole Markup Language)
Keyhole Markup Language (KML Wiki)
So now I was able to draw shapes on google maps, but still I wasn’t having any near plans to write or draw this KML files by myself in this life for Australian continent, landing me up to the page for Australian Bureau of Statistics
Postal Areas (POA) 2006 Digital Boundaries in ESRI Shapefile Format
Now next challenge which I faced was, how to convert the SHP file which I have obtained from the site to the required KML file for the google map.
The problem was answered by the following open source software which was very helpful to convert the shape file to KML, with also depicting how my shape file would lay out on the map.
QGIS, A Free and Open Source Geographic Information System
Now when I opened my newly generated KML file I faced with my next task, that it comprised the bulk of postcode data all in one file. This file needed to be split apart into to post code areas so that on demand the respective KML to the post code area can be picked up and rendered on the map.
That’s where my skills of Excel/VBA came into play to split the bulk KML file into their respective postal code files. And hence finally I was able to obtain what I wanted to bring all pieces of the puzzle into one place and render my final solution.
With just a last bit of a knack that my KML files didn’t work, I was able to see my map, with the respective marker and info window with the required chart inside it, but no postal area was highlighted.
Eventually after little hair pulling on my end I was able to resolve the issue by noticing that the KML files which was being passed to google for rendering, required some kind of parsing for which google can’t pick the files from the local host URL, it needs a publicly hosted URL to pick the relevant KML file and parse render it on the map, hence to overcome the last glitch the following JavaScript library was required:
KML processor for the Google Maps JavaScript API V3
And after usage of the above library… Voila… I ended up with my required result as desired
Please contact us for further information or help with your Jedox Business Intelligence requirements @ [email protected]
