|
This little gem is used to construct the where condition for an SQL query that selects all coordinates within a certain distance from a point (in a PHP app). The coordinates are stored in the db in a single field, where the lat and lon are separated by a comma. The SQL is splitting on that and running a whole bunch of calculations. The table can be millions of entries big. I kid you not.
$distance = "ROUND(((ACOS( SIN({$latitude} * PI()/180 ) * SIN(SUBSTRING_INDEX({$tablename}.{$fieldname}, ',',1) * PI()/180 ) + COS({$latitude} * PI()/180 ) * COS(SUBSTRING_INDEX({$tablename}.{$fieldname}, ',',1) * PI()/180 ) * COS(({$longitude} - SUBSTRING_INDEX({$tablename}.{$fieldname}, ',',-1)) * PI()/180))*180/PI())*60*1.1515),6)";
$where = "{$distance} <= " . $dbs->addQuotes( "5" );
|
|
|
|
|
It's probably immaterial, but what does the constant 1.1515 represent in the equation? 
The great thing about generated SQL is that you never have to maintain the SQL. The horror about generated SQL is that you have to maintain the generator. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Somebody manually wrote this, not auto generated...
|
|
|
|
|
Chris Meech wrote:
It's probably immaterial, but what does the constant 1.1515 represent in the equation? Confused
Its the distance of one nautical mile.
|
|
|
|
|
Thanks. It was a constant that didn't ring any bells for me. Now that I know it's for nautical miles, it will be a ship's bell that should go off next time.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
elchupathingy wrote: Its the distance of one nautical mile
In case anyone is wondering about the relevance of a nautical mile, it's because a nautical mile is defined as one minute of arc around the Earth's circumference. So this calculation is converting minutes of arc into miles.
According to a quick search around the interweb, a nautical mile is actually defined as 1.1508 miles, not 1.1515. So for large distances, this calculation is going to be slightly off. If you sail half way round the world, you'll be 8 miles away from where you think you are with this calculation.
|
|
|
|
|
While we're all dragging out our ocean navigation knowledge...
David Skelly wrote: a nautical mile is defined as one minute of arc around the Earth's circumference
It's worth highlighting that this works at the equator and along meridians, but is not accurate if you are traveling east/west along say 45 degrees north lattitude.
Another reason this calculation probably isn't going to be accurate is that the shortest distance between two points is an arc. The further you get from the equator the more curve there is in that arc.
Of course if they are just trying to locate stores within 50 miles of your location, their math is probably good enough...
|
|
|
|
|
It's actually not 1.1515, but 60*1.1515, i.e. 69.09. One degree corresponds to 69.09 miles (non-nautical).
I found that out by doing the maths behind that formula. He converts lots of degree-coordinates into radian-coordinates by that *PI/180, then calculates the shortest distance along a great circle in radians (that's the spherical geometry equivalent of the Pythagoras formula for orthogonal triangles), converts that back to degrees along that circle, and then transforms that to miles; for kilometers use 111.1.
Nonetheless, I do not understand why he uses 60*1.1515 instead of 69.09.
|
|
|
|
|
A full circle means 360 degrees or 2*PI radians. Consequently, I would suggest the original programmer replace all PI()/180 by 2*PI()/360. That makes things much easier to understand!
|
|
|
|
|
The ugliest part is database structure.
The coordinates are stored in the db in a single field, where the lat and lon are separated by a comma
I would TALK to the programmer, but I would FIRE the database architect immediately.
I did similar thing (if distances you deal with are not that big, you can do it without acos and sin, and even without SQRT in SQL code if you calculate distance*distance before you run the query). Yet it works horribly slow. I made it run very fast by indexing lat and long and finding all locations within the lat,long square, then cutting off everything outside of a circle...
Now you see why I suggest to fire the database architect. You cannot use index on lat, long, so any code you write is doomed to work sluggish.
|
|
|
|
|
for( int i = 1 ; i <= 3 ; i++)
{
if(i==1){…}
else if(i==2){…}
else if(i==3){…}
else{…}
}
source: http://www.coding-horror.de/?p=258
|
|
|
|
|
Unfortunately I've seen this sort of thing far too often.
It's up there with if((x == 1) && (x != 2)) and similar stuff. 
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
The only valid measurement of code quality: WTFs/minute.
|
|
|
|
|
Some people really don't get when they should use switch/select statements
|
|
|
|
|
|
The fact that the final else block will never be executed suggests either (a) this code used to do something different and has been hacked around blindly, or (b) the person who wrote it didn't really understand what he was doing.
|
|
|
|
|
or (c) he really didn't trust integers. To be sure, to be sure, to be sure...
You should never use standby on an elephant. It always crashes when you lift the ears. - Mark Wallace
C/C++ (I dont see a huge difference between them, and the 'benefits' of C++ are questionable, who needs inheritance when you have copy and paste) - fat_boy
|
|
|
|
|
...but in real-life I've seen "obviously unnecessary" else-blocks quite often. Most times there was an error-handling routine, which put something like "unhandled case; please look at routine foo()" to your log-file. And in some cases it make sense - who guarantees you that a guy who extends this routine, thought on all possible occurances of "i"?
|
|
|
|
|
I already saw a teacher showing "how" to use switches.
The combo-box had 2 values:
Gif.
Jpg.
Then, in the code, he did something like:
for(int i=0; i<2; i++)
{
if (i == combo.SelectedIndex)
{
switch(i)
{
case 1:
break;
case 2:
break;
}
}
}
Note that not only the "for" is useless, the "switch" using the values of a for is ridiculous... but the valid values are 0 and 1, and the code checked for 1 and 2.
And finally, but not presented in the code, the code was copied and the filename was something + ".gif" or something + ".jpg"... he could simple do a better working code with:
string name = editName.Text + '.' + combo.SelectedValue;
|
|
|
|
|
or maybe he is just impressing his students how genius he is
|
|
|
|
|
Someone who has been using Python for a while?
|
|
|
|
|
Found this description on a function called "Periodic" yesterday.
<br />
"This function is periodic, it runs periodically. This function will be executed every period"<br />
Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen
|
|
|
|
|
That's exactly what we need - functions with PMT!
You should never use standby on an elephant. It always crashes when you lift the ears. - Mark Wallace
C/C++ (I dont see a huge difference between them, and the 'benefits' of C++ are questionable, who needs inheritance when you have copy and paste) - fat_boy
|
|
|
|
|
A comment like that would make you wonder if it actually was executed every period... Or was the commenter just trying to egg it on...
|
|
|
|
|
"then shalt thou count to three, no more, no less"
Hey yes, exactly three times telleth he the nature of this very function!
|
|
|
|
|
 I fail to see the problem. It's clear and to the pouint.
Everything makes sense in someone's mind
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
