Call Google Maps Geocode API in Parallel using C# and TPL

Google Maps Geocode API provides a way to validate addresses by getting latitude and longitude and address type of a given address. In this post I would like to show you how to call this web service in parallel so you can speed up the address validation process. One thing to note is URL to get Geocode details from Google Maps  API is different if you are only using free version in comparison to when you are using Google Maps API for work. By default Google Maps API provide 2500 request per day for free. When you are calling Google Maps API using paid version you need to encrypt your request using your client ID and encryption key provided to you when you buy it.

First thing first. You will need a key to run this application so sign up for Google Maps API and get your key https://developers.google.com/maps/documentation/geocoding/intro

You can download the full source code from GoogleGeocode.GoogleMapsAPI Source

Setup your app.config file
In AppSettings section add

 
  <add key="IsGoogleMapsAPIPaid" value="0"/>
   <add key="URL" value="https://maps.googleapis.com/maps/api/geocode/json?address="/>
   <add key="APIClient" value="yourclientId"/>
   <add key="APIKey" value="yourcryptokey"/>

In your code you will get a list of addresses you want to validate from your database. In this demo I have put some addresses in a list.

 
 List lstAddresses = new List() 
{ 
 "UNIT 7, 7 ERINDALE ROAD BALCATTA WA  6021",                            
 "226 MCINTYRE ROAD SUNSHINE VIC  3020",                           
 "UNIT 1 & 2, 12 PREMIER COURT WARANA QLD  4575",
 "PETERSHAM NSW  2049",
 "UNIT 7, 7 ERINDALE ROAD BALCATTA WA  6021",                            
 "226 MCINTYRE ROAD SUNSHINE VIC  3020",                           
 "UNIT 1 & 2, 12 PREMIER COURT WARANA QLD  4575",
 "WENTWORTHVILLE NSW  2049",
 "UNIT 7, 7 ERINDALE ROAD BALCATTA WA  6021",                            
 "MCINTYRE ROAD SUNSHINE VIC  3020",                           
 "UNIT 1 & 2, 12 PREMIER COURT WARANA QLD  4575",
 "PETERSHAM NSW  2049"
};

There is a limit of 10 API calls per second when you are calling Google Maps APIs so I am passing 10 addresses to Parallel.ForEach loop and calling Google Geocode API. This will parallalize API calls. Also there is a check if all API calls finish within one second wait for 1 second before calling next batch to avoid getting QUERY_OVER_LIMIT error.

 
Parallel.ForEach(selected, new ParallelOptions() { MaxDegreeOfParallelism = 10 }, sel =>
{
 try
 {
  string address = sel.ToString();
  Console.WriteLine("Address = " + address);
  GeoDetail objResult = GeoDetail.GetGeoDetails(key, address);
  lock (lockMe)
  {
   lstResult.Add(objResult);
  }

 }
 catch (Exception ex)
 {
  Console.WriteLine(ex.ToString());
 }

});

GetGeoDetails() method calls Google Maps API using HttpRequest and Get HttpResponse object back which then being converted to JSON object using JSON.Net library.

 
public static GeoDetail GetGeoDetails(string APIKey, string address)
{
 string uri = ConfigurationManager.AppSettings["URL"];
 GeoDetail objResult = new GeoDetail() { Address = address, Latitude = -1, Longitude = -1, AddressType = "", Error = "" };

 try
 {
  string requestURL = "";
  if (ConfigurationManager.AppSettings["IsGoogleMapsAPIPaid"].Trim() == "0")
  {
   requestURL = uri + address + "&key=" + APIKey; // No need to sign URL and there is no APIClient ID to pass.
  }
  else
  {
   requestURL = GoogleSignedUrl.Sign(uri + address + "&client=" + ConfigurationManager.AppSettings["APIClient"].Trim(), APIKey);
  }

  HttpWebRequest request = WebRequest.Create(requestURL) as HttpWebRequest;

  request.Accept = "application/json";

  // Get response  
  using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
  {
   if (response.StatusCode == HttpStatusCode.OK)
   {
    StreamReader readert = new StreamReader(response.GetResponseStream());
    string x = readert.ReadToEnd();
    JObject jObject = JObject.Parse(x);
    //Console.WriteLine(x);
    if (jObject["status"].ToString() == "OK")// successful API call
    {
     if (jObject["results"].Count() > 0)
     {
      string locationType = jObject["results"][0]["geometry"]["location_type"].ToString();

      string lat = jObject["results"][0]["geometry"]["location"]["lat"].ToString().Trim();
      string lng = jObject["results"][0]["geometry"]["location"]["lng"].ToString().Trim();

      Console.WriteLine("Geolocation lat lng :  {0}    {1}  Type  : {2}", lat, lng, locationType);

      double? latValue = null;
      double? lngValue = null;

      if (!string.IsNullOrWhiteSpace(lat))
      {
       latValue = double.Parse(lat);
      }
      if (!string.IsNullOrWhiteSpace(lng))
      {
       lngValue = double.Parse(lng);
      }

      objResult.Latitude = latValue;
      objResult.Longitude = lngValue;
      objResult.AddressType = locationType; //locationType == "ROOFTOP")//exact address match

     }
     else
     {
      Console.WriteLine("No result found");
      objResult.Error = "No result found";
     }
    }
    else
    {
     Console.WriteLine(jObject["status"].ToString());
     objResult.Error = jObject["status"].ToString();
    }


   }
  }

 }
 catch (Exception ex)
 {
  Console.WriteLine(ex.ToString());
  objResult.Error = ex.ToString();
 }

 return objResult;
}

Search all tables in MSSQL for a specific value in all columns

I came across this little snippet which creates a stored procedure that can be used to query all the tables in MSSQL database to search for specific value. It may be useful to someone looking for a similar solution

 
CREATE PROC SearchAllTables
(
 @SearchStr nvarchar(100)
)
AS
BEGIN

    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL

    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL

            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + 'WITH (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM #Results
END