Departures from Origins and Arrivals at Destinations

  • Dwain Camps

    SSC Guru

    Points: 86893

    Comments posted to this topic are about the item Departures from Origins and Arrivals at Destinations


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Dwain Camps

    SSC Guru

    Points: 86893

    Between submission of this article and publication, the POC for PNG has been tweaked a little and while doing so I noticed a couple of issues so I will publish this here as errata:

    1. In creatng the DailyFlightSchedules table, the segment distance in miles is INT, so it is likely you'd want the segment distance in KM to be likewise an INT. CAST the computed column's results as follows to achieve this.

    CREATE TABLE DailyFlightSchedules

    (SegmentID INT IDENTITY

    ,Airline CHAR(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

    FOREIGN KEY REFERENCES Airlines(AirlineCode)

    ,Flight VARCHAR(4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

    ,Origin CHAR(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

    FOREIGN KEY REFERENCES Airports(IATA_Code)

    ,Destination CHAR(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

    FOREIGN KEY REFERENCES Airports(IATA_Code)

    ,DepartureTime TIME NOT NULL

    ,ArrivalTime TIME NOT NULL

    ,EffectiveStart DATETIME NULL

    ,EffectiveEnd DATETIME NULL

    ,[Day] CHAR(2) NULL

    ,[DayOfWeek] INT DEFAULT (127) -- Default = every day of the week

    ,SegmentTime INT DEFAULT(0)

    ,SegmentDistanceMiles INT NULL DEFAULT(0)

    ,SegmentDistanceKM AS (CAST(ROUND(SegmentDistanceMiles * 1.60934, 0) AS INT))

    ,FlightNo AS (Airline+Flight) PERSISTED

    ,DepartureDay AS (CASE LEFT([Day], 1) WHEN '1' THEN '+1' ELSE '' END)

    ,ArrivalDay AS (CASE RIGHT([Day], 1) WHEN '1' THEN '+1' ELSE '' END))

    2. An issue cropped up while I was experimenting with effective dates while at the same time only displaying arrival and departure times as TIME. Basically the function wasn't recognizing segments with different effective dates as different flights. This version of the FlightSchedules iTVF should correct this and also now returns effective dates of the flight.

    ALTER FUNCTION [dbo].[FlightSchedule]

    (@Airline CHAR(2) = NULL

    ,@Origin VARCHAR(3) = NULL

    ,@Destination VARCHAR(3) = NULL

    ,@DepartureDate DATETIME = NULL)

    RETURNS TABLE

    RETURN

    -- Select all flight segments with corresponding departure and arrival times

    WITH Flights AS (

    SELECT SegmentID, FlightNo, Origin, Destination

    ,DepartureTime

    ,ArrivalTime

    ,[Day]

    -- Format for display

    ,[DaysOfWeek]=CASE [DayOfWeek] % 2 WHEN 1 THEN 'SU' ELSE '^^' END +

    CASE ([DayOfWeek]/2) % 2 WHEN 1 THEN 'MO' ELSE '^^' END +

    CASE ([DayOfWeek]/4) % 2 WHEN 1 THEN 'TU' ELSE '^^' END +

    CASE ([DayOfWeek]/8) % 2 WHEN 1 THEN 'WE' ELSE '^^' END +

    CASE ([DayOfWeek]/16) % 2 WHEN 1 THEN 'TH' ELSE '^^' END +

    CASE ([DayOfWeek]/32) % 2 WHEN 1 THEN 'FR' ELSE '^^' END +

    CASE ([DayOfWeek]/64) % 2 WHEN 1 THEN 'SA' ELSE '^^' END

    ,[DayOfWeek]

    ,DepartureDay, ArrivalDay

    -- The PARTITION below identifies cases where the same flight number

    -- operates on a different schedule on different days of the week

    ,SegmentNo=ROW_NUMBER() OVER (

    PARTITION BY FlightNo, [DayOfWeek] ORDER BY SegmentID)

    ,SegmentTime

    ,SegmentDistanceMiles

    ,SegmentDistanceKM

    ,OperatingDate

    ,Airline

    ,EffectiveStart, EffectiveEnd

    FROM DailyFlightSchedules

    -- Empty string as @DepartureDate is handled as a NULL (no) value

    -- Drop the time portion of any actual date supplied.

    OUTER APPLY (

    SELECT ISNULL(DATEADD(day, 0, DATEDIFF(day, 0, @DepartureDate)), '')

    ) b(OperatingDate)

    WHERE (OperatingDate = '' OR EffectiveStart IS NULL OR

    OperatingDate >= EffectiveStart) AND

    (OperatingDate = '' OR EffectiveEnd IS NULL OR

    OperatingDate <= EffectiveEnd))

    SELECT a.FlightNo, SegmentNo, a.Origin, a.Destination

    ,DepartureTime=CASE

    WHEN OperatingDate = ''

    THEN CONVERT(VARCHAR(5), a.DepartureTime, 108)

    WHEN OperatingDate <> '' AND InitialDeparture > 1 AND

    a.SegmentNo > 1 AND [Day] = '11'

    THEN CONVERT(VARCHAR(10), OperatingDate, 121) + ' ' +

    CAST(a.ArrivalTime AS VARCHAR(5))

    ELSE CONVERT(VARCHAR(16)

    ,DATEADD(day, CAST(LEFT([Day], 1) AS INT), OperatingDate) + ' ' +

    CAST(a.DepartureTime AS VARCHAR(5)),121) END

    ,ArrivalTime=CASE

    WHEN OperatingDate = ''

    THEN CONVERT(VARCHAR(5), a.ArrivalTime, 108)

    WHEN OperatingDate <> '' AND InitialDeparture > 1 AND

    a.SegmentNo > 1 AND [Day] = '11'

    THEN CONVERT(VARCHAR(10), OperatingDate, 121) + ' ' +

    CAST(a.ArrivalTime AS VARCHAR(5))

    ELSE CONVERT(VARCHAR(16)

    ,DATEADD(day, CAST(RIGHT([Day], 1) AS INT), OperatingDate) + ' ' +

    CAST(a.ArrivalTime AS VARCHAR(5)),121) END

    ,DepartureDay=CASE WHEN OperatingDate IS NULL THEN DepartureDay ELSE ' ' END

    ,ArrivalDay=CASE WHEN OperatingDate IS NULL THEN ArrivalDay ELSE ' ' END

    -- Decode the DayOfWeek INT to a string of operating days

    ,DaysOfWeek

    ,SegmentTime

    ,SegmentDistanceMiles

    ,SegmentDistanceKM

    ,EffectiveStart, EffectiveEnd

    FROM Flights a

    INNER JOIN (

    SELECT FlightNo, [DayOfWeek]

    ,InitialDeparture=CASE

    -- No (NULL) or empty string as origin supplied so include

    -- the earliest segment

    WHEN ISNULL(@Origin, '') = '' THEN MIN(SegmentNo)

    -- If % not in @Origin, we find the first segment that's an exact match

    -- to @Origin

    WHEN CHARINDEX('%', @Origin) = 0

    THEN MIN(CASE WHEN Origin = @Origin THEN SegmentNo END)

    -- If % in @Origin, do a LIKE search to identify the first matching segment

    ELSE MIN(CASE WHEN Origin LIKE @Origin THEN SegmentNo END) END

    ,FinalArrival=CASE

    -- No (NULL) or empty string as destination supplied so include

    -- the earliest segment

    WHEN ISNULL(@Destination, '') = '' THEN MAX(SegmentNo)

    -- If % not in @Destination, we find the last segment that's an exact match

    -- to @Destination

    WHEN CHARINDEX('%', @Destination) = 0

    THEN MAX(CASE WHEN Destination = @Destination THEN SegmentNo END)

    -- If % in @Destination, do a LIKE search to identify the last matching segment

    ELSE MAX(CASE WHEN Destination LIKE @Destination THEN SegmentNo END) END

    FROM Flights

    GROUP BY FlightNo, [DayOfWeek], EffectiveStart) b

    ON a.FlightNo = b.FlightNo AND a.[DayOfWeek] = b.[DayOfWeek]

    WHERE -- Select only segments between the search criteria

    a.SegmentNo BETWEEN InitialDeparture AND FinalArrival AND

    -- Empty string as @Airline is handled as a NULL (no) value

    (ISNULL(@Airline, '') = '' OR Airline = @Airline) AND

    -- OperatingDate IS NULL if no @DepartureDate was supplied

    (OperatingDate = '' OR

    -- Decode the days of the week on which the flight operates

    -- Note: Sensitive to @@DATEFIRST

    (b.[DayOfWeek]/POWER(2, DATEPART(dw, OperatingDate) -1)) % 2 = 1)

    3. My apologies that the link to the map of PNG doesn't seem to display the graphic properly in the article. PNG is really an interesting place so the article is woefully incomplete without a colorful map such as the one you can find here: http://www.oceania-maps.com/guinea.htm

    Looking forward to hearing a discussion about this approach, in particular if anyone has a good general solution to the DST issue.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • mickyT

    SSChampion

    Points: 10360

    Hi Dwain

    After reading your article (nice work), I couldn't help myself and added a Geography column to the airports table.

    This allows the distance to be calculated using the Geography method STDistance and you can also start visualising the data

    ALTER TABLE Airports ADD Location Geography

    GO

    UPDATE Airports

    SET Location = Geography::STGeomFromText(

    'POINT (' +

    CAST(CASE WHEN LongDir = 'E' THEN CAST(LongDeg AS DECIMAL(11,8)) ELSE CAST(-LongDeg AS DECIMAL(11,8)) END +

    CAST((LongMin / 60.00) + ( LongSec / 60.00 / 60.00) AS DECIMAL (11,8)) AS varchar(15))

    + ' ' +

    CAST(CASE WHEN LatDir = 'N' THEN CAST(LatDeg AS DECIMAL(11,8)) ELSE CAST(-LatDeg AS DECIMAL(11,8)) END +

    CAST((LatMin / 60.00) + ( LatSec / 60.00 / 60.00) AS DECIMAL (11,8)) AS varchar(15))

    + ')'

    ,4326

    )

    WHERE LatDeg+LatMin+LatSec+LongDeg+LongMin+LongSec <> 0

    SELECT f.flight, o.IATA_Code, d.IATA_Code,

    f.SegmentDistanceMiles, f.SegmentDistanceKM, cast(o.Location.STDistance(d.Location) / 1000 as decimal(9,4)) CalcDistanceKM,

    case when o.location is not null and d.location is not null then

    Geography::STGeomFromText(

    'LINESTRING (' + REPLACE(REPLACE(REPLACE(o.Location.ToString(),'POINT',''),'(',''),')','') +

    ', ' +

    REPLACE(REPLACE(REPLACE(d.Location.ToString(),'POINT',''),'(',''),')','') +

    ')'

    ,

    4326)

    else

    null

    end PathGeom

    FROM dailyflightschedules f

    inner join airports o on f.origin = o.IATA_Code

    inner join airports d on f.destination = d.IATA_Code

    from airports

  • Dwain Camps

    SSC Guru

    Points: 86893

    mickyT (12/12/2012)


    Hi Dwain

    After reading your article (nice work), I couldn't help myself and added a Geography column to the airports table.

    This allows the distance to be calculated using the Geography method STDistance and you can also start visualising the data

    ALTER TABLE Airports ADD Location Geography

    GO

    UPDATE Airports

    SET Location = Geography::STGeomFromText(

    'POINT (' +

    CAST(CASE WHEN LongDir = 'E' THEN CAST(LongDeg AS DECIMAL(11,8)) ELSE CAST(-LongDeg AS DECIMAL(11,8)) END +

    CAST((LongMin / 60.00) + ( LongSec / 60.00 / 60.00) AS DECIMAL (11,8)) AS varchar(15))

    + ' ' +

    CAST(CASE WHEN LatDir = 'N' THEN CAST(LatDeg AS DECIMAL(11,8)) ELSE CAST(-LatDeg AS DECIMAL(11,8)) END +

    CAST((LatMin / 60.00) + ( LatSec / 60.00 / 60.00) AS DECIMAL (11,8)) AS varchar(15))

    + ')'

    ,4326

    )

    WHERE LatDeg+LatMin+LatSec+LongDeg+LongMin+LongSec <> 0

    SELECT f.flight, o.IATA_Code, d.IATA_Code,

    f.SegmentDistanceMiles, f.SegmentDistanceKM, cast(o.Location.STDistance(d.Location) / 1000 as decimal(9,4)) CalcDistanceKM,

    case when o.location is not null and d.location is not null then

    Geography::STGeomFromText(

    'LINESTRING (' + REPLACE(REPLACE(REPLACE(o.Location.ToString(),'POINT',''),'(',''),')','') +

    ', ' +

    REPLACE(REPLACE(REPLACE(d.Location.ToString(),'POINT',''),'(',''),')','') +

    ')'

    ,

    4326)

    else

    null

    end PathGeom

    FROM dailyflightschedules f

    inner join airports o on f.origin = o.IATA_Code

    inner join airports d on f.destination = d.IATA_Code

    from airports

    Pretty cool Micky!

    Do you need to run anything special on SQL 2008 to support the internal functions you're calling?

    Happy that someone actually read the article.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • mickyT

    SSChampion

    Points: 10360

    dwain.c (12/12/2012)


    mickyT (12/12/2012)

    Pretty cool Micky!

    Do you need to run anything special on SQL 2008 to support the internal functions you're calling?

    Happy that someone actually read the article.

    2008 comes with the geography and geometry as standard. Management Studio will also give you a reasonably simply map view of the data if you include a geography/geometry column. Just click on the spatial tab in the results pane.

    The trick is making sure that you pick the right SRID for the data. I used 4326 (WGS84). This is the same coordinate system used by GPS units.

  • andrew.mckee

    SSC Veteran

    Points: 215

    FYI: Pacific flights crossing the date line can arrive the day before - eg:Air NewZealand NZ4082 Auckland to Tahiti(Papeete) Departs 3:50pm Sunday 16th Dec & arrives 9:50pm Saturday 15th Dec.

  • Dwain Camps

    SSC Guru

    Points: 86893

    andrew.mckee (12/12/2012)


    FYI: Pacific flights crossing the date line can arrive the day before - eg:Air NewZealand NZ4082 Auckland to Tahiti(Papeete) Departs 3:50pm Sunday 16th Dec & arrives 9:50pm Saturday 15th Dec.

    Andrew - Thanks for this. I guess I'm just going to have to run this through and see what modifications are needed t omake it work!


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dennisrg

    SSC Enthusiast

    Points: 149

  • Dwain Camps

    SSC Guru

    Points: 86893

    dennisrg (12/23/2012)


    If you want good data for airlines or airports go to http://transtats.bts.gov/Tables.asp?DB_ID=595&DB_Name=Aviation%20Support%20Tables&DB_Short_Name=Aviation%20Support%20Tables.%5B/quote%5D

    Dennis - Thanks. Actually I have a "paid for" table of airports that I couldn't post for obvious reasons. But I'll look into this as an alternate "free" option.

    Still swamped in holiday madness (and the PNG project that led to this article) so I've only had the time to offer short posts.

    Clickable version of the link provided by Dennis: http://transtats.bts.gov/Tables.asp?DB_ID=595&DB_Name=Aviation%20Support%20Tables&DB_Short_Name=Aviation%20Support%20Tables


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Neha05

    Default port

    Points: 1494

    useful article.

  • Dwain Camps

    SSC Guru

    Points: 86893

    Neha05 (12/24/2012)


    useful article.

    And thank you Neha05 for stopping by, having a read and commenting.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply