Version 1.2 of the ReportMap Google Map plugin has been released today. While the rest of you have been idling away under Covid-19 restrictions, I’ve been happy as a clam working on some exciting enhancements to the plugin.
Included in this release are the following new features:
New visualisation: Spidifier
Show turn-by-turn Directions
Customise each Marker with your own JavaScript function
Load large data sets in batches
Show spinner while data is loading
Localisation options
A bug when the new Friendly URLs feature of Oracle APEX 20.1 is used with the Clustering visualisation has also been fixed in this release.
The full list of enhancements and bugfixes, with links to the issues register, may be viewed here.
If you use the Directions or Route Map options, you can now show the turn-by-turn directions on your page, anywhere you want.
The documentation has been updated. The plugin now has four new plugin attributes, as well as a number of other attributes that can be set via JavaScript (the officially supported ones are documented on the plugin attributes page). Three new plugin events have also been added to support the new features.
Spiderfier
If you have a map that needs to show a lot of pins, especially ones that are close together, the plugin previously had the option of Clustering them at high zoom levels. The user could click on a cluster to zoom in enough to show the individual pins. One weakness of this approach is that if one or more pins are almost (or exactly) overlapping, the cluster never “unclusters” – the user cannot zoom in far enough to get the pins to show individually.
This release provides another Visualisation option, Spiderfier. This uses the OverlappingMarkerSpiderfier to control how pins react when clicked. When the user clicks a pin that is close or overlapping with other pins, it shifts the pins in that area into a ring, or a spiral (depending on how many pins are there) with lines pointing back to their original location. It also colours them blue to indicate they’ve been “spiderfied”. The user can then hover and click each marker separately.
If the user zooms in, the Spiderfier automatically returns all the pins to their original location.
I think the defaults I’ve set work reasonably well. If needed, you can customise the Spiderfier by setting its options via the JavaScript Initialisation Code (refer to “spiderfier options” here for details). You can also provide your own formatting function to change how the markers look when they are “spiderfied”.
Marker Icons
The WIKI has been augmented with a handy guide to Map Icons. The plugin has long supported the ability to specify custom images for the marker icons. This release gives a whole lot more control over the markers to the developer:
If all the icons in the query are being loaded from the same location, you can now set the iconBasePath option once and just have a relative icon file name in the query. When there is a lot of data to show in the map, this can significantly reduce the volume of data loaded to the client, which can lead to a significant performance improvement.
The developer can now supply a custom JavaScript function (via the markerFormatFn option) to format each marker using whatever logic they need.
For example, if the marker icon needs to be different according to some data value, you can send the data via one of the flex fields, and then write your custom function to set the marker icon depending on the value of the flex field.
You could also modify other characteristics of the marker, such as the title (hover text), info text (popup window), icon anchor point, opacity, and even position (although usually I’d expect your query would provide the correct lat/lng coordinates).
If you have a large number of custom icons you wish to use, along with a large data set of pins to render on the map, you could even compile the icons into a single sprite map to reduce network overhead. This means the image file is loaded once to the client, and then the map “cuts out” bits of the sprite map to render the marker icons. This can be done by setting just a few attributes of the marker’s Icon object. I haven’t tried it myself yet, but this tool looks like it would be useful for this purpose.
Loading Large Datasets
This release adds the Show Spinner and the Rows Per Batch attributes. These attributes are independent of each other, and they help to improve the quality of the experience for your end users when you are rendering a large number of pins on the map.
By default, new maps will have Show Spinner set on. For any existing maps, after upgrading you can turn this option on by setting it in the plugin attributes. This option causes the map to show the default APEX spinner while the data is loaded. The spinner is then removed when the last marker has been rendered. The effect is to give the user an indication that the map is “working”, and gives them immediate feedback when the data has finished loading and they may now interact with the map.
If the spinner seems to stay forever, it may indicate an issue with connectivity to the server (or perhaps that the server is under severe load or has stopped responding to requests).
When the APEX page has been rendered on the client, the Google Map is shown but the data is not immediately loaded; instead, a separate AJAX request is sent to the server to run your query and download all the data to render the pins on the map. By default, this is all done in one single AJAX call, which is the fastest way to get from start to finish; the downside is that the user will not see any pins on the map until all the data has been downloaded. You can change this behaviour by setting Rows Per Batch to some number (e.g. 1000). With this attribute set, the plugin will send a series of AJAX calls to the database (one at a time) and get a batch of records at a time. After loading a batch, the plugin will render the pins on the map (and if necessary, it will pan / zoom the map to show them all) and then send another AJAX request to get the next batch. When it has finished receiving all the batches, it adds any finishing touches needed (e.g. for a visualisation) and returns control to the user.
The advantage of this approach is that the user can see the pins being shown gradually, and they will know that “something is working”. This may help to give them a nicer user experience.
The downside of this approach is that it may cause a bigger load on the server (because each AJAX request requires running a new query, with an offset) and will usually take longer from start to finish. Generally, if your data comprises only a few hundred records at most, you will probably want to leave the Rows Per Batch setting blank.
The Future
There are still a few little enhancements on my “todo” list, but I’m keen to hear how you are using (or perhaps planning to use) this plugin, and if there are any new features or improvements that you need or want. If so, please raise them on the GitHub Issues page.
Quite a few people have raised questions or ideas in the past and sometimes I’ve incorporated them straight away, and other times it’s taken a little longer but I get there eventually. If you’re keen to contribute, feel free to have a poke around in the code and perhaps even do a pull request on the GitHub source to suggest a change. It would be great to collaborate with you because everyone has something unique to offer.
Long-term, I’m watching with interest the future direction of Oracle APEX. I remember at one point they were talking about incorporating some sort of new map region into the product, although the mention of this seems to have been dropped from the Statement of Direction (or maybe my memory is misleading me). I guess time will tell.
Font APEX is preferred most of the time but sometimes there are icons I really want to use which are not (yet) included. For these cases I want to load the latest Font Awesome library.
It is possible to load Font Awesome instead of Font APEX by opening Shared Components -> Themes -> Universal Theme, and setting Custom Library File URLs to the location of the library (wherever you have loaded it). However, this replaces the Font APEX font completely so you can’t use both at the same time using this method.
These regions are shown in the same page; the first region uses a Font APEX icon, the second uses a Font Awesome 5 Free icon.
In order to use both at the same time, I’ve downloaded the latest free version of Font Awesome 5 from here (fontawesome.com), taken a copy of the file css/all.css and edited it to replace all occurrences of “.fa” with “.fa5” (if you use CSS precompiler you can do this by editing the appropriate variables file, e.g. _variables.less). This is necessary because the “fa” class prefix would conflict with Font APEX. I named my custom file “fa5.css” and created a minified version as well.
On my web server I created the folder /fa5 under my public html folder, and copied the following files / folders into it:
/fa5/css/fa5.css
/fa5/css/fa5.min.css
/fa5/webfonts/* (all contents)
In my APEX application, in the Universal Theme properties I set:
Custom Library File URLs = /fa5/css/fa5#MIN#.css
Custom Prefix Class = fa5
(optional)Custom Classes = (comma-delimited list of your favourite icons)
Theme attributes to load a custom library.
Alternatively, you could upload the library into your Static Application Files and load them from there.
Files loaded into Static Application Files.Theme settings to load the custom library from Static Application Files. If you set the Custom Classes attribute on the theme, you get them listed for convenience in the Pick Icon / Custom list. It doesn’t show previews of the icons, however, since I don’t know how to load a custom library into the APEX builder environment itself.
If I want my page to use an icon from Font APEX, I use the fa- icons as usual, e.g. fa-apex. Where I need an icon from Font Awesome, I have to include both the fa5 class as well as the icon class, e.g. fa5-restroom. For brand icons, of which Font Awesome has a large selection, the class is fa5b, e.g. fa5b fa5-amazon-pay. Font Awesome also includes a range of modifiers including sizes, spin, pulse, rotating, mirroring, and stacking.
The spin and pulse effects are not visible in the screenshot above. A live demo can be viewed and examined here: https://jk64.com/apex/f?p=TEST:FA5:0.
You are, of course, asking, can I stack two icons AND spin just one of them? The answer, of course, is yes:
Issue #1: Featured Hero Region Icon
When I tried to use a Font Awesome icon in a Hero region with the “Featured” style, the font failed to load. This is because the “Featured” style overrides the font-family causing it to fail to use the Font Awesome font. To fix this, on the page I added the following CSS:
In a navigation menu, APEX includes the “fa” class which controls the positioning of the icons in the menu, but it also overrides the font library and fails to load the icon from Font Awesome. To fix this, I further edited my fa5.css file (as well as the minified version) to add the following:
The Icon attribute on a region can only be used to provide a class (or list of classes) to serve as the icon for the region. To use a Stacked icon in this case is impossible as the stack must be specified using a span with nested nodes for each icon in the stack. A workaround for this is to use some jQuery to modify the html at runtime, as follows:
Set the region’s Static ID, e.g. stacked
Set the region’s Icon attribute to one of the icons in the stack (just so that there is something shown if the javascript is delayed), eg. fa5-camera
Add this to the page’s Execute When Page Loads (this example is for a Hero region:
It’s a messy kludge, and you’ll have to adapt it if you want to use it in other region templates (check what the span class is), but if this provides significant business benefit then it might be worthwhile.
Comparing Font Awesome 5 Free with Font APEX
I’ve loaded lists of all the icons in the Font Awesome 5 Free and the Font APEX libraries into a table and created a little application that allows me to compare them.
Note: these stats are not perfect because some of the icon names are slightly different between the libraries – for example, all of the “hand” icons have slightly different names between the two libraries.
A good question – how to load fairly largish GeoJSON documents into a Google Map in APEX?
To investigate this I started by downloading a source of GeoJSON data for test purposes – one containing the borders of countries around the world: https://datahub.io/core/geo-countries. This file is 23.5MB in size and contains a JSON array of features, like this:
(the data does not appear to be very accurate for a lot of countries, but it will do just fine for my purposes)
Uploading the file to the database
To load this file into my database I copied the file to the server and ran this to load the data into a temporary table:
Alternatively, I could also have created a temporary APEX application with a File Browse item to upload the file and insert it into the import_lob_tmp table.
Parsing the JSON
I wanted to get the array of features as a table with one row per country; to get this I used json_table; after a fair bit of muddling around this is what I ended up with:
create table country_borders as
select j.*
from import_lob_tmp,
json_table(the_clob, '$.features[*]'
columns (
country varchar2(255) path '$.properties.ADMIN',
iso_a3 varchar2(255) path '$.properties.ISO_A3',
geometry clob format json
)) j;
alter table country_borders modify country not null;
alter table country_borders modify iso_a3 not null;
alter table country_borders modify geometry not null;
alter table country_borders add
constraint country_border_name_uk unique (country);
alter table country_borders add
constraint geometry_is_json check (geometry is json);
The first JSON path expression allowed me to drill down from the document root ($) to the features node; this is an array so I added [*] to get one row for each entry.
The COLUMNS list then breaks down each entry into the columns I’m interested in; each entry consists of a type attribute (which I don’t need), followed by a more interesting properties node with some attributes which are extracted using some relative JSON path expressions; followed by the geometry node with the GeoJSON fragment that represents the country borders that I wish to store “as is” in a clob column.
Now if I query this table it’s interesting to see which countries are likely to have the most complex coastlines (at least, as far as the data quality provided in this file will provide):
select country,
iso_a3,
dbms_lob.getlength(geometry) geometry_size,
geometry
from country_borders
order by 3 desc;
It should be noted that since I’ve extracted the geometry node from each feature, the resulting data in the geometry column do not actually represent valid GeoJSON documents. However, it’s easy to construct a valid GeoJSON document by surrounding it with a suitable JSON wrapper, e.g.:
'{"type":"Feature","geometry":' + geometry + '}'
Showing the GeoJSON on a map
The next step is to load this border data onto a map for display. I recently released version 1.1 of my Report Map Google Map plugin which adds support for loading and manipulating geoJSON strings, so I started by importing region_type_plugin_com_jk64_report_google_map_r1 into my APEX application.
I created a page with a region using this plugin. I set the map region Static ID to testmap. On the same page I added a text item, P1_GEOJSON, to hold the GeoJSON data; and a Select List item P1_COUNTRY with the following query as its source:
select country
|| ' ('
|| ceil(dbms_lob.getlength(geometry)/1024)
|| 'KB)' as d
,country
from country_borders
order by country
I added a dynamic action to the Select List item on the Change event to load the geometry from the table into the map. Initially, I added the following actions:
A Set Value action that sets P1_GEOJSON to the result of the query: select geometry from country_borders where country = :P1_COUNTRY
An Execute JavaScript action that loads the GeoJSON into the map (after first clearing any previously loaded features):
This technique works ok, but only for smallish countries where the GeoJSON of their borders is less than 4K in size. For countries with more border detail than can fit within that limit, the Set Value action query only loads part of the JSON data, resulting in an invalid JSON string – and so the map failed to load it. The Set Value action was therefore unsuitable for my purpose.
To load the entire CLOB data I used another plugin. There are a few CLOB load plugins available for APEX – search the Plugins list at apex.world for “clob”. I chose APEX CLOB Load 2 by Ronny Weiß.
I imported the plugin dynamic_action_plugin_apex_clob_load_2 into my application, then replaced the Set Value action with the action APEX CLOB Load 2 [Plug-In]. I set SQL Source to:
select /* Element type dom - for jQuery selector e.g. body or #region-id,
item - for item name e.g. P1_MY_ITEM */
'item' as element_type,
/* jQuery selector or item name */
'P1_GEOJSON' as element_selector,
geometry as clob_value
from country_borders
where country = :P1_COUNTRY
I set Items to Submit = P1_COUNTRY and Sanitise HTML = No. I also set Selection Type = Region and select the map region so that the spinner is shown while the data is loaded.
Result
The plugin works well. The border for any country can be loaded (for some countries, it takes a few extra seconds to load) and drawn on the map:
If you would like to see this in action, you may play with it here: https://jk64.dev/apex/f?p=JK64_REPORT_MAP:GEOJSON
Over the past couple of weeks I’ve been working on an overhaul of my Google Maps region for Oracle Application Express. This free, open-source plugin allows you to integrate fully-featured Google Maps into your application, with a wide range of built-in declarative features including dynamic actions, as well as more advanced API routines for running custom JavaScript with the plugin.
Route map
Marker Clustering
Geo Heatmap
Report Map
The plugin has been updated to Oracle APEX 18.2 (as that is the version my current system is using). Unfortunately this means that people still on older versions will miss out, unless someone is willing to give me a few hours on their APEX 5.0 or 5.1 instance so I can backport the plugin.
EDIT: Release 1.0.1 includes some bugfixes and a backport for APEX 5.0, 5.1 and 18.1.
The plugin is easy to install and use. You provide a SQL Query that returns latitude, longitude, and information for the pins, and the plugin does all the work to show them on the map.
The plugin has been rewritten to use the JQuery UI Widgets interface, at the suggestion of Martin D’Souza. This makes for a cleaner integration on any APEX page, and reduces the JavaScript footprint of each instance on the page if you need two or more map regions at the same time. This represented a rather steep learning curve for me personally, but I learned a lot and I’m pleased with the result. Of course, I’m sure I’ve probably missed a few tricks that the average JavaScript coder would say was obvious.
The beta releases of the plugin (0.1 to 0.10) kept adding more and more plugin attributes until it hit the APEX limit of 25 region-level attributes. This was obviously not very scaleable for future enhancements, so in Release 1.0 I ran the scythe through all the attributes and consolidated, replaced, or removed more than half of them – while preserving almost every single feature. This means v1.0 is not backwards compatible with the beta versions; although many attributes are preserved, others (including the SQL Query itself, which is rather important) would be lost in the conversion if the plugin was merely replaced. For this reason I’ve changed the Internal ID of the plugin. This is so that customers who are currently using a beta version can safely install Release 1.0 alongside it, without affecting all the pages where they are using the plugin. They can then follow the instructions to gradually upgrade each page that uses the plugin.
All of the plugin attributes relating to integrating the plugin with page items have been removed. Instead, it is relatively straightforward to use Dynamic Actions to respond to events on the map, and an API of JavaScript functions can be called to change its behaviour. All of this is fully documented and sample code can be found in the wiki.
New features include, but are not limited to:
Marker Clustering
Geo Heatmap visualisation (this replaces the functionality previous provided in a separate plugin)
Draggable pins
Lazy Load (data is now loaded in a separate Ajax call after the page is loaded)
The plugin attributes that have been added, changed or removed are listed here.
If you haven’t used this plugin before, I encourage you to give it a go. It’s a lot of fun and the possibilities presented by the Google Maps JavaScript API are extensive. You do need a Google Maps API Key which requires a Google billing account, but it is worth the trouble. It is recommended to put a HTTP Referer restriction on your API Key so that people can’t just copy your public key and use it on their own sites. For more information refer to the Installation Instructions.
If you are already using a beta version of the plugin in your application, please review the Upgrading steps before starting. Don’t panic! It’s not quite as simple as just importing the plugin into your application, but it’s not overly complicated. If you were using any of the Page Item integration attributes, you will need to implement Dynamic Actions to achieve the same behaviour. If you had any JavaScript integrations with the plugin, you will need to update them to use the new JQuery UI Widget API calls. I am keen for everyone to update to Release 1.0 as soon as possible, so I will provide free support (via email) for anyone needing help with this.
I am very keen to hear from everyone who is using the plugin, and how it is being used – please let me know in the comments below.
If you notice a bug or have a great idea to enhance the plugin, commenting on this post is not the best place – instead, please raise an issue on GitHub.
I had a simple form where the user can select a vendor from a select list (using the Select2 plugin), and the form would retrieve the latest purchase details based on a simple string search of that vendor’s name – for most cases. Sometimes, however, it failed to find anything even though I knew the data was there.
There was a dynamic action that fires on Change of the list item that executes a PL/SQL procedure and retrieves the Total Amount and the Memo:
This didn’t work in some cases where a vendor name included special characters, such as &. This is because the item had Escape special characters set to the default (Yes), which is good practice to protect against Cross-Site Scripting (XSS) attacks. Therefore, the value sent by the dynamic action to my PL/SQL procedure had the special html characters escaped, e.g. “Ben & Jerry’s” was escaped to “Ben & Jerry's“. I believe APEX uses the apex_escape.html function to do this.
Usually, I would try to rework my code to send a numeric ID instead of a string; but in this particular case the data model does not have surrogate keys for vendors (it’s just a free-text field in the transactions table) so I want to use the name.
If I was doing this properly, I would fix the data model to make vendors first-class entities, instead of using a free-text field. This would allow using a surrogate key for the vendor list and this escaping behaviour would no longer be a problem.
Another alternative here is to use the latest transaction ID as a surrogate key for each vendor; but then I would need to modify the form to translate this back into a vendor name when inserting or updating the table; and this would add unnecessary complexity to this simple form, in my opinion.
Instead, before sending this string to my procedure, I’ve chosen to unescape the data. To do this, I add a call to utl_i18n.unescape_reference:
After upgrading APEX I found this query useful to review all the plugins I had installed across multiple workspaces and in multiple applications to find ones that needed to be upgraded.
select name
,plugin_type
,version_identifier
,workspace
,application_id
,application_name
from APEX_APPL_PLUGINS
where workspace not in ('INTERNAL','COM.ORACLE.CUST.REPOSITORY')
order by name, workspace, application_id;
