Solved

Geocoding my SQL data from ASP.NET VB.NET page

Posted on 2016-08-31
8
75 Views
Last Modified: 2016-09-02
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

0
Comment
Question by:Cobra967
  • 4
  • 4
8 Comments
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 41779623
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
0
 

Author Comment

by:Cobra967
ID: 41779773
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
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 41781144
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

0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 41781208
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

0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:Cobra967
ID: 41782173
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

Errors.jpg
0
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 500 total points
ID: 41782515
the first error is due to not setting a return value.... no idea about the second as it is msdn standard. I have added a check on connection state just for testing.

Also found my Geocode project source and have updated the examples to use XML lookup instead of your DataRow methods as it makes more sense to me. (DataSets seem overly complex)

    Protected Sub DoGeoCode()
        Dim conString As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
        Dim rs As SqlDataReader
        Dim fulladdress As String, tmpRow As DataRow, 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], [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)
                    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] = " & tmpRow("Latitude") & ", [Lng] = " & tmpRow("Longitude") & " WHERE [RecordId] = " & rs("RecordId") & ";"
                            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

Open in new window

0
 

Author Comment

by:Cobra967
ID: 41782543
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

0
 

Author Closing Comment

by:Cobra967
ID: 41782545
Again, thank you!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now