Celestia MySQL star database add-on. v 0.1.4
http://mostlyharmless.sourceforge.net




Introduction

To use this software you need to have Celestia installed on your computer. If you are not familiar with Celestia (the best 3D astronomy program ever made), you can download it from http://shatters.net/celestia.


This software is not a game. It is a tool for filtering and visualising the stars in Celestia. However, the programming behind this software is laying down the foundations for the Mostly Harmless game.

I expect that this software will be most interesting to advanced Celestia users, Astronomers and database programmers.


Installation


If you have not downloaded the installer yet go to the link above and follow the links. The installer for this program is fully automated and it provides plenty of information to let you know what is going on. Hopefully there should be no problems here.

Quick start

In summary:
Ctrl+Enter Bring up the MySQL console.
 Ctrl+Z last statement
 Ctrl+A mark/unmark stars
 Ctrl+S select stars
 Ctrl+X Cut to clipboard
 Ctrl+C
Copy to clipboard
 Ctrl+V Paste from clipboard
 Ctrl+W Write MySQL database to stars.dat and starnames.dat.


There are some special Lua scripts in the root directory.


System requirements

Hardware requirements are the same as Celestia but with a little more memory. (See the technical notes below).
It will work on Win98, NT, 2000 and XP.
If you would like to help me port it to Linux or OSX you can email me or drop a message on the Celestia forum. Porting should be relativley straight forward as MySQL is completly cross platform and so is Celesita.
The generation of the database takes up about 30M of hard drive space. If you are using one of the larger stars.dat files then it will use proportionally more disk space.


Tutorial


Here is a quick tutorial of how to use the Celestia MySQL add-on once it is installed.

Once installed the shortcut Celestia with Mostly Harmless MySQL add-on should be on your desktop. If it is not there have a look in your Celestia folder for mostlyharmless.exe.
Once you run it, something looking very much like Celestia 1.3.0 should start up. It may start up a little slower than usual.

I have replaced the internet info for a star with data from the database. You can right click on a star and click Info to bring up all the information that is stored in a star's row. This information is present for every star in the database. The MySQL add-on allows us to quickly filter and visualise this information in a variety of ways.

Press Ctrl+Enter to bring up the MySQL console. In this mode you can type any SQL query. Here is a simple query, type the following:
SELECT pi();
You should see 3.141593 in its own little table.

Note that you must have the semicolon on the end of queries in order to execute them. If a query is long it can be split up on multiple lines.
Press Ctrl+Enter to go back to the SQL console.
 
Now press Ctrl+A This brings up the first part of a query that will mark or unmark (toggle) the selected stars. You should see the following:
mysql> MARK STARS

Complete the query so it looks like this and press enter.
mysql> MARK STARS WHERE spectralClass='M' AND distFromSol<30;
The selected stars will be marked with little green squares. You may have to use the mouse and zoom out  to see them.
If you still cant see the markers make sure you have star marking turned on (Ctrl+K).

Star marking can take some time so there is a limit to how many can be marked. This limit is specified in celestia.cfg and can be changed if desired. By default it is set at 10,000 stars.

Bring up the console again with Ctrl+Enter and then press Ctrl+Z. This brings up the last statement which is useful for fixing an error or unmarking stars. Because MARK STARS toggles the marked property of stars repeating a query will unmark the stars. Press enter to unmark the previously marked stars.


MARK STARS is an alias for SELECT hip FROM star which is what is really passed to the database. Aliases are used so that the parser knows that it should do something special with the result set rather than just presenting the information as a table. To show the same information in a tabular format enter this into the console.
SELECT hip FROM star WHERE spectralClass='M' AND distFromSol<30;

To display all the information on the same stars you would enter:
SELECT * FROM star WHERE spectralClass='M' AND distFromSol<30;
Unfortunatly there is too much information to display on the screen.
 

To display something more useful you could enter the following:
SELECT hip, CONCAT(spectralClass,spectralSubClass,' ',luminosityClass) AS class, bolometricMagnitude FROM star WHERE spectralClass='M' AND distFromSol<30;

At startup only the hipparacos stars that come with Celestia are displayed. If you have generated the database using one of Pascal Hartmann's larger stars.dat files (see below) then you can display all of the stars by doing the following:
Bring up the console with CTRL+Enter  and press Ctrl+S. This enters the LOAD STARS alias for you which can be used to tell Celestia which stars to display. To display all the stars simply complete the statement with a ; and press enter. If you have used one of the larger stars.dat files then this query may take a little while so be patient. The query LOAD STARS; is also useful to unmark all the stars when things start to get messy.

To go back to the default hipparacos stars enter the following statement into the console.
LOAD STARS WHERE hip < 1000000;

The console has copy and paste capability. Ctrl+X,C, and V should do what you expect. This is useful for longer queries so you can type them out in notepad then paste them into the console when they are ready. Try copying some of the below queries into the SQL console.

Count how many stars there are in the database.
SELECT COUNT(*) FROM star;

Count the number of O class stars that are in the database.
SELECT COUNT(*) FROM star WHERE spectralClass='O';

Mark the 20 brightest stars using thier bolometric magnitude.
MARK STARS ORDER BY bolometricMagnitude LIMIT 20;

Mark the 20 dimmest stars using their absolute magnitude.
MARK STARS ORDER BY absoluteMagnitude DESC LIMIT 20;

Mark 20 random stars.
MARK STARS ORDER BY RAND() LIMIT 20;

To display a half shell of stars then mark a spiral pattern of stars onto it.
LOAD STARS WHERE distFromSol BETWEEN 600 AND 700 AND declination > 0;
Followed by:
MARK STARS where declination*RA BETWEEN 60 and 90;

Display only the first 10000 stars that are in the hipparcaros catalog.
LOAD STARS ORDER BY hip LIMIT 10000;

Reload all the stars.
LOAD STARS;


Mark all the visible G3V class stars.
MARK STARS WHERE CONCAT(spectralClass,spectralSubClass,luminosityClass) = 'G3V';


This query calculates the cartesian co-ordinates of the the star Achernar in light years.
SELECT hip,
distFromSol*COS(RA*pi()/12)*SIN((90-declination)*pi()/180) AS x,
distFromSol*SIN(RA*pi()/12)*SIN((90-declination)*pi()/180) AS y,
distFromSol*COS((90-declination)*pi()/180) AS z
FROM STAR WHERE hip = 7588;

Do the same query again and store the values as user variables..
SELECT hip,
(@x:=distFromSol*COS(RA*pi()/12)*SIN((90-declination)*pi()/180)) AS x,
(@y:=distFromSol*SIN(RA*pi()/12)*SIN((90-declination)*pi()/180)) AS y,
(@z:=distFromSol*COS((90-declination)*pi()/180)) AS z
FROM STAR WHERE hip = 7588;


This query marks all the stars that are within 40 light years of the star Achernar.
MARK STARS WHERE

( POW( (@x - (distFromSol*COS(RA*pi()/12)*SIN((90-declination)*pi()/180)) ) ,2)

+ POW( (@y - (distFromSol*SIN(RA*pi()/12)*SIN((90-declination)*pi()/180)) ) ,2)
+ POW( (@z - (distFromSol*COS((90-declination)*pi()/180)) ) ,2) )
< 40*40;


SQL is very powerful and I have only touched on some of the possibilities. I have essentially built a fully functional MySQL client and server into Celestia. It is possible to create new tables, and even modify the star table. (after editing generate_all.bat) It is also possible to modify the database settings (such as memory cache sizes) while it is running. If your interested in learning more about SQL have a read of chapter 4 of the MySQL reference manual. http://www.mysql.com

Lua scripting

I have added the Lua script function executesql so that any query can be invoked from a Lua script.
eg: celestia:executesql("MARK STARS WHERE spectralClass = 'O';")

There are three example scripts in celestia's root directory. Note that these scripts will not work with the standard Celestia.
hipscan.celx  Shows in what order the hipparcos catalog was mapped.
distfromsol.celx  Look like some nice fireworks.
southerncross.celx  Demonstrates writing to the database, creating custom star.dat files and shows the southern cross from various perspectives. The database will need to be generated with generate_db_writeable.bat before and after using this script.


Technical Notes

Two tables are genereated at install time, the star and starNames tables.
All the above queries use the star table. To see what information the starNames table contains you can perform a DESCRIBE starNames; or a SELECT * FROM StarNames; query.

If you have more or less memory than average you can edit the my.ini file to get more performance.  Edit the key_buffer_size variable accordingly. If you have less than 128M of ram you could try key_buffer_size=16M if you are lucky and have more than 512M of ram try key_buffer_size=128M. I have not tried all these settings so only use them as a guide.

The installer adds its own mh.cfg file. there are two extra entries which can be changed. These are sqlOutDuration and maxMarkedStars


This add-on also works with Pascal Hartman's extended stars.dat files for Celestia which are available here: http://perso.wanadoo.fr/celestia.stars/
Once the new stars.dat file is installed execute generate_db_compressed.bat from the command line to regenerate the MySQL database. Please note that with the larger stars.dat files the generation will take a lot longer and some queries will also take a while.

Advanced users can run the generate_db_writeable.bat file so the database is writable.
UPDATE and INSERT queries can then be used to modify the star database.

generate_db_compressed.bat
  Compresses the database (also makes it read only)
  Creates indexes on all columns to make queries faster.
  Takes longer to generate.
   
generate_db_writeable.bat
 
  The database is writeable.
  No indexes, queries are slower.
  Is quicker to generate.

Finally if you come up with any interesting queries then feel free to share them on the Celestia forum.

Please post any feedback or questions to this Celestia forum thread. Alternatively I can be contacted by email.

Changelog : http://mostlyharmless.sourceforge.net/mhforum/viewtopic.php?p=82
Credits : http://mostlyharmless.sourceforge.net/mhforum/viewtopic.php?t=32

Appendix

UncompressedNoIndex    CompressedAllColumnsIndexed
2kUncompressedNoIndex    2kCompressedAllColumnsIndexed


MARK STARS WHERE spectralClass='M' AND distFromSol<30;
0.10    0.03
1.60    0.10

LOAD STARS WHERE hip < 1000000;
5.82    6.30
7.18    7.48

SELECT COUNT(*) FROM star WHERE spectralClass='O';
0.11    0.00
1.57    0.04

MARK STARS ORDER BY bolometricMagnitude LIMIT 20;
0.24    0.00
6.95    0.09

MARK STARS ORDER BY absoluteMagnitude DESC LIMIT 20;
0.24    0.00
7.15    0.02

MARK STARS ORDER BY RAND() LIMIT 20;
0.38    0.35
31.38    32.25

LOAD STARS WHERE distFromSol BETWEEN 600 AND 700 AND declination > 0;
0.29    0.28
3.24    3.34

MARK STARS where declination*RA BETWEEN 60 and 90;
0.10    0.33
2.01    7.13

LOAD STARS ORDER BY hip LIMIT 10000;
0.58    0.61
0.60    0.62

LOAD STARS;
5.91        6.10
143.57    148.25

SELECT hip,
distFromSol*COS(RA*pi()/12)*SIN((90-declination)*pi()/180) AS x,
distFromSol*SIN(RA*pi()/12)*SIN((90-declination)*pi()/180) AS y,
distFromSol*COS((90-declination)*pi()/180) AS z
FROM STAR WHERE hip = 7588;
0.03    0.00
1.37    2.01

MARK STARS WHERE
( POW( (70.86 - (distFromSol*COS(RA*pi()/12)*SIN((90-declination)*pi()/180)) ) ,2)
+ POW( (32.185 - (distFromSol*SIN(RA*pi()/12)*SIN((90-declination)*pi()/180)) ) ,2)
+ POW( (-120.933 - (distFromSol*COS((90-declination)*pi()/180)) ) ,2) )
< 140*140;
0.68    0.95
22.33    21.74


select * from star where spectralClass='K' and spectralSubClass = 0 and luminosityClass='V';
0.59    0.82
2.81    7.11

select * from star where distFromSOl between 300 and 400 and ra between 20 and 24
 and declination > 0;
0.13    0.13
1.70    0.76