PDA

Click to See Complete Forum and Search --> : Zipcode Radius database

tagriffith
05-09-2005, 11:00 PM
I work for an environmental agentcy. I am currently setting up a webpage where the residents from the county can enter their zip code and they would then be able to see all the recycling drop-off locations within their area. I have a webpage currently setup where they can enter the zip code and it returns all the zipcodes in the county with distance from the zipcode entered. I am using a MS ACCESS database. The database consist of two tables. The "zipcode" table with the zipcodes, city, state,longitude, and latitude. The "Dropoffs" table includes Name of location, Address, zipcodes. The problem that I am having, is trying to get the results from the "zipcode" table for the longitude and latitude radius, but then SEND the results from "Drop-offs" table. My results are only coming from the "zipcode" table. I have been trying to setup a join to join relation between the tables with no luck. You can use the link below to see the current page. You can use the zipcode 45219 as a test.

Recycle Finder test page (http://www.hcdoes.org/sw(new)/recyclefinder/asp_access/zipcoderadiusaccesstest.asp)

code:
<%@ Language=VBScript %>

<%

dim Databasestring : Databasestring="Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.Mappath("\_private\sw\recyclefinder.mdb")
dim Conn : set Conn = server.CreateObject("ADODB.Connection")
Conn.open Databasestring

dim passedzipcode : passedzipcode = request.form("passedzipcode")
dim passedradius : passedradius = request("passedradius")
dim passedround : passedround = request("passedround")
dim rowonecolor : rowonecolor = request("rowonecolor")
dim rowtwocolor : rowtwocolor = request("rowtwocolor")
dim rowthreecolor : rowthreecolor = request("rowthreecolor")
dim dsname : dsname = request("dsname")

'if passedzipcode<>"" then
passedzipcode1 = passedzipcode
passedzipcode2 = isNumeric(passedzipcode)
if passedzipcode2=False or passedzipcode1="" then
errormessage = "Please pass a valid Zip Code"
end if
'end if

'if passedradius<>"" then
passedradius1 = passedradius
passedradius2 = isNumeric(passedradius)
if passedradius2=False or passedradius1="" then
errormessage = "Please pass a valid mile range"
end if
'end if

if errormessage<>"" then
Response.Redirect "zipcoderadiusaccesstest.asp?errmss="&errormessage
end if
%>
<HTML>
<Head>
<Title>Recycle Finder</Title>
</Head>
<Body>
<table cellspacing=0 cellpadding=0 border=0 width="100%" height="100%">
<tr height="10">
<td height="42" valign=top>
<a href="zipcoderadiusaccesstest.asp"><font color="<%=rowthreecolor%>">New Search</font></a></td>
</tr>
<tr height="15"><td></td></tr>
<tr height="30">
<td valign=top>
<h2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Data as a table</h2>
</td>
</tr>
<tr>
<td valign=top align=top><p style="margin-left:30">
<table cellspacing=0 cellpadding=0 border=0 width="50%">
<%
dim rsZip :set rsZip = conn.execute("SELECT * FROM zipcodes WHERE ZipCode="&passedzipcode)

'dim rsgetlocs :set rsgetlocs = conn.execute("SELECT * FROM zipcodes a, Dropoffs b WHERE a.Latitude <= (18.49352 + (5 / ((6076 / 5280) * 60))) AND a.zipcode = b.zipcode AND a.Latitude >= (18.49352 - (5 / ((6076 / 5280) * 60))) AND a.Longitude >= (67.135883 - (5 / (((cos(cdbl(18.49352 * 3.141592653589 / 180)) * 6076) / 5280) * 60))) AND a.Longitude <= (67.135883 + (5 / (((cos(cdbl(18.49352 * 3.141592653589 / 180)) * 6076) / 5280) * 60)))")
if not rsZip.EOF then
%>
<tr>
<td valign=top align=center width="30%" bgcolor="<%=rowthreecolor%>">
<font color="<%=rowtwocolor%>">City</font>
</td>
<td valign=top align=center width="30%" bgcolor="<%=rowthreecolor%>">
<font color="<%=rowtwocolor%>">State</font>
</td>
<td valign=top align=center width="20%" bgcolor="<%=rowthreecolor%>">
<font color="<%=rowtwocolor%>">ZipCode</font>
</td>
<td valign=top align=center width="20%" bgcolor="<%=rowthreecolor%>">
<font color="<%=rowtwocolor%>">Distance</font>
</td>
</tr>
<%
'dim rsgetlocs :set rsgetlocs = conn.execute("SELECT * FROM zipcodes a, Dropoffs b WHERE a.Latitude <= ("&rsZip("latitude")&" + ("&passedradius&" / ((6076 / 5280) * 60))) AND a.zipcode = b.zipcode AND a.Latitude >= ("&rsZip("latitude")&" - ("&passedradius&" / ((6076 / 5280) * 60))) AND a.Longitude >= ("&rsZip("longitude")&" - ("&passedradius&" / (((cos(cdbl("&rsZip("latitude")&" * 3.141592653589 / 180)) * 6076) / 5280) * 60))) AND a.Longitude <= ("&rsZip("longitude")&" + ("&passedradius&" / (((cos(cdbl("&rsZip("latitude")&" * 3.141592653589 / 180)) * 6076) / 5280) * 60)))")

dim rsgetlocs :set rsgetlocs = conn.execute("SELECT distinct ((69.1*(Latitude - " & rsZip("latitude")& "))*(69.1*(Latitude - " & rsZip("latitude") & "))+(69.1*( Longitude - " & rsZip("longitude") & ")*cos( " & rsZip("latitude") & "/57.3)*(69.1*(Longitude - " & rsZip("longitude") & ")*cos( " & rsZip("latitude") & "/57.3)))) as disc,zipcodes.* FROM zipcodes WHERE Latitude <= ("&rsZip("latitude")&" + ("&passedradius&" / ((6076 / 5280) * 60))) AND Latitude >= ("&rsZip("latitude")&" - ("&passedradius&" / ((6076 / 5280) * 60))) AND Longitude >= ("&rsZip("longitude")&" - ("&passedradius&" / (((cos(cdbl("&rsZip("latitude")&" * 3.141592653589 / 180)) * 6076) / 5280) * 60))) AND Longitude <= ("&rsZip("longitude")&" + ("&passedradius&" / (((cos(cdbl("&rsZip("latitude")&" * 3.141592653589 / 180)) * 6076) / 5280) * 60)))")
count = 1
while not rsgetlocs.EOF
if(count Mod 2 = 0) then
strBg = rowtwocolor
else
strBg = rowonecolor
end if
%>
<tr>
<td>
<%

'Dim sSql
'sSql = "SELECT * FROM zipcodes a, Dropoff b WHERE a.Latitude <= ("&rsZip("latitude")&" + ("&passedradius&" / ((6076 / 5280) * 60))) AND a.zipcode = b.zipcode AND a.Latitude >= ("&rsZip("latitude")&" - ("&passedradius&" / ((6076 / 5280) * 60))) AND a.Longitude >= ("&rsZip("longitude")&" - ("&passedradius&" / (((cos(cdbl("&rsZip("latitude")&" * 3.141592653589 / 180)) * 6076) / 5280) * 60))) AND a.Longitude <= ("&rsZip("longitude")&" + ("&passedradius&" / (((cos(cdbl("&rsZip("latitude")&" * 3.141592653589 / 180)) * 6076) / 5280) * 60)))"
'response.write (sSql)
'response.end
'Response.WRite "SELECT * FROM zipcodes a, Dropoff b WHERE a.Latitude <= ("&rsZip("latitude")&" + ("&passedradius&" / ((6076 / 5280) * 60))) AND a.zipcode = b.zipcode AND a.Latitude >= ("&rsZip("latitude")&" - ("&passedradius&" / ((6076 / 5280) * 60))) AND a.Longitude >= ("&rsZip("longitude")&" - ("&passedradius&" / (((cos(cdbl("&rsZip("latitude")&" * 3.141592653589 / 180)) * 6076) / 5280) * 60))) AND a.Longitude <= ("&rsZip("longitude")&" + ("&passedradius&" / (((cos(cdbl("&rsZip("latitude")&" * 3.141592653589 / 180)) * 6076) / 5280) * 60)))"
%>
</td>
</tr>
<tr>

<td valign=top align=center bgcolor="<%=strBg%>">
<%=rsgetlocs("City")%>
</td>
<td valign=top align=center bgcolor="<%=strBg%>">
<%=rsgetlocs("StateName")%>
</td>
<td valign=top align=center bgcolor="<%=strBg%>">
<%=rsgetlocs("ZipCode")%>
</td>
<td valign=top align=center bgcolor="<%=strBg%>">
<%if rsZip("zipcode") = rsgetlocs("zipcode") then
Distance = 0.00
else
' Distance = SQR((69.1*(rsgetlocs("latitude")&"-"&rsZip("latitude")&"))*(69.1*("&rsgetlocs("latitude")&"-"&rsZip("latitude")&"))+(69.1*("&rsgetlocs("longitude")&"-"&rsZip("longitude")&")*COS("&rsZip("latitude")&"/57.3)*(69.1*("&rsgetlocs("longitude")&"-"&rsZip("longitude")&")*COS("&rsZip("latitude")&"/57.3))))
Distance = SQR((69.1*(rsgetlocs("latitude") - rsZip("latitude")))*(69.1*(rsgetlocs("latitude") - rsZip("latitude")))+(69.1*(rsgetlocs("longitude") - rsZip("longitude"))*COS(rsZip("latitude")/57.3)*(69.1*(rsgetlocs("longitude") - rsZip("longitude"))*COS(rsZip("latitude")/57.3))))
end if
if passedround = "NoRound" then
distance = (Round(distance,6))
else
distance = (Round(distance,0.00))
end if
Response.WRite distance
%>
<td valign=top align=center bgcolor="<%=strBg%>">
</td>
</tr>
<%
rsgetlocs.movenext
count = count+1
wend
else%>
<tr>
<td>
No records found for passed search <%=passedzipcode%>
</td>
</tr>
<%end if

%>
</table>
</td>
</tr>
</table>
</Body>
</HTML>
wmif
05-10-2005, 03:58 PM
try naming the columns instead of using *.
tagriffith
05-10-2005, 04:08 PM
Would I then have to seperate the SELECT statement up so that I can state what fields I want from zipcode (zipcodes,longitude,and Latitude) and then the fields from Dropoffs (City,State,locationAddress,DropoffItems,MoreInfo)? How would I go about writing that?
Thanks for the help.
wmif
05-10-2005, 05:01 PM
callout the columns using the same syntax you are using in the where clause.
Bullschmidt
05-10-2005, 05:47 PM
The problem that I am having, is trying to get the results from the "zipcode" table for the longitude and latitude radius, but then SEND the results from "Drop-offs" table.
Here's a little something I put together about subqueries.

Example of one query (QueryB) based on the results of another query (QueryA):

QueryA = "SELECT CustID FROM tblCUSTOMERS WHERE CustName = 'A%'"

QueryB = "SELECT CustID, CustName FROM tblCUSTOMERS WHERE CustID IN (" & QueryA & ")"

But the following is even faster and allows for more than one field to be returned in QueryA:

QueryB = "SELECT tblCUSTOMERS.CustID, CustName FROM (" & strSQLA & ") AS tblSQLA INNER JOIN tblCUSTOMERS ON tblSQLA.CustID = tblCUSTOMERS.CustID"

So QueryA would include all the CustID's for customers starting with A.

And QueryB would include more fields in the customers table (i.e. not just the CustID field) for the records returned in QueryA (which was the customers starting with A).

I suppose it wouldn't hurt to always use LEFT JOIN's in QueryB and build from the tblSQLA on the left to other tables that have fields you want to return.
Bullschmidt
05-10-2005, 05:48 PM
And here are some related zip code distance links:

http://sqljunkies.com/WebLog/ktegels/archive/2003/12/22/652.aspx
Some thoughts on "Distance between 2 zip codes?"

http://www.webdeveloper.com/forum/showthread.php?s=&threadid=38755
tagriffith
05-11-2005, 11:11 AM
Thanks everyone for all your help. Taking what everyone stated I was able to come up with a solution.