Getting driving distance with Google maps API and

Question

UPDATE: Found the Google distancematrix and tried to modify my code accordingly. I am getting an invalid arguments error here:

return new GeoLocation(dstnc, uri.ToString());
            }
            catch
            {
                return new GeoLocation(0.0, "https://");
            }

Basically, I need to get the driving distance from two known lat/longs. I am using an SSIS package and I found a fantastic tutorial online that comes very close to producing the results I need.

Tutorial: http://www.sqlmusings.com/2011/03/25/geocode-locations-using-google-maps-v3-api-and-ssis/

What they are doing is passing a known street address to Google and reading the lat/long from the returned XML.

What I need to do differently is pass two known lat/longs and read the returned distance.

Example: https://maps.googleapis.com/maps/api/distancematrix/xml?origins=32.1576780,%20-82.9070920&destinations=27.6536997,%20-81.5158944&mode=driving&units=imperial&sensor=false

They use C# which I am not good enough with to know how to make the modification. I took a stab at it anyway and here is what I came up with:

 using System;
using System.Collections.Generic;
using System.Text;

//added these
using System.Data;
using System.Net;
using System.Web;
using System.Xml;

//  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
//  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
//  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
//  PARTICULAR PURPOSE.

namespace sqlmusings
{

    public interface IGeoLocation
    {
        string geocodeurl { get; set; }
        float distance { get; set; }
    }

    public struct GeoLocation : IGeoLocation
    {
        private string _geocodeurl;
        private Double _distance;

        public GeoLocation(string geocodeurl, Double distance)
        {
            _geocodeurl = geocodeurl;
            _distance = distance;
        }

        public string geocodeurl
        {
            get { return _geocodeurl; }
            set { _geocodeurl = value; }
        }

        public Double distance
        {
            get { return _distance; }
            set { _distance = value; }
        }
    }

    public class GeoCode
    {
        const string _googleUri = "https://maps.googleapis.com/maps/api/distancematrix/xml?origins=";
        //sample
        //https://maps.googleapis.com/maps/api/distancematrix/xml?origins=32.1576780,-82.9070920&destinations=27.6536997,-81.5158944&mode=driving&units=imperial&sensor=false

        private static Uri GetGeoCodeURI(string origins)
        {
            origins = HttpUtility.UrlEncode(origins);
            string uri = String.Format("{0}{1}&sensor=false", _googleUri, origins);

            return new Uri(uri);

        public static GeoLocation GetCoordinates(string origins)
        {
            WebClient wc = new WebClient();
            Uri uri = GetGeoCodeURI(origins);

            try
            {
                string geoCodeInfo = wc.DownloadString(uri);
                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.LoadXml(geoCodeInfo);

                string status = xmlDoc.DocumentElement.SelectSingleNode("status").InnerText;
                double dstnc = 0.0;
                XmlNodeList nodeCol = xmlDoc.DocumentElement.SelectNodes("result");
                foreach (XmlNode node in nodeCol)
                {

                    dstnc = Convert.ToDouble(node.SelectSingleNode("distance/text").InnerText, System.Globalization.CultureInfo.InvariantCulture);
                }
                return new GeoLocation(dstnc, uri.ToString());
            }
            catch
            {
                return new GeoLocation(0.0, "https://");
            }
        }

    }

}

I added the distance and _distance at the top of the code but Im not sure where to make the change to add a field for my second lat/long.

Just in case, here is the Main.cs (I have not modified it at all):

/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using SC_65a998228f6546ed965116b9f8b76953.csproj;

//  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
//  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
//  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
//  PARTICULAR PURPOSE.

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    public override void PreExecute()
    {
        base.PreExecute();
        /*
          Add your code here for preprocessing or remove if not needed
        */
    }

    public override void PostExecute()
    {
        base.PostExecute();
        /*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        /*
          Add your code here
        */
        sqlmusings.GeoLocation geolocation = sqlmusings.GeoCode.GetCoordinates(Row.AddressLine1 + "," + Row.City + "," + Row.State + "," + Row.Country);
        Row.Latitude = Convert.ToDecimal(geolocation.latitude);
        Row.Longitude = Convert.ToDecimal(geolocation.longitude);
        Row.GeoCodeURL = geolocation.geocodeurl;
   }

}

Just a push in the right direction is all Im asking.

Answer 1

The trick with the String.Format function is that the numbers in braces (e.g. {0} and {1}) are the bits that will be replaced by the arguments you provide in respective order.

What you usually want to do is something like this:

double oLat = 32.1576780, oLng = -82.9070920;
double dLat = 27.6536997, dLng = -81.5158944;

String url = String.Format("https://maps.googleapis.com/maps/api/distancematrix/xml?origins={0},{1}&destinations={2},{3}&mode=driving&sensor=false", oLat, oLng, dLat, dLng);

One thing to keep in mind is that the Maps APIs have Terms & Conditions that require certain things in order to be able to use the API for free. Make sure you read over them, as you may need a commercial license for your use case.