APEX plugin: OpenStreetMaps with Leaflet

APEX plugin: OpenStreetMaps with Leaflet

Gepubliceerd: Categorie: Oracle


One of the few things I don’t like in Oracle Application Express is the Map Chart component. In the current 19.2 version of APEX, it does not even seem to work anymore. Aside from this, it’s also quite a hassle to use. In the Geolocation Showcase sample application, Oracle created their own “Oracle HTML5 Maps - Region” plugin based on their own Oracle eLocation Service maps, but this plugin is not available on APEX plugins page and the maps are not so easy to use.

When looking for alternatives, I found a really nice plugin based on Google Maps, and also a basic plugin based on OpenStreetMaps. I decided to have a closer look at the OpenStreetMaps plugin, because I like OpenStreetMaps and you don’t need an API-key to use it.

Plugin Basics

There is a Youtube video that shows how to create an APEX plugin step by step, but I started by looking at the existing OpenStreetMap plugin created by Dmytro Zarezenko. My first goal is to create a map in APEX where markers are shown based on a data set. This data set is basically a list of Dutch train stations, containing the name, latitude and longitude of the train stations. I used the APEX Web Source Module to retrieve the data set.

You can see the result on my sample application, where you can also download the plugin.

Explanation of the plugin

You can import plugins and view the contents of the plugins by going to the Shared Components of your application and clicking on “Plug-ins”.

Let’s check out the contents of the “OpenStreetMap with Leaflet Plugin” (after you imported the plugin):

The “Source” of the plugin is actually the part where the magic happens. Here, you will generate the HTML and JavaScript needed to render the map and the markers. We will have a closer look at the PL/SQL code:

  1. 1. function render_osm_map (p_region in apex_plugin.t_region
  2. 2. ,p_plugin in apex_plugin.t_plugin
  3. 3. ,p_is_printer_friendly in boolean
  4. 4. ) return apex_plugin.t_region_render_result
  5. 5. is
  6. 6. -- attributes
  7. 7. l_height varchar2(10) := nvl(p_region.attribute_01, '700px');
  8. 8. l_source varchar(4000) := nvl(p_region.attribute_02, '');
  9. 9. l_zoom_level varchar2(10) := nvl(p_region.attribute_03, '10');
  10. 10. -- variables
  11. 11. l_center varchar2(100);
  12. 12. l_collection_name varchar2(250) := upper('COLLECTION_'||p_region.name);
  13. 13. begin
  14. 14. -- One of the region attributes is a string containing a SQL-query.
  15. 15. -- This query will be executed now into a collection
  16. 16. if apex_collection.collection_exists (l_collection_name)
  17. 17. then
  18. 18. apex_collection.delete_collection(l_collection_name);
  19. 19. end if;
  20. 20. apex_collection.create_collection_from_query (p_collection_name => l_collection_name
  21. 21. ,p_query => l_source
  22. 22. );
  23. 23. --
  24. 24. -- Calculate the center of the map, based on an average of the marker points
  25. 25. --
  26. 26. select to_char(avg(to_number(c001)))||', '|| to_char(avg(to_number(c002)))
  27. 27. into l_center
  28. 28. from apex_collections
  29. 29. where collection_name = l_collection_name ;
  30. 30. --
  31. 31. -- Now start defining the HTML, CSS and JavaScript needed for displaying the map
  32. 32. --
  33. 33. apex_css.add_file(p_name => 'leaflet'
  34. 34. ,p_directory => p_plugin.file_prefix||'leaflet/'
  35. 35. );
  36. 36. apex_javascript.add_library(p_name => 'leaflet'
  37. 37. ,p_directory => p_plugin.file_prefix||'leaflet/'
  38. 38. );
  39. 39. htp.p('
  40. 40. <div id="map" style="width: 100%; height: ' || l_height || ';"></div>
  41. 41. <script>
  42. 42. function initMap() {
  43. 43. var osmUrl = ''http://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png'';
  44. 44. osmAttrib = ''&copy; <a href="http://openstreetmap.org/copyright">OpenStreetMap</a> contributors'';
  45. 45. osm = L.tileLayer(osmUrl, { maxZoom: 18, attribution: osmAttrib });
  46. 46. map = new L.Map(''map'' , {layers: [osm], center: new L.LatLng('||l_center||'), zoom: '||l_zoom_level||' });
  47. 47. // Place the markers
  48. 48.
  49. 49. ');
  50. 50. --
  51. 51. -- Now generate the JavaScript calls to set the markers
  52. 52. --
  53. 53. for x in (select c001 as lat
  54. 54. , c002 as lon
  55. 55. , c003 as label
  56. 56. from apex_collections
  57. 57. where collection_name = l_collection_name
  58. 58. )
  59. 59. loop
  60. 60. htp.p('L.marker(['||x.lat||','||x.lon||']).addTo(map).bindPopup("<b> '||replace(x.label,'''','''''')||' </b>");');
  61. 61. end loop;
  62. 62. htp.p('
  63. 63. // End of markers
  64. 64. };
  65. 65.
  66. 66. </script>
  67. 67. ');
  68. 68. --
  69. 69. apex_javascript.add_onload_code('initMap();');
  70. 70. return null;
  71. 71. end;

The function has a pre-defined signature with parameters, that will pass on the settings a developer can make when using the plugin on an APEX page (lines 1-4).
The PL/SQL variables used in the function (line 6-12) are needed to hold the values calculated in PL/SQL and blend them in JavaScript later on.

Since I wanted to use a data set to supply the markers that will be placed on the map, a query is needed to deliver the data set. This query can be supplied as Custom Attribute in this plugin. When rendering the map, this query needs to be executed in order to use the data set. I used an APEX collection to get this data set (line 16-22). Once the collection is populated, I’m using the data set first to define the center of the map (line 23-29).

Next, the apex_css and apex_javascript packages are used to generate the HTML code that will include the required CSS and JavaScript files.
On line 40, the div-element is created, which will actually hold the map.
Lines 42 to 64 create a JavaScript function that will do the “magic” of creating the map, and center it at the right point and zoom level.
On lines 47 to 63, JavaScripts lines are generated with a PL/SQL loop. For every train station, a marker will be placed on the map using the lat and lon coordinates. Each marker gets a popup with the name of the train station.
Line 69 creates the instruction to call the JavaScript-function once the page has been loaded.


I hope this example shows how relatively easy it is to generate a nice map with some markers on it. It would also be possible to get the map with a plain PL/SQL region, but the creation of a plugin for that has some advantages, such as reusability within your own application and the ability to share the plugin with others. Feel free to modify the plugin, so it suits your own needs!

Peter de Boer
Over auteur Peter de Boer

Peter de Boer is een consultant bij Qualogy.

Meer posts van Peter de Boer
Reacties (1)
  1. om 15:03

    No render function has been defined for plug-in PLUGIN_REGION.APEX.OSM_LEAFLET_PLUGIN_1

Reactie plaatsen