DECLARE @ORILat FLOAT ,@ORILong FLOAT ,@DESLat FLOAT ,@DESLong FLOAT ,@segments INTSELECT @ORILat = 14.50861111 -- MNL (Manila) ,@ORILong = 121.0194444 ,@DESLat = 18.58194444 -- PNQ (Pune, India) ,@DESLong = 73.91944444 ,@segments = 1000DECLARE @Log_Details TABLE (Log_Detail_ID INT,Log_Detail_Latitude FLOAT,Log_Detail_Longitude FLOAT,Log_Detail_Altitude INT);WITH Nbrs_3(n) AS (SELECT 1 UNION SELECT 0) ,Nbrs_2 (n) AS (SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2) ,Nbrs_1 (n) AS (SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2) ,Nbrs_0 (n) AS (SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2) ,Nbrs (n) AS (SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2) ,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY n) As n FROM Nbrs)INSERT @Log_DetailsSELECT TOP (@segments) 1 ,@ORILat + (@DESLat - @OriLat) * n / @segments ,@ORILong - (@ORILong - @DESLong) * n / @segments ,1000 + (1000*(n % 15))FROM Tally

-- Convert degrees to radiansDECLARE @b FLOATSELECT @b = ACOS(-1.)/180.SET STATISTICS TIME ONSELECT * FROM @Log_DetailsCROSS APPLY ( SELECT TOP(1) ICAO, [Name],LatDec,LongDec FROM Airports ORDER BY 6731*2*ATN2(SQRT(SQUARE(SIN(@b*(Log_Detail_Latitude-LatDec)/2.)) + COS(LatDec*@b)*COS(Log_Detail_Latitude*@b)*SQUARE(SIN(@b*(Log_Detail_Longitude-LongDec)/2.))), SQRT(1.-(SQUARE(SIN(@b*(Log_Detail_Latitude-LatDec)/2.)) + COS(LatDec*@b)*COS(Log_Detail_Latitude*@b)*SQUARE(SIN(@b*(Log_Detail_Longitude-LongDec)/2.)))))) x SET STATISTICS TIME OFFDECLARE @LatDiff FLOAT, @LongDiff FLOATSELECT @LatDiff = 0.5*ABS(@OriLat - @DesLat), @LongDiff = 0.5*ABS(@OriLong - @DesLong) SET STATISTICS TIME ONSELECT * FROM @Log_DetailsCROSS APPLY ( SELECT TOP(1) ICAO, [Name], LatDec, LongDec FROM Airports WHERE LatDec BETWEEN Log_Detail_Latitude - @LatDiff AND Log_Detail_Latitude + @LatDiff and LongDec BETWEEN Log_Detail_Longitude - @LongDiff AND Log_Detail_Longitude + @LongDiff ORDER BY 6731*2*ATN2(SQRT(SQUARE(SIN(@b*(Log_Detail_Latitude-LatDec)/2.)) + COS(LatDec*@b)*COS(Log_Detail_Latitude*@b)*SQUARE(SIN(@b*(Log_Detail_Longitude-LongDec)/2.))), SQRT(1.-(SQUARE(SIN(@b*(Log_Detail_Latitude-LatDec)/2.)) + COS(LatDec*@b)*COS(Log_Detail_Latitude*@b)*SQUARE(SIN(@b*(Log_Detail_Longitude-LongDec)/2.)))))) x SET STATISTICS TIME OFF-- Now create an index and time it againCREATE INDEX latlong ON Airports (LatDec, LongDec)SET STATISTICS TIME ONSELECT * FROM @Log_DetailsCROSS APPLY ( SELECT TOP(1) ICAO, [Name], LatDec, LongDec FROM Airports WHERE LatDec BETWEEN Log_Detail_Latitude - @LatDiff AND Log_Detail_Latitude + @LatDiff and LongDec BETWEEN Log_Detail_Longitude - @LongDiff AND Log_Detail_Longitude + @LongDiff ORDER BY 6731*2*ATN2(SQRT(SQUARE(SIN(@b*(Log_Detail_Latitude-LatDec)/2.)) + COS(LatDec*@b)*COS(Log_Detail_Latitude*@b)*SQUARE(SIN(@b*(Log_Detail_Longitude-LongDec)/2.))), SQRT(1.-(SQUARE(SIN(@b*(Log_Detail_Latitude-LatDec)/2.)) + COS(LatDec*@b)*COS(Log_Detail_Latitude*@b)*SQUARE(SIN(@b*(Log_Detail_Longitude-LongDec)/2.)))))) x SET STATISTICS TIME OFF

(1000 row(s) affected)SQL Server Execution Times: CPU time = 7613 ms, elapsed time = 7676 ms.(1000 row(s) affected)SQL Server Execution Times: CPU time = 686 ms, elapsed time = 687 ms.(1000 row(s) affected)SQL Server Execution Times: CPU time = 265 ms, elapsed time = 609 ms.

SELECT l.* ,( SELECT TOP(1) ICAO FROM Airports WHERE LatDec BETWEEN Log_Detail_Latitude - @LatDiff AND Log_Detail_Latitude + @LatDiff and LongDec BETWEEN Log_Detail_Longitude - @LongDiff AND Log_Detail_Longitude + @LongDiff ORDER BY 6731*2*ATN2(SQRT(SQUARE(SIN(@b*(Log_Detail_Latitude-LatDec)/2.)) + COS(LatDec*@b)*COS(Log_Detail_Latitude*@b)*SQUARE(SIN(@b*(Log_Detail_Longitude-LongDec)/2.))), SQRT(1.-(SQUARE(SIN(@b*(Log_Detail_Latitude-LatDec)/2.)) + COS(LatDec*@b)*COS(Log_Detail_Latitude*@b)*SQUARE(SIN(@b*(Log_Detail_Longitude-LongDec)/2.))))) ) as AirportFROM @Log_Details l

DECLARE @StartLat DECIMAL(8, 5) , @EndLat DECIMAL(8, 5) , @StartLong DECIMAL(8, 5) , @EndLong DECIMAL(8, 5) , @LatDiff DECIMAL(8, 5) , @LongDiff DECIMAL(8, 5); SELECT TOP(1) @StartLat = Latitude, @StartLong = LongitudeFROM dbo.Log_DetailsORDER BY Latitude, Longitude;SELECT TOP(1) @EndLat = Latitude, @EndLong = LongitudeFROM dbo.Log_Details;ORDER BY Latitude DESC, Longitude DESC;SELECT @LatDiff = ABS(@StartLat - @EndLat), @LongDiff = ABS(@StartLong - @EndLong);SELECT Log_Detail_ID, Latitude, Longitude, Airport_Latitude , Airport_Longitude, DistanceFROM dbo.Log_Details CROSS APPLY(SELECT TOP(1) *, Distance = Airport_Geography.STDistance(GEOGRAPHY:oint(Latitude, Longitude, 4326))FROM dbo.Airports WHERE Airport_Latitude BETWEEN Latitude - @LatDiff AND Latitude + @LatDiffAND Airport_Longitude BETWEEN Longitude - @LongDiff AND Longitude + @LongDiffORDER BY Airport_Geography.STDistance(GEOGRAPHY:oint(Latitude, Longitude, 4326))) AS a;

/********************************************************************Nearest Airports********************************************************************/-- drop database Flightscreate database Flights;gouse Flights;go-- Base tables -- Airports create table dbo.Airports( AirportCode char(3) primary key, AirportName varchar(255), CityName varchar(255), Country varchar(255), CountryCode char(3), Latitude decimal(10,7), Longitude decimal(10,7), Location Geography ) CREATE SPATIAL INDEX Airport_Index ON dbo.Airports(Location) USING GEOGRAPHY_GRID WITH ( GRIDS = (MEDIUM, HIGH, MEDIUM, LOW ), CELLS_PER_OBJECT = 64, PAD_INDEX = ON ); -- insert into dbo.Airports select * from FlightGeo.dbo.Airports (internet sourced 9317 worldwide airport locations) -- Flight path (POINTS) create table dbo.FlightPath( PathID int identity(1,1) primary key, FlightPoint geography ) CREATE SPATIAL INDEX FlightPath_Index ON dbo.FlightPath(FlightPoint) USING GEOGRAPHY_GRID WITH ( GRIDS = (MEDIUM, HIGH, MEDIUM, LOW ), CELLS_PER_OBJECT = 64, PAD_INDEX = ON ); -- Tally table create table dbo.Tally( T int primary key ) go ;WITH Nbrs_3(n) AS (SELECT 1 UNION SELECT 0) ,Nbrs_2 (n) AS (SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2) ,Nbrs_1 (n) AS (SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2) ,Nbrs_0 (n) AS (SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2) ,Nbrs (n) AS (SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2) ,TallyNow (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY n) As n FROM Nbrs) insert into dbo.Tally select * from TallyNow ---------------------------------------- sample flight London/Bordeaux (missing Northern France)-- Given flight path details (points)go truncate table dbo.FlightPathinsert into dbo.FlightPath values (geography::STGeomFromText('POINT(-0.4513890 51.4697220)',4326))insert into dbo.FlightPath values (geography::STGeomFromText('POINT(-1.3595581 51.2653521)',4326))insert into dbo.FlightPath values (geography::STGeomFromText('POINT(-1.7413330 50.8631777)',4326))insert into dbo.FlightPath values (geography::STGeomFromText('POINT(-2.6531982 50.2998670)',4326))insert into dbo.FlightPath values (geography::STGeomFromText('POINT(-2.9196166 49.7422316)',4326))insert into dbo.FlightPath values (geography::STGeomFromText('POINT(-3.2244873 49.1619507)',4326))insert into dbo.FlightPath values (geography::STGeomFromText('POINT(-3.8369750 48.6710126)',4326))insert into dbo.FlightPath values (geography::STGeomFromText('POINT(-4.1638183 47.9329065)',4326))insert into dbo.FlightPath values (geography::STGeomFromText('POINT(-4.1033935 47.2363545)',4326))insert into dbo.FlightPath values (geography::STGeomFromText('POINT(-3.6199951 46.6682870)',4326))insert into dbo.FlightPath values (geography::STGeomFromText('POINT(-3.1228637 45.9568782)',4326))insert into dbo.FlightPath values (geography::STGeomFromText('POINT(-2.0462036 45.1781648)',4326))insert into dbo.FlightPath values (geography::STGeomFromText('POINT(-0.6756591 44.8305521)',4326))insert into dbo.FlightPath values (geography::STGeomFromText('POINT(-0.7152780 44.8286110)',4326))set statistics time on;declare @returnRows int = 1declare @maxDistance int = 100000;with Distances as ( select *, AP.Location.STDistance(FP.FlightPoint) as Distance, ROW_NUMBER() over (partition by FP.PathID order by AP.Location.STDistance(FP.FlightPoint) asc) as Priority from dbo.FlightPath as FP left join (select dbo.Airports.* from dbo.Airports, dbo.FlightPath where Location.STIntersects(FlightPoint.STBuffer(@maxDistance))=1) as AP on AP.Location.STDistance(FP.FlightPoint)<@maxDistance )select PathID, AirportName, CityName, Country,Location, Distance from Distances where Priority = 1set statistics time offgo-- Given flight path details (linestring)set statistics time ondeclare @returnRows int = 1declare @maxDistance int = 100000;declare @flightpath geographyset @flightpath = geography::STGeomFromText( 'LINESTRING(-0.4513890 51.4697220, -1.3595581 51.2653521, -1.7413330 50.8631777, -2.6531982 50.2998670, -2.9196166 49.7422316, -3.2244873 49.1619507, -3.8369750 48.6710126, -4.1638183 47.9329065, -4.1033935 47.2363545, -3.6199951 46.6682870, -3.1228637 45.9568782, -2.0462036 45.1781648, -0.6756591 44.8305521, -0.7152780 44.8286110)', 4326);with AirportsToBeConsidered as( select * from dbo.Airports where Location.STIntersects(@flightPath.STBuffer(@maxDistance))=1)select * from (select *,ROW_NUMBER() over (partition by T order by A.Location.STDistance(@flightPath.STPointN(T))) as Priority, A.Location.STDistance(@flightPath.STPointN(T)) as Distance from (select * from dbo.Tally where T <= @flightpath.STNumPoints()) as T left join AirportsToBeConsidered as A on A.Location.STDistance(@flightPath.STPointN(T))<@maxDistance ) as A where Priority<=@returnRowsset statistics time off go----Straight line between start and end points (Cleveland -> St Louis)declare @flightbit int = 0declare @flightbits int = 50declare @startpoint geography declare @endpoint geographyselect @startpoint = location from dbo.Airports where AirportCode='CLE'select @endpoint = location from dbo.Airports where AirportCode='STL'declare @distance decimal(10,2) = @StartPoint.STDistance(@EndPoint)declare @flightstart_LAT decimal(10,7)=@StartPoint.Latdeclare @flightstart_LON decimal(10,7)=@StartPoint.Longdeclare @flightend_LAT decimal(10,7)=@EndPoint.Latdeclare @flightend_LON decimal(10,7)=@EndPoint.Longtruncate table dbo.FlightPathwhile @flightbit <= @flightbits begin insert into dbo.FlightPath(FlightPoint) select geography::STGeomFromText('POINT(' + convert(varchar(25),@Flightstart_LON-( (@Flightstart_LON-@flightend_LON)*(@flightbit*1.0/@flightbits)) )+' '+ convert(varchar(25),@Flightstart_LAT-( (@Flightstart_LAT-@flightend_LAT)*(@flightbit*1.0/@flightbits)) )+')',4326) set @flightbit = @flightbit + 1 endselect * from dbo.FlightPathgoset statistics time on;declare @maxDistance int = 350000;with Distances as ( select *, AP.Location.STDistance(FP.FlightPoint) as Distance, ROW_NUMBER() over (partition by FP.PathID order by AP.Location.STDistance(FP.FlightPoint) asc) as Priority from dbo.FlightPath as FP left join (select dbo.Airports.* from dbo.Airports, dbo.FlightPath where Location.STIntersects(FlightPoint.STBuffer(@maxDistance))=1) as AP on AP.Location.STDistance(FP.FlightPoint)<@maxDistance )select PathID, AirportName, CityName, Country,Location, Distance from Distances where Priority = 1set statistics time offgo

declare @line geographydeclare @mtomiles int = 1609declare @waypointeveryXmiles int = 25-- London Heathrow to Seattle Tacomaselect @line = Geography::STGeomFromText( 'LINESTRING (-0.451389 51.469722,-122.309444 47.448889)',4326)select @line declare @waypoints table ( ID Int identity(1,1) primary key, Point geography )-- add first point at start of lineinsert into @waypoints select @line.STStartPoint()-- add intermediate points along lineinsert into @waypoints select @line.STStartPoint().STBuffer(T * @waypointeveryXmiles * @mtomiles).STIntersection(@line).STEndPoint() from Tally where (@line.STLength()/@mtomiles) > (T * @waypointeveryXmiles) order by T-- add last point at end of lineinsert into @waypoints select @line.STEndPoint()select * from @waypoints