City based demand

[JumboShrimp]

However after working with this it seems that at least for this passenger group the square's 8 level (-6 to +6) system is not accurate enough, since for example Paris and Bordeaux area are both assigned a +6 on tourism, while in reality Paris would be higher. So basically this means that person's travel desire is the same to both of these places. And it's a bit worse effect in very large countries .. But it's not practical to start changing the square data anymore.

Scale it up by population of the destination square?

The second and perhaps the bigger issue is to make the destinations work in a logical way and somewhat similar to real life. If we take holiday travellers from Sweden as an example. Currently the system calculates that an airport in the Ukranian area would be their 5th largest destination. Why? Simply because #1) Ukraine is (or in real life "was") a large holiday destination, ranking 12th in the world in the number of foreign tourist visitors (lots of visitors in the Krim area for example), and #2) because that airport is relatively nearby Sweden (80% of holiday travels happen on the same continent, ~<1500 nm). But compared to real life it makes no sense at all, since Swedes don't visit there but fly to Spain for example. But with the current data the system cannot know of this at all. There's some country vs. country relation data, but only on a general level and not for all countries. So in order to make this work properly, each and every country would need dataset on these relations (sort of "where would the people like to go"). This data would then supplement this calculated data, and in this example for example assign a "0.1" factor for the Ukraine.  ...but getting all this data is rather difficult, but on the other hand a must.

However has to be noted that with these current (test) values and factors, the number #1 destination for those tourists Swedes is Palma de Mallorca, so that's rather accurate .. But as you see, without the detailed country relation data there will be a very large number of these oddball destinations.

Maybe each country could have top X country destinations for its citizens.  It would be just a scalar, 1.0 default if country is not specified in the country to country table.  Eventually replaced by a some factor derived from the country to country table.

This way, this particular item would not be a showstopper, since a good way to address it would already be planned, and eventually implemented when data is gathered.

Another thing that may be useful is a long term event "war" for a country when it is fought on its soil, based on historical facts, and it would dramatically reduce holiday travel.  And that could also go to this single factor.  Say from 1.0 to 0.50.

Or another factor, say Olympics or World Cup that would increase it from 1.0 to say 2.0

[ChrisR]

Been looking through the thread and think many of these ideas will be good for airway. Sami and the airway sim are doing a good job despite challengers which could arise. Are there any dates when some of these feactures will start to be added to games ? I do also have a question too. Has there been talk about the opinion of a cargo airline and commercial airline when you are creating your airline ? I have had a look but didn't seem to find anything.

[11Air]

Getting there Sami.  Been reading through as it's a subject I have a keen interest in.
I appreciate all you have achieved so far, just a couple of tweaks that might help:-

Seems to me each airport needs a 'Seating Plan' (based on the aircraft system you use) so LHR will be more first and business class than Gatwick (Tourist).  But this factor can be influenced by the services provided.  If all/most Gatwick First (or Business) traffic is being moved while LHR's is not, then Gatwick will expand (new terminal or upgrade to meet the through flow like real world).
On the long games it is possible for Gatwick to be the major airport for London (why not, it's better placed, just needs another runway and a couple of terminals).

Regionally (in my area) Exeter and Southampton are doing well, while Bournemouth lost a major holiday carrier (2009? soon after building a new terminal) and is struggling but is still a bigger airport and longer runway than Southampton EGHI. 

Stanstead is a lovely airport to use, needs another runway, Gatwick is still trying to find itself due to mismanagement and miss design, LHR is at 100pc capacity.  Rumours are Luton will have a second runway soon (land purchased) driven by the London demand. 
Manchester currently runs at 60pc capacity (flights) but in 5 yrs the new high speed rail link will make it a viable alternate to LHR.

The New London Airport should already be just north of Oxford on the old USAAF base of Upper Heyford which is near where phase one of the new high speed rail link. Unfortunately it's gradually becoming a housing estate - such is the planning on the UK.  Why no 'invented' airports?

On the longer games it's up to you whether the game reflects the real world, or accepts that the real world isn't perfect either.
My suggestions are to help game airports grow in relation to their level of capacity and service (quality) would allow those serving a major centre to develop naturally which will help Gatwick airlines to compete with LHR.

And finally an airport upgrade always leads to an increase in airport 'Landing Charges'.  LHR has 6 terminals now, 3 when I first went there in the 60's.
And, sorry - Can my airline take an investment in my First Airport to help it grow traffic and capacity?

loving the game - and your efforts to keep it growing.

[JH]

Seems to me each airport needs a 'Seating Plan' (based on the aircraft system you use) so LHR will be more first and business class than Gatwick (Tourist).  But this factor can be influenced by the services provided.  If all/most Gatwick First (or Business) traffic is being moved while LHR's is not, then Gatwick will expand (new terminal or upgrade to meet the through flow like real world).

Isn't this one of the things CBD is going to improve, allowing any airport that has London within its catchment area to pick up this business, why would we need a 'seating plan', it defies the point of having a more flexible system. If you are going to hard code first/business demand whats the point in changing to CBD.

[CarlBagot]

Heya Sami, is there anything I or anyone can help you with getting this done  ?

I mean this sincerely.

[Sami]

Haven't worked on this for a while, since I've focused on other things .. Planned a bit so that cargo would be possibly the first thing that is actually tried out in any test games .. but let's see.

The data is collected. ..for passenger travel side, the actual destination calculation is giving me grey hairs still (= how to calculate smartly where passengers will want to go from a certain location; cannot use the old code there).

[Jona L.]

Haven't worked on this for a while, since I've focused on other things .. Planned a bit so that cargo would be possibly the first thing that is actually tried out in any test games .. but let's see.

So that means we will see cargo soon? Very well.... can you already give some previews?

Will that work only with specific cargo planes?
Will there also be Cargo using spare payload on regular (PAX) flights?
Is cargo also as time sensitive as passengers, or can night slots be used as IRL?
How will cargo demand be displayed? Will that call for a reintroduction of the good old demand bars?

Thanks ahead

cheers,
Jona L.

[Mr Yoda]

If cargo will be introduced in this game then belly cargo would be a nice add on. It would give the player an opportunity to generate extra cash and transport cargo without having to operate cargo only planes.

[JumboShrimp]

The data is collected. ..for passenger travel side, the actual destination calculation is giving me grey hairs still (= how to calculate smartly where passengers will want to go from a certain location; cannot use the old code there).

As far as square to square demand, it should come from the geographical data connected, using the population, GDP etc.  The system does not have to store this data, just know how to calculate it at any given time.

As far as how they get there, I suggested earlier splitting the task to 2 independent tasks, in order to manage complexity:

1. pin the square to square demand to airports (resulting in familiar airport to airport demand)
2. allocate airport to airport design using much of the same existing logic

1. pin the square to square demand to airports

This could be done asynchronously.  The allocation of square to square demand to airports (as far as which airport pair to pin the demand to) would (after initial default allocation) depend on availability, quality and capacity (supply of flights) between the airport pairs.  Also, how well this pinned demand is being served.

2. allocate airport to airport design using much of the same existing logic

Except, it needs to be enhanced by introducing connecting flights - which, IMO, is the most challenging part (from programming standpoint), and also most rewarding to the players.

In theory, Step 1. could be done independently, a game world could run with just Step 1 being completed, and Step 2 could use all of the existing code (assuming the existing system already has the demand stored in database in form of airport to airport demand).

[Sami]

No, technically it's already all done pretty much (and in a way you described there too). The main issue I am having (and what I wrote there) was figuring out the destinations from each square. The best way to figure _where_ the people from each square wish to go, in order to make it realistic ..

[LemonButt]

Haven't worked on this for a while, since I've focused on other things .. Planned a bit so that cargo would be possibly the first thing that is actually tried out in any test games .. but let's see.

The data is collected. ..for passenger travel side, the actual destination calculation is giving me grey hairs still (= how to calculate smartly where passengers will want to go from a certain location; cannot use the old code there).

I think there are some fundamental questions that need to be answered before you can even attempt to do the algorithm to distribute pax demand.  Let's take 3 airports in Los Angeles, Chicago, New York.  Let's assume an airline is flying NYC-LAX and charging $200.  Another airline is flying NYC-CHI and CHI-LAX and charging $150 for both flights.  If a pax goes NYC-CHI-LAX then how much is the ticket?  $200 or $300?  If you force an airline to sell it for $200, they are missing out on $100 in revenue that could have been earned by not allowing connecting flights.  If the airline is getting $200, they shouldn't be because the direct flight is $200--they should be charging something less than $200 for the inconvenience of connecting.

I don't think you can setup a pax demand algorithm without first setting up business plans, the simplest form being 3 options: Traditional hub/spoke with connecting pax (like Delta), Focus City airlines that will use multiple legs for a single flight number so they would have 3 routes with 3 prices being LAX-CHI, CHI-NYC, and LAX-CHI-NYC (like Southwest), and then strict point to point carriers where there are no connections and you pay per leg (like Allegiant).

Once you have these 3 business models in place, you can create a master database for routing with 3 columns: departure-arrival-connection.  There should be limits on connections, such as the next flight must depart within 4 hours to be considered a connecting flight.  Then it's just a matter of populating the database as flights are created.  For the hub/spoke model every permutation would be added within 4 hours and a discount rate could be added to each leg for connections for simplicity.  So if 2 legs are $100 each and a 20% discount (modifiable by the player) for connecting flights you'd have a $160 ticket instead of a $200 one.  For the focus city model a player would have to explicitly add LAX-CHI-NYC as a separate flight number and/or use ABCBA routing where it would be implicit where you can assign price values for those 2 leg flights.  For the point to point carrier, they'd simply have each route added.

Once you have the database setup, it's just a matter of pulling the flights where departure= and arrival= the specified values.  You are already using a haversine equation for distance, so you just need to modify it to get the angle of the two legs (between 0 and 180) versus the angle of a direct flight (180).  You can then use that value logarithmically to start penalizing connecting flights with extreme connections.  For example, flying NYC to LAX is a 180 degree flight.  Flying NYC to LAX via CHI has maybe a 150 degree angle.  Flying NYC to LAX via Moscow, Russia is going to have an extreme angle of maybe 20 degrees.  The traffic would be distributed across these flights, but the Moscow flight would be penalized exponentially compared to the Chicago flight.  Additionally, since these numbers would remain static, you could add them to the database so they don't have to be calculated each time.  Then it would just be a matter of calculating each flight's pax ratio/value against a sum() of the matching rows.

[JumboShrimp]

I think there are some fundamental questions that need to be answered before you can even attempt to do the algorithm to distribute pax demand.  Let's take 3 airports in Los Angeles, Chicago, New York.  Let's assume an airline is flying NYC-LAX and charging $200.  Another airline is flying NYC-CHI and CHI-LAX and charging $150 for both flights.  If a pax goes NYC-CHI-LAX then how much is the ticket?  $200 or $300?  If you force an airline to sell it for $200, they are missing out on $100 in revenue that could have been earned by not allowing connecting flights.  If the airline is getting $200, they shouldn't be because the direct flight is $200--they should be charging something less than $200 for the inconvenience of connecting.

Let's say the default ticket price (to keep things very simple) is $100 to take off + $1 per mile   And distances as follows:

JFK-ORD = 600nm, $700
ORD-LAX = 1600nm, $1700
JFK-LAX = 2000nm, $2100

Then full JFK-ORD-LAX would be 2200 nm and $2300

I think what can come into play is pricing.  On dense, competitive routes, player can chose to lower than default pricing (as a formula derived from default), in order to drive more traffic into its hub, making the route more viable pricewise, even accounting for transfer inconvenience.

If the player sets price of both ORD-JFK and ORD-LAX at 80% of default, resulting in total price of $560 + $1360 = $1920, which is less than $2300 default price, and some price conscious customers could chose to go this route, assuming everybody at the direct flight is charging $2300 (or there is no capacity left on the direct flights).

But majority of people connecting would be doing so because there is no direct flight.  There are no direct flights that can connect a vast majority of the demand square in the world.  If one lives in, say Albany, NY, there may only be a 10-15 destinations you can fly to (I am guessing) and everywhere else, you have to connect.  If a person wants to go from Albany, NY to Split, Croatia, it will take 2 connections (not sure if it is viable to model more than 1 connection in the system).

I don't think you can setup a pax demand algorithm without first setting up business plans, the simplest form being 3 options: Traditional hub/spoke with connecting pax (like Delta), Focus City airlines that will use multiple legs for a single flight number so they would have 3 routes with 3 prices being LAX-CHI, CHI-NYC, and LAX-CHI-NYC (like Southwest), and then strict point to point carriers where there are no connections and you pay per leg (like Allegiant).

If a user has to manually price all the combinations, than the system would be a complete fail, and there would not be any point to even build it, since the tedium user setting up pricing would take 95% of play time, and fun part of play would just not be there.  Even existing manual pricing for all AB flights to an absolute value is a fail, as is, since nobody has the time to do it.

The pricing would have to be automatic, based on formulas (if player choses to tinker with the formulas), or default as the system currently has.

As far as connection taking place (passengers transferring flights) it would be completely automatic, the player would not have to do anything.  So player would not have to set up his airline any differently than he currently does.  Passenger connections would offer new strategies (to take advantage of the feature), if player choses to pursue these strategies, but it should pretty much all be invisible to the player, especially a new player.

Once you have these 3 business models in place, you can create a master database for routing with 3 columns: departure-arrival-connection.  There should be limits on connections, such as the next flight must depart within 4 hours to be considered a connecting flight.  Then it's just a matter of populating the database as flights are created.  For the hub/spoke model every permutation would be added within 4 hours and a discount rate could be added to each leg for connections for simplicity.  So if 2 legs are $100 each and a 20% discount (modifiable by the player) for connecting flights you'd have a $160 ticket instead of a $200 one.  For the focus city model a player would have to explicitly add LAX-CHI-NYC as a separate flight number and/or use ABCBA routing where it would be implicit where you can assign price values for those 2 leg flights.  For the point to point carrier, they'd simply have each route added.

I don't think the player strategy should drive the set up of database.  The database should be able to cover all situations regardless of player strategy.

All there is to figure out is how demand from airport A to B is allocated.  The system (I am assuming) will have a table of all AB airport pairs.

Now, suppose there is a demand of 100 pax from NYC squares to SPU (Split, Croatia).  The system will have a table that will have 3 choices:
JFK-SPU
EWR-SPU
LGA-SPU

Based on some magic (examining supply and its attractiveness), it allocates:

JFK-SPU: 70 pax
EWR-SPU: 25 pax
LGA-SPU: 5 pax
(subject to periodic re-allocations)

Let's go ahead with only the JFK-SPU, 70 pax demand, that is now pinned to these 2 airports. 

Nobody flies direct (and even if somebody did, alternatives would have to be considered).  To allocate this demand, the system will have to look up viable combinations, ranked from best to worst (based on number of variables).  Let's say, there are flights with connections exist (are being flown):
JFK-LHR-SPU (l10x JFK-LHR, 1x LHR-SPU, resulting in 10 combinations)
JFK-FRA-SPU (4x JFK-FRA, 2x FRA-SPU, resulting in 8 combinations)
JFK-CDG-SPU
JFK-MUC-SPU
JFK-VIE-SPU
and maybe 10s more.

The system can rank them, and store them in a table.  So for JFK-SPU pair there would be several flight combination candidates for this table.  As far as how many of possibly endless combinations the system stores, the system can limit them based on the pax demand between these airports.

So for example, for 70 pax, between JFK-SPU the system will store top 10 combinations.
For 25 pax demand between EWR-SPU, the system would store 4 combinations.
For 5 pax demand between LGA-SPU, the system would store 2 combination.

Or whatever works.  The system would have to re-evaluate the candidates periodically.

Then, back to JFK-SPU, it is just a matter of allocating the pax between the 10 connecting flight possibilities.  The same table that keeps the list of candidate connecting flights can also keep a tally of how successful they were on the pax allocation.  Let's say there is a top flight combo:
JFK-FRA, Airline XY, 8pm flight, flight #XY001 connecting to
FRA-SPU Airline XY, 12noon flight XY301

This flight was allocated and flown 30 pax.  The row with a combination XY001, XY301 (related to airport pair JFK-SPU) would store this figure of 30 pax. Or it can be used as a running average for past 7 days with a formula:
(old value * 6 + 30 pax) / 7

So we would have 10 records of these with various allocations.  In order to keep the flight combinations fresh, the system can keep top 70% of these 10 (=7) and replace the bottom 30% (=3) and do so periodically.

This can also be used to re-allocate the demand between JFK, EWR and LGA, based on successful completions.  From the above table, we would know that the sum of the 10 flights connecting JFK-SPU completed flights of 60 pax (of available 70).
Sum of EWR-SPU was 25 (of available 25)
Sum of LGA-SPU was 0 (of available 5)

Based on this, the system would reallocate NYC pax demand, shifting demand away from LGA-SPU, with the benefit going mostly to EWR-SPU.

Once you have the database setup, it's just a matter of pulling the flights where departure= and arrival= the specified values.  You are already using a haversine equation for distance, so you just need to modify it to get the angle of the two legs (between 0 and 180) versus the angle of a direct flight (180).  You can then use that value logarithmically to start penalizing connecting flights with extreme connections.  For example, flying NYC to LAX is a 180 degree flight.  Flying NYC to LAX via CHI has maybe a 150 degree angle.  Flying NYC to LAX via Moscow, Russia is going to have an extreme angle of maybe 20 degrees.  The traffic would be distributed across these flights, but the Moscow flight would be penalized exponentially compared to the Chicago flight.  Additionally, since these numbers would remain static, you could add them to the database so they don't have to be calculated each time.  Then it would just be a matter of calculating each flight's pax ratio/value against a sum() of the matching rows.

I see what you are trying to say here, and it seems like a good way to select candidate flight combinations to be considered.  And my recent flight (using frequent flyer miles, on the cheap) would never make it into your candidate database, based on the angles.  Yet, United Airlines (Star Alliance) offered this to me as the cheapest flight (using fewest miles) during peak travel week:
LGA-TOR-SXM
SXM-PTY-JFK

[JumboShrimp]

No, technically it's already all done pretty much (and in a way you described there too). The main issue I am having (and what I wrote there) was figuring out the destinations from each square. The best way to figure _where_ the people from each square wish to go, in order to make it realistic ..

I don't think there would be a problem with somewhat generic allocation, that can be further fine tuned by country to country relations.  Let's say USA-Canada would way above average relations, or Germany-Turkey (due to immigrant population).  So the country to country relation of USA-Canada might be 150%, modifying generic value of 100 for the Canadian to USA squares.  As far as where, within these 2 countries, some of the values collected for squares, as far as business, industrial, tourist destination would take care of that.

Then, the distance between the squares would be a factor.  Let's say there is a sweet spot of 700nm (or whatever) that is the most popular distance flown.  So the squares around that distance would get a bonus, and squares that are, say 70nm or 7000nm apart would be receive less than average demand.  The distance can be a function that is called, and it can be fine tuned.  I don't think the system has to perfectly match the real world.

[LemonButt]

I don't think the player strategy should drive the set up of database.  The database should be able to cover all situations regardless of player strategy.

Then the answer to "how much does this flight cost" needs to be answered.  Also, I am assuming that the existing database is an SQL table with one column for departure airport and one for arrival airport.  In order to determine whether a connecting flight is even possible you'd have to overwhelm the database with SELECT queries, or at least index lookups which is going to be intensive even with the most efficient queries.  For JFK to LAX:

SELECT flight FROM database WHERE dep='JFK' and arr='LAX'

for each player (100+), go through a loop to determine if there is a connecting option:

+ SELECT flight FROM database WHERE dep='JFK' and arr!='LAX' (could return hundreds of rows)

+ SELECT flight FROM database WHERE dep!='JFK' and arr='LAX' (another few hundred rows)

Then you'd have to find figure out which connecting airports are possible, likely with an inner join or similar which is going to use temporary database tables and write to disk anyways.

So from a database engineering/infrastructure standpoint you can execute hundreds to hundreds of thousands/millions of queries to determine connecting pax which is going to remain relatively static and only change when routes are created or just crunch the numbers once and setup a database that will give you the answer with a single query. This is why setting a connecting flights database makes sense.

From an algorithmic standpoint, the function that spits out actual pax distribution is going to require inputs, which are going to rely heavily on price and time/distance or as I'm proposing, angle as it eliminates many other variables such as tech stops, aircraft speed, layover time, etc.  I'm not advocating micromanaging and setting every routes price, but if you set the price of JFK-ORD and ORD-LAX as being $150 each and you only get $200 in revenue because the game decided to connect a pax for $200 instead of 2 pax for $150 each it makes it extremely difficult to actually have a pricing strategy because you don't even know what your pricing.  Same goes with connecting pax pricing--an airline should be able to set the discount level as part of their pricing strategy where a connecting pax pays the price of both legs minus the % discount.  For hub/spoke it would be all connections, for point to point it would be no connections at all, and for focus cities it would be a hybrid where the only connections allowed are those a player explicitly allows (i.e. JFK-ORD-LAX would only be allowed if they explicitly say they will allow connections, which would be at the price of both legs minus the discount %).

Additionally, pax connecting wouldn't be solely because there is no direct flight.  I live between Atlanta and Charlotte.  It's usually far cheaper to drive to Charlotte and connect in Atlanta then fly directly to Atlanta from my home airport.  I just looked at Google Flights and flying from my home airport to Atlanta to Las Vegas is $810.  If I fly from Charlotte to Atlanta to Las Vegas it is $416--a direct flight from Charlotte to Las Vegas is $714.  Those are huge discounts.  In a typical game world the LAX-JFK route would be oversupplied, but that doesn't mean people aren't willing to connect in Chicago at a lower price.  Obviously leisure travelers are more price sensitive than business travelers, but even business travelers have their limits when it comes to price and opportunity cost of connecting--there should be some price point where pax overall will be more likely to connect in ORD than fly direct.

I don't know what the database stats or capacity looks like, but I own/operate a company that uses big data and with connecting flights we're talking about millions of potential combinations and if it isn't setup correctly in the beginning there could be an astronomical amount of resources needed to make it work.

[JumboShrimp]

Then the answer to "how much does this flight cost" needs to be answered.  Also, I am assuming that the existing database is an SQL table with one column for departure airport and one for arrival airport.  In order to determine whether a connecting flight is even possible you'd have to overwhelm the database with SELECT queries, or at least index lookups which is going to be intensive even with the most efficient queries.  For JFK to LAX:

I will post my thoughts on database design and maybe some SQL later (I am very rusty, but used to be good at it).  But in general, the goal is to limit the result sets to something that's manageable.  Here is the rank of the size of the result set based on player conditions:

- allow connections between any players (the biggest set, probably too big to work with)
- limit connections to airline within alliance + manual player code shares (this should be the goal of the design, should be manageable)
- limit connections to airline within alliance (simplified and smaller than above)
- limit connections to within a single airline (smallest result sets)

Code sharing should be automatic.  If 2 airlines agree to a code share, the system will automatically make all of their flights code shared.  This takes the lease amount of player management.  This should automatically apply to airlines within the same alliance.  A player can selectively negotiate a code share with another player, not within the alliance.

[JumboShrimp]

Then the answer to "how much does this flight cost" needs to be answered.  Also, I am assuming that the existing database is an SQL table with one column for departure airport and one for arrival airport.  In order to determine whether a connecting flight is even possible you'd have to overwhelm the database with SELECT queries, or at least index lookups which is going to be intensive even with the most efficient queries.  For JFK to LAX:

SELECT flight FROM database WHERE dep='JFK' and arr='LAX'

for each player (100+), go through a loop to determine if there is a connecting option:

+ SELECT flight FROM database WHERE dep='JFK' and arr!='LAX' (could return hundreds of rows)

+ SELECT flight FROM database WHERE dep!='JFK' and arr='LAX' (another few hundred rows)

Then you'd have to find figure out which connecting airports are possible, likely with an inner join or similar which is going to use temporary database tables and write to disk anyways.

As I said, my SQL is a bit rusty, but figuring out the connecting flights should be relatively easy, with something like:

SELECT some fields
FROM flights as flights1
FROM flights as flights2
WHERE
  flights1.origin = "JFK"
  flights1.destination = flights2.origin AND
  flights2.destination = "LAX" AND
  flights1.player.allianceID = flights2.player.allianceID   // putting restriction that only connection within alliance is possible
ORDER BY
  SomeFunctionToDetermineTotalTime (flights1, flights2)

The SomeFunctionToDetermineTotalTime would be able to determine things like flight arriving at 9am, connecting flight departing at 8am would actually add 7 days to the total time, and would of course go across week boundaries, to know that Monday is after Sunday.

This will get you a nicely sorted list of candidates.
 

So from a database engineering/infrastructure standpoint you can execute hundreds to hundreds of thousands/millions of queries to determine connecting pax which is going to remain relatively static and only change when routes are created or just crunch the numbers once and setup a database that will give you the answer with a single query. This is why setting a connecting flights database makes sense.

Agreed, but, IMO, the records in the database do not have to span the universe (all possibilities), they can be transient, based on the feedback loop I described in the previous post, where the flights combinations that result in no pax being carried will get thrown out and replaced with new candidates from the SQL statement above.

From an algorithmic standpoint, the function that spits out actual pax distribution is going to require inputs, which are going to rely heavily on price and time/distance or as I'm proposing, angle as it eliminates many other variables such as tech stops, aircraft speed, layover time, etc.  I'm not advocating micromanaging and setting every routes price, but if you set the price of JFK-ORD and ORD-LAX as being $150 each and you only get $200 in revenue because the game decided to connect a pax for $200 instead of 2 pax for $150 each it makes it extremely difficult to actually have a pricing strategy because you don't even know what your pricing.  Same goes with connecting pax pricing--an airline should be able to set the discount level as part of their pricing strategy where a connecting pax pays the price of both legs minus the % discount.  For hub/spoke it would be all connections, for point to point it would be no connections at all, and for focus cities it would be a hybrid where the only connections allowed are those a player explicitly allows (i.e. JFK-ORD-LAX would only be allowed if they explicitly say they will allow connections, which would be at the price of both legs minus the discount %).

Pricing can start by simply adding the sum of the prices of the legs, later can be improved by a player customizable formula for pricing, or even later, a pricing overlay can be built.

Additionally, pax connecting wouldn't be solely because there is no direct flight.  I live between Atlanta and Charlotte.  It's usually far cheaper to drive to Charlotte and connect in Atlanta then fly directly to Atlanta from my home airport.  I just looked at Google Flights and flying from my home airport to Atlanta to Las Vegas is $810.  If I fly from Charlotte to Atlanta to Las Vegas it is $416--a direct flight from Charlotte to Las Vegas is $714.  Those are huge discounts.  In a typical game world the LAX-JFK route would be oversupplied, but that doesn't mean people aren't willing to connect in Chicago at a lower price.  Obviously leisure travelers are more price sensitive than business travelers, but even business travelers have their limits when it comes to price and opportunity cost of connecting--there should be some price point where pax overall will be more likely to connect in ORD than fly direct.

I think the actual pax allocation is going to be the most challenging part.  In the current system, you have a pair of airports that have demand, you have a set of flights that provide supply, and it takes one pass in how to allocate this demand to supply, and you are done with the airport pair.

In the scenario of connecting pax, the demand part remains simple, but the supply is complicated.  The supply of a flight JFK-ORD is used for direct flight, but it can also be used for 10s of other connecting flights.

I am a bit at a loss how this can be approached.  I think I had a post here a year or more ago, talking about various buckets, but it will take more than one pass, I believe to get the pax allocation done. 

Basically, the capacity of an aircraft is going to be used for number of routes (direct plus connecting flights) where the second route does not know how much of the capacity was allocated already.  I guess it could be done in one pass, until the capacity is filled up, but that may not be ideal from the supplier POV (player) who's aircraft may fill up with pax from discounted connected routes

I don't know what the database stats or capacity looks like, but I own/operate a company that uses big data and with connecting flights we're talking about millions of potential combinations and if it isn't setup correctly in the beginning there could be an astronomical amount of resources needed to make it work.

As I said, only if the system keeps all of the combinations of routes would the system blow up with too much data.  I don't remember exactly, but there are about 2000 airports, so are already a number of airport combinations (2000 + 1999 + 1998 + ... 1) = 2000 * 2000 / 2 = 2 million, some of which may not be viable or have anybody flying to these airports (reducing the number greatly).

Each one of the airport pairs has a number of possible connections between them to an appropriate number, and not store all the combinations.  For example, if a JFK-ORD flight pretty much always gets filled to the capacity from the direct flight, there is no capacity left, this particular flight would get knocked out from pretty much all of the connecting flight possibilities, through the feedback loop I described in the previous post.

[LemonButt]

I am a bit at a loss how this can be approached.

The main point of setting up the connecting flights database as I described solves this.  You calculate the angle of every connecting flight and divide by 180 degrees (the angle of a direct flight) and call it the angle quotient.  You could raise it to an exponent to get the exponential effect.  Then it's just a matter of a simple "SELECT SUM(quotient) FROM database WHERE..." and then you do a "SELECT quotient FROM database WHERE..." and allocate pax proportionally.

Example:

JFK to LAX is 100 pax demand

direct flight = (180 / 180)^2 = 1.00
connect in Chicago = (150 / 180)^2 = 0.69

The sum is 1.69.  Then you just send 1.00/1.69 = 59% or 59 pax direct and 41% or 41 pax through Chicago.  This of course assumes everything else is equal (the influence of price etc).

The caveat being the only way those flights through Chicago get into the database are if they are automatically added as connections for a hub/spoke or explicitly added by a focus city carrier.  As I stated before, you may not want connecting pax because it makes it near impossible to have a pricing strategy if you don't know what you're pricing.  The database/system I described would create the lowest load possible on the database as the values are static and don't need to be calculated on demand and the flights can be added in real time as they are added versus in processing batches which limit the IOPS and keeps resources free for other processes.  The long SQL statement you built involves creating temporary tables and sort indexes that have to be created on demand which is resource intensive and it goes back to the fact those values are static--they don't change.  A flight connecting in Chicago is always going to have (most of) the same connection values so crunching the numbers longhand every game day would be a waste of resources.  And the database wouldn't have to check for every single possible route (2000 factorial), but would just have to do a simple loop that would further save resources:

SELECT DISTINCT(departure) FROM database

foreach departure: SELECT DISTINCT(arrival) FROM database WHERE departure='departure'

This means the only flights getting processed are the flights that exist.  I assume the current system sets all traffic for routes to zero and then just calculates pax allocation in this manner.  And hard disk space is cheap--I seriously doubt storage is the constraint sami is facing, but compute resources.  In the end, the simplest system is going to be the least resource intensive and least prone to bugs.  When you break it down, connecting pax is actually very simple if you don't overthink it.

[slither360]

For pricing, I'd suggest a simple 2-tiered model - players should set a separate "point to point" price and a "connection" price for each flight.

So for fly XY001 from AAA-BBB, there would be a price (let's say $80) for passengers flying AAA-BBB, and a separate price (let's say $50) for passengers flying AAA-BBB-CCC or ZZZ-AAA-BBB. If the airline wants to incentivize connections (to increase loads on that particular flight or other flights in the airline's network), they could set a lower price for connecting passengers. If the airline would rather focus purely on point to point traffic, they could set the connecting price at exactly the same as point to point price.

To calculate the fare for AAA-BBB-CCC, all the system would need to do is add the connecting passenger price for AAA-BBB and BBB-CCC.





As far as passenger allocation, I think distance is fairly irrevant. What matters more is timing and price. If flying JFK-LAX via MIA takes less time than JFK-LAX via ORD (e.g. due to the way connections are timed or due to the speed of the airplane), then passengers would prefer the shorter connection. If flying JFK-LAX via MIA is half the price of JFK-LAX via ORD, passengers would also prefer that flight. Basing allocation on geographic information like distance or the angle doesn't reflect how passengers actually make decisions when choosing flights.



As far as creating a database of connecting possibilities, every time a new flight is scheduled, the system can go through and compile all the possible connections with that flight. A "possible connection" could be defined as any flight departing within 4 hours of arrival, or any flight which arrived 4 hours or less before departure.

The system could then store the top connection possibilities for each route pair. For example, lets say the fastest option on BOS-YVR is 8 hours. The system could store all possible connections that take 12 hours or less (1.5 * 8 hours). That arbitrary factor could increase/decrease depending on demand.

[JumboShrimp]

For pricing, I'd suggest a simple 2-tiered model - players should set a separate "point to point" price and a "connection" price for each flight.

So for fly XY001 from AAA-BBB, there would be a price (let's say $80) for passengers flying AAA-BBB, and a separate price (let's say $50) for passengers flying AAA-BBB-CCC or ZZZ-AAA-BBB. If the airline wants to incentivize connections (to increase loads on that particular flight or other flights in the airline's network), they could set a lower price for connecting passengers. If the airline would rather focus purely on point to point traffic, they could set the connecting price at exactly the same as point to point price.

To calculate the fare for AAA-BBB-CCC, all the system would need to do is add the connecting passenger price for AAA-BBB and BBB-CCC.

Or even better, AAA-BBB can be defined as 100% of default for direct flights and, say 80% of default for connecting price (to get away from the insanity of absolute, non-inflation adjusted prices the current system has).

As far as passenger allocation, I think distance is fairly irrevant. What matters more is timing and price. If flying JFK-LAX via MIA takes less time than JFK-LAX via ORD (e.g. due to the way connections are timed or due to the speed of the airplane), then passengers would prefer the shorter connection. If flying JFK-LAX via MIA is half the price of JFK-LAX via ORD, passengers would also prefer that flight. Basing allocation on geographic information like distance or the angle doesn't reflect how passengers actually make decisions when choosing flights.

Again, agreed.  The geographic information, angle of connecting flights, makes absolutely no difference to a possible passenger.  Let's say a passenger is going from Albany, NY to LAX, connecting in one of NYC airports creates highly unfavorable "angle", yet it is perfectly fine, maybe even preferable way for most pax to fly, since there are probably more frequent flights, less time waiting for connecting flight.

As far as creating a database of connecting possibilities, every time a new flight is scheduled, the system can go through and compile all the possible connections with that flight. A "possible connection" could be defined as any flight departing within 4 hours of arrival, or any flight which arrived 4 hours or less before departure.

The system could then store the top connection possibilities for each route pair. For example, lets say the fastest option on BOS-YVR is 8 hours. The system could store all possible connections that take 12 hours or less (1.5 * 8 hours). That arbitrary factor could increase/decrease depending on demand.

I think, the time to wait for possible connection is also something that should not determine if a flight combination is a good candidate.  Far more relevant is the total time the flight it takes, from departure to arrival.  How much of that time is spent in the airplane vs. at the gate waiting for connecting flight is irrelevant.

JFK-LAX via MIA may get a very quick connecting flight, yet it can still takes more time than a far longer flight via DEN, LAS with time waiting for connection for more than 8 hours.

The SQL pseudo-code I posted will give you a neatly sorted list, mostly affected by total time, without taking into consideration the time it takes to wait for connecting flight.

[slither360]

I think, the time to wait for possible connection is also something that should not determine if a flight combination is a good candidate.  Far more relevant is the total time the flight it takes, from departure to arrival.  How much of that time is spent in the airplane vs. at the gate waiting for connecting flight is irrelevant.

JFK-LAX via MIA may get a very quick connecting flight, yet it can still takes more time than a far longer flight via DEN, LAS with time waiting for connection for more than 8 hours.

The SQL pseudo-code I posted will give you a neatly sorted list, mostly affected by total time, without taking into consideration the time it takes to wait for connecting flight.

I guess my phrasing wasn't very good, but that's exactly what I meant. The system should figure out the fastest possible routing (from initial departure to final arrival) and store all other flight possibilities that come within a reasonable time difference.

Demand would also have a big impact on how many possible flights are stored. If demand is only 5 pax per day, then storing 100s of flight combinations is unnecessary. If demand is 5000 per day, then storing more combinations is a good idea