Cobra967
asked on
Geocoding my SQL data from ASP.NET VB.NET page
Hello,
I need your help in bringing to life my little Frankenstein. I am trying to find the Latitude and Longitudes for each record in my SQL database Table (DATA) and store them (less than 100 records). The idea is to press a button in my webpage that will run this process (I don't need to see the result in a grid). After reading endless pages on internet about this subject, I came up with this Geocoding Frankenstein :-) ...I know it needs a lot of re-work and that's why I need your expertise to get it to work. Thanks a million (or two). P.S. I don't know anything about C# but I do have a "limited" knowledge in VB.NET
I need your help in bringing to life my little Frankenstein. I am trying to find the Latitude and Longitudes for each record in my SQL database Table (DATA) and store them (less than 100 records). The idea is to press a button in my webpage that will run this process (I don't need to see the result in a grid). After reading endless pages on internet about this subject, I came up with this Geocoding Frankenstein :-) ...I know it needs a lot of re-work and that's why I need your expertise to get it to work. Thanks a million (or two). P.S. I don't know anything about C# but I do have a "limited" knowledge in VB.NET
Imports System.Data
Imports System.Configuration
Imports System.IO
Imports System.Net
Imports System.Text
Imports System.Data.SqlClient
Public Class Geocode
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Dim dt As DataTable = GetData("SELECT [RecordId], [City], [Address], [State], [Zip], [Status], [Lat], [Lng] FROM [DATA] WHERE (([Territory] IS NULL) AND ([Lat] IS NULL) AND ([Address] IS NOT NULL) AND ([City] IS NOT NULL))")
End If
End Sub
Protected Sub ButtonGeocode_Click(sender As Object, e As EventArgs) Handles ButtonGeocode.Click
Dim url As String = "http://maps.google.com/maps/api/geocode/xml?address="(Address+City+State) + "&sensor=false"
Dim request As WebRequest = WebRequest.Create(url)
Using response As WebResponse = DirectCast(request.GetResponse(), HttpWebResponse)
Using reader As New StreamReader(response.GetResponseStream(), Encoding.UTF8)
Dim dsResult As New DataSet()
dsResult.ReadXml(reader)
Dim dtCoordinates As New DataTable()
dtCoordinates.Columns.AddRange(New DataColumn(3) {New DataColumn("Id", GetType(Integer)), New DataColumn("Address", GetType(String)), New DataColumn("Latitude", GetType(String)), New DataColumn("Longitude", GetType(String))})
For Each row As DataRow In dsResult.Tables("result").Rows
Dim geometry_id As String = dsResult.Tables("geometry").[Select]("result_id = " + row("result_id").ToString())(0)("geometry_id").ToString()
Dim location As DataRow = dsResult.Tables("location").[Select](Convert.ToString("geometry_id = ") & geometry_id)(0)
dtCoordinates.Rows.Add(row("result_id"), row("formatted_address"), location("lat"), location("lng"))
Next
'GridView1.DataSource = dtCoordinates
'GridView1.DataBind()
End Using
End Using
End Sub
Private Function GetData(query As String) As DataTable
Dim conString As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
Dim cmd As New SqlCommand(query)
Using con As New SqlConnection(conString)
Using sda As New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dt As New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
End Using
End Function
End Class
ASKER
1. Yes. each record has a RecordID, Address, City, State, ZipCode, Latitude, Longitude (Plus more that are not important for this task) . All my records have an address, city state and zip. However, right now some record do not have Longitude and Latitude.
2. Yes.
3. Yes, save the Latitude and Longitude for each record that was not previously geocoded already
2. Yes.
3. Yes, save the Latitude and Longitude for each record that was not previously geocoded already
here is code I use to Geocode from an address.
Pstreet1 etc are text fields on HTML form that are linked to the database.
still trying to find the code I used to bulk geocode my address list 3 years ago.
Pstreet1 etc are text fields on HTML form that are linked to the database.
still trying to find the code I used to bulk geocode my address list 3 years ago.
<script src="https://maps.googleapis.com/maps/api/js?v=3.exp"></script>
<script>
var geocoder;
var map;
var markersArray = [];
// Deletes all markers in the array by removing references to them
function deleteOverlays() {
if (markersArray) {
for (i in markersArray) {
markersArray[i].setMap(null);
}
markersArray.length = 0;
}
}
function addMarker() {
var lat = document.getElementById('loc_lat').value; var lng = document.getElementById('loc_lng').value;
if (lat != -999) {
latlng = new google.maps.LatLng(lat, lng);
map.setCenter(latlng);
var marker = new google.maps.Marker({
map: map,
draggable: true,
position: latlng
});
//handle user drag on marker...
google.maps.event.addListener(marker, 'dragend', function (event) {
document.getElementById("loc_lat").value = this.getPosition().lat();
document.getElementById("loc_lng").value = this.getPosition().lng();
});
markersArray.push(marker);
}
}
function initialize() {
geocoder = new google.maps.Geocoder();
var latlng = new google.maps.LatLng(-29.46365, 151.02635);
var mapOptions = {
streetViewControl: false,
zoomControl: true,
scaleControl: true,
zoom: 14,
zoomControlOptions: {
style: google.maps.ZoomControlStyle.SMALL
},
center: latlng
}
map = new google.maps.Map(document.getElementById('map-canvas'), mapOptions);
}
function codeAddress() {
//var address = document.getElementById('address').value;
var address = document.getElementById('PStreet1').value + ',' + document.getElementById('PSuburb').value + ',' + document.getElementById('PState').value + ',' + document.getElementById('PCountry').value;
//alert(address);
geocoder.geocode({ 'address': address }, function (results, status) {
if (status == google.maps.GeocoderStatus.OK) {
map.setCenter(results[0].geometry.location);
deleteOverlays();
document.getElementById("loc_lat").value = results[0].geometry.location.lat();
document.getElementById("loc_lng").value = results[0].geometry.location.lng();
addMarker();
} else {
alert('Geocode was not successful for the following reason: ' + status);
}
});
}
google.maps.event.addDomListener(window, 'load', initialize);
</script>
try this .... you need to rework your logic somewhat i believe.
for each address record
get GeoCode
''>>todo.. check GeoCode is ok
update the record
next address
for each address record
get GeoCode
''>>todo.. check GeoCode is ok
update the record
next address
Imports System.Data
Imports System.Configuration
Imports System.IO
Imports System.Net
Imports System.Text
Imports System.Data.SqlClient
Partial Class Geocode
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
End If
End Sub
Protected Sub DoGeoCode()
Dim conString As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
Dim rs As SqlDataReader
Dim fulladdress As String, tmpRow As DataRow
Using con As New SqlConnection(conString)
con.Open()
Using cmd As New SqlCommand()
cmd.Connection = con
cmd.CommandText = "SELECT [RecordId], [City], [Address], [State], [Zip], [Status], [Lat], [Lng] FROM [DATA] WHERE (([Territory] IS NULL) AND ([Lat] IS NULL) AND ([Address] IS NOT NULL) AND ([City] IS NOT NULL))"
rs = cmd.ExecuteReader()
Do While rs.Read()
'have a record
fulladdress = rs("Address") & ", " & rs("City") & ", " & rs("State") & ", " & rs("Zip")
tmpRow = GetGeoCode(fulladdress)
Using con2 As New SqlConnection(conString)
con2.Open()
Dim cmd2 As SqlCommand = con2.CreateCommand()
cmd2.CommandText = "UPDATE [DATA] Set [Lat] = " & tmpRow("Latitude") & ", [Lng] = " & tmpRow("Longitude") & " WHERE [RecordId] = " & rs("RecordId") & ";"
cmd2.ExecuteNonQuery()
End Using
Loop
rs.Close()
End Using
End Using
End Sub
Private Function GetGeoCode(address As String) As DataRow
Dim url As String = "http://maps.google.com/maps/api/geocode/xml?address=" + address + "&sensor=false"
Dim request As WebRequest = WebRequest.Create(url)
Using response As WebResponse = DirectCast(request.GetResponse(), HttpWebResponse)
Using reader As New StreamReader(response.GetResponseStream(), Encoding.UTF8)
Dim dsResult As New DataSet()
dsResult.ReadXml(reader)
Dim dtCoordinates As New DataTable()
dtCoordinates.Columns.AddRange(New DataColumn(3) {New DataColumn("Id", GetType(Integer)), New DataColumn("Address", GetType(String)), New DataColumn("Latitude", GetType(String)), New DataColumn("Longitude", GetType(String))})
For Each row As DataRow In dsResult.Tables("result").Rows
Dim geometry_id As String = dsResult.Tables("geometry").[Select]("result_id = " + row("result_id").ToString())(0)("geometry_id").ToString()
Dim location As DataRow = dsResult.Tables("location").[Select](Convert.ToString("geometry_id = ") & geometry_id)(0)
Dim output As DataRow = dtCoordinates.Rows.Add(row("result_id"), row("formatted_address"), location("lat"), location("lng"))
Next
End Using
End Using
End Function
Protected Sub ButtonGeoCode_Click1(sender As Object, e As EventArgs) Handles ButtonGeoCode.Click
DoGeoCode()
End Sub
End Class
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Frankenstein is alive!!!! Robr, YOU are the man!!!! Everything works perfectly! Thank you Very much!!!!! I know it was a mess. The only edit I had to make to you code was to remove the reference to "tmpRow As DataRow" on line 4 since it was not used anymore. To everyone benefit, below is my (Robr) working final product:
Imports System.Data
Imports System.Configuration
Imports System.IO
Imports System.Net
Imports System.Text
Imports System.Data.SqlClient
Partial Class Geocode
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
Protected Sub DoGeoCode()
Dim conString As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
Dim rs As SqlDataReader
Dim fulladdress As String, geoloc As Collection
Using con As New SqlConnection(conString)
con.Open()
Using cmd As New SqlCommand()
cmd.Connection = con
cmd.CommandText = "SELECT [RecordId], [City], [Address], [State], [Zip], [Lat], [Lng] FROM [DATA] WHERE (([Lat] IS NULL) AND ([State] IS NOT NULL) AND ([Address] IS NOT NULL) AND ([City] IS NOT NULL))"
rs = cmd.ExecuteReader()
Do While rs.Read()
'have a record
fulladdress = rs("Address") & ", " & rs("City") & ", " & rs("State") & ", " & rs("Zip")
geoloc = GetLocation(fulladdress)
If geoloc("Accuracy") > 0 Then
'got an answer at least
Using con2 As New SqlConnection(conString)
con2.Open()
If con2.State <> ConnectionState.Open Then
Console.WriteLine(con2.State)
Stop
End If
Dim cmd2 As SqlCommand = con2.CreateCommand()
cmd2.CommandType = CommandType.Text
cmd2.CommandText = "UPDATE [DATA] Set [Lat] = " & geoloc("Latitude") & ", [Lng] = " & geoloc("Longitude") & " WHERE [RecordId] = " & rs("RecordId") & ";"
cmd2.ExecuteNonQuery()
End Using
End If
Loop
rs.Close()
End Using
End Using
End Sub
'get a Geographic location for a street address
Private Function GetLocation(ByVal Address As String) As Collection
Dim thisLoc As Collection = New Collection()
Dim requestUri As String = String.Format("http://maps.google.com/maps/api/geocode/xml?address={0}&sensor=false", Uri.EscapeDataString(Address))
Dim dLat As Decimal = -999
Dim dLong As Decimal = -999
Dim dAccuracy As Integer = -999
Dim xrequest = WebRequest.Create(requestUri)
Dim xresponse = xrequest.GetResponse()
Dim xdoc As XDocument = XDocument.Load(xresponse.GetResponseStream())
Dim status As String = xdoc.Element("GeocodeResponse").Element("status").Value
'higher accuracy = better
If status = "OK" Then
Dim result As XElement = xdoc.Element("GeocodeResponse").Element("result")
Dim locationElement = result.Element("geometry").Element("location")
Dim lat As XElement = locationElement.Element("lat")
Dim lng As XElement = locationElement.Element("lng")
dLat = CType(lat, Decimal)
dLong = CType(lng, Decimal)
Dim locationtype As String = result.Element("geometry").Element("location_type").Value
Select Case locationtype
Case "ROOFTOP"
dAccuracy = 9
Case "RANGE_INTERPOLATED"
dAccuracy = 6
Case "GEOMETRIC_CENTER"
dAccuracy = 4
Case "APPROXIMATE"
dAccuracy = 2
Case Else
dAccuracy = 1
End Select
ElseIf status = "OVER_QUERY_LIMIT" Then
Console.WriteLine("Google GeoCode: " + status + ".")
Throw New Exception("Google GeoCode: " + status + ".")
Else
'no useful data so save the -999
dAccuracy = 0
End If
thisLoc.Add(dLat, "Latitude")
thisLoc.Add(dLong, "Longitude")
thisLoc.Add(dAccuracy, "Accuracy")
Return thisLoc
End Function
Protected Sub ButtonGeoCode_Click1(sender As Object, e As EventArgs) Handles ButtonGeocode.Click
DoGeoCode()
End Sub
End Class
ASKER
Again, thank you!
1. You have a SQL table with ids and street addresses
2. You want to use google API to determine a Lat/Long that matches this address as much as possible ?
3. Update the table with this data