InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 06:07:47 GMT20http://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspxGiven that, I hope your boss doesn't have the misconception that finding the nearest airport to any given airport has anything to with finding the shortest path between airports.Wed, 02 May 2012 06:44:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspxHello guys.Sorry, it took this long to reply to your posts. I have to do other database stuff.I try some of the solutions posted but my query is still slow even though I've already put an indexes on the affected tables.I ask for some suggestion from our senior project manager and he gave his opinion about the situation. His suggestion is just simple. That I didn't have to calculate the nearest airport point by point in the log, I just have to determine whether the speed of the airplane is zero and get from there the nearest airport based on the lat/long. I should have posted the whole table definition of the log as what Evil Kraig F suggested. It's my fault, I should have posted it so not to waste your time on trying to figure out what the best solution to the problem. Sorry, I should have been more clear. Lesson learned. I've learned a lot from all of your suggestions. This is the updated code I made.[code="sql"]DECLARE @LogID INT;SET @LogID = 45;DECLARE @NearestAirports TABLE(Airport_ID INT, Airport_Ident VARCHAR(20), Airport_Name VARCHAR(255), Airport_Latitude DECIMAL(8, 5), Airport_Longitude DECIMAL(8, 5), Airport_Elevation INT, Airport_Geography GEOGRAPHY);INSERT INTO @NearestAirportsSELECT *FROM dbo.AirportsWHERE Airport_ID IN (SELECT Airport_IDFROM (SELECT Latitude, Longitude FROM dbo.Log_Details WHERE Log_Detail_Log_ID = @LogID AND Ground_Speed = 0) AS lCROSS APPLY(SELECT TOP(1) *FROM dbo.AirportsWHERE Airport_Geography.STDistance(GEOGRAPHY::Point(Latitude, Longitude, 4326)) <= 5000ORDER BY Airport_Geography.STDistance(GEOGRAPHY::Point(Latitude, Longitude, 4326))) a);-- Distance in Nautical MilesDECLARE @NMRange MONEY;SET @NMRange = (1852. * 5); -- Set it to 5NMSELECT Log_Details.*FROM Log_DetailsCROSS APPLY( SELECT TOP(1) * FROM @NearestAirports -- Only limit the log record to within 5NM from the airport and plane altitude is within 20 ft WHERE Airport_Geography.STDistance(GEOGRAPHY::Point(Latitude, Longitude, 4326)) <= @NMRange AND Baro_Alt - Airport_Elevation BETWEEN -20 AND 20 ) aWHERE Log_Detail_Log_ID = @LogID;[/code]Thank you. I'm really grateful.Wed, 02 May 2012 05:08:31 GMTamTryingSoHardhttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspxThis post intentionally left blank.Tue, 01 May 2012 15:45:55 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspx1) To the OP: if you aren't good with analysis and mathematics, why are you trying to do this VERY complicated stuff? And without regular computer access too?2) Isaac Kunen's powers-of-two stuff is likely the most efficient public method for finding nearest with spatial, but it doesn't compute nearest point to a line like it seems you need.3) Spatial stuff is VERY complicated, and indexing it properly is also complex. I did some neat work for a stealth startup company that was a quite a bit more efficient than Isaac's stuff but it doesn't do nearest to line either (assuming that is what you need). I believe it could be modified to suit, but this isn't forum-post stuff. If this is for a work product, drop me a line and we can talk consulting to get you what you need.4) As someone else mentioned, SQL Server 2012 will have a built-in nearest calculation (as well as some much-needed improvements to spatial indexing), but that nearest isn't as efficient as the stuff I came up with. It is a hell of a lot simpler however! :hehe:Tue, 01 May 2012 07:58:47 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspxMark,It is always nice to see someone with obvious experience weigh in on a thread like this. Your initial algorithm looks pretty complex and I'll need to study it before I comment.The second one about identifying way points along the great circle routes... I thought about trying something like this. I did in fact after I posted my performance tome. Didn't have much success with it though.Thanks for your contribution to this very interesting thread!Mon, 30 Apr 2012 19:12:39 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspx[quote][b]facturan.antonio (4/30/2012)[/b][hr]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?[code="sql"]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::Point(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::Point(Latitude, Longitude, 4326))) AS a;[/code]Thank you.[/quote]I'm thinking that it will not. You need a flight time in your log table and then you could use that.Mon, 30 Apr 2012 19:09:56 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspx[quote][b]The Dixie Flatline (4/30/2012)[/b][hr][quote][b]facturan.antonio (4/30/2012)[/b][hr]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.[/quote]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.[/quote]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.[code="sql"]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[/code]FitzMon, 30 Apr 2012 16:29:32 GMTMark Fitzgerald-331224http://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspxJust 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 point2) a linestring (flight path) and a requirement to return all @returnRows points/airports within @maxDistance from each point3) a startpoint and endpoint, a number of steps (calculated) for a geometric straight line between the points, to return the nearest point within @maxDistanceBuilt 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 FranceExample 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.[code="sql"]/********************************************************************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[/code]FitzMon, 30 Apr 2012 12:58:17 GMTMark Fitzgerald-331224http://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspx[quote][b]facturan.antonio (4/30/2012)[/b][hr]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.[/quote]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.Mon, 30 Apr 2012 12:46:57 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspxHello 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?[code="sql"]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::Point(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::Point(Latitude, Longitude, 4326))) AS a;[/code]Thank you.Mon, 30 Apr 2012 10:12:49 GMTamTryingSoHardhttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspxHi 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.Mon, 30 Apr 2012 07:05:47 GMTamTryingSoHardhttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspxEven 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.Mon, 30 Apr 2012 05:48:00 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspxAnytime 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: [url]http://www.partow.net/miscellaneous/airportdatabase/[/url]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.[code="sql"]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[/code]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.[code="sql"]-- 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[/code]The last step creates an index on Airports latitude and longitude and runs the second query again.The timing results are:[code="plain"](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.[/code]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).[code="sql"]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[/code]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.Mon, 30 Apr 2012 03:02:48 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspxActually, I'm amazed. You couldn't have picked a better region to make your point about the "half way" mark. Well done.Sun, 29 Apr 2012 15:11:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspx[quote][b]Jeff Moden (4/29/2012)[/b][hr]Nope... not the way I meant it. For the first and second optimizations, you would limit the lat/lons to the following yellow square.[/quote]D'oh, okay, now I getcha. :) Sometimes you just need to have it drawn out in pictures! :w00t:Sun, 29 Apr 2012 14:55:02 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspxNope... not the way I meant it. For the first and second optimizations, you would limit the lat/lons to the following yellow square.[img]http://www.sqlservercentral.com/Forums/Attachment11406.aspx[/img]That would be a bit conservative and would cover even the adventures of "Wrongway Peachfuzz" :-D. If you really want to narrow it down quickly, you can use the more radical "mid point" approach as follows (again, use the yellow square limits):[img]http://www.sqlservercentral.com/Forums/Attachment11407.aspx[/img]Sun, 29 Apr 2012 14:51:22 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspx[quote][b]Jeff Moden (4/29/2012)[/b][hr]So far as any "mid point" error goes, how would there be an error unless you were off course? You can't be more than half way away the total distance from either end point unless you're fairly well off course.[/quote]Unless I'm misunderstanding what you mean, a graphical example:[img]http://www.sqlservercentral.com/Forums/Attachment11405.aspx[/img]This is rough and isn't exactly computed (heck, the blue areas overlap a bit) so I fudged in the larger direction. However, with the blue circles being 'the halfway distances' as I understood what you were describing, notice that Chicago gets left out of the discussion. Depending on where you are on your flight line between Cleveland and Kansas City, there are positions in the flight line where that is the closest airport. At exact calculations I believe Indianapolis would get left out too, but it's a very close thing and GIMP doesn't give me exactly what I want for computations there.That's more what I meant. If Indianapolis wasn't part of the discussion, the closest airport is definately Chicago, and it's left out of the search. Either that or I *really* didn't understand what you meant.Sun, 29 Apr 2012 13:20:28 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspx[quote][b]Evil Kraig F (4/29/2012)[/b][hr]Jeff (and OP) if I read this problem correctly, he doesn't have an endpoint yet and is trying to find said endpoint. Also using that technique you could miss an airport near the center of the flightline if you're looking to locate emergency landing areas.You're basically just trying to find the closest point in the list from a selected point. This means you'll always have to calculate all of the rows against that point, sort the result, and pick the minimum. That's going to be a very heavy handed process. What Jeff's trying to point out is you need to restrict the list of what you're comparing so you can get a better query, and it needs to be precomputed or an existing attribute.Try restricting it to airports in the same country, or those on the same side of the equator, or similar. You'll find the process will take a lot less time. In the meanwhile I'll see if I can come up with anything. Is that table you described the FULL table and the only attributes you have available, or was it simplified for the purposes of this example? If there's more attributes we may have some other ideas.[/quote]Actually, it's simpler than all of that for restrictions if you don't know what the end point is. You can't fly much farther than you have fuel for and, hopefully, you know how much fuel you have so could estimate the range left. Convert that range to degrees of Latitutde and use the same suggestion as before.So far as any "mid point" error goes, how would there be an error unless you were off course? You can't be more than half way away the total distance from either end point unless you're fairly well off course.Sun, 29 Apr 2012 13:04:33 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspxJeff (and OP) if I read this problem correctly, he doesn't have an endpoint yet and is trying to find said endpoint. Also using that technique you could miss an airport near the center of the flightline if you're looking to locate emergency landing areas.You're basically just trying to find the closest point in the list from a selected point. This means you'll always have to calculate all of the rows against that point, sort the result, and pick the minimum. That's going to be a very heavy handed process. What Jeff's trying to point out is you need to restrict the list of what you're comparing so you can get a better query, and it needs to be precomputed or an existing attribute.Try restricting it to airports in the same country, or those on the same side of the equator, or similar. You'll find the process will take a lot less time. In the meanwhile I'll see if I can come up with anything. Is that table you described the FULL table and the only attributes you have available, or was it simplified for the purposes of this example? If there's more attributes we may have some other ideas.Sun, 29 Apr 2012 12:52:14 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspx[quote][b]facturan.antonio (4/28/2012)[/b][hr]Hello Jeff.I was thinking about your suggestion. Is it somehow similar to this?I will get the min/max lat and long based on the flight log. Get the distance between the min/max lat/long and limit the airports based on that distance?Thank you.[/quote]Ummm... kind of but a little bit differently.1. Get the distance between the two airports for the flight and divide that distance by two.2. Convert that distance to degrees of Latitude.3. Subtract and Add that those degrees of Latitude from the Latitude for the given flight point. Those should be your new min an max Latitudes to search for closest airports for points during the flight. It will greatly increase the speed of your original calculation because it can eliminate virtually all other airports in the world for shorter flights.If you create the same limits for Longitude (taking curvature of the Earth into consideration), you can quickly focus in on a pretty tight window.Sun, 29 Apr 2012 12:44:04 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspxHere is a link to a blog by Isaac Kunen discussing "nearest neighbor" solutions using spatial functions in SQL 2008. (This query will actually become fairly easy in SQL 2012.) [url=http://blogs.msdn.com/b/isaac/archive/2008/10/23/nearest-neighbors.aspx]http://blogs.msdn.com/b/isaac/archive/2008/10/23/nearest-neighbors.aspx[/url]Edited to drop a lot of nonsense. Sat, 28 Apr 2012 21:09:41 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspxHello Jeff.I was thinking about your suggestion. Is it somehow similar to this?I will get the min/max lat and long based on the flight log. Get the distance between the min/max lat/long and limit the airports based on that distance?Thank you.Sat, 28 Apr 2012 21:02:06 GMTamTryingSoHardhttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspxHello Jeff.Thanks for the immediate reply. Sorry it this long for me get back, don't have a computer at home. Have to go to an internet shop.I kind of get a blur picture of your suggestion. Can you give me an example? Sorry my analytical skills are not really that good :crying:Sat, 28 Apr 2012 20:43:16 GMTamTryingSoHardhttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspxThe "nearest" airport anywhere in a given flight will never be more than half the distance between the start and end airports of the flight. One very easy and effective optimization to make is to calculate the min and max latitude based on that knowledge and limit the airports you look at to that. A secondary and slightly more complex calulation is to do the same with longitude.Sat, 28 Apr 2012 11:38:34 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1292019-391-1.aspxHi guys. Good day to all.This is the first time I've post in this forum though I was for sometime now looking at the posts of others to gain knowledge in this great RDBMS and to learn from the best on this field.I wanted to ask some help from the community about optimizing the query I've made in finding the nearest airport based on latitude and longitude using STDistance method of the Geography datatype.Here's the scenario, I've imported a csv file that contains all the airports in the world which is around 40k plus records. Also, I've imported another csv file which contain the logs from an airplane. It contains the lat/long, fuel used, altitude, etc. The size of this file will depend on how long the airplane is flying. One file I have imported consists of 12k rows. The table is structured something like this:Log_Details table:Log_Detail_ID,Log_Detail_Latitude,Log_Detail_Longitude,Log_Detail_Altitude1, 40.3215, -112.35, 2000 2, 39.5423, -111.45, 15003, 41.2563, -113.53, 1500......Airport table:Airport_Ident,Airport_Name,Airport_Latitude,Airport_Longitude,Airport_GeogMCIAA, Mactan Cebu Airport, 10.6543, -15.1233, 0x00013213112LAX, Los Angeles, 75.1235, -55.2135, 0x000132654546NAIA, Ninoy Airport, 15.4566, -26.5463, 0x4564789797....Based on the Log_Details table records, I wanted to find the nearest airport based on the Latitude and Longitude. I made a test query to see how long does it take get the nearest airport based on the Log_Details. This is the only set based query I can think of to get the nearest airport.SELECT Log_Detail_ID,Log_Detail_Latitude,Log_Detail_LongitudeFROM dbo.Log_Details lCROSS APPLY (SELECT TOP(1) Airport_Ident,Airport_Name,Airport_Latitude,Airport_Longitude FROM dbo.AirportORDER BY Airport_Geog.STDistance((GEOGRAPHY::Point(Log_Detail_Latitude, Log_Detail_Longitude, 4326))));When I ran this query, after it reached 1 min plus, I cancelled it.That's why I wanted the help of the community if there is any other way that I could speed up the performance of the query considering there are many records in both table? Or is there another way of getting the record without using STDistance method. I don't if this method is slow. Just wanted to know if there is any faster alternatives.Thank you in advance. :-)Sat, 28 Apr 2012 05:48:58 GMTamTryingSoHard