Link to home
Start Free TrialLog in
Avatar of Cobra967
Cobra967Flag for United States of America

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  

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

Open in new window

Avatar of Robberbaron (robr)
Robberbaron (robr)
Flag of Australia image

need clarification on your task....
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
Avatar of Cobra967

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
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.

	<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>  

Open in new window

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

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

Open in new window

Thank you very much robr! My Frankenstein is starting to make some move but is not running yet :-)
I got a warning/error for Line 71 and an error on Line 39

User generated image
ASKER CERTIFIED SOLUTION
Avatar of Robberbaron (robr)
Robberbaron (robr)
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Open in new window

Again, thank you!