Optimize query for finding nearest airport based on latitude and longitude using STDistance

DECLARE @ORILat FLOAT
,@ORILong FLOAT
,@DESLat FLOAT
,@DESLong FLOAT
,@segments INT

SELECT @ORILat = 14.50861111 -- MNL (Manila)
,@ORILong = 121.0194444
,@DESLat = 18.58194444 -- PNQ (Pune, India)
,@DESLong = 73.91944444
,@segments = 1000

DECLARE @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_Details
SELECT TOP (@segments) 1
,@ORILat + (@DESLat - @OriLat) * n / @segments
,@ORILong - (@ORILong - @DESLong) * n / @segments
,1000 + (1000*(n % 15))
FROM Tally

-- Convert degrees to radians
DECLARE @b FLOAT
SELECT @b = ACOS(-1.)/180.

SET STATISTICS TIME ON
SELECT *
FROM @Log_Details
CROSS 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 OFF

DECLARE @LatDiff FLOAT, @LongDiff FLOAT
SELECT @LatDiff = 0.5*ABS(@OriLat - @DesLat), @LongDiff = 0.5*ABS(@OriLong - @DesLong)

SET STATISTICS TIME ON
SELECT *
FROM @Log_Details
CROSS 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 again
CREATE INDEX latlong ON Airports (LatDec, LongDec)

SET STATISTICS TIME ON
SELECT *
FROM @Log_Details
CROSS 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 Airport
FROM @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 = Longitude
FROM dbo.Log_Details
ORDER BY Latitude, Longitude;

SELECT TOP(1) @EndLat = Latitude, @EndLong = Longitude
FROM 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, Distance
FROM dbo.Log_Details
CROSS APPLY(SELECT TOP(1) *, Distance = Airport_Geography.STDistance(GEOGRAPHY::Point(Latitude, Longitude, 4326))
FROM dbo.Airports WHERE Airport_Latitude BETWEEN Latitude - @LatDiff AND Latitude + @LatDiff
AND Airport_Longitude BETWEEN Longitude - @LongDiff AND Longitude + @LongDiff
ORDER BY Airport_Geography.STDistance(GEOGRAPHY::Point(Latitude, Longitude, 4326))) AS a;

/********************************************************************
Nearest Airports
********************************************************************/

-- drop database Flights
create database Flights;
go

use 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.FlightPath
insert 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 = 1
declare @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 = 1
set statistics time off
go

-- Given flight path details (linestring)
set statistics time on
declare @returnRows int = 1
declare @maxDistance int = 100000;
declare @flightpath geography

set @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<=@returnRows
set statistics time off
go

----Straight line between start and end points (Cleveland -> St Louis)
declare @flightbit int = 0
declare @flightbits int = 50
declare @startpoint geography
declare @endpoint geography
select @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.Lat
declare @flightstart_LON decimal(10,7)=@StartPoint.Long
declare @flightend_LAT decimal(10,7)=@EndPoint.Lat
declare @flightend_LON decimal(10,7)=@EndPoint.Long

truncate table dbo.FlightPath

while @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
end
select * from dbo.FlightPath
go

set 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 = 1
set statistics time off
go

declare @line geography
declare @mtomiles int = 1609
declare @waypointeveryXmiles int = 25
-- London Heathrow to Seattle Tacoma
select @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 line
insert into @waypoints
select @line.STStartPoint()
-- add intermediate points along line
insert 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 line
insert into @waypoints
select @line.STEndPoint()
select * from @waypoints

facturan.antonio (4/30/2012)

Hello guys.

I try to somehow copy what dwain.c did in his query. I was just wondering if there's a possibility that the orig lat and long would be the same as the destination lat and long. Like the airport they flew from would also be their destination.

I have my code as this: I just wanted to ask if the code for getting the @StartLat, @StartLong, @EndLat, and @EndLong would work?

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 = Longitude
FROM	dbo.Log_Details
ORDER BY Latitude, Longitude;

SELECT TOP(1) @EndLat = Latitude, @EndLong = Longitude
FROM	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, Distance
FROM	dbo.Log_Details 
CROSS APPLY(SELECT TOP(1) *, Distance = Airport_Geography.STDistance(GEOGRAPHY::Point(Latitude, Longitude, 4326))
FROM	dbo.Airports WHERE Airport_Latitude BETWEEN Latitude - @LatDiff AND Latitude + @LatDiff
AND Airport_Longitude BETWEEN Longitude - @LongDiff AND Longitude + @LongDiff
ORDER BY Airport_Geography.STDistance(GEOGRAPHY::Point(Latitude, Longitude, 4326))) AS a;

Thank you.