RPG, jQuery, Google Maps and Geocoding


RPG, jQuery, Google Maps and Geocoding

I recently started playing around with Google Maps and Geocoding.  I wanted to create a map on my main site that would show the cities that our customers are in.  This meant manipulating a Google Map with markers.

After a quick search, I ran across Google's own documentation for this.  It turns out it really isn't that difficult.  You just need to provide latitude and longitude points for the markers to be placed on the maps.  See the Google Maps APIs Documentation.   A list of marker images can be found at

To get the latitude and longitude from the data you most likely have stored (ie, address, city, state, zip country) you need to use Google's Geocoding API.  I at first thought about just doing this on the fly, but the GeoCoding API has quotas (2,500 a day) and doing that with each page load would not only be super slow, but the quota would be reached in the first couple page displays.  So, instead I decided to convert the address info to latitude and longitude data once.  

In order to get a list of latitude and longitude markers I first created a table to hold that data:

Sidebar: Curious about the Terms of Service for Google Geocoding?  I was too, wondering if I could store the data.  From what is understood after a lot of searching is you can store the data as long as it is only used with Google's apps and not others (like Bing or Yahoo).  Here's a link to a good discussion on this.  If you feel this is a misinterpretation please feel free to contact me

File Name . . . . CSTLLPF                                                  
  Library . . . .   BVSCOMP                                                
Format Descr  . .                                                          
Format Name . . . RCSTLL                                                   
File Type . . . . PF            Unique Keys - N                            
Field Name FMT Start Lngth Dec Key Field Description                       
LLLAT       P      1     9  06     Latitude                               
LLLNG       P      6     9  06     Longitude                               
LLCOUNT     P     11    13  00     Count                                   
LLFMTADD    A     18   256         Formatted Address    

For this file I made a key on the Latitude and Longitude fields so that if there was more than one customer in that city I could display that count on the marker.

Next I ran through my customer master file and for each location attempted to retrieve the geocoding data for the location.  To do this you can use the Google Maps Geocoding API.  This is a web service that you can pass address information to and retrieve the latitude and longitude data required.  This API needs to be enabled in your Google API Manager Console.  You will need to create a project and request a key for the use of the API.  There is also a limit per day of 2,500 requests per day.  

The JSON data is returned from the API in the following format:

   "results" : [
         "address_components" : [
               "long_name" : "Riccione",
               "short_name" : "Riccione",
               "types" : [ "locality", "political" ]
               "long_name" : "Riccione",
               "short_name" : "Riccione",
               "types" : [ "administrative_area_level_3", "political" ]
               "long_name" : "Province of Rimini",
               "short_name" : "RN",
               "types" : [ "administrative_area_level_2", "political" ]
               "long_name" : "Emilia-Romagna",
               "short_name" : "Emilia-Romagna",
               "types" : [ "administrative_area_level_1", "political" ]
               "long_name" : "Italy",
               "short_name" : "IT",
               "types" : [ "country", "political" ]
               "long_name" : "47838",
               "short_name" : "47838",
               "types" : [ "postal_code" ]
         "formatted_address" : "47838 Riccione, Province of Rimini, Italy",
         "geometry" : {
            "bounds" : {
               "northeast" : {
                  "lat" : 44.0253177,
                  "lng" : 12.6839613
               "southwest" : {
                  "lat" : 43.9801,
                  "lng" : 12.6136248
            "location" : {
               "lat" : 43.999296,
               "lng" : 12.6555485
            "location_type" : "APPROXIMATE",
            "viewport" : {
               "northeast" : {
                  "lat" : 44.0253177,
                  "lng" : 12.6839613
               "southwest" : {
                  "lat" : 43.9801,
                  "lng" : 12.6136248
         "place_id" : "ChIJe-91HWHdLBMRJ4yGKB1Dum8",
         "types" : [ "locality", "political" ]
   "status" : "OK"

The program to gather the geocoding data and populate our database is as follows:

     FCSTMSTLL  UF   E           K DISK
     FCSTLL1    UF A E           K DISK    USROPN
      * Prototypes                                                   *
      /include QCOPYSRC,yajl_h
     D GetUriRG        pr                  extpgm('GETURIRG')
     D   G_In                              like(GetUri_In)
     D   G_Out                             like(GetUri_Out)
     D   G_Head                            like(GetUri_Head)
     D   G_Data                            like(GetUri_Data)
     D   G_MsgCd                           like(GetUri_MsgCd)
     D   G_Msg                             like(GetUri_Msg)
     D #QCmdExc        PR                  ExtPgm('QCMDEXC')
     D  Cmd                       32000    Const
     D  CmdLen                       15  5 Const
      * Work variables
     D docNode         s                   like(yajl_val)
     D resultsList     s                   like(yajl_val)
     D node            s                   like(yajl_val)
     D addressNode     s                   like(yajl_val)
     D geometryNode    s                   like(yajl_val)
     D errorMsgNode    s                   like(yajl_val)
     D locationNode    s                   like(yajl_val)
     D val             s                   like(yajl_val)
     D yajl_errMsg     s            500a   varying inz('')
     D WKLat           S                   LIKE(LLLAT)
     D WKLng           S                   LIKE(LLLNG)
     D thisLat         S                   LIKE(LLLAT)
     D thisLng         S                   LIKE(LLLNG)
     D thisAddress     S                   LIKE(LLFMTADD)
     D QCmdCmd         S          32000
     D i               S             10i 0
     D quit            S               N   INZ(*OFF)

       open CSTLL1;
       read CSTMSTLL;

       dow (not %eof(CSTMSTLL));

         if (CMPRC <> 'Y');
           EXSR $getLatLong;

           if (thisLat <> 0) and (thisLng <> 0);
             WKLat = thisLat;
             WKLng = thisLng;

             CHAIN LLKEY CSTLL1;

             if (%found(CSTLL1));
               LLCOUNT += 1;
               UPDATE RCSTLL;
               LLLAT = WKLat;
               LLLNG = WKLng;
               LLCOUNT = 1;
               LLFMTADD = thisAddress;
               WRITE RCSTLL;


           if (quit);
             CMPRC = 'Y';
             UPDATE RCSTMST;


         read CSTMSTLL;

       close CSTLL1;

       *INLR = *ON;
       //* Get Lat and Long
       begsr $GetLatLong;

         thisLat = 0;
         thisLng = 0;

         Clear GetUri_In;
         GI_URI = '';
         GI_Data = 'key=<mykey>' + 
                   '&address=' +
                   %trim(CMFCITY) + ',' +
                   %trim(CMFSTATE) + ',' +

         GI_ReqMeth = 'GET';
         GI_OutType = '*RETURN';
         //GI_OutType = '*STMF';
         //GI_Stmf = '/tmp/geo.json';
         GI_SSL = '*YES';
         GI_Port = 443;
         GI_SprHead = '*YES';
         GI_CCSID = 1208;
         GI_CODPAG = 1208;
         //GI_Debug = '*YES';


         docNode = yajl_buf_load_tree(%addr(GetUri_Out):

         if (docNode <> *NULL);
           errorMsgNode = YAJL_object_find(docNode:'error_message');

           if (errorMsgNode = *NULL);
             resultsList = YAJL_object_find(docNode:'results');

             i = 0;

             dow YAJL_ARRAY_LOOP(resultsList:i:node);
               addressNode = YAJL_object_find(node:'formatted_address');
               thisAddress = yajl_get_string(addressNode);

               geometryNode = YAJL_object_find(node:'geometry');
               locationNode = YAJL_object_find(geometryNode:'location');

               val = YAJL_object_find(locationNode: 'lat');
               thisLat = yajl_get_number(val);

               val = YAJL_object_find(locationNode: 'lng');
               thisLng = yajl_get_number(val);

             quit = *ON;


      * INZ                                                          *
     C     *INZSR        BEGSR
     C     LLKey         KLIST
     C                   KFLD                    WKLat
     C                   KFLD                    WKLng
     C                   ENDSR

The program is pretty simple.  It reads through the customer master file (CSTMSTLL) and uses the address information to call the Google Geocoding API.  The data is returned in JSON format and parsed using the invaluable IBM i port of YAJL from Scott Klement.  Because this program is only going to be run once (or once every month or year to update the list) I didn't do a lot of error checking and used RLA instead of SQL.  I may change that in the future.  It's not of any consequence, though.  Also, the reason I open and close the CSTLLPF file manually is I was thinking of adding some CL commands to clear the file if needed.

Once the program completes we have a full list of data to use in our web application.  The data looks like the following:

    LLLAT        LLLNG             LLCOUNT   LLFMTADD                                   
37.971559    87.571089-                  3   Evansville, IN, USA                        
51.441641     5.469722                   1   Eindhoven, Netherlands                     
34.147784   118.144515-                  2   Pasadena, CA, USA                          
36.677737   121.655501-                  1   Salinas, CA, USA                           
53.277962   110.006145-                  2   Lloydminster, AB, Canada                   
40.904019    74.408740-                  1   Boonton, NJ 07005, USA                     
42.069750    87.787840-                  2   Glenview, IL, USA                          
49.166589   123.133569-                  1   Richmond, BC, Canada    

The main processing for displaying the web page with the map and the markers will be done using jQuery which calls an eRPG (RPG CGI) program to return the geocoding data as a JSON object.

You will need to specify a link to a JavaScript source used by Google's Map application:

<script src="//<mykey>"></script>

Yes, you need to supply your key here as well.  The good news is the quotas for displaying maps using the Google Maps Javascript API is 25,000 a day.  

The HTML for the web page is very simple.  Shown here is the main part of the page that we use to define the maps DIV object where the map will be placed:

Here is a map with locations for most of our customers.  Hover your mouse on a marker to see the count for that city/area.
<div id="map" style="width: auto; min-height: 500px; height: auto; border: 1px solid black;"></div>

The jQuery used when the page loads is as follows:

var centerPosition = {lat: 39.8282, lng: -98.5795};
var mapOptions = {
    zoom: 2,
    mapTypeId: google.maps.MapTypeId.ROADMAP,
    center: centerPosition

$(document).ready(function() {
  $("#map").html("Loading locations...");

  .fail(function(xhr, status, error) {
    $("#map").html("Error loading map markers. " + error);
  .done(function(data) {

function doMap(cityList) {
  map = new google.maps.Map(document.getElementById("map"), mapOptions);
  var image = '';
  $.each(cityList, function(index, value) {
    var marker = new google.maps.Marker({
        position: {lat:parseFloat(, lng:parseFloat(value.lng)},
        title: value.title,
        draggable: false,
        map: map,
        icon: image

The jQuery first calls program GETCSTLL which returns the geocoding data in the following JSON format.

  {"lat": 33.2323200,
   "lng": -223.113343,
   "title": "City, State (count)"

We then read through the JSON array results and for each result place a marker on the map.

The RPG program that builds the JSON geocoding data, GETCSTLL, appears as the following:

      * Prototypes                                                   *
      /include qcopysrc,yajl_h
     D CSTLLDS       E DS                  EXTNAME(CSTLLPF)
     D errMsg          S            500    Varying
       exec sql Set Option Commit=*NONE;


       exec sql declare C1 cursor for
       exec sql open C1;

       exec sql fetch from C1
         into :LLLAT, :LLLNG, :LLCOUNT, :LLFMTADD;

       dow (xSQLState2 = Success_On_Sql);
         yajl_beginObj();  //Detail Object
         yajl_addChar('title':%trimr(LLFMTADD) + ' (' + %char(LLCOUNT) + ')');
         yajl_endObj(); //Detail Object

         exec sql fetch from C1
           into :LLLAT, :LLLNG, :LLCOUNT, :LLFMTADD;

       exec sql Close C1;


       *INLR = *on;

Again you can see we are using the YAJL product, but this time it is used to generate the JSON as well as write it out to standard output for the jQuery to use in it's processing.

One note: for the latitude and longitude I first was using yajl_addNum() to create them as numbers.  The only problem was for negative numbers without a number in the ones place they did not have a leading zero (ie, they were -.0343 instead of -0.0343).  This caused an issue with the JSON as without the leading zero the JSON is invalid.  I was able to get around that by making the value a string and then converting it to a float data type when plugging the value into the map position parameter.

When done and working we know can see a map containing markers for each city, state.  To see the count just hover your mouse pointer over the marker on the map.

This was a fun project for sure, and I'm sure there are many features that could be added.  We shall see!

Last edited 03/30/2017 at 09:25:59


Copyright 1983-2024 BVSTools
GreenBoard(v3) Powered by the eRPG SDK, MAILTOOL Plus!, GreenTools for Google Apps, jQuery, jQuery UI, BlockUI, CKEditor and running on the IBM i (AKA AS/400, iSeries, System i).