December 11, 2012 at 12:01 am
Comments posted to this topic are about the item Departures from Origins and Arrivals at Destinations
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
December 11, 2012 at 6:04 pm
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 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
December 12, 2012 at 4:03 pm
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
December 12, 2012 at 5:19 pm
mickyT (12/12/2012)
Hi DwainAfter 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 GeographyGO
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 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
December 12, 2012 at 5:36 pm
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.
December 12, 2012 at 10:59 pm
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.
December 13, 2012 at 2:44 am
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 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
December 23, 2012 at 1:16 pm
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.
December 23, 2012 at 5:37 pm
dennisrg (12/23/2012)
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 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
December 24, 2012 at 11:30 am
useful article.
December 24, 2012 at 7:05 pm
Neha05 (12/24/2012)
useful article.
And thank you Neha05 for stopping by, having a read and commenting.
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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply