APEX plugin: OpenStreetMaps with Leaflet

APEX plugin: OpenStreetMaps with Leaflet

Published on: Category: Oracle

Introduction

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.

Conclusion

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
About the author Peter de Boer

Peter de Boer is a consultant at Qualogy.

More posts by Peter de Boer
Comments (3)
  1. om 15:03

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

  2. om 19:07

    I tried your PLug in but I get the following error, may you help me to solve it ?

    Errore nel codice PLSQL durante l'elaborazione del plugin.
    Rivolgersi all'amministratore delle applicazioni.

    Informazioni tecniche (visibili solo per gli sviluppatori)
    is_internal_error: true
    apex_error_code: WWV_FLOW_PLUGIN.RUN_PLSQL_ERR
    ora_sqlcode: -1722
    ora_sqlerrm: ORA-01722: numero non valido ORA-06512: a "APEX_200100.WWV_FLOW_DYNAMIC_EXEC", line 1539 ORA-06512: a "APEX_200100.WWV_FLOW_DYNAMIC_EXEC", line 2658 ORA-06512: a line 28 ORA-06512: a line 74 ORA-06512: a "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: a "SYS.WWV_DBMS_SQL_APEX_200100", line 590 ORA-06512: a "APEX_200100.WWV_FLOW_DYNAMIC_EXEC", line 2621 ORA-06512: a "APEX_200100.WWV_FLOW_DYNAMIC_EXEC", line 1514
    component.type: APEX_APPLICATION_PAGE_REGIONS
    component.id: 4413768541964312002
    component.name: New
    error_backtrace:
    ORA-06512: a line 28
    ORA-06512: a line 74
    ORA-06512: a "SYS.DBMS_SYS_SQL", line 2120
    ORA-06512: a "SYS.WWV_DBMS_SQL_APEX_200100", line 590
    ORA-06512: a "APEX_200100.WWV_FLOW_DYNAMIC_EXEC", line 2621
    ORA-06512: a "APEX_200100.WWV_FLOW_DYNAMIC_EXEC", line 1539
    ORA-06512: a "APEX_200100.WWV_FLOW_DYNAMIC_EXEC", line 2658
    ORA-06512: a line 28
    ORA-06512: a line 74
    ORA-06512: a "SYS.DBMS_SYS_SQL", line 2120
    ORA-06512: a "SYS.WWV_DBMS_SQL_APEX_200100", line 590
    ORA-06512: a "APEX_200100.WWV_FLOW_DYNAMIC_EXEC", line 2621
    ORA-06512: a "APEX_200100.WWV_FLOW_DYNAMIC_EXEC", line 1514
    ORA-06512: a "APEX_200100.WWV_FLOW_PLUGIN", line 1460
    error_statement:
    begin declare
    function render_osm_map (p_region in apex_plugin.t_region
    ,p_plugin in apex_plugin.t_plugin
    ,p_is_printer_friendly in boolean
    ) return apex_plugin.t_region_render_result
    is
    -- attributes
    l_height varchar2(10) := nvl(p_region.attribute_01, '700px');
    l_source varchar(4000) := nvl(p_region.attribute_02, '');
    l_zoom_level varchar2(10) := nvl(p_region.attribute_03, '10');
    -- variables
    l_markers varchar2(32676) ;
    l_center varchar2(100);
    l_collection_name varchar2(250) := upper('COLLECTION_'||p_region.name);
    begin
    -- One of the region attributes is a string containing a SQL-query.
    -- This query will be executed now into a colection
    if apex_collection.collection_exists (l_collection_name)
    then
    apex_collection.delete_collection(l_collection_name);
    end if;
    apex_collection.create_collection_from_query (p_collection_name => l_collection_name
    ,p_query => l_source
    );
    --
    -- Calculate the center of the map, based on an average of the marker points
    --
    select to_char(avg(to_number(c001)))||', '|| to_char(avg(to_number(c002)))
    into l_center
    from apex_collections
    where collection_name = l_collection_name ;
    --
    -- Now generate the javascript calls to set the markers
    --
    for x in (select c001 as lat
    , c002 as lon
    , c003 as label
    from apex_collections
    where collection_name = l_collection_name
    )
    loop
    l_markers := l_markers||'L.marker(['||x.lat||','||x.lon||']).addTo(map).bindPopup("<b> '||replace(x.label,'''','''''')||' </b>");';
    end loop;
    --
    -- Now start defining the HTML, CSS and JavaScript needed for displaying the map
    --
    apex_css.add_file(p_name => 'leaflet'
    ,p_directory => p_plugin.file_prefix||'leaflet/'
    );
    apex_javascript.add_library(p_name => 'leaflet'
    ,p_directory => p_plugin.file_prefix||'leaflet/'
    );
    htp.p('
    <div id="map" style="width: 100%; height: ' || l_height || ';"></div>
    <script>
    function initMap() {
    var osmUrl = ''http://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png'';
    osmAttrib = ''&copy; <a href="http://openstreetmap.org/copyright">OpenStreetMap</a> contributors'';
    osm = L.tileLayer(osmUrl, { maxZoom: 18, attribution: osmAttrib });
    map = new L.Map(''map'' , {layers: [osm], center: new L.LatLng('||l_center||'), zoom: '||l_zoom_level||' });
    // Place the markers
    ' ||l_markers||'
    // End of markers
    };

    </script>
    ');
    --
    apex_javascript.add_onload_code('initMap();');
    return null;
    end;

    begin
    wwv_flow_plugin_api.g_region_render_result := render_osm_map (p_region => wwv_flow_plugin_api.g_region,p_plugin => wwv_flow_plugin_api.g_plugin,p_is_printer_friendly => (:p_is_printer_friendly='Y') );end;
    end;

    1. om 11:11

      Hi Alessandro,

      The problem is likely caused by the Marker query that is in the Settings of the plugin region. This query should start with 2 numeric columns for the latitude and longitude of the marker.
      The plugin tries to calculate the center of the markers and converts the values to number:

      --
      -- Calculate the center of the map, based on an average of the marker points
      --
      select to_char(avg(to_number(c001)))||', '|| to_char(avg(to_number(c002)))
      into l_center
      from apex_collections
      where collection_name = l_collection_name ;

      Hope this helps!

Reply