Zip code radius query

[Thorlabs]

I found this query in another post and since it appeared to be exactly what i
needed, i copied it. thank you to the author.

My problem is that it fails and gets an Undefined Function 'ACos' message.

Can anyone help me troubleshoot this? it's been 2 hours and i'm getting jumpy.

SELECT zipcode
FROM zipcodes
WHERE ROUND((Acos((SIN(#get_lat_long.latitude#/57.2958) *
SIN(latitude/57.2958)) +
(COS(#get_lat_long.latitude#/57.2958) * COS(latitude/57.2958) *
COS(longitude/57.2958 - #get_lat_long.longitude#/57.2958)))) * 3963, 3) <= 20
ORDER BY zipcode

[jdeline]

I may be missing something, but your WHERE clause does not conform to expected
syntax: WHERE fieldname op value. Where's your field name?

Otherwise, since ColdFusion supports the arc cosine, use it to evaluate the
function, as shown below.


<CFSET foo = ROUND((Acos((SIN(#get_lat_long.latitude#/57.2958) *
SIN(latitude/57.2958)) +
(COS(#get_lat_long.latitude#/57.2958) * COS(latitude/57.2958) *
COS(longitude/57.2958 - #get_lat_long.longitude#/57.2958)))) * 3963, 3)>

[Kronin555]

jdeline,

WHERE clauses don't need to follow the syntax you gave. All that's absolutely
necessary is that you have a condition that evaluates to a boolean. You don't
even need to use a fieldname if you don't want. This will work:
WHERE 1 = 1
and meets expected SQL syntax.

That said, Thorlabs' query is pulling in 2 fieldnames, latitude and longitude.

Fieldname:
ROUND((Acos((SIN(#get_lat_long.latitude#/57.2958) * SIN(latitude/57.2958)) +
(COS(#get_lat_long.latitude#/57.2958) * COS(latitude/57.2958) *
COS(longitude/57.2958 - #get_lat_long.longitude#/57.2958)))) * 3963, 3)

Op:
<=

Value:
20

Thorlabs, what database are you on? You're calling a database function called
Acos(), and it sounds like that doesn't exist in the database you're using.

[rmorgan]

If your up for changing your db around a some, here is the query I use that I
found out on the net somewhere, works perfectly for me

SELECT b.zipcode, b.state,
(3956 * (2 * ASIN(SQRT(POWER(SIN(((a.latitude-b.latit?ude)*0.017453293)/2),2)
+ COS(a.latitude*0.017453293) *
COS(b.latitude*0.017453293) *
POWER(SIN(((a.longitude-b.long?itude)*0.017453293)/2),2))))) AS distance
FROM zipcodes a, zipcodes b, zipcodes c
WHERE
a.zipcode = '02134' AND # <-- Your starting zipcode
a.zipcode = c.zipcode AND (3956 * (2 *
ASIN(SQRT(POWER(SIN(((a.latitude-b.latit?ude)*0.017453293)/2),2) +
COS(a.latitude*0.017453293) * COS(b.latitude*0.017453293) *
POWER(SIN(((a.longitude-b.long?itude)*0.017453293)/2),2)
)))) <= 5 # <-- Your target radius
GROUP BY distance

[Thorlabs]

That's for everyones help. Originally it was running on MS access. I trasnfered it to an SQL database and it now works fine.

thanks to all for your help! great support as always.