Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Optimize query for finding nearest airport based on latitude and longitude using STDistance Expand / Collapse
Author
Message
Posted Sunday, April 29, 2012 2:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:08 PM
Points: 5,401, Visits: 7,514
Jeff Moden (4/29/2012)
Nope... not the way I meant it. For the first and second optimizations, you would limit the lat/lons to the following yellow square.


D'oh, okay, now I getcha. :) Sometimes you just need to have it drawn out in pictures!



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1292211
Posted Sunday, April 29, 2012 3:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
Actually, I'm amazed. You couldn't have picked a better region to make your point about the "half way" mark. Well done.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1292213
Posted Monday, April 30, 2012 3:02 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
Anytime you start asking questions about performance, it helps to have some test data.

So I created a table of Airports from the attachment. It's not a very good table because it only has about 2700 airports with geospatial coordinates but it will do. I got it here: http://www.partow.net/miscellaneous/airportdatabase/

Next I had to create data for a flight. I did this by assuming the plane flies in a straight line (from Manila to Pune, India) and captures 1000 data points.

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

The next step, of which I will not bore you with the gory details was to simulate the haversine formula for calcuating distance from spatial coordinates because I'm running in SQL 2005 so don't have access to that neat STDistance function. You'll see that on the ORDER BY clause below (it is close but not perfect).

The code below runs the 1000 points against all airports and then vs. airports only within a narrow range of latitude and longitude.

-- 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

The last step creates an index on Airports latitude and longitude and runs the second query again.

The timing results are:
(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.


As you can see, limiting the airports in the range of the search helped significantly. I think I did it more or less the way Jeff was suggesting. The index helped even more.

No doubt you'll want to check that the airports along the path are correct but they start with MNL and end at PNQ.

This version appears to operate just slightly faster and you could use it if you don't need to return the rest of the stuff about the closest airport (just the name).

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

Query plan cost is just slightly better than the others.

Edit: Note that the longitude difference is not corrected properly for trans-Pacific crossings. That is the added complexity that Jeff mentioned. Just trying to show the performance comparison here.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!


  Post Attachments 
Airports.txt (2 views, 910.21 KB)
Post #1292324
Posted Monday, April 30, 2012 5:48 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
Even the Latitude calculations can get a bit screwy on things like trans-Pacific flights because the actual flight path will usually follow the "Great Circle Path" which is hardly ever parallel to lines of Latitude. For those, you'd need to calculate the Lat/Lon Limits using the half-distance thing for every point. That'll still be lightning quick compared to trying to compare to all the airports of the world.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1292425
Posted Monday, April 30, 2012 7:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:37 AM
Points: 80, Visits: 484
Hi guys.

I'm really grateful for all the help you've shared. Analyzing the formulas takes a lot of time for me to somehow grasp what does it do. I'm not really good in mathematics I'm still trying to understand what do these functions do. Guess have to read more about this. Anyway, earlier today, I've tried The Dixie Flatline's post but somehow it takes so long, maybe it's because I don't have an index in place in the Airports table for Lat and Long fields. I will try dwain.c's post and hopefully it work for me. As for Jeff's suggestion for trans-Pacific flights , I still have to read about this as I don't have any clue.

Thanks you guys.
Post #1292482
Posted Monday, April 30, 2012 10:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:37 AM
Points: 80, Visits: 484
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.
Post #1292637
Posted Monday, April 30, 2012 12:46 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
facturan.antonio (4/30/2012)
Hi guys.

I'm really grateful for all the help you've shared. Analyzing the formulas takes a lot of time for me to somehow grasp what does it do. I'm not really good in mathematics I'm still trying to understand what do these functions do. Guess have to read more about this. Anyway, earlier today, I've tried The Dixie Flatline's post but somehow it takes so long, maybe it's because I don't have an index in place in the Airports table for Lat and Long fields. I will try dwain.c's post and hopefully it work for me. As for Jeff's suggestion for trans-Pacific flights , I still have to read about this as I don't have any clue.

Thanks you guys.


Unless you have an index in place, and the query uses it, spatial queries can run insanely long.

Also, you really cant ignore the great circle routes, when calculating distances between airports. You can only use "flat" distance calculations when the scale is small enough that any difference is insignificant.


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #1292731
Posted Monday, April 30, 2012 12:58 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 3:15 PM
Points: 3,163, Visits: 1,378
Just another view using SQL 2008 R2. This is based on a piece that I did for a customer but scaled to airports and flights. The customer wanted to fill in the path (similar to your flights) and customer locations (similar to your airports).

Below are three examples given:
1) a list of way points (flight record points) and a requirement to return all @returnRows points/airports within @maxDistance from each point
2) a linestring (flight path) and a requirement to return all @returnRows points/airports within @maxDistance from each point
3) a startpoint and endpoint, a number of steps (calculated) for a geometric straight line between the points, to return the nearest point within @maxDistance

Built on the same ideas as Jeff and Dwain, but changing the circles into a STBuffer around the path to restrict the airports to be examined.

Notes:
Examples 1 and 2 involve a flight from London Heathrow to Bordeaux bypassing the crowded airspace over northern France
Example 3 uses the same Cleveland to St Louis as per one of the other examples (and results in Cleveland,Griffing Sandusky,Galion,Findlay,Allen County,Neil Armstrong (Wapakoneta), Decatur, Delaware County, Marion, Indianapolis International, Vermillion County, Coles County Memorial, Vandalia, Municipal, Alton and St Louis International)

Performance-wise : may not be the best ever, but restricting the STBuffer(@maxDistance) helps.

/********************************************************************
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


Fitz
Post #1292745
Posted Monday, April 30, 2012 4:29 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 3:15 PM
Points: 3,163, Visits: 1,378
The Dixie Flatline (4/30/2012)
facturan.antonio (4/30/2012)
Hi guys.

I'm really grateful for all the help you've shared. Analyzing the formulas takes a lot of time for me to somehow grasp what does it do. I'm not really good in mathematics I'm still trying to understand what do these functions do. Guess have to read more about this. Anyway, earlier today, I've tried The Dixie Flatline's post but somehow it takes so long, maybe it's because I don't have an index in place in the Airports table for Lat and Long fields. I will try dwain.c's post and hopefully it work for me. As for Jeff's suggestion for trans-Pacific flights , I still have to read about this as I don't have any clue.

Thanks you guys.


Unless you have an index in place, and the query uses it, spatial queries can run insanely long.

Also, you really cant ignore the great circle routes, when calculating distances between airports. You can only use "flat" distance calculations when the scale is small enough that any difference is insignificant.


Thanks this got me thinking about the great circles and how we could split a line into even pieces given a geographic line from point to point without intermediate points.

This code will split a great circle route into steps @waypointeveryXmiles apart. This can then be used in my last post to give the nearest airport to each of these points.

Hope this is some use to somebody.

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

Fitz
Post #1292862
Posted Monday, April 30, 2012 7:09 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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.


I'm thinking that it will not. You need a flight time in your log table and then you could use that.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1292892
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse