mickyT (12/12/2012)

---

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