Back to the NBOS Home Page
›

SQL Server Express Opening AstroDB Files

AdamQuarkAAdamQuark
edited January 2014 in AstroSynthesis General
Does anyone know of a tutorial that will explain how to open the AstroDB files in SQL Server Express?

I figure I use the Import Data task, but I'm not sure what data format to use for the AS3 files.

Thanx!

Comments

  • Ed_NBOSEd_NBOS
    AS3 files are SQLite database files. Not sure if MS-SQL can load those or not. But there may be ODBC drivers for SQLite you can use to load the database.

    Alternatively, if you just want to browse the files, there's a number of GUI's for SQLite. They wont be as polished as SQL Server's, but will let you see the tables and run queries.
  • AdamQuarkAAdamQuark
    OK, got SQLite installed and it seems to work. Looks like it can't take a TOP 100 in the SELECT, but maybe that's just T-SQL.

    Anyway, the reason I'm trying to see the content is so I can determine all the options for importing a csv file with premade objects.

    Going by the Help file, Body Type can only be "Star," "White Dwarf," or "Multiple." I'd like to be able to import all the available objects; "Nebula," "Mega Structures," "Black Holes," etc., but I don't know what values to use for their body types or how else to identify them. I really don't want to enter them manually, because I'm importing a good bit of the Hipparos catalog and that's over 100,000 systems. I also need to - I think - identify the White and Brown Dwarf stars. Or, because the star's physical data, including color, is part of the import file, is "Star" sufficient?
  • Ed_NBOSEd_NBOS
    The Astro's CSV importer is limited to those types. CSV isn't a particularly good format for hierarchical data like star systems with planets. So it was never meant to import anything other than very basic data sets.

    In Astro's bodies table, what tells you the type is the type_id field. Its an integer based on the following:
    BODY_TYPE_STAR = 1
BODY_TYPE_MULT = 2
BODY_TYPE_BLACKHOLE = 3
BODY_TYPE_STATION = 4
BODY_TYPE_FREEOBJECT = 5
BODY_TYPE_NEUTRON = 6
BODY_TYPE_NEBULA = 7
BODY_TYPE_WHITEDWARF = 8
BODY_TYPE_REDGIANT = 9
BODY_TYPE_BLUEGIANT = 10
BODY_TYPE_BROWNDWARF = 11
BODY_TYPE_CLOSEMULT = 12
BODY_TYPE_DSO = 13
BODY_TYPE_TERRESTRIAL = 100
BODY_TYPE_GASGIANT = 101
BODY_TYPE_ASTEROIDBELT = 102
BODY_TYPE_PLANETOID = 103
BODY_TYPE_RING = 104
BODY_TYPE_PLANETSYSTEM = 105
BODY_TYPE_SMALLBODY = 106
BODY_TYPE_MEGASTRUCTURE = 107
BODY_TYPE_FLEET = 200
BODY_TYPE_SHIP = 201
BODY_TYPE_MARKER = 202

    The body_type text field does not have to match the type_id. It can be any text that describes the body. For example, you can a Terrestrial Planet 'Earthlike' instead.

    If you want to do a massive import, you might be better off writing a PHP script or something to import right into the db from your data source.

    btw, in SQLite you can use limit (similar to MySQL) instead of top. 
    select * from bodies limit 100
  • AdamQuarkAAdamQuark
    Ah, so, "Body Type" is not a binding qualifier, except for "Multiple?"

    I'll have to study the SQLite tables more.

    I don't suppose there's a data dictionary available for AS3's database?
  • Ed_NBOSEd_NBOS
    It's not clear... are you trying to use Astro's own CSV importer to bring in data, or are you trying to write to the AstroDB file with your own program?
  • AdamQuarkAAdamQuark
    I'm using Excel to create a .csv file (at some point to be upgraded to SSMS) and I then use AS3's Import command to import that .csv. If I can, I'm trying to use Excel to do as much of the work as possible before importing. I can do searches and editing much faster with Excel - and, even faster, when I put in SSMS - than I can with AS3. But, the actual importing is done using AS3.

    Another issue I've run across concerns the components of "Multiple" body systems; i.e., Alpha Centauri. The star marking the location of the system - body type "Multiple" in the .csv file - has the positional data for the primary. The children of this system is composed of the component stars; same ID number, but typed as "Star."

    The problem is, how do I import the positional data for the child stars? In AS3, one can enter the component position - in AU - but, how is the position coded in the .csv for importing?

    In fact, since I have the orbital data for most of my multiples, I'd probably want to load them as "planets," so that I can use the orbital parameters to properly map the system. Unfortunately, I see no way to import planets.
  • Ed_NBOSEd_NBOS
    If you are using Astro's built in csv importer, its limited to the body types and fields that are listed in the help file. Its only designed to be a very rudimentary importer.

    What you may be able to do using VBA in your excel spreadsheet is connect to the AstroDB and directly write to it.

    For multiple star systems, I believe the position values of the components are in AU. So you'll provide an x/y/z in AU, relative to the 'center' of the multiple star system. Astro doesn't track orbital information for stars. But the database fields wills still be there, and you can change the system data display to show them if you want.
  • AdamQuarkAAdamQuark
    Ed_NBOS wrote:
    What you may be able to do using VBA in your excel spreadsheet is connect to the AstroDB and directly write to it.

    Ick. I'll stick with what I have.
    For multiple star systems, I believe the position values of the components are in AU. So you'll provide an x/y/z in AU, relative to the 'center' of the multiple star system. Astro doesn't track orbital information for stars. But the database fields wills still be there, and you can change the system data display to show them if you want.

    That's kinda the route I was taking. But, as I can find a lot of data for multiples that have a definite primary with detailed orbital information for companions, I'll probably just deal with them individually, using the AU data only for those that don't have the detail.

    Thanx for the info!
  • HalCHHalC

    Hi All,
    Having seen this entry - is there by chance, any instructions on how to utilize either of VBA or Visual Basic (VB.NET) with the database itself? I am Soooooooo tempted to try my hand at using VB.NET in such a manner that ALL of the variables can be stored in an Excel Spreadsheet, have the VB.NET software read the excel spreadsheet and move the data directly into a AstroDB file.

    I use GURPS for my role playing vehicle of choice, and being able to control more of the interface with Astrosynthesis 3 software would be a dream for me.

    In addition, I can't help but think that despite the claim that this is not an emulation piece of software - the fact that you can have a system map showing positions of various planets at any given time, add time in to the equation and show a new location for the planet in question is a DREAM I wish I could have fulfilled in the past. See - TRAVELLER has always argued about whether or not pirates could exist in a "sane" traveller universe, and I had set out once upon a time, to craft some rules for tracking objects within the game universe. Where I kept running into a brick wall was the real motion aspects of planets and where they would be at any given point in time (using 20 minute game units of time).

    At least with this - one could play with the variables and figure out how it all works. Any pointers on how to integrate vb.net or vba with this, would be appreciated.

       Hal

Leave a Comment