GeoLocation by Radius Using Google Maps and .NET

Imports ProviderGeoCodingScheduledTask.UtilityClass
Imports System.Data.SqlClient
Imports System.Data

Module Module1



    Sub Main()

        SetLatLong()
        Console.WriteLine("Completed")

    End Sub

    ''' <summary>
    ''' This method retrieves the recordsets into a data table (open/close connection)
    ''' 
    ''' Next, iterates through table, updating records in the db.
    ''' 
    ''' Since we are limited to 15,000 queries to the Google Maps API per day,
    ''' entries are datestamped.
    ''' 
    ''' On each run, select only records who's GeoAddedDate is more than 5 days passed
    ''' </summary>
    ''' <remarks></remarks>
    Sub SetLatLong()


        Dim AddressID As Integer
        Dim geoLat, geoLong, city, state, origzip, zip As String
        Dim formattedAddress As String = ""
        Dim street As String = ""
        Dim latlong As String = ""
        Dim dt As New DataTable

        'dt = GetSqlData("SELECT * FROM tbl_GeoAddresses WHERE TYPE LIKE 'OFFICE%' -- AND GeoAddedDate < GetDate() - Day(5)")
        dt = GetSqlData("SELECT * FROM tbl_GeoAddresses")

        For Each row As DataRow In dt.Rows
            Dim GeoSearch As New GeocodingService.svcGeoSearch

            Delay(0.5)        ' delay 

            'retrieve all address portions
            AddressID = row("AddressID")
            street = row("STREET").ToString()
            city = row("CITY").ToString()
            state = row("STATE").ToString()
            origzip = row("ZIP").ToString()
            zip = origzip

            If Not OmitRecord(street) Then
                latlong = ""

                ' zip code can be 921120350 or 90405;
                ' geocode services recognizes 92112-0350
                If zip.Length() > 5 Then
                    zip = zip.Insert(5, "-")
                End If

                Try


                    'pass the formatted address to retrieve the lat/long values from the geocoding service
                    If Not String.IsNullOrEmpty(street) Then
                        ' parse street
                        street = ParseStreet(street)
                        If street.Length > 0 Then
                            formattedAddress = street & ", " & city & ", " & state & " " & zip
                            latlong = GeoSearch.GetLatLong(formattedAddress).Replace("<Point><coordinates>", "")
                            Console.WriteLine(formattedAddress & " : " & latlong)
                        End If
                    End If

                    ' retry retrieving the geocode without street address
                    If street.Length = 0 Or latlong.Length = 0 Then
                        formattedAddress = city & ", " & state & " " & zip
                        latlong = GeoSearch.GetLatLong(formattedAddress).Replace("<Point><coordinates>", "")
                        Console.WriteLine(formattedAddress & " : " & latlong)
                    End If
                    'parse lat/long

                Catch ex As Exception
                    ' do nothing, as the web request may have timed out.
                    ' if a record is missed it will be updated next time.
                End Try
                If latlong.Length > 0 Then


                    geoLat = Trim(latlong.Substring(0, latlong.IndexOf(",")))
                    geoLong = Trim(latlong.Substring(latlong.IndexOf(",") + 1, (latlong.Length - latlong.IndexOf(",") - 1)))

                    Try
                        Dim updateCount As Integer
                        Dim sqlConn As New SqlConnection(DbConnectionString)
                        sqlConn.Open()
                        'update the rows
                        Using updateCommand As New SqlCommand("UPDATE tbl_GeoAddresses SET Geo_Lat=@Geo_Lat, Geo_Long=@Geo_Long, GeoAddressUsed=@GeoAddressUsed, GeoAddedDate=GetDate() WHERE AddressID=@AddressID AND ZIP=@OrigZip", sqlConn)
                            With updateCommand
                                .CommandType = CommandType.Text
                                .Parameters.Add("Geo_Lat", SqlDbType.VarChar).Value = geoLat
                                .Parameters.Add("Geo_Long", SqlDbType.VarChar).Value = geoLong
                                .Parameters.Add("GeoAddressUsed", SqlDbType.VarChar).Value = formattedAddress
                                .Parameters.Add("AddressID", SqlDbType.Int).Value = AddressID
                                .Parameters.Add("OrigZip", SqlDbType.VarChar).Value = origzip
                                updateCount = .ExecuteNonQuery()
                            End With
                        End Using

                    Catch ex As Exception
                        Console.Write(ex.Message.ToString())
                    End Try

                End If ' latlong.Length > 0

            End If ' Not OmitRecord()
            GeoSearch = Nothing

        Next

    End Sub

    Private Function OmitRecord(ByVal street As String) As Boolean

        If street.StartsWith("USED") Or street.StartsWith("HAND DELV") Or street.StartsWith("Remit") Or street.StartsWith("PREV") Or street.StartsWith("*USE") Then
            Return True
        Else
            Return False
        End If

    End Function

    ''' <summary>
    ''' Remove Ste, Suite ####, etc from end of street addresses
    ''' </summary>
    ''' <param name="street"></param>
    ''' <remarks></remarks>
    Private Function ParseStreet(ByVal street As String) As String
        street = TruncateString(street, "SUITE")
        street = TruncateString(street, "STE")
        street = TruncateString(street, "#")
        street = TruncateString(street, "UNIT ")


        street = TruncateString(street, "USED")
        street = RemoveStreet(street, "HAND DELV")
        street = RemoveStreet(street, "Remit")


        Return street
    End Function

    Private Function RemoveStreet(ByVal street As String, ByVal badText As String) As String


        Return street
    End Function
    Private Function TruncateString(ByVal street As String, ByVal removeText As String) As String
        If street.Contains(removeText) Then
            If street.IndexOf(removeText) = 0 Then
                street = street.Substring(0, street.IndexOf(removeText))
            Else
                street = street.Substring(0, street.IndexOf(removeText) - 1)
            End If

        End If

        Return street
    End Function

    Private Sub Delay(ByVal dblSecs As Double)

        Const OneSec As Double = 1.0# / (1440.0# * 60.0#)
        Dim dblWaitTil As Date
        Now.AddSeconds(OneSec)
        dblWaitTil = Now.AddSeconds(OneSec).AddSeconds(dblSecs)
        Do Until Now > dblWaitTil
            'Application.DoEvents() ' Allow windows messages to be processed
        Loop

    End Sub
End Module