Exploration module - create column for estimated scan values in star and body tables

[Daniel-J-Mason]

Create formulae for estimated scan values for both star and body, use generated column to calculate this information.

[00Chaotic]

I've asked around and it seems this forum post is still valid, so we can use that to determine scan value estimations.

Considering the exploration data values are based on body type, a better approach may be to create a static table or mapping for scan values based on body type, rather than recording the scan value for every body.

From what I understand, this value is intended to be used for generating exploration routes similar to Spansh's Road to Riches functionality. If this is the case, with the proposed approach, we would retrieve the body type of the relevant body with a JOIN to the scan values table for the respective scan value depending on the level of scanning performed (FSS vs DSS).

This would remove the need for an extra column in the body and star tables, which realistically have billions of entries. Thoughts?

[Daniel-J-Mason]

I agree given we cant do true virtual columns on Postgres at the moment that it's millions of entries.

My only note to that is that the booleans mapped and discovered impact price. First for each gives a bonus of 30%. And we need to quickly index those prices. Is it going to be fast enough if we have to run that calculation every time we construct a route?

[00Chaotic]

I was under the assumption that the entries we have info on are already discovered/mapped since the data comes from player journals, and that any discovered/mapped booleans we receive would be specific to the user whose journal is being read. Is this not the case?

I'm not sure how to measure whether it'll be fast enough to run the calculations but it would require an additional check for each body.

Edit: You're right, I believe only around 80,000,000 of the 400 billion systems have been discovered so far, so we most likely don't have billions of records yet.

[Daniel-J-Mason]

It seems we get the data on system honk. Database entries show cases of both being false.

[00Chaotic]

I tested it in-game by honking a system I hadn't already 100% FSS'ed and the below is the journal event, which doesn't include any body data:

{ "timestamp":"2023-11-14T23:28:56Z", "event":"FSSDiscoveryScan", "Progress":0.200155, "BodyCount":10, "NonBodyCount":0, "SystemName":"Scorpii Sector ZE-A d160", "SystemAddress":5508052289907 }

However you're right, the WasMapped field is about whether the body was ever mapped by anyone, rather than the specific player. Journal entries from FSS scanning three bodies:

{ "timestamp":"2023-11-14T23:33:57Z", "event":"FSSDiscoveryScan", "Progress":0.200155, "BodyCount":10, "NonBodyCount":66, "SystemName":"Scorpii Sector ZE-A d160", "SystemAddress":5508052289907 }
{ "timestamp":"2023-11-14T23:34:04Z", "event":"Scan", "ScanType":"Detailed", "BodyName":"Scorpii Sector ZE-A d160 8", "BodyID":11, "Parents":[ {"Star":0} ], "StarSystem":"Scorpii Sector ZE-A d160", "SystemAddress":5508052289907, "DistanceFromArrivalLS":878.178841, "TidalLock":false, "TerraformState":"Terraformable", "PlanetClass":"High metal content body", "Atmosphere":"thick nitrogen atmosphere", "AtmosphereType":"Nitrogen", "AtmosphereComposition":[ { "Name":"Nitrogen", "Percent":99.937935 } ], "Volcanism":"minor metallic magma volcanism", "MassEM":2.136519, "Radius":7607296.000000, "SurfaceGravity":14.714840, "SurfaceTemperature":275.787628, "SurfacePressure":1042700.250000, "Landable":false, "Composition":{ "Ice":0.000000, "Rock":0.676739, "Metal":0.323261 }, "SemiMajorAxis":263610726594.924927, "Eccentricity":0.001334, "OrbitalInclination":0.186275, "Periapsis":232.057391, "OrbitalPeriod":67620522.379875, "AscendingNode":-79.388501, "MeanAnomaly":344.869589, "RotationPeriod":111209.747216, "AxialTilt":-0.211512, "WasDiscovered":true, "WasMapped":true }
{ "timestamp":"2023-11-14T23:34:09Z", "event":"ScanBaryCentre", "StarSystem":"Scorpii Sector ZE-A d160", "SystemAddress":5508052289907, "BodyID":1, "SemiMajorAxis":29933876991.271973, "Eccentricity":0.000026, "OrbitalInclination":0.007276, "Periapsis":79.473933, "OrbitalPeriod":2587488.055229, "AscendingNode":-167.437983, "MeanAnomaly":119.569641 }
{ "timestamp":"2023-11-14T23:34:09Z", "event":"Scan", "ScanType":"Detailed", "BodyName":"Scorpii Sector ZE-A d160 2", "BodyID":3, "Parents":[ {"Null":1}, {"Star":0} ], "StarSystem":"Scorpii Sector ZE-A d160", "SystemAddress":5508052289907, "DistanceFromArrivalLS":99.961278, "TidalLock":true, "TerraformState":"", "PlanetClass":"High metal content body", "Atmosphere":"", "AtmosphereType":"None", "Volcanism":"", "MassEM":0.148591, "Radius":3382660.000000, "SurfaceGravity":5.175902, "SurfaceTemperature":705.426392, "SurfacePressure":0.000000, "Landable":true, "Materials":[ { "Name":"iron", "Percent":22.899694 }, { "Name":"nickel", "Percent":17.320370 }, { "Name":"sulphur", "Percent":16.485455 }, { "Name":"carbon", "Percent":13.862560 }, { "Name":"manganese", "Percent":9.457340 }, { "Name":"phosphorus", "Percent":8.875049 }, { "Name":"vanadium", "Percent":5.623368 }, { "Name":"arsenic", "Percent":2.174290 }, { "Name":"tin", "Percent":1.482859 }, { "Name":"mercury", "Percent":0.999992 }, { "Name":"technetium", "Percent":0.819030 } ], "Composition":{ "Ice":0.000000, "Rock":0.676736, "Metal":0.323264 }, "SemiMajorAxis":45063347.816467, "Eccentricity":0.124416, "OrbitalInclination":-2.001024, "Periapsis":57.274176, "OrbitalPeriod":339799.356461, "AscendingNode":74.779278, "MeanAnomaly":209.541393, "RotationPeriod":439751.055818, "AxialTilt":0.507493, "WasDiscovered":true, "WasMapped":true }
{ "timestamp":"2023-11-14T23:34:13Z", "event":"Music", "MusicTrack":"Supercruise" }
{ "timestamp":"2023-11-14T23:34:13Z", "event":"Scan", "ScanType":"Detailed", "BodyName":"Scorpii Sector ZE-A d160 1", "BodyID":2, "Parents":[ {"Null":1}, {"Star":0} ], "StarSystem":"Scorpii Sector ZE-A d160", "SystemAddress":5508052289907, "DistanceFromArrivalLS":99.774926, "TidalLock":true, "TerraformState":"", "PlanetClass":"High metal content body", "Atmosphere":"", "AtmosphereType":"None", "Volcanism":"", "MassEM":0.220193, "Radius":3829437.500000, "SurfaceGravity":5.984714, "SurfaceTemperature":705.426392, "SurfacePressure":0.000000, "Landable":true, "Materials":[ { "Name":"iron", "Percent":21.954933 }, { "Name":"nickel", "Percent":16.605791 }, { "Name":"sulphur", "Percent":15.805322 }, { "Name":"carbon", "Percent":13.290639 }, { "Name":"chromium", "Percent":9.873862 }, { "Name":"phosphorus", "Percent":8.508895 }, { "Name":"vanadium", "Percent":5.391367 }, { "Name":"germanium", "Percent":4.661277 }, { "Name":"niobium", "Percent":1.500503 }, { "Name":"tin", "Percent":1.421682 }, { "Name":"antimony", "Percent":0.985740 } ], "Composition":{ "Ice":0.000000, "Rock":0.676736, "Metal":0.323264 }, "SemiMajorAxis":30409756.302834, "Eccentricity":0.124416, "OrbitalInclination":-2.001024, "Periapsis":237.274170, "OrbitalPeriod":339799.356461, "AscendingNode":74.779278, "MeanAnomaly":209.546294, "RotationPeriod":535318.500665, "AxialTilt":0.050446, "WasDiscovered":true, "WasMapped":true }

Just for reference, here are the journal entries related to mapping a body:

{ "timestamp":"2023-11-14T23:43:27Z", "event":"SAAScanComplete", "BodyName":"Scorpii Sector ZE-A d160 1", "SystemAddress":5508052289907, "BodyID":2, "ProbesUsed":4, "EfficiencyTarget":6 }
{ "timestamp":"2023-11-14T23:43:28Z", "event":"Music", "MusicTrack":"Supercruise" }
{ "timestamp":"2023-11-14T23:43:29Z", "event":"ScanBaryCentre", "StarSystem":"Scorpii Sector ZE-A d160", "SystemAddress":5508052289907, "BodyID":1, "SemiMajorAxis":29933876991.271973, "Eccentricity":0.000026, "OrbitalInclination":0.007276, "Periapsis":79.473933, "OrbitalPeriod":2587488.055229, "AscendingNode":-167.437983, "MeanAnomaly":119.647530 }
{ "timestamp":"2023-11-14T23:43:29Z", "event":"Scan", "ScanType":"Detailed", "BodyName":"Scorpii Sector ZE-A d160 1", "BodyID":2, "Parents":[ {"Null":1}, {"Star":0} ], "StarSystem":"Scorpii Sector ZE-A d160", "SystemAddress":5508052289907, "DistanceFromArrivalLS":99.774385, "TidalLock":true, "TerraformState":"", "PlanetClass":"High metal content body", "Atmosphere":"", "AtmosphereType":"None", "Volcanism":"", "MassEM":0.220193, "Radius":3829437.500000, "SurfaceGravity":5.984714, "SurfaceTemperature":705.426392, "SurfacePressure":0.000000, "Landable":true, "Materials":[ { "Name":"iron", "Percent":21.954933 }, { "Name":"nickel", "Percent":16.605791 }, { "Name":"sulphur", "Percent":15.805322 }, { "Name":"carbon", "Percent":13.290639 }, { "Name":"chromium", "Percent":9.873862 }, { "Name":"phosphorus", "Percent":8.508895 }, { "Name":"vanadium", "Percent":5.391367 }, { "Name":"germanium", "Percent":4.661277 }, { "Name":"niobium", "Percent":1.500503 }, { "Name":"tin", "Percent":1.421682 }, { "Name":"antimony", "Percent":0.985740 } ], "Composition":{ "Ice":0.000000, "Rock":0.676736, "Metal":0.323264 }, "SemiMajorAxis":30409756.302834, "Eccentricity":0.124416, "OrbitalInclination":-2.001024, "Periapsis":237.274170, "OrbitalPeriod":339799.356461, "AscendingNode":74.779278, "MeanAnomaly":210.134501, "RotationPeriod":535318.500665, "AxialTilt":0.050446, "WasDiscovered":true, "WasMapped":true }

So basically...you're right, it's possible for a body to be unmapped. However, in the case of the WasDiscovered field being false, I believe it could be because a player has FSSed the body, but not handed in the data. In this case, we should just assume they'll hand the data in soon and treat WasDiscovered as always true to avoid overestimating the exploration data value.

This brings us back to the question of whether to calculate the first mapping bonus or to store it as a column in the database. If we calculate it, the performance impact would depend on the routing algorithm but the mapping value would just be one additional check, in its simplest form:

if (!body.mapped) {
  bonusValue = scanValue * 1.3;
  scanValue = Math.max(bonusValue, 555); // or whatever calculation needs to be done here
}

If we store the value in the database, we'd be storing another column for millions of database records. I'm not sure whether that's a significant impact but I would assume it's not a small one either.

I'm not sure which option would be better so I'd love to hear any input anyone may have.

[00Chaotic]

Update: After discussion on Discord, this issue will most likely be paused until the exploration route generation functionality is in development and we have determined whether the scan values can be calculated or need to be stored in the db.