The SQL Query from hell

[NogginBoink]

So I've got a database of people with their ZIP codes, and another table that lists latitudes and longitudes for each ZIP code in the united states.

I want to find the 10 closest people to me in the database. I enter my ZIP code and resolve to lat/lon.

Target location is:
Latitude: 32.675604
Longitude: -97.023462

The equation for great circle distance is fairly straightforward:

D = acos[sin(La1) sin(La2) + cos(La1) cos(La2) cos(Lo1 - Lo2)]
(D = distance, La = latitude, Lo = longitude)

However, my SQL implementation (MS Access) doesn't have the acos() function, so it has to be derived from other trig functions. Oh yes, and let us not forget that computers use radians for trig functions instead of degrees.

The gawdawful result:


Select TOP 10 Name, Num, City, ZipCodes.State, ((Atn(-((sin(ZipCodes.Latitude*3.1415/180) * sin(0.570296874877829) + cos(ZipCodes.Latitude*3.1415/180) * cos(0.570296874877829) * cos(ZipCodes.Longitude*3.1415/180 - -1.6933788635836)))/ Sqr((-((sin(ZipCodes.Latitude*3.1415/180) * sin(0.570296874877829) + cos(ZipCodes.Latitude*3.1415/180) * cos(0.570296874877829) * cos(ZipCodes.Longitude*3.1415/180 - -1.6933788635836)))* ((sin(ZipCodes.Latitude*3.1415/180) * sin(0.570296874877829) + cos(ZipCodes.Latitude*3.1415/180) * cos(0.570296874877829) * cos(ZipCodes.Longitude*3.1415/180 - -1.6933788635836))))+1))+2*Atn(1))*57.29746) AS Distance2, Int(Int(Distance2) *60 + (Distance2 - Int(Distance2)) * 60) AS Distance from Builders, ZipCodes WHERE Builders.BuilderZip = ZipCodes.ZIP ORDER BY ((Atn(-((sin(ZipCodes.Latitude*3.1415/180) * sin(0.570296874877829) + cos(ZipCodes.Latitude*3.1415/180) * cos(0.570296874877829) * cos(ZipCodes.Longitude*3.1415/180 - -1.6933788635836)))/ Sqr((-((sin(ZipCodes.Latitude*3.1415/180) * sin(0.570296874877829) + cos(ZipCodes.Latitude*3.1415/180) * cos(0.570296874877829) * cos(ZipCodes.Longitude*3.1415/180 - -1.6933788635836)))* ((sin(ZipCodes.Latitude*3.1415/180) * sin(0.570296874877829) + cos(ZipCodes.Latitude*3.1415/180) * cos(0.570296874877829) * cos(ZipCodes.Longitude*3.1415/180 - -1.6933788635836))))+1))+2*Atn(1))*57.29746)

Ick!

But I'm so proud of myself.... it's actually working! 😀

 

[loup garou]

LMAO! You weren't kidding...that's UGLY.

It's accurate? Great job! 😀

 

[Hoober]

Jeese, that's terrible. I love SQL when it works, but I hate sitting down and figuring out how to write the friggin queries to get the information from the DB's.


<------ SQL junkie

 

[MercenaryForHire]

For greater accuracy, you may wish to use a more precise value of pi ...

3.141592653489793238462643383279502

... and my memory fades there. :Q 😀

- M4H

 

[Armitage]

A couple things...
Zip code table probably isn't that big. Why not add two more columns for the radian values to clean up your query a bit.

Does access have any inverse trig functions?

acos(x) = pi/2-asin(x) = pi/2 - atan(x/sqrt(1 - x^2))

 

[Descartes]

Gee, do you think you've duplicated your work enough? That violates at least two principles...

- DRY: don't repeat yourself
- OAOO: once and only once

Access has a few virtues (I'll hold my diatribal Access remarks for a different time), one of them being the hardcore integration of it's non-conforming and neutered (ok, the only diatribal remark) sql engine with vba. Why not simply add a module, and create a routine that performs the calculations? Also, put those numeric values behind a public constant in the module. A very simple example might be...

Public Const SOME_LONG_ASS_NUMBER = "0.570296874877829"

Public Function GetSin(ByVal n As Decimal) As Decimal
GetSin = sin(ToDegrees(n * 3.1415) * SOME_LONG_ASS_NUMBER)
End Function

Public Function GetCos(ByVal n As Decimal) As Decimal
GetCos = cos(ToDegrees(n * 3.1415) * SOME_LONG_ASS_NUMBER)
End Function

Private Function ToDegrees(ByVal n As Decimal) As Decimal
ToDegrees = n / 180
End Function

then in your query you'd simply have...

select GetSin(Zipcodes.Latitude) + GetCos(ZipCodes.Latitude), ...

I just typed that out now, so you may have to look it over. Hopefully you get the idea at least...

The alternative would be sql server 2k and a udf (user-defined function). This didn't exist pre-sql server 2k, so Access had an upperhand in that regard; you may as well take advantage of it.

 

[NogginBoink]

Descartes,

If I were working purely in Access, I totally agree with you.

However, I'm generating the query on an active server page, sending the query to Access, and processing the results on the web page. So I don't think I can use an Access code module to do that. (Maybe I'm wrong?)

I know that the query's a mess; as you can imagine I built it up a little at a time and haven't cleaned it up all that much. I just thought y'all would be amused by how ludicrous it is. 🙂

Ergeorge, adding columns for radian lat/lon in the ZipCode table makes so much sense! I'll have to do that; maybe do that in an Access query and then have the .asp page do a SELECT from the query instead of from the underlying table.

Access/VBScript doesn't have acos(), so a lot of the duplication you see in the query is the "X" in:

Arccos(X) = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1) (VB Help on derived math functions)

That's three copies of "X", where X = [sin(La1) sin(La2) + cos(La1) cos(La2) cos(Lo1 - Lo2)]

Then, I can't do a

SELECT valueA * valueB AS ValueC ORDER BY ValueC

I have to do ORDER BY valueA * valueB, which is another big part of the duplication in the query. That makes for six copies of X... three in the inverse cosine function twice (once in the SELECT clause, again in the ORDER BY clause).

Let's see if I can tighten it up here... now it looks like this... Carriage returns added for readability.

=====
Select TOP 10 BuilderName, BuilderNum, BuilderAircraftMake, BuilderAircraftModel, BuilderCity, State,

((Atn(-((sin(Latitude) * sin(0.691893332055085) + cos(Latitude) * cos(0.691893332055085) * cos(Longitude - -1.86606581162907)))/ Sqr((-((sin(Latitude) * sin(0.691893332055085) + cos(Latitude) * cos(0.691893332055085) * cos(Longitude - -1.86606581162907)))* ((sin(Latitude) * sin(0.691893332055085) + cos(Latitude) * cos(0.691893332055085) * cos(Longitude - -1.86606581162907))))+1))+2*Atn(1))*57.29746) AS Distance2,

Int(Int(Distance2) *60 + (Distance2 - Int(Distance2)) * 60) AS Distance
from Builders, RadianZipCodes
WHERE Builders.BuilderZip = RadianZipCodes.ZIP
ORDER BY

((Atn(-((sin(Latitude) * sin(0.691893332055085) + cos(Latitude) * cos(0.691893332055085) * cos(Longitude - -1.86606581162907)))/ Sqr((-((sin(Latitude) * sin(0.691893332055085) + cos(Latitude) * cos(0.691893332055085) * cos(Longitude - -1.86606581162907)))* ((sin(Latitude) * sin(0.691893332055085) + cos(Latitude) * cos(0.691893332055085) * cos(Longitude - -1.86606581162907))))+1))+2*Atn(1))*57.29746)
=====

Which, admittedly, is still pretty ludicrous.

Any other ideas to clean this up? (I could calcualte "Distance" from "Distance2" in my .asp code instead of in the query, I suppose...)

 

[Descartes]

Originally posted by: NogginBoink
Descartes,

If I were working purely in Access, I totally agree with you.

However, I'm generating the query on an active server page, sending the query to Access, and processing the results on the web page. So I don't think I can use an Access code module to do that. (Maybe I'm wrong?)

I know that the query's a mess; as you can imagine I built it up a little at a time and haven't cleaned it up all that much. I just thought y'all would be amused by how ludicrous it is. 🙂

Ergeorge, adding columns for radian lat/lon in the ZipCode table makes so much sense! I'll have to do that; maybe do that in an Access query and then have the .asp page do a SELECT from the query instead of from the underlying table.

Access/VBScript doesn't have acos(), so a lot of the duplication you see in the query is the "X" in:

Arccos(X) = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1) (VB Help on derived math functions)

That's three copies of "X", where X = [sin(La1) sin(La2) + cos(La1) cos(La2) cos(Lo1 - Lo2)]

Then, I can't do a

SELECT valueA * valueB AS ValueC ORDER BY ValueC

I have to do ORDER BY valueA * valueB, which is another big part of the duplication in the query. That makes for six copies of X... three in the inverse cosine function twice (once in the SELECT clause, again in the ORDER BY clause).

Let's see if I can tighten it up here... now it looks like this... Carriage returns added for readability.

=====
Select TOP 10 BuilderName, BuilderNum, BuilderAircraftMake, BuilderAircraftModel, BuilderCity, State,

((Atn(-((sin(Latitude) * sin(0.691893332055085) + cos(Latitude) * cos(0.691893332055085) * cos(Longitude - -1.86606581162907)))/ Sqr((-((sin(Latitude) * sin(0.691893332055085) + cos(Latitude) * cos(0.691893332055085) * cos(Longitude - -1.86606581162907)))* ((sin(Latitude) * sin(0.691893332055085) + cos(Latitude) * cos(0.691893332055085) * cos(Longitude - -1.86606581162907))))+1))+2*Atn(1))*57.29746) AS Distance2,

Int(Int(Distance2) *60 + (Distance2 - Int(Distance2)) * 60) AS Distance
from Builders, RadianZipCodes
WHERE Builders.BuilderZip = RadianZipCodes.ZIP
ORDER BY

((Atn(-((sin(Latitude) * sin(0.691893332055085) + cos(Latitude) * cos(0.691893332055085) * cos(Longitude - -1.86606581162907)))/ Sqr((-((sin(Latitude) * sin(0.691893332055085) + cos(Latitude) * cos(0.691893332055085) * cos(Longitude - -1.86606581162907)))* ((sin(Latitude) * sin(0.691893332055085) + cos(Latitude) * cos(0.691893332055085) * cos(Longitude - -1.86606581162907))))+1))+2*Atn(1))*57.29746)
=====

Which, admittedly, is still pretty ludicrous.

Any other ideas to clean this up? (I could calcualte "Distance" from "Distance2" in my .asp code instead of in the query, I suppose...)

You could simply put the routines in a normal asp page, put that in an include directory, then <!--#include file="include/math.asp" --> (or whatever your name the include). At least this way you better segregate the data access code from the interface elements. I Google.Search("vbscript acos")'d and came up with...

function Acos(a) ' en VBScript
if Abs(a)=1 then Acos = (1-a)*PI/2 _
else Acos = Atn(-a/Sqr(1-a*a))+2*Atn(1)
end function

There are others I'm sure. You could of course save your sql statement as a query in Access and have your Recordset use that as the recordsource instead of the dynamic sql.

 

[NogginBoink]

You could simply put the routines in a normal asp page, put that in an include directory, then <!--#include file="include/math.asp" --> (or whatever your name the include). At least this way you better segregate the data access code from the interface elements. I Google.Search("vbscript acos")'d and came up with...

function Acos(a) ' en VBScript
if Abs(a)=1 then Acos = (1-a)*PI/2 _
else Acos = Atn(-a/Sqr(1-a*a))+2*Atn(1)
end function

There are others I'm sure. You could of course save your sql statement as a query in Access and have your Recordset use that as the recordsource instead of the dynamic sql.

That's what I'd like to do. I just don't know how to pass parameters into an Access query. I can do it in SQL Server, but not Access.

Can anyone help?

 

[Descartes]

Originally posted by: NogginBoink

You could simply put the routines in a normal asp page, put that in an include directory, then <!--#include file="include/math.asp" --> (or whatever your name the include). At least this way you better segregate the data access code from the interface elements. I Google.Search("vbscript acos")'d and came up with...

function Acos(a) ' en VBScript
if Abs(a)=1 then Acos = (1-a)*PI/2 _
else Acos = Atn(-a/Sqr(1-a*a))+2*Atn(1)
end function

There are others I'm sure. You could of course save your sql statement as a query in Access and have your Recordset use that as the recordsource instead of the dynamic sql.

That's what I'd like to do. I just don't know how to pass parameters into an Access query. I can do it in SQL Server, but not Access.

Can anyone help?

Just like you would a sproc in oracle/sql server using their oledb provider/odbc driver...

Dim oCmd
Set oCmd = Server.CreateObject("ADODB.Command")

With oCmd
.ActiveConnection = ' your connection string or Connection object
.CommandText = "yourAccessQueryName"
.CommandType = adCmdStoredProc
.Parameters.Append oCmd.CreateParamater("@ParameterName", adVarChar, adParamInput, 50, "some value here")
Set oCmd = Nothing

There you're just creating the proverbial command object, setting it's command type to a sproc (query in access), the name of the query, and appending a parameter. If you don't know about Parameter objects, just have a look-see at the MDAC SDK documentation. You'll need to specify the type (adVarChar), direction (adParamInput), and size (50 for adVarChar as 1 byte for each char -- 100 for adNVarChar as it's unicode, 2 bytes for each char), then of course your value...

You can get your recordset using this command object doing the following:

Dim oRS
Set oRS = oCmd.Execute()
oRS.Close
Set oRS = Nothing

There are a few other methods I use, but this is the most typical. You can also call sprocs as a method of the connection object...

Dim oDB
Set oDB = Server.CreateObject("ADODB.Connection")
oDB.Open ' connection string

Dim oRS
oDB.SomeSproc oRS

' do something w/ the recordset
oRS.Close
Set oRS = Nothing

oDB.Close
Set oDB = Nothing

I've used the above method in sql server only, but it uses the same late-binding method invocation facilities as any COM compoent, so it should work in Access as well...

I wrote the above w/o declaring types as you would in VB so you should be able to plug that straight into your ASP. If you want to put this in VB you'd obviously want to reference the typelib for MDAC, and explicitly declare each type instead of using variants.

<disclaimer>
I wrote the above OTTOMH, so double-check it if you use it...
</disclaimer>

 

[NogginBoink]

Argh.

I moved all the messy logic inside Access. I put some of the calculations in a code module.

It won't work. The .asp page sends the query to the Jet engine, which is not Access and knows nothing of code modules.

Arrgh.

I'm leaving it as an ugly messy dynamically built SQL query, primarily because it's working. And working, inelegant code beats elegant but nonworking code any day.

Thanks for the input, all.

 

[Descartes]

Sorry man, I didn't mean to make it confusing. My module suggestion for access was before I knew you were doing this in asp...

I thought you just wanted to know how to call an access query from a recordset/command object, but as you found out, it can't contain references to routines that exist within a code module because the access runtime would not have been instantiated. You could of course use the Access COM component (the progid is Access.Application I believe), but that's probably not worth it.