SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Zip Code Proximity Search Function

    I wrote on function for dealing with zip code searches. Basically, I want to enter a zip code and a proximity and it returns a string of zip codes which can then be used in the IN clause of another query to find records with matching zip codes.

    I have a database of all of the zip codes with the latitude and longitude coordinates for each. The function works, but there are a couple of improvements I'd like to make.

    1) Replace the Select statement and nested if statements with some sort of trig formula so I can just give it any number of miles rather than 1 of 3 settings.

    2) I'd like to be able to calculate distance between the original zip code and the ones returned like I see on MapQuest.

    Code:
    <script language="vb" runat="server">
     'class for zip code related searches
     Public Class ZipCode
      'enumeration for proximity
      Public Enum ZipProximity
       M10 = 1 '10 miles
       M25 = 2 '25 miles
       M50 = 3 '50 miles
      End Enum
      
      'returns zip codes within proximity range
      Public Shared Function GetNearByZips(zipcode as String,Optional zipprox as ZipProximity = ZipProximity.M10) as String
       Dim sSQL1,sSQL2 as String
       Dim iStartLong,iStartLat,iLongVary,iLatVary as Decimal
       'look up lat and long on passed zip
       sSQL1 = "Select * From ZipSource Where ZipCode = '" & zipcode & "';"
       Dim oConn as New SQLConnection(Client.sAltConnStr)
       Try 
    	oConn.Open()  
    	Dim oComm as New SQLCommand(sSQL1, oConn)
    	Dim dr as SQLDataReader = oComm.ExecuteReader(CommandBehavior.CloseConnection)
    	If dr.Read() Then
    	 iStartLat = dr("Latitude")
    	 iStartLong = dr("Longitude")
    	Else
    	 'return error if no zip code was found
    	 Return "Sorry, no matching zip code was found."
    	End If
    	oConn.Close()
    	'determine longitude variance based on latitude - 10, 25, or 50 miles
    	Select Case zipprox
    	 Case ZipProximity.M10
    	  iLatVary = 0.15
    	  If iStartLong >= 20 Then
    	   iLongVary = 0.15
    	  ElseIf iStartLong < 20 And iStartLong > 30 Then
    	   iLongVary = 0.17
    	  ElseIf iStartLong < 30 And iStartLong > 40 Then
    	   iLongVary = 0.18
    	  ElseIf iStartLong < 40 And iStartLong > 50 Then
    	   iLongVary = 0.21
    	  ElseIf iStartLong < 50 And iStartLong > 60 Then
    	   iLongVary = 0.26
    	  ElseIf iStartLong < 60 And iStartLong > 70 Then
    	   iLongVary = 0.35
    	  ElseIf iStartLong < 70 Then
    	   iLongVary = 0.56
    	  End If
    	 Case ZipProximity.M25
    	  iLatVary = 0.37
    	  If iStartLong >= 20 Then
    	   iLongVary = 0.38
    	  ElseIf iStartLong < 20 And iStartLong > 30 Then
    	   iLongVary = 0.40
    	  ElseIf iStartLong < 30 And iStartLong > 40 Then
    	   iLongVary = 0.45
    	  ElseIf iStartLong < 40 And iStartLong > 50 Then
    	   iLongVary = 0.52
    	  ElseIf iStartLong < 50 And iStartLong > 60 Then
    	   iLongVary = 0.64
    	  ElseIf iStartLong < 60 And iStartLong > 70 Then
    	   iLongVary = 0.86
    	  ElseIf iStartLong < 70 Then
    	   iLongVary = 1.4
    	  End If
    	 Case ZipProximity.M50
    	  iLatVary = 0.73
    	  If iStartLong >= 20 Then
    	   iLongVary = 0.75
    	  ElseIf iStartLong < 20 And iStartLong > 30 Then
    	   iLongVary = 0.80
    	  ElseIf iStartLong < 30 And iStartLong > 40 Then
    	   iLongVary = 0.89
    	  ElseIf iStartLong < 40 And iStartLong > 50 Then
    	   iLongVary = 1.03
    	  ElseIf iStartLong < 50 And iStartLong > 60 Then
    	   iLongVary = 1.27
    	  ElseIf iStartLong < 60 And iStartLong > 70 Then
    	   iLongVary = 1.72
    	  ElseIf iStartLong < 70 Then
    	   iLongVary = 2.8
    	  End If
    	End Select
    	'query zip codes that fall with variance
    	sSQL2 += "Select ZipCode From ZipSource "
    	sSQL2 += "Where Longitude Between " & (iStartLong - (iLongVary/2)) & " And " & (iStartLong + (iLongVary/2)) & " "
    	sSQL2 += "And Latitude Between " & (iStartLat - (iLatVary/2)) & " And " & (iStartLat + (iLatVary/2)) & ";"
    	Dim oConn2 as New SQLConnection(Client.sAltConnStr)
    	oConn2.Open()  
    	Dim oComm2 as New SQLCommand(sSQL2, oConn2)
    	Dim dr2 as SQLDataReader = oComm2.ExecuteReader(CommandBehavior.CloseConnection)
    	Dim sZipcodeList as String
    	Do While dr2.Read()
    	 sZipcodeList += "'" & dr2("ZipCode") & "',"
    	Loop
    	Return sZipcodeList
    	Catch ex as Exception
    	 Return ex.Message
       End Try
      End Function
     End Class
    </script>
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  2. #2
    SitePoint Wizard silver trophy
    Join Date
    Sep 2002
    Location
    Cleveland, Ohio, USA
    Posts
    1,494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have something like this on CampusFish for you that's substantially less code. I can't get to the code from here, so bump this and I'll post it tonight, hopefully.

  3. #3
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I wrote this a long time ago(adapted from script found somewhere) and its in PHP, maybe you can adapt to your needs.

    Code:
    if (!$this->error) {
    $sql .= "select DISTINCT zipdata.zipcode, sqrt(power(69.1*(zipdata.latitude - $lat),2)+ power(69.1*(zipdata.longitude-$long)*cos(zipdata.latitude/57.3),2)) as dist, 
    locations.* from zipdata, locations WHERE " ; 
    $sql .= "LEFT(locations.zip, 5) = zipdata.zipcode AND sqrt(power(69.1*(zipdata.latitude - $lat),2)+ 
    power(69.1*(zipdata.longitude-$long)*cos(zipdata.latitude/57.3),2)) < " . $radius ." ORDER by city, name"; 
    $result2 = mysql_query($sql);
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  4. #4
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Freddy, thanks for the sample, but I wasn't able to a whole lot with it. I think it far exceeds my math skills
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  5. #5
    SitePoint Wizard silver trophy
    Join Date
    Sep 2002
    Location
    Cleveland, Ohio, USA
    Posts
    1,494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    double dblMiles = Convert.ToDouble(RadiusDropDown.SelectedItem.Value);
    // calculate ranges
    double dblLatRange = dblMiles/((6076/5280) * 60);
    double dblLongRange = dblMiles/(((Math.Cos(Convert.ToDouble(objZip.Latitude * Math.PI/180)) * 6076)/5280) * 60);
    //Trace.Warn("LatRange = " + dblLatRange.ToString() + ", LongRange = " + dblLongRange.ToString());
    SqlCommand objCommand = new SqlCommand();
    string sql = "SELECT TOP 250 People.Name, People.Loc, People.PeopleID, People.Photo, Status.StatusName FROM People INNER JOIN Status ON People.Web = Status.StatusID WHERE ";
    sql += " (People.Zip IN (SELECT Zip FROM ZipCode WHERE (Latitude BETWEEN @LatHigh AND @LatLow) AND (Longitude BETWEEN @LongHigh AND @LongLow)))";
    objCommand.Parameters.Add("@LatHigh",objZip.Latitude-dblLatRange);
    objCommand.Parameters.Add("@LatLow",objZip.Latitude+dblLatRange);
    objCommand.Parameters.Add("@LongHigh",objZip.Longitude-dblLongRange);
    objCommand.Parameters.Add("@LongLow",objZip.Longitude+dblLongRange);
    objCommand.Parameters.Add("@ExpirationDate",DateTime.UtcNow);
    objCommand.CommandText = sql;
    // do your reader or whatever
    OK, that's kind of a mess, and the fields I'm getting from the DB probably aren't that relevant, but I'm sure you get the idea. I have a ZipCode object (objZip in this case) that has the long/lat values in it, so you can replace those with whatever your variables are. Just make sure the ZipCode table is well indexed and this will be plenty fast.

  6. #6
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I missed this update.

    So, at the point this code runs, you've already done a look-up of the initial zip code to find it's latitude and longitude.

    You are then adding and subtracting the results of your calculation the original latitude/longitude to get your range.

    Thanks for the help!
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •