Since we were doing all our demo’s based on data obtained from Geonames we thought we’d mock up a quick and dirty mashlet based on the contents of our report grid. The great thing about using an ExtJS grid for APEX reports is that your data is available in a JSON object and easily accessible/retrievable so this in this particular example when the data changes in the report we can fire off a “load” event to update our Google Maps window. The event fires after the data has been loaded into the Ext data store (this can either be from a filter, sort, search, or pagination). So the example we are about to show is 100% ajax based, each time we change the contents of the report the map is updated with markers of the locations contained within the report/grid.

This is pretty much a straight copy from the Ext sample code, we simply change the markers to use the data defined in our store and use an address to set the map center (it just so happens to be our office address) . However I’m sure there’s probably a more efficient way than destroying the object and re-creating it, like I said it’s quick and dirty!

<script src="http://maps.google.com/maps?file=api&amp;amp;amp;amp;amp;amp;amp;v=2&amp;amp;amp;amp;amp;amp;amp;key=&amp;amp;amp;amp;amp;amp;amp;YOUR_GMAP_KEY." type="text/javascript"></script>
<script type="text/javascript">
var mapwin;
function renderGMap() {

         if (mapwin) {
            mapwin.destroy();
         }
         gridMarkers = new Array();
         for (var i = 0; i < store.data.items.length; i++) {
             gridMarkers.push({
               "lat": store.data.items[i].data.LATITUDE,
               "lng": store.data.items[i].data.LONGITUDE,
               "marker": {
                  "title": store.data.items[i].data.NAME
               }
            });
         }
         mapwin = new Ext.Window({
            layout: 'fit',
            title: 'GMap Window',
            closeAction: 'hide',
            width: 400,
            height: 400,
            x: 40,
            y: 60,
            items: {
               xtype: 'gmappanel',
               region: 'center',
               zoomLevel: 5,
               gmapType: 'map',
               mapConfOpts: ['enableScrollWheelZoom', 'enableDoubleClickZoom', 'enableDragging'],
               mapControls: ['GSmallMapControl', 'GMapTypeControl', 'NonExistantControl'],
               setCenter: {
                  geoCodeAddr: '48A Old Steine, Brighton, England, UK',
                  marker: {
                     title: 'e-DBA Ltd.'
                  }
               },
               markers: gridMarkers
            }
         });

         mapwin.show();

   }
Ext.onReady(function() {

   gMap = new Ext.Toolbar.Button({
      pressed: false,
      disabled: false,
      enableToggle: false,
      text: 'GMap',
      id: 'gmap',
      cls: '',
      handler: renderGMap
   });
   grid.getBottomToolbar().add('-', gMap);
   store.on({'load': renderGMap });
});
</script>

To make this example more useful in the real world, we’ve modified the report to show towns within an X km radius of a town selected by the user via APEX form options with a little Ext flavouring.

Note: the RADIANS function is not a default function provided by Oracle so we had to create our own….

FUNCTION radians
( p_degree IN NUMBER
) RETURN NUMBER DETERMINISTIC
IS
BEGIN
  RETURN p_degree / 57.2957795; --1R = 180C
END radians;

SELECT htmldb_item.checkbox(1, geoname_id) selection
,      geoname_id
,      extjs_utils.asciiEscape(name) name
,      modification_date
,      latitude
,      longitude
,      population
,     round((ACOS((SIN(extjs_utils.RADIANS(:P13_LATITUDE))*SIN(extjs_utils.RADIANS( latitude))) + (COS(extjs_utils.RADIANS(:P13_LATITUDE))*COS(extjs_utils.RADIANS(latitude))*COS(extjs_utils.RADIANS(longitude)- extjs_utils.RADIANS(:P13_LONGITUDE)))) * 6378.388),2) AS distance
,      comments
FROM (
SELECT *
FROM   geoname)
WHERE (ACOS((SIN(extjs_utils.RADIANS(:P13_LATITUDE))*SIN(extjs_utils.RADIANS( latitude))) + (COS(extjs_utils.RADIANS(:P13_LATITUDE))*COS(extjs_utils.RADIANS(latitude))*COS(extjs_utils.RADIANS(longitude)- extjs_utils.RADIANS(:P13_LONGITUDE)))) * 6378.388) <=  13_RADIUS_KM)