22 Apr 2013

Oracle APEX & Google Maps Integration

I've read so many blogs about how to integrate Google Maps into Oracle Application Express, but most of them were very old and maybe needed updating for the current versions of both Apex and Maps.

With that in mind, this was using Oracle Apex v4.2.1 and Maps API version 3

It actually isn't too hard to achieve, but there's one or two points not clear in the sites I read.

Once you have Apex running, all you need then is an access key from Google API. In version 3 you do not need to put the key reference in, so I'm not sure how it knows it's your key! Perhaps through the referrer sites.

This example assumes many markers, on a single map, and the marker details are kept in a database table

Anyway, here goes....
  1. SQL Workshop > Object Browser
  2. Create Procedure
  3. Give your Procedure a name "MAPIT"
  4. You can uncheck Arguments, we don't need any for this example
  5. Next we'll need to enter some PL/SQL code ..
  6. cursor c_klt is
    
    select  
    ("MARKERS"."LATITUDE" || ',' || "MARKERS"."LONGTITUDE") AS geoloc, 
    "MARKERS"."MARKER_NAME" as "MARKER_NAME"
    from "MARKERS";
    
    l_t number(3) := 0;
    
    begin
    htp.print('
    <script type="text/javascript">
     function initialize() {
     var mapOptions = { 
      center: new google.maps.LatLng(-33.771214,151.080967),
      zoom: 11,
      mapTypeId: google.maps.MapTypeId.ROADMAP
     };
    
    var map = new google.maps.Map(document.getElementById("map-canvas"), mapOptions);
    ');
    
    for r_klt in c_klt
    loop
    htp.print('
     var geocoder = new google.maps.LatLng('||r_klt.geoloc||''||');
     
     var marker = new google.maps.Marker(
      {position: geocoder,map: map,animation:google.maps.Animation.DROP,title:"Marker: '||r_klt.MARKER_NAME||''||'"});
    ');
    
    l_t := l_t + 1;
    
    end loop;
    
    htp.print('};
    </script>;
    
    <body onload="initialize()" style="font-family: Arial;border: 0 none;">
     <div id="map-canvas" style=" height: 400px; width: 800px;"></div>
    </body>;
    ');
    
    end;
    
    
    
    Couple of things to note: (i.e. the things I did wrong)

    • The ; at the end of the SQL statement
    • The canvas name, just make sure it's consistent
    • The height and width of the DIV "map-canvas". Change to match your page

  7. Now create your page, keep it blank for now
  8. Edit Page Properties (Right Click Page name - first item under page rendering)
  9. In the HTML Header Section, HTML Header input box, add the API link:
  10. <script src="http://maps.googleapis.com/maps/api/js?sensor=false" type="text/javascript">
    </script>
    
  11. In the same section, the Page HTML Body-Attribute:
  12. onload="initialize()"
    
  13. Now, apply changes and go back to Page overview
  14. Right Click Region, to create a new region
  15. Choose PL/SQL Dynamic Content
  16. Give it a name, and on the next page, enter the Procedure name we created above. Don't forget the ;
  17. MAPIT;
    
That's it. Make sure you've got geo coded data in your tables. (There's plenty of blogs about geo-coding on the fly)

Good Luck

14 comments:

  1. Glad to see you finally got round to updating the site bro! :-)

    ReplyDelete
  2. Stuart, this page's script is just Great !!!, i have implemented and works as i spected, conratulations Man !!!!.
    thanks
    Manuel

    ReplyDelete
  3. Stuart, i am trying to implement and infowindow over my map, but when i call it in the procedure, the map does not show. Can you give me some tips how to achieve this using your example procedure ? thanks in advance.
    here is the link i have been reading : https://developers.google.com/maps/documentation/javascript/examples/infowindow-simple-max

    ReplyDelete
    Replies
    1. Hi Manuel,

      Will take a look and see if I can provide any assistance

      (Sorry for taking so long to reply)

      Delete
  4. This procedure works only if you have a table containing LngLat data in your database. If you have more text addresses to decode using geocoder function, the script mensioned above is inefficient. Trying to display more addresses, I have unsuccessfull implemented the same procedure in my application using google gecoder function (api v3). The google maps is displayed as well but the markers do not.

    ReplyDelete
    Replies
    1. Hi Raoult
      I agree..
      I actually needed something different (i.e. already encoded in DB).. There were lots of scripts on the 'net to achieve geocoding, but none for my use case. Also, as mentioned many of the scripts were a little out of date.

      I found it very hit and miss as to what worked and what didn't, but thanks for your comments

      Delete
  5. Ok, Raoult, actually in my pc when i put the mouse over the markers i got the marker_name hint displayer, but in my tablet (mac or android) i push the marker but no marker_name comment is dispalyed. Any tip ?

    ReplyDelete
  6. Hi,

    is there a way I can add a search box so that my users may enter the address and apart from everything that you are showing here, will use this address as the center point of the map

    Have to say, this is the simplest post I've seen so far to integrate Google Maps with APEX

    Thanks

    ReplyDelete
  7. Hi Stuart,

    For simplicity, I removed the cursor and loop part and tried the "MAPIT" stored procedure, only to have a blank page with no map displayed. Could you kindly tell me why? (BTW, I use https://maps.googleapis.com/maps/api/js?sensor=false instead, which works with other pages in APEX using an HTML region based on the javascript function.)

    Thanks a million.

    ReplyDelete
  8. I would like to use the autocomplete functionality of the places api. Any idea how to achieve this?

    ReplyDelete
  9. Thanks, your article was very helpful for me and help me to solve my old problem. I always love to embed some maps in my sites, cause I think that its very useful for my users. Usually I do this with help of this nice service https://elfsight.com/google-maps-widget/ that helps me to do it easily and without any extra efforts.

    ReplyDelete
  10. When your website or blog goes live for the first time, it is exciting. That is until you realize no one but you and your. click here

    ReplyDelete
  11. This is such a great resource that you are providing and you give it away for free. I love seeing blog that understand the value of providing a quality resource for free. the page

    ReplyDelete