Calculatin distance between two zip codes

[zxcdf5je]

Hi friends,

I need some help in doing the following:
The supervisor has the zip code of the customer displayed on the form.We have the database of about 10,000 people ,with their addresses and zipcodes.When the supervisor hits submit,i should get a list of all the people who are more near to the clients place.May be we can display the list in asending order.
I have the function how to calculate distances between two zipcodes.What i dont know is how should i integrate this fucntion and how should i pass the zip codes (of client ) and tell the code to check it agains the database of 10,000 people ?
Here is the fucntion which i got it online:
<%
const pi = 3.14159265358979323846
function GetDistance(Lat1, Long1, Lat2, Long2, Unit)
dim x
' do the nasty calcs
x = (sin(DegToRads(Lat1)) * sin(DegToRads(Lat2)) + cos(DegToRads(Lat1)) * _
cos(DegToRads(Lat2)) * cos(abs((DegToRads(long2))-(DegToRads(long1)))))
' Get Acos(x)
x = atn((sqr(1-x^2))/x)
' Get distance in kilometers
GetDistance = 1.852 * 60.0 * ((x/pi)*180)
' Convert units if necessary
select case ucase(Unit)
case "M"
GetDistance = GetDistance / 1.609344
case "N"
GetDistance = GetDistance / 1.852
end select
end function
'::::::::::::::::::::::::::::::::::::::::::::::::: ::::::::::::::::::::::::::::::
'::: This function converts decimal degrees (e.g. 79.1928376) to radians :::
'::::::::::::::::::::::::::::::::::::::::::::::::: ::::::::::::::::::::::::::::::
function DegToRads(Deg)
DegToRads = cdbl(Deg * pi / 180)
end function
'::::::::::::::::::::::::::::::::::::::::::::::::: ::::::::::::::::::::::::::::::
'::: This function converts decimal degrees (e.g. 79.1928376) to degrees, :::
'::: minutes and seconds :::
'::::::::::::::::::::::::::::::::::::::::::::::::: ::::::::::::::::::::::::::::::
sub DecimalDegToDMS(DecimalDegrees, Degs, Mins, Secs)
Dim temp
Degs = fix(DecimalDegrees)
temp = (DecimalDegrees - Degs) * 60.
Mins = fix(temp)
temp = (temp - Mins) * 60.
Secs = fix(temp)
end sub
%>

Please help!!!!

 

[gmmastros]

What database are you using? If it's Microsoft SQL Server, you can do the calculations in the database. Performance will be better.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[zxcdf5je]

unfortunately it is MS Access.Any more suggestions?

 

[gmmastros]

Do you have a table of zipcode with latitude and longitude. The calculations are based on Latitude and Longitude, not zipcode.

If you do a google search, you should be able to find a database that contains the data you are looking for.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[zxcdf5je]

What should i do after i get this data?How should i integrate this in my code and tell the code to calculate it according to the clients zip code and display all the peole who are near to that zip code?
I guess i have that table of latitude and longitude information.
The person who had done this before on a system in my company has done it in foxpro so i have the DBF files and tables he used for doing this.

 

[gmmastros]

Import that data from FoxPro to Access. Then, when you want to get the nearest zip code...

1. Get the lat/long of the zip code to search on.
2. Get the complete list of zipcode/lat/long
3. In a loop, perform the calculation.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[zxcdf5je]

yes i do have a dB of zipcode,lat and longitudes for all the zip codes.
Now that i have this..is this the way i should go on doing it?
We pass Lat1,Lat2,long1,long2 to the getdistance function.So i should basically catch the zip code of the client and give a query like this:
Select Lat1,Long1 where zipcode = "& Request.form("Zipcode")&"
** Now my question is where should i store this lat1 and long1.
** Secondly how should i compare this lat1 and long1 with all other lat and long in my DB and pass them to this function.
I am stil not clear how i should do this.If you can clarify this again,that would be great!!!

 

[gmmastros]

Set RS = db.execute("Select Lat, Long From Table Where ZipCode = " & Request.Form("ZipCode"))

This will return a recordset (presumably with 1 record).

Then, to store the data...

Latitude = RS("Lat")
Longitude = RS("Long")

Then, create another recordset and get all the Latitude and longitudes for all zipcode. Loop through the recordset and perform the calculations.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[zxcdf5je]

Hi Friend,
This is what i have.I just want it to display the distances for the zip code i have given in my code.But i am getting an error:
Data type mismatch in criteria expression on this line:
rsTemp.open mysql, connTemp
This is my code.Dont know where i am going wrong.
<%
mysql ="select * from zipcode where ZIP=44134 "
call getfromdatabase(mysql, rsTemp)
If not rsTemp.eof then
write results
End IF
call closeDb()
'here is the subs, this speeds things up and makes code shorter:
Dim connTemp
sub openDb()
set connTemp = server.createObject("adodb.connection")
StrConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("db1.mdb")
connTemp.Open StrConn
end sub

sub getFromDatabase(mysql, rsTemp)

call openDb()
Dim lat1,long1
set rsTemp = server.createObject("adodb.recordset")
' set the cursor
rsTemp.cursorType = adOpenForwardOnly
' set locktype
rsTemp.lockType = 1
rsTemp.open mysql, connTemp
lat1 = rsTemp("lat")
long1 = rsTemp("long")


end sub

sub updateDatabase(mysql, rsTemp)
on error resume next
call openDb()

set rsTemp=connTemp.execute(mysql)
end sub


function closeDB()
on error resume next
rsTemp.close
set rsTemp = nothing
connTemp.close
set connTemp = nothing
end function

'for your work i will assume it is field long and lat

mysql = "SELECT * FROM zipcode"
call getFromDatabase(mysql, rsTemp)
do while not rsTemp.eof
pDistance = GetDistance(Lat1, Long1, rsTemp("lat"), rsTemp("long"), M)
If pDistance < 10 Then
response.Write pDistance
Else
'do nothing
End IF
rsTemp.MoveNext
Loop
call closeDb()
%>
Please do let me know if you think i am going wrong anywhere

 

[Chopstik]

Ok, I think I see a couple of things wrong, but it's late and I'm tired, so take it for what it's worth. My notes are in red below.

<%
mysql ="select * from zipcode where ZIP=44134 "
call getfromdatabase(mysql, rsTemp) [COLOR=red]'You don't need to pasa rsTemp because you haven't created it except in the sub that you're calling.  You should only pass mysql variable to your sub.[/color]
If not rsTemp.eof then [COLOR=red]'This only exists in your sub, not here, so would take this sub out.  If not, expect it to fail here should it ever reach this point. [/color]
write results
End IF
call closeDb()
'here is the subs, this speeds things up and makes code shorter:
Dim connTemp
sub openDb() [COLOR=red]'Do not make this a sub, or put this code anywhere you are calling the connTemp variable.  It is *not* available outside of this sub.  You can keep the code and just get rid of the sub and end sub lines.  This is probably where your error is happening because it doesn't recognise the variable in your other sub. [/color]
set connTemp    = server.createObject("adodb.connection")      
StrConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ 
Server.MapPath("db1.mdb")
connTemp.Open StrConn
end sub

sub getFromDatabase(mysql, rsTemp) 
 
   call openDb()
   Dim lat1,long1
   set rsTemp = server.createObject("adodb.recordset")
   ' set the cursor
   rsTemp.cursorType = adOpenForwardOnly 
   ' set locktype
   rsTemp.lockType = 1
   rsTemp.open mysql, connTemp 
  lat1 = rsTemp("lat")
  long1 = rsTemp("long")


end sub

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill