Zip Code radius search

[RandyHJ]

I have a web page that allows a user to put in a zip code and a radius in miles. The page is supposed to pull rows out of a database based on the zip code within the selected number of miles. The database contains latitude and longitude information for each row (location) of data.

If I put in a zip code that matches the zip code of an existing row, I can get results that include the corresponding row (location) only if I search within 25 miles or more. If I search within 10 miles, I do not get the location, even though I type in the proper zip code.

I am puzzled why the search will work for 25 miles or more, but will not work for 10 miles.

The code behind is written in VB.NET. It uses the Yahoo Maps api to determine the latitude and longitude of the zip code entered by the user. Then it calculates a high and low latitude and longitude for the database selection based on the distance desired.

Imports System.Data
Imports System.Data.SqlClient
Imports System
Imports System.Xml

Partial Class dealersearch
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        

		Dim coordinates As ArrayList = getCoordinates()
        Dim distance As ArrayList = calculateDistance(coordinates(0), coordinates(1))
        
        getCertifiedDealers(distance(0), distance(1), distance(2), distance(3))
        getDealers(distance(0), distance(1), distance(2), distance(3))

    End Sub

    Sub getCertifiedDealers(ByVal lowLat As Decimal, ByVal highLat As Decimal, ByVal lowLong As Decimal, ByVal highLong As Decimal)
        Dim SqlConnObj As New SqlConnection(ConfigurationManager.ConnectionStrings("Dealers").ToString)
        Dim SqlCmd1 As New SqlCommand("SELECT COUNT(ID) FROM Dealers WHERE OnWebsite = 'Yes' AND Certified = 'Yes' AND latitude BETWEEN " & lowLat & " AND " & highLat & " AND longitude BETWEEN " & lowLong & " AND " & highLong, SqlConnObj)

        SqlConnObj.Open()
        SqlCmd1.CommandType = CommandType.Text

        Dim count As String = SqlCmd1.ExecuteScalar
        If count > 0 Then
            Dim SqlReader As SqlDataReader
            Dim SqlCmd2 As New SqlCommand("SELECT DealerCompany, Certifications, Telephone, DealerFirstName, StreetAddress, City, StateProv, Zip, OnWebsite FROM Dealers WHERE OnWebsite = 'Yes' AND Certified = 'Yes' AND latitude BETWEEN " & lowLat & " AND " & highLat & " AND longitude BETWEEN " & lowLong & " AND " & highLong & "ORDER BY DealerCompany", SqlConnObj)

            SqlCmd2.CommandType = CommandType.Text
            SqlReader = SqlCmd2.ExecuteReader

            certifiedDealers.DataSource = SqlReader
            certifiedDealers.DataBind()
        Else
            certifiedResults.Visible = True
        End If
        SqlConnObj.Close()
    End Sub

    Sub getDealers(ByVal lowLat As Decimal, ByVal highLat As Decimal, ByVal lowLong As Decimal, ByVal highLong As Decimal)
        Dim SqlConnObj As New SqlConnection(ConfigurationManager.ConnectionStrings("Dealers").ToString)
        Dim SqlCmd1 As New SqlCommand("SELECT COUNT(ID) FROM Dealers WHERE OnWebsite = 'Yes' AND Certified = 'No' AND latitude BETWEEN " & lowLat & " AND " & highLat & " AND longitude BETWEEN " & lowLong & " AND " & highLong, SqlConnObj)

        SqlConnObj.Open()
        SqlCmd1.CommandType = CommandType.Text

        Dim count As String = SqlCmd1.ExecuteScalar
        If count > 0 Then
            Dim SqlReader As SqlDataReader
            Dim SqlCmd2 As New SqlCommand("SELECT DealerCompany, Telephone, DealerFirstName, StreetAddress, City, StateProv, Zip, OnWebSite FROM Dealers WHERE OnWebsite = 'Yes' AND Certified = 'No' AND latitude BETWEEN " & lowLat & " AND " & highLat & " AND longitude BETWEEN " & lowLong & " AND " & highLong & "ORDER BY DealerCompany", SqlConnObj)

            SqlCmd2.CommandType = CommandType.Text
            SqlReader = SqlCmd2.ExecuteReader

            dealers.DataSource = SqlReader
            dealers.DataBind()
        Else
            Results.Visible = True
        End If
        SqlConnObj.Close()

		'Dim zipCode As String = Request.QueryString("zip")
		'zip.Text = zipCode
		
    End Sub

    Function getCoordinates() As ArrayList
        Dim url As String
        Dim latitude As Decimal
        Dim longitude As Decimal
        Dim coordinates As ArrayList = New ArrayList
        Dim zip As String = Request.QueryString("zip")

        url = "http://api.local.yahoo.com/MapsService/V1/geocode?appid=OQ7rOPTV34EfC.pCU8D7BRHWYIIPX1avnYCBd1gjzD11V4QA6Ie4XXCr3eiGC_znbkMrjQ--&zip=" & zip

        Dim xmlReader As XmlTextReader = New XmlTextReader(url)

        While xmlReader.Read()
            If xmlReader.Name.ToString() = "Latitude" Then
                latitude = xmlReader.ReadString().ToString()
            End If

            If xmlReader.Name.ToString() = "Longitude" Then
                longitude = xmlReader.ReadString().ToString()
            End If
        End While

        coordinates.Add(latitude)
        coordinates.Add(longitude)

        Return coordinates
    End Function

    Function calculateDistance(ByVal latitude As Decimal, ByVal longitude As Decimal) As ArrayList
        Dim miles As Integer = Request.QueryString("miles")
        Dim degrees As Decimal = 69.172
        Dim PI As Decimal = 3.14159265358979

        Dim highLat As Decimal = latitude + (miles / degrees)
        Dim lowLat As Decimal = latitude - (highLat - latitude)
        Dim highLong As Decimal = longitude + miles / (System.Math.Cos(lowLat * PI / 180) * degrees)
        Dim lowLong As Decimal = longitude - (highLong - longitude)

        Dim distanceArray As ArrayList = New ArrayList
        distanceArray.Add(lowLat)
        distanceArray.Add(highLat)
        distanceArray.Add(lowLong)
        distanceArray.Add(highLong)

        Return distanceArray
    End Function

    Protected Sub search_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles search.Click
        Response.Redirect("dealersearch.aspx?zip=" & zip.Text & "&miles=" & miles.SelectedValue)
    End Sub
End Class

Once again, the search returns results correctly if I search by 25 miles or more, but if I search by 10 miles, I do not get all results, even though they are in the same zip code.

 

[rickbull]

Great Code Example! I never thought of using Yahoo Maps

This looks like the perfect code example that will help me finish a project. I have a coupld of questions if you dont mind assisting me.
In your code, you reference "certifiedDealers". Is that a database or dataset? If so, could you provide the structure please? Also, you reference "certifiedResults".
Is that a gridview?

Thank you
Rick Bull