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

0 Members and 1 Guest are viewing this topic.

Offline skoormit (OP)

  • Commodore
  • **********
  • Posts: 764
  • Thanked: 310 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: Laurence, Demonides, Garfunkel, Zincat, Zap0

Offline skoormit (OP)

  • Commodore
  • **********
  • Posts: 764
  • Thanked: 310 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

Offline db48x

  • Commodore
  • **********
  • d
  • Posts: 641
  • Thanked: 200 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)

  • Commodore
  • **********
  • Posts: 764
  • Thanked: 310 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.
 

Offline db48x

  • Commodore
  • **********
  • d
  • Posts: 641
  • Thanked: 200 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)

  • Commodore
  • **********
  • Posts: 764
  • Thanked: 310 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.
 

Offline db48x

  • Commodore
  • **********
  • d
  • Posts: 641
  • Thanked: 200 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

  • Vice Admiral
  • **********
  • Posts: 1112
  • Thanked: 298 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 »
 

Offline db48x

  • Commodore
  • **********
  • d
  • Posts: 641
  • Thanked: 200 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.
 
The following users thanked this post: TMaekler

Offline Llamageddon

  • Sub-Lieutenant
  • ******
  • L
  • Posts: 118
  • Thanked: 15 times
Re: Spreadsheet: Skoormit's Terraforming Planner
« Reply #9 on: October 13, 2020, 02:18:29 PM »
This looks really well made, thanks for sharing.

A key would be really helpful. I'm really lost about what to put in each input field. I'm confused by most of the abbreviations in the first fields; what are Bkm, Tfly, MS, Cur and LG? I assume TF is your terraforming rate. Not sure what Q, Scr and TY are either.

Sorry if I am being a bit dim. I hope these aren't annoying questions. I've terraformed before but I think this is way more in-depth than I have knowledge of.
Currently using Aurora 1.12 - Unmodded
 

Offline Ogamaga

  • Leading Rate
  • *
  • O
  • Posts: 14
  • Thanked: 1 times
Re: Spreadsheet: Skoormit's Terraforming Planner
« Reply #10 on: November 01, 2020, 10:20:43 AM »
I would also appreciate a glossary and guide for this, especially the query thing.
 

Offline Marski

  • Commander
  • *********
  • Posts: 389
  • Thanked: 137 times
Re: Spreadsheet: Skoormit's Terraforming Planner
« Reply #11 on: May 02, 2021, 02:23:39 PM »
I would also appreciate a glossary and guide for this, especially the query thing.
Same, I can't tell where you're supposed to start slapping down information without breaking everything.
 

Offline skoormit (OP)

  • Commodore
  • **********
  • Posts: 764
  • Thanked: 310 times
Re: Spreadsheet: Skoormit's Terraforming Planner
« Reply #12 on: May 02, 2021, 03:12:31 PM »
I would also appreciate a glossary and guide for this, especially the query thing.
Same, I can't tell where you're supposed to start slapping down information without breaking everything.

The query returns data in the same order as the columns.
Copy the query output, select the first cell under the Sys column header, and paste the data.
(Then delete any leftover rows from earlier data.)

I'm not up for writing a full guide on how to query the database.
Briefly, though: download DB Browser for SQLLite, use it to open Aurora.db, click the Execute SQL tab, paste the query, hit F5, click the top left cell of the results grid to select all the data, hit ctrl-c to copy to your clipboard, then paste it into the sheet.

Let me know what in particular about the sheet you'd like more information on.

KEEP IN MIND: the sheet was written for the old version of the terraforming model, which treated anti-greenhouse gases differently than it does now. So for any body that needs to be cooled off, the math will be wrong--you will need more AGH than it says.
« Last Edit: March 07, 2022, 08:58:13 AM by skoormit »
 

Offline hostergaard

  • Warrant Officer, Class 2
  • ****
  • h
  • Posts: 73
  • Thanked: 27 times
Re: Spreadsheet: Skoormit's Terraforming Planner
« Reply #13 on: October 04, 2021, 08:41:22 AM »
Fantastic work, appreciate it. But there is something of about temperature calculation, I am not sure what exactly, right now my best guess it has something to do with water vapor or tidal lock.

Some of my planets the target atmospheric pressure for green house gasses is way higher than it should be, like, twice the amount leading to a hundred degree C surface temp. I am working on figuring out where it is, right now I found the issue to persist in TargetTemp and TempChangeNeeded. Trying to figure out where those cells are wrong, one planet I have that have perfect temperature ( and tidal lock) but it tells me the target temp is +70!

Gonna see if I can fix it, or maybe make my sheet. But if you ever gonna go around to fixing it, figuring out how to include AGH would be good too.

Edit: figured out the issue; the race max and mins in the upper left corner is set up wrong for humans, probably some costum race. Still, there is something weird since it was recommending the planet be 100 degrees celcius while the costum race was comfortable in that temperature. Might have something do with how its calculated versus the data in the database of my standard humans.
« Last Edit: October 05, 2021, 05:37:57 AM by hostergaard »
 

Offline Garfunkel

  • Registered
  • Admiral of the Fleet
  • ***********
  • Posts: 2781
  • Thanked: 1048 times
Re: Spreadsheet: Skoormit's Terraforming Planner
« Reply #14 on: October 09, 2021, 01:46:05 AM »
I wonder if Skoormit, or someone else capable, could make a simplified version of this, something similar to the old VB6 terraforming sheet, where you just put in race detail and body details and it spits out the terraforming needed.