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