The members of the Saguaro Astronomy Club (SAC) have a long history of producing useful data from observations, and making it available for download by amateur astronomers worldide at no cost.
One data set that they produce is SAC DeepSky list which is currently at version 8.1, from 2010.
The data is provided in a variety of formats, from Microsoft Excel to comma separated variables.
This data set is used in many places, from commercial GOTO telescope hand sets by major manufacturers, to commercial Digital Setting Circles, to planetarium software like Cartes du Ciels (Sky Chart), and Stellarium.
I was intrigued by this database, and wondered what else can be done with it, specially for me, someone who is well versed in databases. So, I took it upon myself to load the data into a relational database. Although I used MySQL since I used it day to day in my work on Drupal, a similar process can load it in PostgreSQL or even SQLite, e.g. importing CSV).
You can also use a much slimmer database that does not need much resources. That is SQLite.
The scripts to create the database table, and to load the data, as well as a copy of the data set is attached below this article, as a tar.gz archive. All you need to do is create the database, but the table will be created automatically for you.
The schema I ended up using looks like this:
CREATE TABLE `deepsky` (
`object_id` varchar(17) NOT NULL COMMENT 'ID of the Object, usually the NGC',
`other_id` varchar(18) NOT NULL COMMENT 'Other Catalog IDs for the Object, usually the NGC',
`object_type` char(5) NOT NULL COMMENT 'Type of object, e.g. Galaxy, Open Cluster, ...etc.',
`constellation` char(3) NOT NULL COMMENT 'The constellation the object is in',
`ra` char(7) NOT NULL COMMENT 'Right Ascension, Equinox 2000.0',
`de` char(6) NOT NULL COMMENT 'Declination, Equinox 2000.0',
`magnitude` decimal(3,1) NOT NULL COMMENT 'Magnitude for the object, with 99.9 for objects with no magnitude and 79.9 for nebulas',
`surf_bri` decimal(3,1) NOT NULL COMMENT 'Surface Brightness',
`u2k` int(3) DEFAULT '0' COMMENT 'Uranometria 2000.0 reference',
`ti` int(3) DEFAULT '0' COMMENT 'Will Tirion Sky Atlas reference',
`size_max` char(8) DEFAULT '' COMMENT 'Maximum size',
`size_min` char(8) DEFAULT '' COMMENT 'Minimum size',
`position_angle` int(3) DEFAULT '0' COMMENT 'Position angle, in degrees',
`classification` varchar(11) DEFAULT '' COMMENT 'Professional classifications for the object',
`num_stars` int(4) DEFAULT '0' COMMENT 'Number of stars in a cluster',
`mag_bright_star` int(5) DEFAULT '0' COMMENT 'Magnitude of the brightest star',
`catalogs` varchar(4) DEFAULT '' COMMENT 'Other catalogs for object: B=SAC Best of NGC, C=Caldwell catalog, H=Herschel 400 from Astronomical League, M=Messier object',
`ngc_desc` varchar(55) DEFAULT '' COMMENT 'NGC Description abbreviations',
`notes` varchar(86) DEFAULT '' COMMENT 'Field notes, including common names, pairing with companion objects, ...etc.',
UNIQUE KEY `object_id` (`object_id`),
KEY `other_id` (`other_id`),
KEY `object_type` (`object_type`),
KEY `constellation` (`constellation`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 Note that some fields are denormalized, like the catalog field (can contain none, one or more catalog letter, all in the same field). Also some fields have faked values, such as 99.9 and 79.9 magnitudes for dark nebulae, ...etc.
Refer to the SACDOC.TXT file in the zip file.
So, what can you do with such a data set?
For SQLite
For my SQLite, things are much easier:
First enter this command, which will create a database called astro.db:
sqlite3 astro.dbThen create the deepsky table:
CREATE TABLE deepsky (
object_id VARCHAR(17) NOT NULL,
other_id VARCHAR(18) NOT NULL,
object_type CHAR(5) NOT NULL,
constellation CHAR(3) NOT NULL,
ra CHAR(7) NOT NULL,
de CHAR(6) NOT NULL,
magnitude DECIMAL(3,1) NOT NULL,
surf_bri DECIMAL(3,1) NOT NULL,
u2k INT(3) DEFAULT '0',
ti INT(3) DEFAULT '0',
size_max CHAR(8) DEFAULT '',
size_min CHAR(8) DEFAULT '',
position_angle INT(3) DEFAULT '0',
classification VARCHAR(11) DEFAULT '',
num_stars INT(4) DEFAULT '0',
mag_bright_star INT(5) DEFAULT '0',
catalogs VARCHAR(4) DEFAULT '',
ngc_desc VARCHAR(55) DEFAULT '',
notes VARCHAR(86) DEFAULT ''
);We then create some indexes to speed up queries:
CREATE INDEX object_id ON deepsky (object_id);
CREATE INDEX other_id ON deepsky (other_id);
CREATE INDEX object_type ON deepsky (object_type);
CREATE INDEX constellation ON deepsky (constellation);Then import the data into the table you just created:
.mode csv deepsky
.import SAC_DeepSky_Ver81_QCQ.TXT deepskyThat is it! You now have the deepsky table, and can use most of the following queries.
Basic Queries
Get the total number of objects in the the data set:
SELECT COUNT(*) AS "Total number of objects"
FROM deepsky;The number of objects that are in the Caldwell Catalog (C):
SELECT COUNT(*) AS "Number of Caldwell objects"
FROM deepsky
WHERE (catalogs like '%C%');The number of objects that are in the Messier List (M):
SELECT COUNT(*) "Number of Messier objects"
FROM deepsky
WHERE (catalogs like '%M%');The number of objects that are in the SAC Best of the NGC (B):
SELECT COUNT(*) "Number of SAC Best of NGC"
FROM deepsky
WHERE (catalogs like '%B%');The number of objects that are in the Astronomy League's Herschel 400 (H):
SELECT COUNT(*) "Number of Hershel 400 objects"
FROM deepsky
WHERE (catalogs like '%H%');The number of objects visible from northern latitudes, so we limit the declination to 20 degrees south of the celestial equator:
SELECT COUNT(*) "Number of objects above DEC -20"
FROM deepsky
WHERE de >= '-20';Okay, all that is simple stuff. What else can we really do with the data? We can do a lot actually ...
Number of Objects, By Type, With Local Visibility
Now, we want to get the total number of objects visible above Declination 20 degrees south, with the minimum magnitude 14. Adjust the declination to what would be visible in your latitude, without atmospheric interference, and the magnitude to your light pollution condition, and your telescope's ability.
SELECT object_type, COUNT(*)
FROM deepsky
WHERE magnitude <= 14
AND de >= -20
GROUP BY object_type
ORDER BY 2 DESC;The results tell me that there are 1 quasar, 5 supernova remnants, 24 bright nebulae, and 155 planetary nebulae!
So, what are these objects? This query tells us:
SELECT object_id, object_type, magnitude, ra, de, notes
FROM deepsky
WHERE magnitude <= 14
AND de >= -20
AND object_type IN ('BRTNB', 'SNREM', 'QUASR')
ORDER BY de;What is Bright and Visible Tonight?
This query gets a list of objects visible in northern latitudes, in the best catalogs (Caldwell, Messier, SAC Best of NGC and Herschel 400), and they have to be brighter than a certain magnitude (I used 10, adjust this up or down depending on your local light pollution levels and your telescope), and sort the result by Right Ascension then Declination.
With this results from this query you can match it with what constellations are visible at night fall, and then you will have the objects visible near your meridian.
SELECT object_id, object_type, other_id, constellation, ra, de, magnitude, surf_bri, size_max, num_stars, mag_bright_star, ngc_desc, notes
FROM deepsky
WHERE (
catalogs like '%C%' OR
catalogs like '%B%' OR
catalogs like '%M%' OR
catalogs like '%H%'
)
AND magnitude <= 10
AND de >= -20
ORDER BY ra, constellation, de;What is Bright and Visible in named Constellations?
Same as the previous query, but in named constellations only. This example limits the data to what is in Leo, Virgo, Canes Venatici, and Coma Berenices (spring constellations):
SELECT object_id, object_type, other_id, constellation, ra, de, magnitude, surf_bri, size_max, ngc_desc, notes
FROM deepsky
WHERE (
catalogs like '%C%' OR
catalogs like '%B%' OR
catalogs like '%M%' OR
catalogs like '%H%'
)
AND magnitude <= 10
AND de >= -20
AND constellation IN (('LEO', 'VIR', 'CVN', 'COM'))
ORDER BY ra, constellation, de;What is Bright and Visible and Big in named Constellations?
Same as the previous query, but only include objects that have a minimum size (e.g. 7 arc minutes), thus excluding those that are too small:
SELECT object_id, object_type, other_id, constellation, ra, de, magnitude, surf_bri, size_max, ngc_desc, notes
FROM deepsky
WHERE (
catalogs like '%C%' OR
catalogs like '%B%' OR
catalogs like '%M%' OR
catalogs like '%H%'
)
AND magnitude <= 10
AND de >= -20
AND size_max > 7
AND constellation IN (('LEO', 'VIR', 'CVN', 'COM'))
ORDER BY ra, constellation, de;What is 'Remarkable'?
List of objects in best catalogs, above a certain magnitude, and not too far south on the horizon, and marked as 'remarkable' by Emil Dreyer, the compiler of the NGC catalog:
SELECT object_id, object_type, other_id, constellation, ra, de, magnitude, surf_bri, size_max, ngc_desc, notes
FROM deepsky
WHERE (
catalogs like '%C%' OR
catalogs like '%B%' OR
catalogs like '%M%' OR
catalogs like '%H%'
)
AND magnitude <= 10
AND de >= -20
AND ngc_desc like '%!%'
ORDER BY ra, constellation, de;Include Dimmer Objects, 'Remarkable'
This query does not filter by any catalog, goes to a dimmer level, and still selects remarkable objects:
SELECT object_id, object_type, other_id, constellation, ra, de, magnitude, surf_bri, size_max, ngc_desc, notes
FROM deepsky
WHERE magnitude <= 12
AND de >= -20
AND ngc_desc LIKE '%!%'
ORDER BY ra, constellation, de;Brighter, Notable Objects
Or maybe we want to limit ourselves to brighter objects since we are in the city, and filter by objects that are in said catalogs, in a more concise format:
SELECT object_id as ID, object_type AS Type, constellation AS const, magnitude AS mag, ra, de, notes
FROM deepsky
WHERE magnitude <= 10
AND de >= -20
AND (
catalogs LIKE '%B%' OR
catalogs LIKE '%M%' OR
catalogs LIKE '%H%' OR
catalogs LIKE '%C%'
)
ORDER by ra, Const, de;| Attachment | Size |
|---|---|
 Shell and SQL scripts to load the SAC DeepSky data set into MySQL | 461.87 KB |
Most Comments
Most commented on articles ...