Tuesday 15 May 2018

Oracle APEX Interactive reports and google maps integration

Oracle APEX Interactive reports and google maps integration


Great way to extend your IR functionality 

GEO location data viewed on a map?

Recently I worked on APEX project that I thought would be cool to show case here purely to demonstrate scaling ability of APEX and one of its core components -  Interactive reports.

Problem: key requirement users to report using maps.

Idea was that they would filter out the data prepare all the columns etc ...and than per each column they would be able to categorize before showing it on a map. 

One way would be to custom build you filters and based on it drive the map data. But we figured why would we reinvent the wheel?

Hope this give you enough insight to why we opted to go with mighty Interactive reports - purely because we had API access to user report data. This was a key driver for us :)
Before further reading there is an excellent blog post by fellow APEX-er Adrian Png where he went into more details of how to setup and live feed your maps data. Great read and another awesome post by Adrian #hugefan. Also there are heaps of Google maps APEX plugins around for you to start playing.

Straight difference to how Adrian's team stored the data was, we were purely working on simple LAT and LONG that were saved in database tables. There was no use of SDO_geometry and all config that comes with it. So we could say this would be another use case.

Our technology was basic: Oracle APEX 5.1 and Leaflet JS library so in theory it should work with any version of APEX as long as IR are there. 

For start a demo that demonstrates how IR works with maps using its own SQL data.

Key features:
  1. All original APEX IR functionality is intact - giving end users all the freedom to analyze their data
  2. Per each column (or most of them) users will be able to show them graphically on a map (for example list all positive screening results in this region or what animals were involved in accident report at xx location)
  3. As we filter out our report data sets all map filters will dynamically show only data contained in a 'user view' 
  4. There is ability to categorize elements shown on a map as markers with different pins and shapes
How does it work?

All data are stored in collections where using APEX_IR package we extracted report SQL running. Pretty much that is it. Once you have this you can filter down any values you are after to be used in your 'prepare' map section. The rest is pure JavaScript and map APIs.

On a map screen to your fetch data you can use a simple apex.server.process and you are ready to rock and roll.

All in all we had ~ 10 reports where this was used and only regret we have is not being able to utilize Interactive grid functionality. :)

Happy APEXing,