|
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
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.
I am a technical architect/senior software engineer, technical trainer, entrepreneur.
I have worked in several sectors from healthcare, to entertainment and global intelligent traffic systems, using .Net, SQL, NoSQL and some of the latest technologies to deliver quality software systems for clients.
Past tech flavors include C#, WCF, SOA, MVC, MVVM, Silverlight, Assembler, Pascal, VB, Java/J2EE/EJB/JDBC, Perl, NLTK, TSQL, NoSQL, KendoUI, NodeJS, SignalR, Backbone JS, Angular JS, Latest .Net technologies, Amazon AWS...