access
I am using MS Access. I already have the complete zip code, long, lat database. I need to know how to setup this near search query in access using a table of customer zip codes and the table of all zip codes. Thankyou.
Posted by: Pat
Posted on: 05/13/2004 03:55 PM
|
access
The query above will not work in access. The acos sign is not a valid function in access.
Once you figure out the query for access, you would do a simple join between the your database and the database of zipcodes.
Ex:
select * from zipcodetable a, yourclients b
where a.zipcode = b.zipcode
Make sure that the data type of the zipcode is the same in both tables.
I could not figure out the query for access so I switched databases to MySQL. It looks like cfmsource.com has a sample for sale in access.
Posted by: cgs
Posted on: 05/13/2004 06:35 PM
|
access
I still can't figure out the query in access. I'm trying to use the code you had in your website code, and access doesn't like it. How do I do #passedzip.latitude and #passedzip.longitude with a parameter query? Can I assign a variable?? What do I do.
Here is my code:
SELECT *
ROUND((ACOSH((SIN(#passedzip.latitude#/57.2958) * SIN(latitude/57.2958)) +
(COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) *
COS(longitude/57.2958 - #passedzip.longitude#/57.2958))))
* 3963) AS distance
FROM tblAll_Zips_Lat_Long, tblCustomer2
WHERE [ZipCode]=#passedzipcode#
(latitude >= #passedzip.latitude# - (#passedradius#/111))
And (latitude <= #passedzip.latitude# + (#passedradius#/111))
AND (longitude >= #passedzip.longitude# - (#passedradius#/111))
AND (longitude <= #passedzip.longitude# + (#passedradius#/111))
ORDER BY distance;
my field names are ZipCode, Latitude, Longitude
Thanks again!
Posted by: Pat
Posted on: 05/14/2004 10:01 AM
|
sql query error
I got this error running the query on MSSQL:
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near 'ACOS'.
SQL = "SELECT zipcode, state, city, latitude, longitude ROUND((ACOS((SIN(40.624499999999998/57.2958) * SIN(latitude/57.2958)) + (COS(40.624499999999998/57.2958) * COS(latitude/57.2958) * COS(longitude/57.2958 - -111.8245/57.2958)))) * 3963, 3) AS distance FROM zipcodes WHERE (latitude >= 40.624499999999998 - (50/111)) And (latitude <= 40.624499999999998 + (50/111)) AND (longitude >= -111.8245 - (50/111)) AND (longitude <= -111.8245 + (50/111)) ORDER BY distance"
Data Source = "RCU"
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (53:3) to (53:45) in the template file D:\rcudev\market\radius.cfm.
Wondering what happened?
Michael
Posted by: Michael
Posted on: 08/02/2004 11:11 AM
|
figured it out
missing comma.
Posted by: Michael
Posted on: 08/02/2004 07:12 PM
|
111?
Where does the constant static value of 111 in your where clause come from?
In my own usage it seems that your where clause is excluding some records which should be returned. For example, if I input a radius of 3000 and a zip of 21122 (Maryland) I would expect to have the zip 90210 (California) to be among the returned records. It is not. In fact, I have to raise the radius to at least 5000 before the 90210 code is returned. When the record is returned, the distance returned is only 2330. Therefore, this record should have been returned given the 3000 radius.
Any ideas?
Thanks,
AndyEye
Posted by: Andrew Eye
Posted on: 10/06/2004 11:53 AM
|
111?
111 is used because that is the number of kilometers in a degree of latitude.
Posted by: kaigler
Posted on: 10/06/2004 12:37 PM
|
111?
The select clause is returning a distance in terms of miles is it not? Shouldn't the where clause and the select cause be using the same units?
Posted by: Andrew Eye
Posted on: 10/06/2004 05:35 PM
|
111?
Also, 111 works as a static divisor for degrees of latitude, however the kilometers to degrees longitude relationship is not static. Degrees of longitude are closer together at the poles. Therefore, shouldn't the divisor for the longitude parameters be a variable value?
Thanks!
AndyEye
Posted by: Andrew Eye
Posted on: 10/06/2004 05:39 PM
|
111 is wrong if you're using miles
I searched Google and found out that there are 46 miles per degree for latitude, and 69 miles per degree of longitude.
Now my search works correctly!
This is my new where clause:
WHERE (zipcode_latitude >= #qLookup.zipcode_latitude# - (#radius#/46))
And (zipcode_latitude <= #qLookup.zipcode_latitude# + (#radius#/46))
AND (zipcode_longitude >= #qLookup.zipcode_longitude# - (#radius#/69))
AND (zipcode_longitude <= #qLookup.zipcode_longitude# + (#radius#/69))
Posted by: Jason Norris
Posted on: 11/16/2004 01:51 PM
|
oops
I spoke too soon. Forget 46, use 69 for both latitude and longitude.
Posted by: Jason Norris
Posted on: 11/16/2004 01:55 PM
|
FREE zip code database
I just found a free zip code database from the census bureau.... It's from 1999 - but hey, it's free! Here's the URL - http://www.census.gov/geo/www/tiger/zip1999.html
Posted by: Jansen Rensma
Posted on: 11/26/2004 02:21 PM
|
69 for miles
If I adjust the formula for 69 instead of the 111, it helps but I still can't search for miles less than 69. Am I missing something?
Posted by: Lance
Posted on: 12/13/2004 08:54 PM
|
new sql
All,
Since I wrote this long ago I have changed the sql a little. try this:
SELECT zipcode, latitude, longitude, statename, city,
3963 * (ACOS((SIN(#passedzip.latitude#/57.2958) * SIN(latitude/57.2958)) +
(COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) *
COS(longitude/57.2958 - #passedzip.longitude#/57.2958)))) AS distance
FROM zipcodes
WHERE 3963 * (ACOS((SIN(#passedzip.latitude#/57.2958) * SIN(latitude/57.2958)) +
(COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) *
COS(longitude/57.2958 - #passedzip.longitude#/57.2958)))) <= #passedradius#
ORDER BY distance
Several have asked and here is a breakdown of some of the numbers.....
To convert latitude or longitude from decimal degrees to radians, divide the latitude and longitude values in this database by 180/pi, or approximately 57.29577951. The radius of the Earth is assumed to be 6,378.8 kilometers, or 3,963.0 miles.
I have a working example of this code at http://www.cfmsource.com/zipcoderadiustest.cfm
Another question often asked is that the information in one example does not match someone else's web site. The problem you will find is not the sql but the data that one site uses versus another site. Over several years data ages and zipcodes latitude and longitudes change as areas get larger populations so there for results from sites to sites range.
Good Luck,
kaigler
Posted by: kaigler
Posted on: 12/13/2004 09:55 PM
|
Another Option
The following is not quite as accurate (I can't really say how much less accurate) but simplified and probably close enough since we are talking about distance between two zip codes and not the distance between the house and the mailbox. Another advantage is that it eliminates the trig functions so it probably runs faster.
I know all those parens are not necessary but I put them there for clarity. It works even with negative longitude numbers in the western hemisphere and negative latitude numbers in the southern hemisphere.
69.1 is the number of miles in one degree of latitude and 1 degree of longitude at the equator. One degree of longitude at the north or south pole is zero miles.
Hope someone can get some use from it.
SELECT zipcode, latitude, longitude, statename, city,
(69.1)*(SQRT(((#passedzip.latitude#-latitude)^2)+(0.6*((#passedzip.longitude#-longitude)^2)))) AS distance
FROM zipcodes
WHERE (69.1)*(SQRT(((#passedzip.latitude#-latitude)^2)+(0.6*((#passedzip.longitude#-longitude)^2)))) <= #passedradius#
ORDER BY distance
Posted by: Chuck D
Posted on: 12/14/2004 10:27 PM
|
RETURNS NULL
My query works great and works 99% of the time. However, for one of my distance calculation, NULL is returned. This only happens in one instance when the zip code is the zip code that I am matching. In most cases, when the zip is the same, it returns 0 (as expected). Any thoughts
Posted by: scott
Posted on: 03/08/2005 02:27 AM
|
ZIP, LATITUDE, LONGITUDE
Null question from above is the following zip...
ZIP = 02053
LAT = 42.145235
LONG = -71.407864
Posted by: scott
Posted on: 03/08/2005 02:41 AM
|
Returns NULL in Zip, Latitude, Longitude calculation
There are a couple of versions of the query listed here and I'm not sure which one you are using or referring to.
If you are referring to the one I left on 12/14/04 (the less accurate but simplified one), the only reasons I can think of initially that would return a NULL set would be either:
1. the value of the #passed.radius# is a negative value.
2. the value of the #passed.radius# is zero and the values of the #passedzip.latitude# and/or the value of the #passedzip.longitude# are very close to the values in the table but not exact. That could even include the number of decimal being different between the two.
The second scenario would result not finding any records that were exactly zero for distance when the #passed.radius# was zero, a NULL set would be returned.
Posted by: Chuck D
Posted on: 03/18/2005 10:38 AM
|
canadian postal code
Will it work for Canadian Postal Code also?
Posted by: Alosious
Posted on: 01/17/2007 09:54 AM
|
Will work with Canadian Postal Codes
The same SQL code will work with Canadian Postal Codes. The database table being accessed contains rows with columns containing:
Postal Code
Latitude
Longitude
State/Province (optional)
City (optional)
Both US Zip codes and Canadian Postal codes can be stored in the same database table. The results returned can be in miles or kilometers. The modification necessary for kilometers would be to change the two places in the SQL where you see 69.1 to 111. (could be done with a variable). If the #passedradius# is in miles, 69.1 should be used in the SQL and the returned distance will be in miles. If the #passedradius# is in kilometers, 111 should be used in the SQL and the returned distance will be in kilometers.
Posted by: Chuck D
Posted on: 04/12/2007 12:35 PM
|
Query String for MS-Access
SELECT zipcode, latitude, longitude, statename, city,ROUND((atn(-(SIN(#passedzip.latitude#/57.2958)* SIN(latitude/57.2958)) + (COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) *COS(longitude/57.2958 - passedzip.longitude#/57.2958))/sqr(-(SIN(#passedzip.latitude#/57.2958)* SIN(latitude/57.2958)) + (COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) *COS(longitude/57.2958 - passedzip.longitude#/57.2958))) * (SIN(#passedzip.latitude#/57.2958)* SIN(latitude/57.2958)) + (COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) *COS(longitude/57.2958 - passedzip.longitude#/57.2958)) + 1)) + 2 * atn(1))* 3963) AS distance FROM zipcodes WHERE (latitude >= #passedzip.latitude# - (#passedradius#/111)) And (latitude <= #passedzip.latitude# + (#passedradius#/111)) AND (longitude >= #passedzip.longitude# - (#passedradius#/111)) AND (longitude <= #passedzip.longitude# + (#passedradius#/111)) ORDER BY distance
Posted by: Satya
Posted on: 09/11/2007 09:21 AM
|
http://www.zipcodesmapped.com/
I would recommend http://www.zipcodesmapped.com/ for any US zip code map.
You can use this to find any US zip code maps. You can search by (City, State OR Zip). they have all the maps showing zip codes and major mile streets in all of the united states. The service is free. Search for five Digit Zip Code and find the Maps for all 50 states. A Free ZIP Code Finder which can do wonders for you.
Posted by: zip code map
Posted on: 01/09/2008 03:25 AM
|
ColdFusion Hosting
Blink
Del.icio.us
Digg
Furl
Google
Simpy
Spurl
Y! MyWeb