## Calculate the distance between two coordinates in SQL Server

If you ever had to quickly retrieve a distance between two points (identified with a latitude and longitude each), you might have had to rely on the Google geocoding service / distance calculator.

For a lot of points, this might prove difficult, so we have found a good function that will do all the processing instantly inside SQL.

```CREATE function [dbo].[F_GREAT_CIRCLE_DISTANCE]
(
@Latitude1  float,
@Longitude1 float,
@Latitude2  float,
@Longitude2 float
)
returns float as
begin

declare @lon1  float
declare @lon2  float
declare @lat1  float
declare @lat2  float

declare @a float
declare @distance float

-- Sets average radius of Earth in Miles

set @lon1 = radians( @Longitude1 )
set @lon2 = radians( @Longitude2 )
set @lat1 = radians( @Latitude1 )
set @lat2 = radians( @Latitude2 )

set @a = sqrt(square(sin((@lat2-@lat1)/2.0E)) + (cos(@lat1) * cos(@lat2) * square(sin((@lon2-@lon1)/2.0E))) )
set @distance = @radius * ( 2.0E *asin(case when 1.0E < @a then 1.0E else @a end ))

return @distance
end
GO
```

To use this function, call it with a pair of coordinates.

Example: Chicago 41.8819° N, 87.6278° W
New York 40.7127° N, 74.0059° W

Result: 1,143.38 km

```print dbo.[F_GREAT_CIRCLE_DISTANCE] (41.8819, 87.6278, 40.7127, 74.0059)
```

If you would like to see the distance in miles rather than kilometers, change
to

The above result will be 710miles.

## HTML5 Geolocation – Making your html location aware

Wherever you go, there you are. And sometimes knowing where you are makes all the difference (especially to a web app). In this chapter we’re going to show you how to create web pages that are location aware—sometimes you’ll be able to pinpoint your users down to the corner they’re standing on, and sometimes you’ll only be able to determine the area of town they’re in (but you’ll still know the town!). Heck, sometimes you won’t be able to determine anything about their location, which could be for technical reasons, or just because they don’t want you being so nosy. Go figure.

In any case, in this chapter we’re going to explore a JavaScript API: Geolocation. Grab the best location-aware device you have (even if it’s your desktop PC), and let’s get started.

Knowing where your users are can add a lot to a web experience: you can give them directions, make suggestions about where they might go, you can know it’s raining and suggest indoor activities, you can let your users know who else in their area might be interested in some activity. There’s really no end to the ways you can use location information.

With HTML5 (and the Geolocation JavaScript-based API) you can easily access location information in your pages. That’s said, there are a few things to know about location before we get started. Let’s check it out…

Q: I heard Geolocation isn’t a real API?
A: Geolocation is not considered a first-class member of the existing HTML5 standard, but that said, it is a standard of the W3C, widely supported and pretty much everyone includes Geolocation in the list of important HTML5 APIs. And it most certainly is a real JavaScript API!
Q: Is the Geolocation API the same as the Google Maps API?
A: No. They are completely different APIs. The Geolocation API is solely focused on getting you information about your position on
So, if you need to display your users location in a map, Google’s API gives you a convenient way to implement that functionality.
Q: Isn’t it a privacy concern to have my device reveal my location?
A: The Geolocation specification specifies that any browser must have the express permission of the user to make use of their location. So, if your code makes use of the Geolocation API, the first thing the browser will do is make sure it is okay with the user to share her location.
Q: How well supported is Geolocation?
A: Very well supported; in fact, it’s available in almost every modern browser including desktop and mobile. You’ll want to be sure you’re using the latest version of your browser; if you are,  then you’re probably good to go.

The Lat and Long of it…
To know where you are, you need a coordinate system, and you need one on the Earth’s surface. Luckily we have such a thing, and it uses latitude and longitude together as a coordinate system. Latitude specifies a north/sourth point on the Earth, and longitude, an east/west point. Latitude is measured from the equator, and longitude is measured from Greenwich, England. The job of the geolocation API is to give us the coordinates of where we are at any time, using these coordinates.

You’ve probably seen latitude and longitude specfied in both  degrees / minutes / seconds, such as (47˚38’34’’, 122˚32’32’’), and in decimal values, such as (47.64, -122.54). With the Geolocation API we always use decimal values. If you need to convert degrees/ minutes/seconds to decimal, you can use this function:

function degreesToDecimal(degrees, minutes, seconds) {
return degrees + (minutes / 60.0) + (seconds / 3600.0);
}

How the Geolocation API determines your location

You don’t have to have the newest smartphone to be location aware. Even desktop browsers are joining the game. You might ask, how would a desktop browser determine its location if it doesn’t have GPS or any other fancy location technologies?

Well, all browsers (in devices and on your desktop) are using a few different ways to determine where you are, some more accurate than others. Let’s take a look:

GPS
Global Positioning System, supported by many newer mobile devices, provides extremely accurate location information based on satellites. Location data may  include altitude, speed and heading information. To use it, though, your device has to be able to see the sky, and it can take a long time to get a location. GPS can also be hard on your batteries.

Location information based on your IP address uses an external database to map the IP address to a physical location. The advantage of this approach is that it can work anywhere; however, often IP addresses are resolved to locations such as your ISP’s local office. Think of this method as being reliable to the city or sometimes neighborhood level.

Cell Phone
Cell phone triangulation figures out your location based on your distance from one or more cell phone towers (obviously the more towers, the more accurate your location will be). This method can be fairly accurate and works indoors (unlike GPS); it also can be much quicker than GPS. Then again, if you’re in the middle of nowhere with only one cell tower, your accuracy is going to suffer.

WiFi
WiFi positioning uses one or more WiFi access points to triangulate your location. This method can be very accurate, works indoors and is fast. Obviously it requires you are somewhat stationary (perhaps drinking a venti iced tea at a coffee house).

The browser implementation is going to determine how location is determined. But the good news is the browser can use any of these means to determine your location. In fact, a smart browser might first use cell phone triangulation, if it is available, to give you a rough idea of location, and then later give you a more accurate location with WiFi or GPS.
We’ll see that you don’t need to worry about how the location is being determined, and we’ll focus more on the accuracy of your location instead. Based on the accuracy, you can determine how useful the location is going to be for you.

The code:

function getMyLocation() {
if (navigator.geolocation) {
navigator.geolocation.getCurrentPosition(displayLocation);
} else {
}
}

function displayLocation(position) {
var latitude = position.coords.latitude;
var longitude = position.coords.longitude;
var div = document.getElementById(“location”);
div.innerHTML = “You are at Latitude: ” + latitude + “, Longitude: ” + longitude;
}

What we just did…
Let’s walk through the code in a little more detail:

if (navigator.geolocation) {

} else {
}

The first thing you need to know if you’re going to write geolocation code is “does this browser support it?” To do that we make use of the fact that browsers have a geolocation property in their navigation object only if geolocation is supported. So we can test to see if the geolocation property exists, and if so make use of it; otherwise, we’ll let the user know.

If your browser supports the Geolocation API, you’ll find a geolocation property in the navigator object.

We can use a simple test to see if geolocation is there (if it’s not then navigator.geolocation evaluates to null and the condition will fail).

Now, if there is a navigator.geolocation property, we’re going to make some more use of it. In fact, the navigator.geolocation property is an  object that contains the entire geolocation API. The main method the API supports is getCurrentPosition, which does the work of getting the browser’s location. Let’s take a closer look at this method, which has three parameters, the second two of which are  optional:

getCurrentPosition(successHandler, errorHandler, options)

The successHandler is a function that is called if the browser is able to successfully determine your location.
These two parameters are optional, which is why we didn’t need them before.
The errorHandler is another function, that is called if something goes wrong and the browser can’t determine your location.
The options parameter allows you to customize the way geolocation works.

Remember, APIs are just objects with properties and methods!

## Geolocation in HTML5

Wherever you go, there you are. And sometimes knowing where you are makes all the difference (especially to a web app). In this chapter we’re going to show  you how to create web pages that are location aware—sometimes you’ll be able to  pinpoint your users down to the corner they’re standing on, and sometimes you’ll only  be able to determine the area of town they’re in (but you’ll still know the town!). Heck,  sometimes you won’t be able to determine anything about their location, which could be for technical reasons, or just because they don’t want you being so nosy. Go figure.
In any case, in this chapter we’re going to explore a JavaScript API: Geolocation. Grab the best location-aware device you have (even if it’s your desktop PC), and let’s get started.

A few questions

Q: I heard Geolocation isn’t a real API?
A: Geolocation is not considered a first-class member of the existing HTML5 standard, but that said, it is a standard of the W3C, widely supported and pretty much everyone includes Geolocation in the list of important HTML5 APIs. And it most certainly is a real JavaScript API!
Q: Is the Geolocation API the same as the Google Maps API?
A: No. They are completely different APIs. The Geolocation API is solely focused on getting you information about your position on the Earth. The Google Maps API is a JavaScript library offered by Google that gives you access to all their Google Maps functionality.
So, if you need to display your users location in a map, Google’s API gives you a convenient way to implement that functionality.
Q: Isn’t it a privacy concern to have my device reveal my location?
A: The Geolocation specification specifies that any browser must have the express permission of the user to make use of their location. So, if your code makes use of the Geolocation API, the first thing the browser will do is make sure it is okay with the user to share her location.
Q: How well supported is Geolocation?
A: Very well supported; in fact, it’s available in almost every modern browser including desktop and mobile. You’ll want to be sure you’re using the latest version of your browser; if you are, then you’re probably good to go.

To know where you are, you need a coordinate system, and you need one on the Earth’s surface. Luckily we have such a thing, and it uses latitude and longitude together as a coordinate system. Latitude specifies a north/sourth point on the Earth, and longitude, an east/west point. Latitude is measured from the equator, and longitude is measured from Greenwich, England. The job of the geolocation API is to give us the coordinates of where we are at any time, using these coordinates.

You’ve probably seen latitude and longitude specfied in both degrees/minutes/seconds, such as (47˚38’34’’, 122˚32’32’’), and in decimal values, such as (47.64, -122.54). With the Geolocation API we always use decimal values. If you need to convert degrees/minutes/seconds to decimal, you can use this function:

function degreesToDecimal(degrees, minutes, seconds) {
return degrees + (minutes / 60.0) + (seconds / 3600.0);
}

How the Geolocation API determines your location
You don’t have to have the newest smartphone to be location aware. Even desktop browsers are joining the game.

You might ask, how would a desktop browser determine its location if it doesn’t have GPS or any other fancy location technologies?
Well, all browsers (in devices and on your desktop) are using a few different ways to determine where you are, some more accurate than others. Let’s take a look:

GPS – Global Positioning System, supported by many newer mobile devices, provides extremely accurate location information based on satellites. Location data may include altitude, speed and heading information. To use it, though, your device has to be able to see the sky, and it can take a long time to get a location. GPS can also be hard on your batteries.
Location information based on your IP address uses an external database to map the IP address to a physical location. The advantage of this approach is that it can work anywhere; however, often IP addresses are resolved to locations such as your ISP’s local office. Think of this method as being reliable to the city or sometimes neighborhood level.

Cell phone triangulation figures out your location based on your distance from one or more cell phone towers (obviously the more towers, the more accurate your location will be). This method can be fairly accurate and works indoors (unlike GPS); it also can be much quicker than GPS. Then again, if you’re in the middle of nowhere with only one cell tower, your accuracy is going to suffer.

WiFi positioning uses one or more WiFi access points to triangulate your location. This method can be very accurate, works indoors and is fast. Obviously it requires you are somewhat stationary (perhaps drinking a venti iced tea at a coffee house).

It’s cool we’ve got so many ways to know where we are. How am I going to know which method my device is using?
The short answer is “you’re not,” as the browser implementation is going to determine how location is determined. But
the good news is the browser can use any of these means to determine your location. In fact, a smart browser might first use cell phone triangulation, if it is available, to give you a rough idea of location, and then later give you a more accurate location with WiFi or GPS.
We’ll see that you don’t need to worry about how the location is being determined, and we’ll focus more on the accuracy
of your location instead. Based on the accuracy, you can determine how useful the location is going to be for you.

The Javascript:

function getMyLocation() {
if (navigator.geolocation) {
navigator.geolocation.getCurrentPosition(displayLocation);
} else {
}
}

function displayLocation(position) {
var latitude = position.coords.latitude;
var longitude = position.coords.longitude;
var div = document.getElementById(“location”);
div.innerHTML = “You are at Latitude: ” + latitude + “, Longitude: ” + longitude;
}

Now – how about computing the distance between two points?

Ever wanted to know how to compute the distance between two points on a sphere? To compute the distance between two coordinates most everyone uses the Haversine equation; you’ll find it implemented below. Feel free to use it anywhere you need to know the distance between two coordinates:

function computeDistance(startCoords, destCoords) {
return distance;
}

var radians = (degrees * Math.PI)/180;
}

Now that we’ve got a function to compute the distance between two coordinates, let’s define our location here.
We’re going to define a literal object for the coordinates of our location. Add this as a global variable at the top of your .js file.

var ourCoords = {latitude: 52.464725,longitude:-0.735789};

function displayLocation(position) {
var latitude = position.coords.latitude;
var longitude = position.coords.longitude;
var div = document.getElementById(“location”);
div.innerHTML = “You are at Latitude: ” + latitude + “, Longitude: ” + longitude;
var km = computeDistance(position.coords, ourCoords);
var distance = document.getElementById(“distance”);
distance.innerHTML = “You are ” + km + ” km from the Easter Islands”;
}