Google Map APEX Plugins

I’ve published two three APEX Region Plugins on apex.world that allow you to incorporate a simple Google Map region into your application. They’re easy to use, and you don’t need to apply for a Google API key or anything like that (although you can plug your key in if you have one, which enables a few additional features).

1. Simple Map

plugin-simplemap-preview

This allows you to add a small map to a page to allow the user to select any arbitrary point. If you synchronize it with an item on your page, it will put the Latitude, Longitude into that item. If the item has a value on page load, or is changed, the pin on the map is automatically updated.

Source

2. Report Map

plugin-reportmap-preview.png

This allows you to add a map to a page, and based on a SQL query you supply, it will render a number of pins on the map. Each pin has an ID, a name (used when the user hovers over a pin), and an info text (which can be almost any HTML, rendered in a popup window when the user clicks a pin).

If the user clicks a pin, the ID can be set in a page item.

Source

3. GeoHeatMap

Visualise a large set of data points on the map using the Google Maps “Heatmap” visualisation. All you need to do is supply a SQL Query that returns the data points to show, and the visualisation library does the rest.

plugin-heatmap-preview

Your SQL Query must be in the following format:

select lat, lng, weight from mydata;

You can set the Map Style (e.g. to the light blue/greyscale style you see above) easily on this plugin; just copy-and-paste the style codes from a site like snazzymaps.com.

Source

I’m very open to feedback, issues and contributions on all of these. Best way is to raise an issue on the associated github page. Have fun!

Refer to my Plugins page for future updates.

APEX API for Tabular Forms
BIG checkboxes

Comments

  1. Hi Jeff, I ‘m developing an Oracle APEX app and included this region_type_plugin_com_jk64_report_google_map.sql plugin. I do step by step, configure items needs, and write correct sql that return latitud, longitude, name and id, but when I execute de page, an error raise. I can’t see in the debug mode the error detail, becouse it’s show pl and the error no data found. But the report sql return one record. If I save without sql report, the page run ok, but the map appears grey, like a gray rectangle only. I can’t found the exact line of the error and why. I’m using APEX 5.0.3 .
    here de dubug code.

    Error in PLSQL code raised during plug-in processing.
    
    ORA-01403: no data found
    
    Technical Info (only visible for developers)
    is_internal_error: true
    apex_error_code: WWV_FLOW_PLUGIN.RUN_PLSQL_ERR
    ora_sqlcode: 100
    ora_sqlerrm: ORA-01403: no data found
    component.type: APEX_APPLICATION_PAGE_REGIONS
    component.id: 35741599501575387
    component.name: RENTOGO MAP
    error_backtrace:
    ORA-06512: at line 58
    ORA-06512: at line 217
    ORA-06512: at line 429
    ORA-06512: at "SYS.DBMS_SYS_SQL", line 1926
    ORA-06512: at "SYS.WWV_DBMS_SQL", line 1033
    ORA-06512: at "SYS.WWV_DBMS_SQL", line 1047
    ORA-06512: at "APEX_050000.WWV_FLOW_DYNAMIC_EXEC", line 895
    ORA-06512: at "APEX_050000.WWV_FLOW_PLUGIN", line 1177
    error_statement:
    begin declare
    PROCEDURE set_map_extents
        (p_lat     IN NUMBER
        ,p_lng     IN NUMBER
        ,p_lat_min IN OUT NUMBER
        ,p_lat_max IN OUT NUMBER
        ,p_lng_min IN OUT NUMBER
        ,p_lng_max IN OUT NUMBER
        ) IS
    BEGIN
        p_lat_min := LEAST   (NVL(p_lat_min, p_lat), p_lat);
        p_lat_max := GREATEST(NVL(p_lat_max, p_lat), p_lat);
        p_lng_min := LEAST   (NVL(p_lng_min, p_lng), p_lng);
        p_lng_max := GREATEST(NVL(p_lng_max, p_lng), p_lng);
    END set_map_extents;
    
    FUNCTION latlng2ch (lat IN NUMBER, lng IN NUMBER) RETURN VARCHAR2 IS
    BEGIN
      RETURN '"lat":'
          || TO_CHAR(lat, 'fm990.0999999999999999')
          || ',"lng":'
          || TO_CHAR(lng, 'fm990.0999999999999999');
    END latlng2ch;
    
    FUNCTION get_markers
        (p_region  IN APEX_PLUGIN.t_region
        ,p_lat_min IN OUT NUMBER
        ,p_lat_max IN OUT NUMBER
        ,p_lng_min IN OUT NUMBER
        ,p_lng_max IN OUT NUMBER
        ) RETURN APEX_APPLICATION_GLOBAL.VC_ARR2 IS
    
    l_data           APEX_APPLICATION_GLOBAL.VC_ARR2;
    l_lat            NUMBER;
    l_lng            NUMBER;
    l_info           VARCHAR2(4000);
    l_icon           VARCHAR2(4000);
    l_radius_km      NUMBER;
    l_circle_color   VARCHAR2(100);
    l_circle_transp  NUMBER;
    l_flex_fields    VARCHAR2(32767);
    
    l_column_value_list  APEX_PLUGIN_UTIL.t_column_value_list;
    
    BEGIN
    
      l_column_value_list := APEX_PLUGIN_UTIL.get_data
        (p_sql_statement  => p_region.source
        ,p_min_columns    => 4
        ,p_max_columns    => 19
        ,p_component_name => p_region.name
        ,p_max_rows       => p_region.fetched_rows);
    
    FOR i IN 1..l_column_value_list(1).count LOOP
    
        l_lat  := TO_NUMBER(l_column_value_list(1)(i));
        l_lng  := TO_NUMBER(l_column_value_list(2)(i));
        l_info := l_column_value_list(5)(i);
    
        -- default values if not supplied in query
        l_icon          := NULL;
        l_radius_km     := NULL;
        l_circle_color  := '#0000cc';
        l_circle_transp := '0.3';
        l_flex_fields   := NULL;
    
        IF l_column_value_list.EXISTS(6) THEN
          l_icon := l_column_value_list(6)(i);
        IF l_column_value_list.EXISTS(7) THEN
          l_radius_km := TO_NUMBER(l_column_value_list(7)(i));
        IF l_column_value_list.EXISTS(8) THEN
          l_circle_color := l_column_value_list(8)(i);
        IF l_column_value_list.EXISTS(9) THEN
          l_circle_transp := TO_NUMBER(l_column_value_list(9)(i));
        END IF; END IF; END IF; END IF;
    
        FOR j IN 10..19 LOOP
          IF l_column_value_list.EXISTS(j) THEN
            l_flex_fields := l_flex_fields
              || ',"attr'
              || TO_CHAR(j-9,'fm00')
              || '":'
              || APEX_ESCAPE.js_literal(l_column_value_list(j)(i),'"');
          END IF;
        END LOOP;
    
        l_data(NVL(l_data.LAST,0)+1) :=
             '{"id":'  || APEX_ESCAPE.js_literal(l_column_value_list(4)(i),'"')
          || ',"name":'|| APEX_ESCAPE.js_literal(l_column_value_list(3)(i),'"')
          || ','       || latlng2ch(l_lat,l_lng)
          || CASE WHEN l_info IS NOT NULL THEN
             ',"info":'|| APEX_ESCAPE.js_literal(l_info,'"')
             END
          || ',"icon":'|| APEX_ESCAPE.js_literal(l_icon,'"')
          || CASE WHEN l_radius_km IS NOT NULL THEN
             ',"rad":' || TO_CHAR(l_radius_km,'fm99999999999990.09999999999999')
              || ',"col":' || APEX_ESCAPE.js_literal(l_circle_color,'"')
              ||   CASE WHEN l_circle_transp IS NOT NULL THEN
             ',"trns":'|| TO_CHAR(l_circle_transp,'fm990.099')
               END
             END
          || l_flex_fields
          || '}';
    
        set_map_extents
          (p_lat     => l_lat
          ,p_lng     => l_lng
          ,p_lat_min => p_lat_min
          ,p_lat_max => p_lat_max
          ,p_lng_min => p_lng_min
          ,p_lng_max => p_lng_max
          );
    
    END LOOP;
    
    RETURN l_data;
    
    END get_markers;
    
    PROCEDURE htp_arr (arr IN APEX_APPLICATION_GLOBAL.VC_ARR2
    
    • Jeffrey Kemp
      1 June 2016 - 6:05 pm

      Hi Juano,

      Thanks very much for trying out my plugin, and for your comment. I believe the error you’re getting may be due to a bug in my plugin where it fails to support a query with only 4 columns.

      Until I’ve fixed the issue, I believe a workaround you can use is to add a 5th column to your query, e.g.

      SELECT lat, lng, name, id, '' as dummy FROM mytable...
      

      Please let me know if the above workaround works for you.

      Jeff

  2. Hi Jeff,

    I am glad to see this (Report map) plugin much helpful but in my requirement user is not comfortable in in providing data of lat,long along with address.
    Can you please suggest me how to get data of lat long in oracle/plsql based upon address.As I am already trying access googlemap api under oracle db subsequenctly faced errors.Please check below link.

    https://stackoverflow.com/questions/47302603/error-while-accessing-google-webservice-from-oracle-db

    Thanks in advance.
    Amol

    • Hi Amol,

      I’m not sure the ReportMap plugin will completely fill your requirement in this regard, however you may find the Geocode Item feature useful for this purpose (or at least give you a pointer in the right direction). The plugin can take an address entered into the Geocode Item and move the map to the lat,long position. This is all done on the client so will not have any problem with your server being unable to access the Google server.

      A separate plugin for converting addresses to lat,long points is the SimpleMap plugin (https://github.com/jeffreykemp/jk64-plugin-simplemap) which has similar functionality – you could convert addresses to lat,long points using some javascript. In fact, if you look at the source code (https://github.com/jeffreykemp/jk64-plugin-simplemap/blob/master/src/simplemap.js) you might find the geocode function of interest.

      I hope this helps a bit.

  3. Hi Jeff,

    I have a question about the report plugin.

    When I click a marker on the report plugin, the map adjusts is position, centering the clicked marker.

    Can i change this behavior so that the map is not automatically adjusted?

    regards form holland,
    hans

    • Hi Hans,

      This behaviour is due to the following line in the jk64reportmap_repPin function (which you can find in jk64reportmap.js):

      opt.map.panTo(this.getPosition());

      The plugin doesn’t offer any option in this regard, at the moment – if you want to raise an enhancement request on the Github project to make this an option, feel free: https://github.com/jeffreykemp/jk64-plugin-reportmap

      In the meantime, if you are comfortable modifying the plugin yourself, you could comment out this line.

      Jeff

  4. Hi Jeff,

    Thnx for your reply; I will issue an enhancement request but also tried to fix it according to your info.

    I found the line you mentoined and changed it by adding //.

    I tried to activate this change by importing the plugin again, but the repin function is still active.

    Is this the correct way to change and activate the the changed jk64reportmap.js source?

    Hans

    • Hi Hans, did you update the minified version as well (jk64reportmap_repPin.min.js)? If not, you can generate this file by uploading your version of jk64reportmap_repPin.js to a service like https://www.minifier.org/.

  5. ahh ok; I did not…. will try this

Leave a Reply

Your email address will not be published / Required fields are marked *