Aurora 4x
C# Aurora => C# Utilities => Topic started by: skoormit 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 (https://docs.google.com/spreadsheets/d/1OujmsH8GaMLAv_kR1otmBGe7VlybB8dZVnpMvM4d-Hs/edit?usp=sharing). 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.
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 (http://aurorawiki.pentarch.org/index.php?title=Terraforming#C.23) 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.
-
By request, I imported the spreadsheet to Google Sheets.
I've added the link to the top of my original post.
-
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?
-
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.
-
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…
-
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.
-
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.
-
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... .
-
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.
-
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.
-
I would also appreciate a glossary and guide for this, especially the query thing.
-
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.
-
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.
-
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.
-
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.
-
I have been mulling over trying to make something like that, or maybe make a utility. Right now I have just been annotating a copy of the sheet with explanations as I starting to understand parts of it.
Edit: I could share my copy of the spreadsheet that I put notes on, and make it open for anyone to work on. As long Skoormit is ok with it and don't complain, its his work so I will respect his wishes on it.
https://docs.google.com/spreadsheets/d/1QoNy-6DOzdf7XZiN-WHINdmYexru2Kjc8o1EZQlqDD0/edit?usp=sharing
Here it is, its open for anyone edit. So trolls can frakk around, but I guess then I can just roll it back, hopefully this forum is small enough it won't be an issue.
So, right now I am just working on noting everything to get a clear picture of how it works. Next step will be to make it more use friendly and ad something like what Garfunkel suggest (TF work seems to be that, tought its a bit unclear, working to figure out how it works). Feel free to add new sheets and work on a solution, but avoid messing around too much with the sheets Skoormit made so as not accidentally mess it up (not that its forbidden, it should be worked on but be smart about it!), then its better if you make your own copy to mess about whit and if you make something really good then copy it over to the shared sheet. Basically, its open for anyone to optimize and work on, I trust people here have the wisdom and discretion to know what to do and not do.
-
Have you thought much about how to update it for 2.0? I am looking at my (edited) copy and wondering where to begin, and how to measure the difficulty of handling a planet where the hydrosphere freezes for half of the year.
-
Have you thought much about how to update it for 2.0? I am looking at my (edited) copy and wondering where to begin, and how to measure the difficulty of handling a planet where the hydrosphere freezes for half of the year.
Honestly, I don't plan to ever play with eccentric orbits.
But if I did, let me think, what would I need to do with this.
I guess I'd never really be interested in the current CC. I just care about the max and min.
Well, unless this planet has a very long orbital period.
In which case I might care a lot about the next, say, ten years. I still need to know what happens in the decades after that, but timing really matters.
However, the timing issue might not matter all that much in practice, because nearly all reasonably terraformable planets have reasonably short orbits (no more than a few years).
The ones that are reasonably terraformable and have very long orbits are probably orbiting an immense star, which is going to make the distances involved so large that I'd probably never consider colonizing it anyway.
So, assume I just ignore timing, because the orbit is far shorter than a typical terraforming timeline.
I would need to duplicate every existing column that refers to surface temperature, and calculate once for max distance and once for min distance.
Actually, might be simpler...I haven't looked at the formula that calculates base temperature from the orbital properties, but orbital distance is surely a factor. Probably an inverse square relationship?
Whatever it is, calculate the base temperature at min distance, and use that plus the eccentricity to infer the temperature at max distance.
Now I just need my "how much do I need to cool the place off" columns to consider the temperature at min distance, and my "how much do I need to heat the place up" columns to consider the temperature at max distance.
In nearly all cases, I'd expect only one set of columns to produce a positive number. Meaning I only need to either heat the place up or cool the place down, and I just need to make sure that I don't do so much of one or the other that I make things worse on the other side of the orbit.
If that's the case, or if both columns show a positive number, then there is no way to get this place to CC 0 permanently.
So, figure out what needs to be done so that the min and max CCs (for temperature) match, and that's my target CC.
In fact, the logic might be simpler overall if you just start from here.
What's the min and max temp CC right now, and how much can I reduce the higher one before the two are equal (or until I've changed the temp as much as it is possible to change it)?
In ideal cases, you can get them both to 0.
I don't even want to think about the hydrosphere issue.
My hunch is that only a vanishingly small number of otherwise desirable targets will be crossing the freezing threshold post-terraforming. Although I'm assuming roughly Human temperature ranges. Certainly one could play a race that likes colder places.
Maybe just figure out, based on orbital properties, how much hydro will freeze per orbit, and add enough extra hydro so that the hydro CC won't exceed the target CC.
-
Maybe just figure out, based on orbital properties, how much hydro will freeze per orbit, and add enough extra hydro so that the hydro CC won't exceed the target CC.
Rereading this, I realize that this is a good example of something that is simple to describe in English but difficult to actually calculate.
"How much hydro will freeze per orbit" is just the rate at which hydro freezes out of the atmosphere in Aurora (when the surface is cold enough for water to freeze) times the amount of time the body spends below that temperature during an orbit.
You'll need to find the orbital distance at which the surface temperature equals the freezing point of water, then figure out how much time the orbit spends from that orbital distance to the aphelion. Double that, and that's how much time water spends freezing out of the hydrosphere.
The orbital distance freezing point is easy enough, given that you already have the calculations for surface temp based on everything, including orbital distance.
Just set that equal to zero and solve for the distance term.
The proportion of the orbital duration that the body spends at that distance or greater is a question for calculus.
It's not as simple as the length of the ellipse segment over the circumference of the ellipse, because the orbital speed changes with the orbital distance.
Instead, it's the area of that portion of the ellipse over the area of the entire ellipse.
I'm confident that, around the time I finished calculus in school, I could have solved such a question in less than an hour.
But that was a long time ago, and besides, now we have google (https://www.piping-designer.com/index.php/mathematics/geometry/plane-geometry/2548-ellipse-sector).
-
I just downloaded the spreadsheet, but I think I found a bug in it. One of my planets has a carbon dioxide level of 0.0304, but for some reason the SQL query is returning that as a toxic gas instead of a greenhouse gas.
-
I just downloaded the spreadsheet, but I think I found a bug in it. One of my planets has a carbon dioxide level of 0.0304, but for some reason the SQL query is returning that as a toxic gas instead of a greenhouse gas.
Not a bug, CO2 above a certain proportion is considered to be a toxic gas in the C# version - this is a change from the old VB6 version.
Greenhouse gas is Aestusium and anti-greenhouse gas is Frigusium, both some TN handwavium substances added in C#.
-
I just downloaded the spreadsheet, but I think I found a bug in it. One of my planets has a carbon dioxide level of 0.0304, but for some reason the SQL query is returning that as a toxic gas instead of a greenhouse gas.
Not a bug, CO2 above a certain proportion is considered to be a toxic gas in the C# version - this is a change from the old VB6 version.
Greenhouse gas is Aestusium and anti-greenhouse gas is Frigusium, both some TN handwavium substances added in C#.
Note:
All toxic gases have a concentration threshold below which the game does not count them as toxic.
The spreadsheet does not take this threshold into account; any presence of a toxic gas is reported as toxic.
For most toxic gases, the concentration threshold is so low that there is only a trivial difference between removing the gas down to the safe level and just removing all the gas.
CO2 has the highest threshold, at 0.5% of total atmosphere.
In extreme cases you could save a non-trivial amount of terraforming time by leaving the extra 0.5% CO2.
In those cases, you can calculate the CO2 amount that you can safely leave in the atmo (when all tforming is done), and set your Maximum Atm number on the Environment tab to that number when removing CO2. (Although you might need to subtract 0.0001 because of rounding issues.)
Methane and Hydrogen thresholds are 0.05%.
All the other toxic gas thresholds are lower than that.
-
I know this topic has been dormant for over a year, but I have been searching everywhere (without success) for exactly this:
Actually, might be simpler. . . I haven't looked at the formula that calculates base temperature from the orbital properties, but orbital distance is surely a factor. Probably an inverse square relationship?
Whatever it is, calculate the base temperature at min distance, and use that plus the eccentricity to infer the temperature at max distance.
Using just information from the database, I am lost on how to calculate min and max temperature.
Has anyone stumbled accross the calculations?