Hi Dwain
After reading your article (nice work), I couldn't help myself and added a Geography column to the airports table.
This allows the distance to be calculated using the Geography method STDistance and you can also start visualising the data
ALTER TABLE Airports ADD Location Geography
GO
UPDATE Airports
SET Location = Geography::STGeomFromText(
'POINT (' +
CAST(CASE WHEN LongDir = 'E' THEN CAST(LongDeg AS DECIMAL(11,8)) ELSE CAST(-LongDeg AS DECIMAL(11,8)) END +
CAST((LongMin / 60.00) + ( LongSec / 60.00 / 60.00) AS DECIMAL (11,8)) AS varchar(15))
+ ' ' +
CAST(CASE WHEN LatDir = 'N' THEN CAST(LatDeg AS DECIMAL(11,8)) ELSE CAST(-LatDeg AS DECIMAL(11,8)) END +
CAST((LatMin / 60.00) + ( LatSec / 60.00 / 60.00) AS DECIMAL (11,8)) AS varchar(15))
+ ')'
,4326
)
WHERE LatDeg+LatMin+LatSec+LongDeg+LongMin+LongSec <> 0
SELECT f.flight, o.IATA_Code, d.IATA_Code,
f.SegmentDistanceMiles, f.SegmentDistanceKM, cast(o.Location.STDistance(d.Location) / 1000 as decimal(9,4)) CalcDistanceKM,
case when o.location is not null and d.location is not null then
Geography::STGeomFromText(
'LINESTRING (' + REPLACE(REPLACE(REPLACE(o.Location.ToString(),'POINT',''),'(',''),')','') +
', ' +
REPLACE(REPLACE(REPLACE(d.Location.ToString(),'POINT',''),'(',''),')','') +
')'
,
4326)
else
null
end PathGeom
FROM dailyflightschedules f
inner join airports o on f.origin = o.IATA_Code
inner join airports d on f.destination = d.IATA_Code
from airports