Using Google Maps API to Get Geocodes for Customer Addresses

I wrote an iOS and Android app that manages a company department’s equipment (Service Objects). This app is used in the field to issue/return equipment, perform surveys, create new equipment, ect…

One of the features I wanted to add was a map to show customers near the user, including AX account information and address. One problem in doing this was that none of the customers in AX had Latitude and Longitude information. So I came up with a 3-part process to retrieve Geolocation data and feed the date back into AX.

The 3 steps could easily be integrated into a single program, but for reasons I won’t go into here I opted to separate each task:

  1. Retrieving the customers needing to be updated
  2. Resolving Latitude and Longitude based on the customer address
  3. Importing the data back into AX and updating the customer records

Step One:
I also have an equivalent AX job that runs and outputs the values to a file, but honestly I found it quicker (although certainly dirtier) to just run this SQL statement and copy the results to a file.


select distinct ct.ACCOUNTNUM + '|' 
                  + ad.STREET + '|' 
                  + ad.CITY + '|' 
                  + ad.STATE
from custtable ct
	join DIRPARTYTABLE dp on dp.PARTYID = ct.PARTYID 
           and dp.DATAAREAID = ct.DATAAREAID
	join ADDRESS ad on ad.ADDRRECID = dp.RECID 
           and ad.DATAAREAID = ct.DATAAREAID 
           and ad.ADDRTABLEID = 2303
where ct.DATAAREAID = 'your_ax_company'
   and ad.TYPE = 2
   and ct.CUSTGROUP in ('FOOD','COFFEE')
   and (ct.rdlat = 0 and ct.rdlon = 0)
   and LEN(ad.STREET) > 5
   and ct.ONETIMECUSTOMER = 0
   and ct.BLOCKED = 0

Step Two:
A console application to connect to Google Maps and pull data, then write to a text file.

Main.cs:


using System;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using Newtonsoft.Json;
using System.Collections.Generic;

namespace GeoCoder
{
    class Program
    {
        static void Main(string[] args)
        {
            int counter = 0;
            string line;

            System.IO.StreamReader file = new System.IO.StreamReader(@"C:\YourInputFile.txt");
            System.IO.StreamWriter geos = new System.IO.StreamWriter(@"C:\YourOutputFile.txt");

            while ((line = file.ReadLine()) != null)
            {
                string[] lineArray;
                string acct, street, city, state;
                lineArray = line.Split('|');

                string lat = "";
                string lon = "";

                acct = lineArray[0];
                street = lineArray[1].Replace(' ', '+').Replace("\\", "").Replace("/", "").Replace("-", "").Replace(",", " ").Replace("#", "").Replace(".", "").Trim();
                city = lineArray[2].Replace(' ', '+').Replace("\\", "").Replace("/", "").Replace("-", "").Replace(",", " ").Replace("#", "").Replace(".", "").Trim();
                state = lineArray[3].Replace(' ', '+').Replace("\\", "").Replace("/", "").Replace("-", "").Replace(",", " ").Replace("#", "").Replace(".", "").Trim();

                string yourAPI_Key = "PasteYourAPI_KeyHere";
                string url = String.Format("https://maps.googleapis.com/maps/api/geocode/json?address={0},+{1},+{2}&key={3}", street, city, state, yourAPI_Key);

                using (WebClient wc = new WebClient())
                {
                    try
                    {
                        // download JSON response
                        var json = wc.DownloadString(url);

                        // deserialize JSON variable to the mapped class GoogleGeoCodeResponse
                        GoogleGeoCodeResponse obj = JsonConvert.DeserializeObject(json);

                        if (obj.results.Length > 0)
                        {
                            lat = obj.results[0].geometry.location.lat;
                            lon = obj.results[0].geometry.location.lng;
                        }
                        else
                        {
                            lat = "0";
                            lon = "0";
                        }
                        
                    }
                    catch
                    {
                        lat = "0";
                        lon = "0";
                    }
                }
                
                geos.WriteLine(acct + "|" + lat + "|" + lon);
                counter++;
            }

            file.Close();
            geos.Close();

            Console.WriteLine("{0} records written to file.\n", counter);
            Console.ReadLine();
        }
    }
}

And the GoogleGeoCodeResponse Class to map the JSON response:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace GeoCoder
{
    public class GoogleGeoCodeResponse
    {

        public string status { get; set; }
        public results[] results { get; set; }

    }

    public class results
    {
        public string formatted_address { get; set; }
        public geometry geometry { get; set; }
        public string[] types { get; set; }
        public address_component[] address_components { get; set; }
    }

    public class geometry
    {
        public string location_type { get; set; }
        public location location { get; set; }
    }

    public class location
    {
        public string lat { get; set; }
        public string lng { get; set; }
    }

    public class address_component
    {
        public string long_name { get; set; }
        public string short_name { get; set; }
        public string[] types { get; set; }
    }
}


And finally Step Three:


static void custGeoImport(Args _args)
{
    CustTable           cust;
    TextIo              file;
    FileName            filename = @"\\YourFolder\YourInputFile.txt";
    Container           con;
    FileIoPermission    permission;

    str                 lats, lons;
    CustAccount         acct;
    AddressLatitude     lat;
    AddressLongitude    lon;

    #File

    int                 x = 0;
    ;

    try
    {
        permission = new FileIOPermission(filename, #io_read);
        permission.assert();

        file = new TextIo(filename, #io_read);

        if (!file)
            throw Exception::Error;

        file.inRecordDelimiter(#delimiterCRLF);
        file.inFieldDelimiter("|");

        info("Account# | Latitude | Longitude");

        con = file.read();

        while (file.status() == IO_Status::Ok)
        {
            acct = conpeek(con, 1);
            lats = conpeek(con, 2);
            lons = conpeek(con, 3);

            if (lats != "0" && lons != "0")
            {
                lat = System.Convert::ToDouble(lats);
                lon = System.Convert::ToDouble(lons);

                select firstonly forupdate cust where cust.AccountNum == acct;

                if (cust.RecId)
                {

                    ttsbegin;
                    cust.RDLat = lat;
                    cust.RDLon = lon;
                    cust.update();
                    ttscommit;

                    info(strfmt("%1 | %2 | %3",acct,lats,lons));

                    x++;
                }
            }

            con = file.read();
        }
    }
    catch
    {
        CodeAccessPermission::revertAssert();
        info("An error was encountered.");
    }

    CodeAccessPermission::revertAssert();

    info(strfmt("%1 records updated.", x));
}