Solved

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

Posted on 2016-08-31
8
65 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

18 Experts available now in Live!

Get 1:1 Help Now