Scripts

Working Days

Philip Leitch — Thu, 15 Apr 2010 03:51:20 GMT

Create function Working_Days(
@Date1 Smalldatetime,
@Date2 Smalldatetime)
/*www.prlsoftware.com
Author: Philip Leitch
Date: 2010
Purpose: This function determines the number of working days between two dates.
Copyright: Philip Leitch 2010
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes: The logic is simply the number of days different between the two dates minus 2 times the number of weeks between
the two dates. Holidays and other issues may need to be factored in.
*/
returns int
as
begin
return abs(datediff(Day, @Date1, @Date2) - 2 * abs(datediff(week, @date1, @date2)))
end
go
select dbo.working_days ('12/4/2010', '16/4/2010') --During Week
select dbo.working_days ('16/4/2010', '19/4/2010') --Over weekend

Philip Leitch ...

Bearing

Philip Leitch — Tue, 13 Apr 2010 00:11:10 GMT

Create function [dbo].[Bearing]
( @Lat1 float, @Lng1 float, @Lat2 float, @Lng2 float)
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2010
Purpose: This function converts two sets of latitude and longatude points in to a bearing.
Copyright: Philip Leitch 2010
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes: Bearing changes during travel, for instance, if you travel heading due north, after enough time you will end up heading due
south even though you haven’t changed direction. Therefore this is the outset direction (direction from point A to get to point B).
*/
returns numeric(10,6) as
begin
Declare @Bearing as numeric(10,6)

    set @Lat1 = @Lat1 * (pi()/180.0)
    set @Lng1 = @Lng1 * (pi()/180.0)
    set @Lat2 = @Lat2 * (pi()/180.0)
    set @Lng2 = @Lng2 * (pi()/180.0)

    set @Bearing = atn2(sin(@Lng2-@Lng1)*cos(@Lat2),cos(@Lat1)*sin(@Lat2)-sin(@Lat1)*cos(@Lat2)*cos(@Lng2-@Lng1))/ (pi()/180.0)

    if @Bearing < 0
        set @Bearing = 360 + @Bearing
    return @Bearing

end

Philip Leitch ...

Point_Bearing_Distance_To_Point

Philip Leitch — Tue, 09 Mar 2010 03:42:41 GMT

CREATE    function [dbo].[Point_Bearing_Distance_To_Point]
( @Latitude float, @Longitude float, @Bearing float, @Distance as float)
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2010
Purpose: This function converts a latitude and longatude, bearing and distance to another latitude and longitude (concatenated together).
Copyright: Philip Leitch 2010
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:
*/
returns varchar(50)
as
begin
Declare @Lat2 float, @Long2 float
set @Latitude = cast(@latitude as numeric(9,6))
Declare @Radius float
    set @Radius = dbo.Earth_Radius(@Latitude)

    set @Bearing = @bearing * (pi()/180.0)
    set @Latitude = @Latitude * (pi()/180.0)
    set @Longitude = @Longitude * (pi()/180.0)

set @lat2 = asin( sin(@Latitude)*cos(@Distance/@Radius) +
                      cos(@Latitude)*sin(@Distance/@Radius)*cos(@Bearing) );
set @long2 = @Longitude + atn2(sin(@Bearing)*sin(@Distance/@Radius)*cos(@Latitude),
                             cos(@Distance/@Radius)-sin(@Latitude)*sin(@lat2));

    return cast(cast(@lat2 * (180/pi()) as numeric(10,6)) as varchar(50)) + ', ' + cast(cast( @long2 * (180/pi()) as numeric(10,6)) as varchar(50))
end

Philip Leitch ...

Distance

Philip Leitch — Tue, 09 Mar 2010 03:37:37 GMT

CREATE function [dbo].[Distance]

(@Lat1 float, @Long1 float, @Lat2 float, @Long2 float)
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2010
Purpose: This function measures the distance between two geospatial points.
Copyright: Philip Leitch 2010
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:
Based on the "ellipsoidal Vincenty formula". This might not be quite as accurate as others, but is close on accuracy and very fast.
Please visit this web site for scripts that are easily converted into any language:
http://www.movable-type.co.uk/scripts/latlong.html

The result is in kilometres Multiply by 1000 for metres

*/
returns float
as
begin

    if abs(@lat1 - @lat2) < 0.00001 and abs(@long1 - @long2) < 0.00001
        return 0

    Declare @Radius float
    set @Radius = dbo.Earth_Radius((@lat1 + @lat2)/cast(2 as float))

    --Convert to radians
    set @Lat1 = @lat1 * (pi()/180)
    set @Long1 = @long1 * (pi()/180)
    set @Lat2 = @lat2 * (pi()/180)
    set @Long2 = @long2 * (pi()/180)

    Declare @return float

    set @return = acos(
    Cos(@Lat1) * cos(@Long1) *
    Cos(@Lat2) * cos(@Long2) +

    cos(@lat1) * sin(@Long1) *
    cos(@lat2) * sin(@Long2) +

    sin(@lat1) * sin(@lat2)

    ) * @Radius

    return @return
end

Philip Leitch ...

Earth_Radius

Philip Leitch — Tue, 09 Mar 2010 03:33:00 GMT

CREATE Function [dbo].[Earth_Radius](@Latitude numeric(9,6))
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2010
Purpose: This function determines the Earth's Radias at a specific latitude.
Copyright: Philip Leitch 2010
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:
*/
returns float
as
begin

    declare @Return as float

    Declare @a as float
    declare @b as float
    set @a = 6378.1370 --Equatorial radius
    set @b = 6356.7523 --Polar radius
    Declare @LatitudeRadians float
    --Convet to radians
    set @LatitudeRadians = @latitude * (pi()/180)
    set @Return = sqrt(
            (power(power(@a, 2)*Cos(@Latitude),2) + power(power(@b, 2)*sin(@Latitude),2))
                                /
            (power(@a*Cos(@Latitude),2) + power(@b*sin(@Latitude),2))
                )
    return @Return
end

Philip Leitch ...

Compass_Direction

Philip Leitch — Tue, 09 Mar 2010 03:30:24 GMT

CREATE Function [dbo].[Compass_Direction] (@Bearing as float)
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2010
Purpose: This function converts a Bearing into human readable compass text.
Copyright: Philip Leitch 2010
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:
*/
returns Varchar(5) as
Begin
declare @North_Distance float
set @north_distance = dbo.North_Distance(@Bearing)
declare @East_Distance float
set @East_Distance = dbo.East_Distance(@Bearing)
return case when @East_Distance between 11.25 and 33.75 then 'E' when @East_Distance between 146.25 and 168.75 then 'W' else '' end +
case when @North_Distance <= 56.26 then 'N' when @North_Distance >= 123.74 then 'S' else '' end +
case when @North_Distance between 11.25 and 33.75 then 'N' when @North_Distance between 146.25 and 168.75 then 'S' else '' end +
case when @East_Distance between 45+11.25 and 45+33.75 then 'E' when @East_Distance between 146.25-45 and 168.75-45 then 'W' else '' end +
case when @North_Distance between 45+11.25 and 45+33.75 then 'N' when @North_Distance between 146.25-45 and 168.75-45 then 'S' else '' end +

case when @East_Distance <= 56.26 then 'E' when @East_Distance >= 123.74 then 'W' else '' end
end

Philip Leitch ...

Angle_To_Bearing

Philip Leitch — Tue, 09 Mar 2010 03:18:51 GMT

CREATE function [dbo].[Angle_To_Bearing]( @North as float, @East as float)
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2010
Purpose: This function converts a North and East distance to a bearing.
Copyright: Philip Leitch 2010
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:
*/
returns float
as
begin
return case when @east <= 90 then @North else 360 - @North end
End

Philip Leitch ...

East_Distance

Philip Leitch — Tue, 09 Mar 2010 03:18:10 GMT

CREATE Function [dbo].[East_Distance] (@Bearing as float)
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2010
Purpose: This function measures the angular distance from due East to the vector bearing.
Copyright: Philip Leitch 2010
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:
*/
returns Float as
begin
return (case when (@Bearing > 270) then (450 - @Bearing) else (abs((@Bearing - 90))) end)
end

Philip Leitch ...

North_Distance

Philip Leitch — Tue, 09 Mar 2010 03:16:31 GMT

CREATE Function [dbo].[North_Distance] (@Bearing as float)
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2010
Purpose: This function measures the angular distance from due North to the vector bearing.
Copyright: Philip Leitch 2010
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:
*/
returns Float as
begin
return (case when (@Bearing > 180) then (360 - @Bearing) else @Bearing end)
end

Philip Leitch ...

Working with Angles

Philip Leitch — Tue, 09 Mar 2010 03:07:23 GMT

When working on directional geospatial data it is often useful to use a bearing (direction of travel). Although using the bearing is very helpful, it does pose a problem: what to do with North. I recently worked on a large project where I needed to determine average bearing of a vehicle at a specific location (the location would be repeated weekly).

If the vehicle repeatedly travels due North then the bearing reading that is taken will variously be just over 0 degrees or just under 360 degrees (either side of due North). The average of this would actually be due South, which is the polar opposite of the direction actually being travelled. This issue doesn’t only apply to premises that are exactly due North, but by any premises where the bearing of the truck occasionally crosses due North. For example, 350 degrees direction might occasionally has a value of 0-5 degrees.

I’ve come up with a way to resolve this by using “Angular Distance”. That is, instead of using bearing which comes back on itself, I’ve used the number of degrees away from North. This way NW would be 45 degrees, as would also be NE, meaning that a truck travelling due North would have an average Angular Distance from north of zero. Similar to a normal compass, South would also be 180 degrees. By doing this, we lose any ability to identify East/West travel, so a second angular distance is required, and I’ve chosen the distance from due East, where 0 is due east and 180 is due West.

Therefore, due north has a Northern Angular Distance Using these two angular distances the original bearing can be easily determined. For instance, an Angular Distance of 45 from North and 45 from East means the truck must be travelling NE. This type of information becomes essential to determining which side of the road a truck is collecting bins. Calculating the total change in bearing can be unintuitive. A change from NE (45 Degrees) to NW (315 Degrees) is a total angular distance of zero from North (as this is the angular distance, not angle). However, over this same change in bearing the angular distance from east is 90 Degrees (45 to 135). Therefore the actual net angular distance of to bearings is calculated by using the Bearing, not the angular distances.

Compass_Direction – Turns a standard bearing (such as 223) into a bearing (SW). The divisions are N, NNE, NE, ENE, E, ESE, SE, SSE, S, SSW, SW, WSW, W, WNW, NW, NNW

North_Distance – Provides the Northern Angular Distance from a bearing.

East_Distiance – Provides the Eastern Angular Distance from a bearing.

Angular_Distance - Given the North_Distance and East_Distance it returns the smallest angle between the two vectors.

I will post each of these functions seperately to aid finding them.

Philip Leitch ...