# SQL Function to find the direction between a mobile device and a POI

• 0
• May 24, 2011

Following on from my previous post about finding the true distance between a mobile user and a POI; I’m now going to look at how to find directions from a triangulated point by a series of mobile towers and a POI.

Nowadays, mobiles are self aware geo-location devices, but not all smartphone’s are geo-location friendly and if you assume or target your application to a specific model; then you are losing out on a significant number of end users. So how can you make sure that your mobile application stays user-friendly?

The application in question (pre-smartphone, I might add) had to show driving or walking directions for the end user on screen for all points of interest. But before the user chooses the category, we needed to provide a summary of nearest interest locations, and their distances and directions from the user location; so the user could then make aÂ choiceÂ based on the distance and number of POI in that direction.

We calculated the distance using haversine formula, but where are these locations? Are they the same side or opposite to each other? There are high chances of the user choosing a location with a high density of POI near him rather a single POI.

Keeping in mind the end user’s dilemma, we decided to find the direction of each location with respect to the end user and show them on the mobile screen. But one major issue was still not addressed – was the end user facing North or South? To resolve this issue, our direction API came toÂ the rescue, and we added a starting reference point with reference to a landmark. This resolved the issue with phones not supporting or embedding a compass-like device.

### The approach:

To find the solution I turned to basicÂ trigonometry; consider the mobile user as centre (0,0) of quadrant system, and POI scattered around in all quadrant with latitude and longitude asÂ CartesianÂ points(x1, y1). I had the distance between two points, and I needed the included angle between the x-axis and line generated by joining these two points.

The X-axis of quadrant represent East and West, and the Y-axis represent North and South. If you split the quadrant further it gives you, NE, NW, SE and SW directions. But our POI will not be exactly on these axis lines, so you need to consider an area formed by further splitting between these directions. In the image, an underground tube station on the X-axis and the other tube station next to it both appear to be East, even though the second tube station is not exactly East. This consideration helps me to group all POI in specific directions.

The clear advantage is gained from the end users point of view, the user will have more choice by following the East direction in this case rather than South-West – where few POI are located.

### The solution:

As you can see from the image, we can easily calculate directions if the included angle values lies in a specific direction pie. The following chart shows various directions and angular values:

Angle in degreesDirection
0 – 22.50East
22.51 – 67.49North East
67.50 – 112.50North
112.51 – 157.49North West
157.50 – 202.50West
205.51 – 247.49South West
247.50 – 292.50South
292.51 – 337.49South East
337.50 – 360.00East

To calculate the included angle of vertex A, I assume vertex A is on the centre of quadrant (0,0) and vertex B is POI. So in theory, the tangent of angle C should return distance “b” (AC). As soon as the distance “b” is calculated, angle A can be calculated using either sine or cosine formula.

The whole equation is resolved to a single arctan formula.Â This function returns the value in radians, so we need to convert it to degrees before applying any calculation logic. But soon the limitations of this approach are highlighted, it can only return values between -90 and +90 degrees and needs major adjustments to get the final value.

As my first attempt failed, I turned towards mathforum, I found a related solution, with some shortcomings.Â After some adjustments and a couple of days of testing with actual data, ultimately I nailed this task of finding directions.

### SQL Function:

The formula from mathforum was written considering North being X-axis, so I have to turn the axis anti-clockwise by 90 degrees.

This formula accepts latitude and longitude values and returns a two-character string denoting direction. If the user and POI is on centre or the same location, it just returns a blank string.

Free of charge. Unsubscribe anytime.