Solved

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

Posted on 2016-08-31
8
105 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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
 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

738 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