Case Study - ORDER BY Clause Producing Unexpected Results

Good afternoon everyone! Today's blog post is going to be a little bit different to what I have been writing previously. This is going to be a case study investigating Iron Speed geo-mapping, based on an application that I have decided to design and make myself in order to improve on my design and development skills.

Case Study 

The project involves calculating the distance between an origin location and various points on the map (latitudes and longitudes). I wanted to sort my results depending on how close these points are to the origin position.

I have been using a piece of software called Iron Speed Designer (for those of you who don't know, it's a code generator to make the creating of web applications much quicker and easier than starting from scratch). During my working experience with Iron Speed, it is very easy to modify WHERE clauses and ORDER BY clauses using their graphical user interface rather than using the SQL code in the code behind. I had read in the Iron Speed release documentation that page sorting can be manipulated by the new mapping functionality and I have attempted to do this using the Geo-Proximity WHERE Clause. The screen shots below show how I have attempted this:  

Adding the Geo-Proximity WHERE Clause.

Searching within the location of the browser. 

Showing the Geo-Proximity set as the Sort Field.

However, upon investigation, it didn't seem to be sorting the locations correctly. So was this a database query issue or a code behind issue? I had to carry out some exploratory work in order to determine what was the case. 

An incredibly useful tool in SQL Server called the SQL Profiler allowed me to create a new trace and find the exact query that was running on the database. That way, I could determine whether it was the query that was causing the problem, or whether there was some kind of override in the code behind that I had to track down.

In order to use the SQL Profiler, open SQL Server Management Studio and click on Tools > SQL Server Profiler: 

Next, connect to the correct Database Engine Server (the one that your application has been hooked up to):

The next page will let you set up a new Trace File (for the purposes of this exercise, I just clicked ok there was no need to save the trace file I was generating): 

As soon as you click on OK, you will see all transactions that are running from that Database Server. So how would I go about trying to capture the query running on the database?

You can control what appears in the Trace file by using the following icons: 

• The Play icon (greyed out above as the trace is running at this point) is used to start the Trace. 
• The Pause icon is used to pause the Trace. When you click Play, the Trace will continue where it left off. 
• The Stop icon is used to Stop the Trace. When you click Play, the Trace will reset and delete the Trace up until that point. 

• The Eraser icon will delete the trace up until the point you press the Eraser button. Very useful for trapping a specific SQL statement that is running on the server. 

After pausing the trace, erasing what's currently in there and then restarting, I refreshed that particular page in my application and the SQL event was displayed in the Event Log. I stopped the Trace at this point as this was the event I needed. I copied and pasted the result into SQL Server, added a new column to see what was being calculated and sorted on:

And this is the result set (Col_7 should be bringing back the distance of each town/city from my current location (Heybridge) in kilometres): 

As you can see though, this looks highly inaccurate and I know that Harlow is in fact the furthest distance away out of that list (of course I double checked as well!). 

I spent quite a lot of time last night investigating (with a Senior Developer) what the actual calculation was that was used. Usually, I would use Pythagoras Theorem to work out the distance (thank you Maths GCSE!) where in order to find the hypotenuse (or the distance between 2 points in this case), you would have to use the equation c^2 = a^2 + b^2: 

However, this wouldn't be accurate for a curved surface such as the Earth, therefore spherical trigonometry would have to be used. It appears as if the formulae used was in fact Equirectangular Approximation (and by looking at the information found in the link below, it's obvious that this formula was preferred as it performs much quicker than other methods, extremely useful when performing it on large data sets):

http://jsperf.com/haversine-vs-spherical-law-of-cosines-vs-equirectangula

Upon further investigations, it appeared that the formulae used to calculate the distance was wrong. It appeared to be working in degrees instead of radians and this is why my results were completely unexpected. 

What is the difference between degrees and radians?

Radians are the standard measure of angles and are used throughout mathematics (though covered more in mechanics than standard mathematics in schools that deals with degrees). Typically, there are 6 radians in a circle (rounded) and 1 radian equates to just under 57.3 degrees. So as you can tell, there is actually quite a large difference between 1 degree and 1 radian! (and this is very likely what's causing my problem). 

And, once converting to radians in SQL and running a new query from there, the result set appeared to be correct.

Here is a snippet of the new code: 

And this is the produced result set (col_7 should be bringing back the distance in kilometres - which as you can see looks much more realistic than the previous result and is a true representation!):

More investigations are being carried out in order to find a suitable fix for the problem. This does look like that is what the issue is though, as the database query was returning the results in a different order.

What I Learnt... 

From this situation, I have learnt:

• The SQL Profiler is a fantastic way of capturing database queries that are run by the application. These can then be investigated as in this case study to determine whether it is a SQL problem or a code-behind problem. I have learnt how to use it to my advantage in order to solve complex problems in the application. 
• Trigonometric functions (such as Cosine) always uses RADIANS, so make sure that any calculations for degrees using cosine uses radian units otherwise it'll produce an unexpected (and very wrong!) result. 
• This exercise has also helped my problem solving and shown that I am a logical thinker! :)
  

As A Final Word...

Thank you for reading my latest blog post. If you have any questions, comments or feedback please don't hesitate to leave a comment below in the comment box and I will get back to you as soon as I can. Alternatively, please like and comment on my SQL Genius Facebook Page.