Author Topic: Spreadsheet: Skoormit's Terraforming Planner  (Read 553 times)

0 Members and 1 Guest are viewing this topic.

Offline skoormit (OP)

  • Captain
  • **********
  • Posts: 466
  • Thanked: 173 times
Spreadsheet: Skoormit's Terraforming Planner
« on: August 14, 2020, 10:29:12 AM »
By request from another thread, here is the spreadsheet I use for figuring out what to terraform and how to terraform it.
EDIT: Here's the same spreadsheet imported to Google Sheets. Not thoroughly tested. Let me know if you encounter any issues.

It gives optimal target atmosphere conditions from any given starting conditions (including race parameters), and also shows how long it will take (in "TFIyrs": Terraforming Installation Years) given your current tech level and naval admin bonus. (Note: the input fields for commander bonus are not factored in.)
It also does some aggregating of mineral deposit data for reporting/reviewing purposes. Useful for deciding which bodies to mine next.
The second sheet calculates target water vapor pressure based on your terraforming rate (so that you don't waste time adding too much), and gives ETAs for finishing vapor, finishing other gases, and vapor evaporating.

There may be far more here than you really want, but you can pare it down as you like.

The easiest way to get the data into the spreadsheet is to run a query against the database.
I've included the query on the third sheet if you'd like to do that.
DB Browser for SQLLite is the free tool I use.

Known limitations:
The SQL query returns only the information known to the most recently created player race. If you aren't playing the most recent game you created, you'll need to manually specify the raceid (or gameid) you want in the query.

The query returns names for uncolonized bodies based on my own naming convention.
Quote
SYS-X#[M#] where SYS is the first three letters of the system name, X is the star letter, # is the order of the planet from the star, and M# is the order of the moon from the planet.
Examples:
Earth = SOL-A3
Luna = SOL-A3M1
Comets and asteroids are handled similarly, using SYS-Com# or SYS-XAst#.
Note that this only applies to uncolonized bodies. If the body is colonized, the name of the colony is used.

The terraforming calculations do not include the albedo increase for a melting ice sheet. I may add that at some point. If you don't want to wait, you can factor it in. The change is .0015 * Hydro extent (at the point in time that the sheet melts).

The calculations are based on the terraforming model for 1.11. The model will change somewhat in 1.12, specifically the mechanics for anti-greenhouse gases. I'll post an update after 1.12 hits.

The calculation treats all toxic as having a colony cost of 3.0 and no safe level allowed. This simplifies things, but gives you very slightly suboptimal results. You can leave some amount of toxic gases in the atmosphere to get done terraforming slightly faster.


DISCLAIMER:
This was designed for personal use, so expect it to contain some things that are not self-explanatory.

I'm happy to answer any questions.
And if you find any errors, I would love to know.
« Last Edit: August 17, 2020, 11:36:21 AM by skoormit »
 
The following users thanked this post: Demonides, Zincat

Offline skoormit (OP)

  • Captain
  • **********
  • Posts: 466
  • Thanked: 173 times
Re: Spreadsheet: Skoormit's Terraforming Planner
« Reply #1 on: August 17, 2020, 09:18:30 AM »
By request, I imported the spreadsheet to Google Sheets.
I've added the link to the top of my original post.
 
The following users thanked this post: Zincat

Online db48x

  • Lt. Commander
  • ********
  • d
  • Posts: 281
  • Thanked: 63 times
Re: Spreadsheet: Skoormit's Terraforming Planner
« Reply #2 on: August 20, 2020, 08:16:38 PM »
Skoormit, care to check my math? I've modified the sheet to calculate the gain in manufacturing population from terraforming.

First I added two columns at the end of the table, CurMaxMfgAtPop and TFMaxMfgAtPop, both with this formula:

    =IF(RC[-115]>=1.7,MIN(RC1,((19-RC[-115])/4.457)^4),RC1)

Then I added two columns between MaxP(m) and Bkm called CurMfg(m) and TFMfg(m), with this formula:

    =MAX(0,RC[120]*(1-(0.05*(RC[5]+1))-MIN(0.7,(RC[120]/1000)^(1/4))))

And finally I changed the TFIy column to ?mfg/TFIy and gave it this formula:

    =IF(RC[2]=0,0,IF(OR(RC[-4]=0,RC[89]=0,RC[2]<=RC[3],RC[3]>=4),-1,(RC[-2]-RC[-3])/(RC[89]/_ATMPerTFPerYear*(RC[7]^2/_EarthDiam^2))))

Bigger numbers represent more gain and/or less effort. The numbers look plausible to me, but you already know more about it than I do. What do you think?
 

Offline skoormit (OP)

  • Captain
  • **********
  • Posts: 466
  • Thanked: 173 times
Re: Spreadsheet: Skoormit's Terraforming Planner
« Reply #3 on: August 20, 2020, 09:17:17 PM »
Skoormit, care to check my math?

It is hard to tell just by looking at the formulas here.
Unfortunately, when I try to open the file, I get an error message that the file is corrupt.
 

Online db48x

  • Lt. Commander
  • ********
  • d
  • Posts: 281
  • Thanked: 63 times
Re: Spreadsheet: Skoormit's Terraforming Planner
« Reply #4 on: August 21, 2020, 12:59:30 AM »
Skoormit, care to check my math?

It is hard to tell just by looking at the formulas here.
Unfortunately, when I try to open the file, I get an error message that the file is corrupt.

What are you opening it in? I saved it in LibreOffice 6.4.5.2…
 

Offline skoormit (OP)

  • Captain
  • **********
  • Posts: 466
  • Thanked: 173 times
Re: Spreadsheet: Skoormit's Terraforming Planner
« Reply #5 on: August 21, 2020, 08:29:35 AM »
Skoormit, care to check my math?

It is hard to tell just by looking at the formulas here.
Unfortunately, when I try to open the file, I get an error message that the file is corrupt.

What are you opening it in? I saved it in LibreOffice 6.4.5.2…

Excel.
If I have some time today I'll get LibreOffice.
 

Online db48x

  • Lt. Commander
  • ********
  • d
  • Posts: 281
  • Thanked: 63 times
Re: Spreadsheet: Skoormit's Terraforming Planner
« Reply #6 on: August 21, 2020, 01:14:09 PM »
I fixed the error your found, and I updated the score column's formula to:

    =IF(OR(RC[-4]=0,RC[5]=""),-1,IF(RC[89]=0,0,1000*(RC[-2]-RC[-3])/(RC[89]/_ATMPerTFPerYear*(RC[7]^2/_EarthDiam^2))))

and renamed it to ?mfg(k)/TFIy, so the units are now thousands of manufacturing population gained per TFIy.
 

Offline TMaekler

  • Commodore
  • **********
  • Posts: 713
  • Thanked: 132 times
Re: Spreadsheet: Skoormit's Terraforming Planner
« Reply #7 on: September 15, 2020, 05:48:29 AM »
I am wondering if this could be included in the game. I mean an automation for terraforming. You enter the desired target values for the different gasses and when you have what you are aiming for, just do a one click and the automation then begins to pump in the gasses according to your tech level. This sheet helps a lot to minimize micromanagement, but having it in game would be even nicer ;-)

Lets think this through: Steve adds fields for target atmosphere calculations. You can enter all necessary gasses there and the program tells you if you can reach your desired outcome. So you basically create the target atmosphere by entering and fiddling with the numbers. Once you have a solution that gets you to a level 0 world, you can click on a button: Terraforming. That button creates the necessary steps to switch from current to planned atomosphere - gas by gas and saves them in a database, that then can be worked upon by the atmosphere processors, gas by gas. When done your planet is terraformed and you had way less trouble fine-tuning the atmosphere back and forth... .
« Last Edit: September 15, 2020, 05:53:15 AM by TMaekler »
 

Online db48x

  • Lt. Commander
  • ********
  • d
  • Posts: 281
  • Thanked: 63 times
Re: Spreadsheet: Skoormit's Terraforming Planner
« Reply #8 on: September 15, 2020, 10:23:31 AM »
Yea, I agree. I think a queue interface would be a lot more usable and discoverable than what we currently have. Terraforming is basically navigating a 5-dimensional configuration space, but once you've devised a route through that space that gets to your goal and satisfies any other criteria you have, it would be nice to write it down somewhere and not have to worry about forgetting it.

Also, I've been meaning to put in a suggestion that the terraforming tab include all the climate information about the planet, including things like the plate tectonics level that you can't change.
 

 

Sitemap 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72