Database Reference
In-Depth Information
Listing 13-11.
Calculating the distance between two points: T-SQL functions
create function dbo.CalcDistance
(
@FromLat decimal(9,6)
,@FromLon decimal(9,6)
,@ToLat decimal(9,6)
,@ToLon decimal(9,6)
)
returns float
with schemabinding
as
begin
declare
@Dist float
,@FromLatR float = radians(@FromLat)
,@FromLonR float = radians(@FromLon)
,@ToLatR float = radians(@ToLat)
,@ToLonR float = radians(@ToLon)
set @Dist =
2 * asin(
sqrt(
power(sin( (@FromLatR - @ToLatR) / 2.), 2) +
(
cos(@FromLatR) *
cos(@ToLatR) *
power(sin((@FromLonR - @ToLonR) / 2.0), 2)
)
)
) * 20001600. / pi()
return @Dist
end;
create function dbo.CalcDistanceInline
(
@FromLat decimal(9,6)
,@FromLon decimal(9,6)
,@ToLat decimal(9,6)
,@ToLon decimal(9,6)
)
returns table
as
return
(
with Rads(FromLatR, FromLonR, ToLatR, ToLonR)
as
(
select
radians(@FromLat), radians(@FromLon),
radians(@ToLat), radians(@ToLon)
)
