Calculate Geographical Coding and Time Zone for an Address in SQL Server with PowerShell, T-SQL and Google Maps

By: John Miner   |   Updated: 2016-09-23   |   Comments (3)   |   Related: > Spatial Data Storage

Problem

For any given postal address, determine the time zone information used by the people that work and live there.

Solution

There are many mapping application interfaces (API's) available to solve this problem. Microsoft Bing Maps, Google Maps and MapQuest are part of the top 10 companies offering these services. Some criteria that differentiates these companies are functionality, popularity, and ease of use. Please see this article for full details.

Business Problem

You were asked by your manager to associate each Contoso Retail store with a latitude/longitude coordinate. In addition, you want to store all date/time data using an GMT+0 offset. The AT TIME ZONE expression was released in SQL Server 2016 and can be used to translate the stored data to the correct date/time for each location. We want to leverage PowerShell for this solution since we will be moving to either Azure Infrastructure As A Service (IAAS) or Platform As A Service (PAAS) in the future. The Contoso Retail BI sample database will be leveraged during the creation and validation of our sample code.

Latitude and Longitude

An understanding of how the world is divided into coordinates will be solid foundation to start our programming journey.

First, the world is divided at the equator into the Northern and Southern hemispheres. The equator is consistently warm since it receives 12 hours of sun every day. Latitude lines horizontally divide the world with the North pole being 90 degrees North and the South Pole being -90 degrees South. The equator is located at 0 degrees North.

There are four notable parallels of latitude. Both the arctic and Antarctic circles have extreme weather. The midnight sun shines during summer and polar nights darken the winter. Both tropics have the sun directly overhead at least one time a year. This occurs during the equinox in March or September. The Tropic of Cancer marks the northern-most position on the Earth. Its twin is the Tropic of Capricorn marking the southern-most position on the Earth.

Second, the world is divided into the Eastern and Western hemispheres at the prime meridian. Longitude lines vertically divide the world with each circle passing thru both the North and South Pole. A half circle is called a meridian. There are 360 meridians corresponding to one degree of angle change at the poles. Cartographers have named the imaginary line that goes thru Greenwich, England as the prime meridian which is location at 0 degrees East. While latitude ranges for 90 to -90 degrees North, longitude ranges from 180 to -180 degrees East.

Below is a drawing that explains these concepts. For more information, please read the following article at timeanddate.com.

Time Zones

In theory, time zones are easy to calculate. There are twenty four hours in a day or twenty four time zones. Again, Greenwich England starts the day off. Going west from Greenwich Mean Time (GMT), you subtract hours. Going east you add hours. Isn't that simple?

In reality, it is hard to calculate time zones! Let us look at the Western Hemisphere between the GMT-3 and GMT-4 times zones. New Foundland decided to use -3.5 hours as the offset from GMT. If we look at Chile in South America, the country uses the GMT-4 time zone while most of the country is in the GMT-5 time zone.

In short, for a given address we need to calculate the latitude and longitude coordinates. For each time zone, we need to draw the corresponding geographical object and plot the coordinate. This can be quite cumbersome.

The above companies that provide web services to solve these mapping problems for us.

Choosing a Web Service Provider

My criteria for selecting a provider is to use a web service that is free, well documented and easy to use. Some vendors are focused on a certain functionality. For instance, four squares is great for mapping a cell phone to a business location and MapQuest is great for driving directions. For our business problem, I selected Google Maps over Bing Maps due to the ease of use for time zone calculations.

Get Geographical Coordinates

The ultimate goal is to craft PowerShell cmdlet that convert an address to geographical coordinates and geographical coordinates to a time zone information.

The documentation for the Google Web Service that does geo-coding is here. Using the service is quite simple. You request an authentication (service) key which is used with each web call. For the free service, you can code 2500 addresses per day. For more details, review the information on this web page.

To obtain information from the web service, we need to leverage two detente classes.

First, the System.Net.WebRequest allows a developer to call a web service. We are going to pass the service key and address as parameters on the query string. The location results can be returned as either XML or JSON. Since PowerShell can work natively with XML, we are going to select that as the output format.

Second, the System.IO.StreamReader allows the programmer to read bytes from the web service into a internal object. This object can be casted into a STRING or XML as the final result. The Get-Geo-Coding cmdlet listed below puts these concepts together into one neat function.

 
#
# Name:      Get-Geo-Coding
# Purpose:   Use Google api 4 address 2 location calc.
#

function Get-Geo-Coding {
    [CmdletBinding()] 
    param(
        [Parameter(Mandatory = $true)]
        [String] $ServiceKey,

        [Parameter(Mandatory = $true)]
        [string] $FullAddress
    )

    # Create request string
    [string]$ApiUrl = ""
    $ApiUrl += "https://maps.googleapis.com/maps/api/geocode/xml?address="
    $ApiUrl += $FullAddress
    $ApiUrl += "&key="
    $ApiUrl += $ServiceKey

    # Make request
    $Request = [System.Net.WebRequest]::Create($ApiUrl)
    $Request.Method ="GET"
    $Request.ContentLength = 0

    # Read responce
    $Response = $Request.GetResponse()
    $Reader = new-object System.IO.StreamReader($Response.GetResponseStream())
    

    # Return the result
    return $Reader.ReadToEnd()
} 

My company has a downtown Boston office located in Cambridge, MA. We will be moving the sales department to Burlington, MA in September 2016. Let's use that address as sample input to the cmdlet. Please notice, I am replacing any spaces with a plus sign in the address parameter. This is due to the fact that this text will end up in a query string.

#
# Step 1 - Test get geo coding function
#

# Clear screen
Clear-Host

# Make up address
[string]$Address = "5 Wayside Road, Burlington, MA 01803"
$Address = $Address -replace ' ', '+'

# Get lat & lng
[string]$Ret = Get-Geo-Coding -FullAddress $Address `
    -ServiceKey "AIzaSyC9dRle6-C1RmRld7DLkchz6ALwT94NPnc"

# Show the result
$Ret

One half of the output from executing cmdlet is listed below. The status code should be checked to see if the web service returned a good result. The XML below returns detailed street address information such as county, country and zip code plus four. While this is interesting, we are looking for latitude and longitude coordinates.

The second half of the output from executing cmdlet is listed below. The location node under the parent node geometry contains the two coordinates we are looking for.

Get Time Zone Information

The documentation for the Google Web Service that does time zone coding is here. Using the service is quite simple. You request a authentication (service) key which is used with each web call. For the free service, you can code 2500 locations per day. For more details, review the information on this web page.

To obtain information from the web service, we need to leverage the same two detente classes.

The first three input parameters are straight forward. The service key provided by Google when you register for their site. The latitude/longitude coordinates are numbers expressing an exact location on the planet.

But what is the last parameter?

This web service will not only tell you the time zone, but it will tell you whether or not to apply day light savings. The datetime parameter is in UTC format which is the number of seconds from January 1st, 1970.

The Get-Time-Zone cmdlet listed below puts these concepts together into one neat function.

#
# Name:      Get-Time-Zone
# Purpose:   Use Google api 4 location 2 timezone calc.
#

function Get-Time-Zone {
    [CmdletBinding()] 
    param(
        [Parameter(Mandatory = $true)]
        [String] $ServiceKey,

        [Parameter(Mandatory = $true)]
        [string] $Latitude,

        [Parameter(Mandatory = $true)]
        [string] $Longitude,

        [Parameter(Mandatory = $false)]
        [String] $DateTime = "1/1/1970"
    )

    # Current or supplied time in UTC format
    $Start = [DateTime]"1/1/1970"
    if ($DateTime -ne $Start)
    {
        $End = [DateTime]($DateTime)
    }
    else
    {
        $End = [DateTime](get-date)
    }  
    $Utc = ($End - $Start)


    # Create request string
    [string]$ApiUrl = ""
    $ApiUrl += "https://maps.googleapis.com/maps/api/timezone/xml?location="
    $ApiUrl += $Latitude
    $ApiUrl += ","
    $ApiUrl += $Longitude
    $ApiUrl += "&timestamp="
    $ApiUrl += $Utc.TotalSeconds.ToString()
    $ApiUrl += "&key="
    $ApiUrl += $ServiceKey

    # Make request
    $Request = [System.Net.WebRequest]::Create($ApiUrl)
    $Request.Method ="GET"
    $Request.ContentLength = 0

    # Read responce
    $Response = $Request.GetResponse()
    $Reader = new-object System.IO.StreamReader($Response.GetResponseStream())
    

    # Return the result
    return $Reader.ReadToEnd()
}
 

We are going to continue with the Burlington, MA office example. The code below calls the cmdlet with all the correct parameters.

#
# Step 2 - Test get time zone function
#

# Clear screen
Clear-Host

# Get time zone
[string]$Ret = Get-Time-Zone -Latitude "42.4825379" -Longitude "-71.2135082" `
    -ServiceKey "AIzaSyC9dRle6-C1RmRld7DLkchz6ALwT94NPnc" -DateTime "11/01/2015 01:00"

# Show the result
$Ret

Output from executing cmdlet is listed below. A offset of 3600 seconds or one hour means we are observing daylight savings time. Any time stored in GMT time should be adjusted by -18,000 seconds or minus five hours. The actual time zone is Eastern Daylight Time.

New Table

I am going to leverage the two PowerShell camlets I created in a previous tip.

The Exec-NonQuery-SqlDb can be used to create and load the new geometry information dimension table with a single DDL statement; and the Get-DataSet-SqlDb cmdlet can be used to return a single record from the new table using a single DML statement.

The code below loads a T-SQL file from disk into a string variable and executes the statement against the Contoso Retail database.

#
# Step 3 - Create n load table 
#


# Set connection string
[string]$ConnStr = `
    'Server=13.82.58.95;Database=ContosoRetailDW;Uid=geocoding;Pwd=MS#tips$2016;'

# Set the path
Set-Location "C:\MSSQLTIPS\MINER2016\ARTICLE-2016-07-GEOCODING-N-TIMEZONE"

# Grab the script
[string]$SqlQry = Get-Content -Delimiter "`n" "create-n-load-table.sql"

# Run the script
Exec-NonQuery-SqlDb  -ConnStr $ConnStr -SqlQry $SqlQry | Out-Null

The T-SQL file (code) below does the following: drops the existing table and creates/loads the table with fresh data. The plan is to update the DimGeoInfo table with newer coordinates and time zone information from the Google web service calls.

/*  
 Create a table to hold geographical information 
*/

-- Delete existing table
DROP TABLE IF EXISTS dbo.DimGeoInfo;

-- Create new table
SELECT 
    S.StoreKey,
    Cast
    (
        Rtrim(Coalesce(S.AddressLine1, '')) + ', ' +
        Coalesce(G.CityName, '') + ', ' +
        Coalesce(G.StateProvinceName, '') + ', ' +
        Coalesce(G.RegionCountryName, '')  + ', ' +
        Coalesce(S.ZipCode, '') 
 as VarChar(1024)
    ) as FullAddress,
    S.GeoLocation.Lat as OldLatitude,
    S.GeoLocation.Long as OldLongitude,
    Cast(0 as float) as Latitude,
    Cast(0 as float) as Longitude,
    Cast('Unknown' as varchar(128)) as TimeZone
INTO 
    dbo.DimGeoInfo
FROM 
    dbo.DimStore as S
LEFT JOIN
    dbo.DimGeography as G
ON 
    S.GeographyKey = G.GeographyKey
ORDER BY
    S.StoreKey

The PowerShell code below retrieves and displays the retail store with id equal to 3.

#
# Step 4 - Examine the dimension table 
#

# Show the deata
[string]$SqlQry = "select * from dbo.DimGeoInfo where StoreKey = 3"

# Run the script
Get-DataSet-SqlDb  -ConnStr $ConnStr -SqlQry $SqlQry

Output from executing cmdlet shows the last three columns need to be update with the correct information.

Partial Matches

I did not choose the third store from the Contoso Retail database by accident. The address is Downtown in the city or town named Kennewick and in the state of Washington. That is pretty vague. Therefore, a call to the web service might return several partial matches.

The code below requests time zone information for Downtown Kennewick.

# Clear screen
Clear-Host

# Make up address
[string]$Address = "Kennewick,  Downtown, Kennewick, Washington, United States, 97001"
$Address = $Address -replace ' ', '+'

# Get time zone
[xml]$Xml =  Get-Geo-Coding -FullAddress $Address `
    -ServiceKey "AIzaSyC9dRle6-C1RmRld7DLkchz6ALwT94NPnc" 

# Two partial matches
$Xml.SelectNodes(“//GeocodeResponse/result")

Output from executing the cmdlet. When we code a solution, we will pick the topmost result since the majority of the partial matches will be in the same time zone.

Reviewing Missing Data

Before we craft a program to calculate geometry coordinates and time zones, it is a good idea to get a look at the inputs to the program. Listed below are the top 10 records from the table.

Calculating Geo Codes

The PowerShell script below puts all the pieces together into one program. One goal of the script is the ability to perform these calculations on database in either an IAAS or PAAS environment.

The following algorithm was used for the script.

1. Set connection string to database.
2. Return key information from table.
3. For each row, do the following.
4. Call Get-Geo-Coding function.
• If good return status, use new coordinates.
• If bad return status, use old coordinates.
5. Call Get-Time-Zone function.
• If good return status, use time zone name.
• If bad return status, label time zone as unknown.
6. Update the row with new latitude, longitude, and time zone values.

#
# Step 5 - Update with lat, lng & time zone
#


# Set connection string
[string]$ConnStr = `
    'Server=13.82.58.95;Database=ContosoRetailDW;Uid=geocoding;Pwd=MS#tips$2016;'

# Show the deata
[string]$SqlQry1 = "select * from dbo.DimGeoInfo"


# Remove empty row (count), grab store id & key data fields 
$List = Get-DataSet-SqlDb -ConnStr $ConnStr -SqlQry $SqlQry1 | `
    Select-Object StoreKey, FullAddress, OldLatitude, OldLongitude | `
    Where-Object { $_.StoreKey -gt 0 }

# For each store
foreach ($Item in $List)
{

    <#
        Get key values from database
    #>

    # Grab values from list
    $MyKey = $Item.StoreKey
    $MyAddress = $Item.FullAddress -replace ' ', '+'


    <#
        Get Geography Coding (latitude/longitude)
    #>

    # Web service call for geo coding
    [xml]$MyDoc1 =  Get-Geo-Coding -FullAddress $MyAddress `
     -ServiceKey "AIzaSyC9dRle6-C1RmRld7DLkchz6ALwT94NPnc" 

    # Was call successful?
    $Status1 = $MyDoc1.SelectSingleNode(“//GeocodeResponse/status”) 

    # Good call
    if ($Status1.FirstChild.InnerText -eq 'OK')
    {

        # Pick top most result?
        $Location = $MyDoc1.SelectNodes(“//location”) | Select-Object -First 1

        # Grab new geo coding
        $MyLat = $Location.Lat
        $MyLng = $Location.Lng
    }

    # Bad call
    else
    {
        # Use old values
        $MyLat = $Item.OldLatitude
        $MyLng = $Item.OldLongitude  
    }


    <#
        Get TimeZone Coding
    #>

    # Get time zone
    [xml]$MyDoc2 = Get-Time-Zone -Latitude $MyLat -Longitude $MyLng `
     -ServiceKey "AIzaSyC9dRle6-C1RmRld7DLkchz6ALwT94NPnc" 
    
    # Succesful service call?
    $Status2 = $MyDoc2.SelectSingleNode(“//TimeZoneResponse/status”) 

    # Good call
    if ($Status2.FirstChild.InnerText -eq 'OK')
    {
        $TimeZone = $MyDoc2.SelectNodes(“//TimeZoneResponse”) 
        $MyZone = $TimeZone.time_zone_name
    }

    # Bad call
    else
    {
        $MyZone = "Unknown"
    }

    # Debugging stmts
    <#
    $MyKey
    $MyAddress
    $MyLat
    $MyLng
    $MyZone
    Write-Host " "
    #>


    <#
        Update database with new values
    #>
     
    # Craft an update statement
    [string]$SqlQry2 = ""   
    $SqlQry2 += 'update DimGeoInfo '
    $SqlQry2 += 'set Latitude  = ' + $MyLat.ToString()
    $SqlQry2 += ', Longitude = ' + $MyLng.ToString()    
    $SqlQry2 += ', TimeZone = ' + [char]39 + $MyZone + [char]39 + ' '
    $SqlQry2 += 'where StoreKey = ' + $MyKey + '; '

    
    # Show record key
    $MyKey

    # Run the update query
    Exec-NonQuery-SqlDb  -ConnStr $ConnStr -SqlQry $SqlQry2 | Out-Null

}

After executing our script, we want to check the results of our program. The majority of rows have been updated correctly.

Refining the calculation

I just wanted to state that getting 90 percent of the data classified is easy. It is usually the last 10 percent that is hard. Our sample database and program is no different. We have 22 out of 306 rows or 7.2 percent that could not be exactly classified.

The T-SQL statement below shows the rows that were converted to a simple GMT label as a time zone.

-- 22 out of 306 partially identified
SELECT 
  [StoreKey]
 ,RIGHT([FullAddress],30)
 ,[Latitude]
 ,[Longitude]
 ,[TimeZone]
FROM [ContosoRetailDW].[dbo].[DimGeoInfo]
WHERE TimeZone like 'GMT%'
ORDER BY TimeZone Desc

The output from executing the T-SQL statement is listed below.

Let's take a closer look at store number 207. If we call the PowerShell snippet labeled step #2 with these longitude and latitude coordinates, we can see the resulting XML. Clearly, London should be labeled as GMT Standard Time.

At this point, we just need to roll up our sleeves and write a T-SQL script to refine any time zones that were missed by the web service. Remember, the new sys.time_zone_info system table in SQL Server 2016 reflects the entries that are stored in the Windows Operating System. Make sure the results of the web service call match these entries.

The T-SQL code below can be placed in a file an executed with a call to the Exec-NonQuery-SqlDb cmdlet. Just remember to remove the SELECT statement from the final input file.

-- Change to find tz options
SELECT * 
FROM sys.time_zone_info
WHERE current_utc_offset = '-08:00';

-- Stores in United Kingdom
UPDATE [DimGeoInfo]
SET TimeZone = 'GMT Standard Time'
WHERE TimeZone = 'GMT+01:00';

-- Stores in Pakistan
UPDATE [DimGeoInfo]
SET TimeZone = 'Pakistan Standard Time'
WHERE TimeZone = 'GMT+05:00';

-- Store in Armenia
UPDATE [DimGeoInfo]
SET TimeZone = 'Georgian Standard Time'
WHERE TimeZone = 'GMT+04:00';

-- Store in Kyrgyzstan
UPDATE [DimGeoInfo]
SET TimeZone = 'Central Asia Standard Time'
WHERE TimeZone = 'GMT+06:00';

-- Store in Alaska
UPDATE [DimGeoInfo]
SET TimeZone = 'Alaskan Standard Time'
WHERE TimeZone = 'GMT-10:00';

Computers Are Sometimes Wrong

Most of Alaska is in the Alaska Time Zone; however, a portion of the Aleutian Islands that is west of 169 degrees 30 minutes west longitude observes the Hawaii-Aleutian Standard Time Zone. If we look at store number 183 that is located in Anchorage Alaska, the Google Web Service classified the location as time zone GMT-10:00.

If we start researching this issue, we find that Get-Time-Zone cmdlet defaults to a date and time of 1/1/1970. For some reason, the web service does no handle this location, date, and time correctly.

I suggest we modify the call the Get-Time-Zone cmdlet by passing the current data and time as a parameter. This might fix the other 22 stores that were misclassified. I leave validation of the coding change as an exercise for you to try.

# Get time zone
[xml]$MyDoc2 = Get-Time-Zone -Latitude $MyLat -Longitude $MyLng `
    -ServiceKey "AIzaSyC9dRle6-C1RmRld7DLkchz6ALwT94NPnc" `
    -DateTime (get-date).ToString()

Next Steps

• SQL Server 2016 introduced several new objects to deal with the greater demand of dates. The AT TIME ZONE expression can convert a datetimeoffset value into a target time zone. This is very helpful for organizations that have locations all over the world.
• However, it falls short in translating an address to a time zone. How did I know that Albany, New York is in the Eastern Standard Time zone?
• Today, I talked about how the world is divided into latitude and longitude coordinates. In addition, there are twenty four time zones in the world. Calculating a time zone given a geo-coordinate can be quite difficult.
• Ten companies that provide mapping solutions were evaluated. The web services of Google Maps was selected as the application programming interface for two custom PowerShell cmdlets. The Get-Geo-Coding cmdlet converts a textual address into a geo-coordinate and the Get-Time-Zone cmdlet coverts a given location into a time zone. This PowerShell code will fill this short coming.
• Check out these related tips:
  • SQL Server Spatial Data Storage Tips
  • PowerShell Tips

About the author

John Miner is a Data Architect at Insight Digital Innovation helping corporations solve their business needs with various data platform solutions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips