Improvements to the Sol system

[db48x (OP)]

I was reading up on the upcoming NASA mission to 16 Psyche, and I began to wonder if it was in Aurora's database or not. It is, but some of the details are wrong; observations over the last few years have refined our measurements of its radius, mass, and density. I suspect that the same will have happened to other minor planets as well. Perhaps we ought to think about updating these values for all objects already in the database? We could divide up the work between interested volunteers, or maybe a clever script could extract the data from Wikipedia automatically.

To get things started, updating 16 Psyche can be done with this SQL statement:

    UPDATE DIM_SolSystemBodies SET Radius = 111, Mass = 4.0352e-6, Density = 4.2 WHERE SystemBodyID = 108265;

Probably the surface gravity and escape velocity need to be updated as well, but those aren't don't have much effect on the game, and I'm not exactly sure what units they're in.

Including 101955 Bennu would be fun now that a probe has sampled it, even if it is a tiny speck by comparison with most of the other items in the database.

[StarshipCactus]

For the wikipedia scraping

t=10404s

This guy was making a quick script to scrape some info from wikipedia. Should be fairly similar to apply to this case, but you would need a list of the wiki pages you want to scrape from.

[Garfunkel]

Wouldn't the first step be comparing which bodies need updating?

[Elvin]

Wouldn't the first step be comparing which bodies need updating?

Not strictly necessary if you just get the most up to date values for all bodies and put them into the database. There's no harm in updating entries which are already correct.

You can then use a tool to compare the databases before and after to see what changes were made, if you are so inclined.

[db48x (OP)]

I've investigated a couple of options. Scraping Wikipedia is the first that came to mind. I haven't watched that video yet, but I've certainly used Python and Beautiful Soup before. My actual thought was to use WikiData, which is supposed to have all the same information in a much more usable form. However, I didn't realize just how obtuse that is. They've really gone all in on their RDF-based design, and it makes learning to query the database quite difficult. I did manage to query it for a list of minor planets, but haven't gotten much further.

Stepping back a bit, it occurred to me that this data might be available in a more traditional format. A quick search turned up the JPL Small Body Database, which includes the names, identifying numbers, oribital elements, and salient physical properties (mass, radius, etc) for quite a large number of objects. I've downloaded a 31MB csv file containing 547,329 bodies that have been assigned a number, which should be all of the largest ones.

Today I will import this into a fresh, unmodified Aurora database (as a separate table) so that a simple join on the name of the body will get me all the values that need to be updated. So much easier than RDF.

Incidentally, did you guys know that Pluto has five known moons now? Four of them are pretty tiny, but perhaps we should include them.

[db48x (OP)]

However, apparently I didn't notice last night that while the JPL website displays mass and density, the export doesn't let you include those fields. I may have to keep looking.

[StarshipCactus]

Good point. A local file would avoid scraping some website a hundred times.

[Steve Walmsley]

Incidentally, did you guys know that Pluto has five known moons now? Four of them are pretty tiny, but perhaps we should include them.

Five moons of Pluto already in