Network Dataset for Airline schedule data?

02-01-2013 04:59 AM
New Contributor

I'm working on a project using historical schedule data of flight delays. It's setup like this:

Flight # (not unique) Tail # (also, not unique) Origin (not unique) Destination (not unique) and Delay (minutes, not unique) [also, time block is included.]

What I've done is concatenate flight#tail#destination because it appears that this combination creates unique ID's per each time block. I'm trying to connect the flight paths---meaning after the first time block's planes arrive, then I need to know where those planes head next.

I'm wondering if this can be achieved using Network Dataset, or route planning, etc...

Please let me know!

Tags (2)
0 Kudos
4 Replies
Occasional Contributor III
What do you mean by a time block ? could you explain more please?
0 Kudos
New Contributor
Sorry, of course yes:

Time block means that departure time and arrival time have been blocked out by hour.

So my resulting table that I'm using to display xy to line uses the departure time block as reference (rather than arrival time)

Unique ID = tail#flight#DepartureArrival
Tim block = dep_blk = 0600-0700

So now I have poly lines that represent departure to arrival at 0600-0700

The next map ill make is departure to arrival poly line at 0700-0800

Ultimately I'm trying to determine which airports out of the arrival column from one time block are the departures for the next time block.

Any suggestions about a tool I can use to compare or try to link the flight paths into a while flight route through all time blocks of the day?
0 Kudos
Occasional Contributor

You are solving an interesting problem. We might be able to help you figure out a solution with either route solver (with time window) or VRP solver. Could you please provide a simple example with the data and the expected solution for your example?


0 Kudos
New Contributor
I wanted to update this thread because you can accomplish sequencing by time block by creating a geodatabase similar to one for State DOT highway inventory.  

You specifically need 3 tables separate from the Route event table that contains your Starting and Ending LRM measurements.

But first you need to know what LRM measurements are and how to produce a table that IDs them to a Route feature.

<quick lesson on Linear Referencing>

(Ultimately you'll use Dynamic Segmentation "dynseg" and MakeRouteEventLayer tool to create a fully functional Route event table to which you'll connect three traversal/sequencing tables)

The input for the Dynseg tool is an LRM (linear referenced) table for each of your route segments (basically a route segment table with 2 fields for LRMFrom and LRMTo, which are kind of like fictional milemarkers for a line feature.) It would be setup like this:

(1) RouteID (primary key - numeric/random)

(2) SegmentID (foreign key - IDs the segments in a Route like how a flight might have a stop, therefore it takes 2 segments to reach it's destination)

(3) LRMFrom & LRMTo - (this is one of the "M" fields, or measurement in the linear referencing workflow, which you'll need to use tomake this table)

For the sake of example, I'm going to say that the linear referencing tool produced a Segment Table:

RouteID: ABC123
SegmentID: JJ
LRMFrom: 0.0 
LRMTo: 6.76

Simply, measuring from a vertex/point to the next vertex/point to make the line segment "jj" is measured 0.0 - 6.76 (it's not in miles or feet or any speicific measurement, it's an abstracted proportionality of that segment in the entire route.)

Ok here's the difference for an airline network you want to time block. Your Routes are just city-pairs, so it's technically one segment per route. But, it's important to know that you need a SegmentID (some say EventID) despite the fact in this case each Route only has one segment. Like this:

Route Segment Table

RouteID: HHHGGG (many segments might be on one route, but in this case there will only be one route per segment)
SegmentID: JJ (you'll have a corresponding table of which airports created segment JJ by using x,y coords to polyline tool, which created the geometry for your initial Route feature class, the primary key for which is RouteID)
LRMFrom: 0.0
LRMTo: 6.7
SegmentLength: 80 (miles)

<<okay, so now you have the Segment table>>

<<you should already have a Route table except instead of x,y points for both To and From, you can just put the length in miles of the route>>

Route Table

RouteType: <<maybe code a domain table of different types // non-stop // red-eye, etc>>
ReferenceTo: <this is from a RouteInventory table that you have to produce from an equivalency relationship table between the x,y coordinates of points that make a route (geometric) and the LRM to and from measurements so that you have a common ReferenceIDpoint for each To and From that's not dependent on LRM or geopoints.>>
ReferenceFrom: <<from the same table above>>

<<and finally the SegmentSequence table>>

Segment ID: jj
<<instead of a FromTime and ToTime, use>>
TimeBlock: 2 <<here you'll have a value that you've coded into a domain table, and in Network Dataset Wizard you can select that domain table to get the connectivity of those coded values. Ex, 1 = 8:00 - 12:00, 2 = 1:00 - 3:00, etc....>>
PositionOnRoute: <<the reason we keep 1 segment : 1 route is so that PositionOnRoute stays constant at 1-allowing our coded domain table to govern the sequence of segment jj>>

Then, you need to make sure you have all the geometric feature classes to build a Network Dataset (ND) in ArcGIS and make sure they're contained in a Feature Dataset along with these SegmentSequence tables. Once the ND is built using the Wizard, you'll see how intuitive it is to configure the drop-down menus with the information above. Beyond that, you'll have to query/program or animate based on whatever you've coded into the domain for TimeBlock, which is driving the SegmentSequence attribute table.

There's likely a bunch of ways to do this, but containing the parts in a Geodatabase as a Feature Dataset prepares you for a Network Dataset build anyways, which is an abstract way to analyze the airspace network.

P.S. - I recommend looking up Coded Domain Tables and Network Dataset Wizard prior to attempting this. I'm using the book Geodatabases for Transportation by A.Butler for guidance. It's very helpful.
0 Kudos