{"id":135,"date":"2018-05-16T15:00:53","date_gmt":"2018-05-16T15:00:53","guid":{"rendered":"http:\/\/ross2.oas.inaf.it\/wp\/imprs18\/?page_id=135"},"modified":"2018-05-31T18:19:30","modified_gmt":"2018-05-31T16:19:30","slug":"the-bright-stars-catalogue","status":"publish","type":"page","link":"https:\/\/ross2.oas.inaf.it\/wp\/imprs18\/the-course\/the-bright-stars-catalogue\/","title":{"rendered":"The Bright Stars Catalogue"},"content":{"rendered":"

Exercise with the BSC<\/h2>\n

We assume that the dump files are in ~\/mpe2018\/Data<\/tt> otherwise change the file path as appropriate.
\nColumns description is here<\/a>.
\nAs usual, the mysql><\/tt> prompt is omitted. The database is always mpe2018db<\/tt>.<\/p>\n

Load the table into the database via the SQL dump file, describe the content and count the number of rows (stars) in the catalogue:<\/p>\n

\r\nuse mpe2018db;\r\nsource ~\/mpe2018\/Data\/simpleBSC.sql\r\nsource ~\/mpe2018\/Data\/bsc.sql\r\n\r\ndescribe simpleBSC;\r\ndescribe BSC;\r\nselect count(*) from simpleBSC;\r\nselect count(*) from BSC;\r\n<\/pre>\n
Rename the table to have it in lower case letters (not necessary on Mac!) and add an index on RA and Magnitude:<\/p>\n
\r\nrename table BSC to bsc;  -- only on Linux\r\nalter table bsc add key (ra);\r\nalter table bsc add key (vmag);\r\n<\/pre>\n
Let’s practice with SELECT, some “informative” queries (MIN(), MAX(), AVG(), ROUND()) and index creation.
\nNote how, if not modified by the user settings, MySQL is not case sensitive, apart the DB and table names.<\/p>\n
Magnitude minimum, maximum and average of all stars in the catalogue:<\/p>\n
\r\nselect MIN(rad), MAX(rad) from bsc;\r\nselect MIN(decd), MAX(decd) from bsc;\r\nselect MIN(vmag), MAX(vmag), AVG(vmag) from bsc;\r\nselect ROUND(AVG(vmag), 2) from bsc where rad > 180 and decd > 0;\r\nselect ROUND(AVG(vmag), 2) from bsc where rad < 180 and decd < 0;\r\n<\/pre>\n
Name and magnitude of the 10 brightest stars. Sort in ascending order using ORDER BY<\/tt> and ASC<\/tt>:<\/p>\n
\r\nselect name, vmag from bsc ORDER BY vmag ASC limit 10;\r\n<\/pre>\n
Count the stars with a name (field name<\/tt> is not empty or NULL, i.e undefined):<\/p>\n
\r\nselect count(*) from bsc where name IS NOT NULL and name != ' ';\r\n\r\n-- result 3143\r\n<\/pre>\n
Then the stars without a name:<\/p>\n
\r\nselect count(*) from bsc where name IS NULL or name = ' ';\r\n\r\n-- result 5953\r\n<\/pre>\n
Let's create a new table temp<\/tt> with 4 columns reading from bsc<\/tt> - only the first 100 stars are inserted:<\/p>\n
\r\ncreate table temp select name, ra, de, vmag from bsc limit 100;\r\n\r\ndescribe temp;\r\nselect * from temp limit 10;\r\n<\/pre>\n
The brightest star in this table:<\/p>\n
\r\nselect * from temp order by vmag asc limit 1;\r\n\r\n-- +------------+------------+-----------+------+\r\n-- | name       | ra         | de        | vmag |\r\n-- +------------+------------+-----------+------+\r\n-- |  21Alp And | 00 08 23.3 | +29 05 26 | 2.06 |\r\n-- +------------+------------+-----------+------+-\r\n<\/pre>\n
If you wanted to select the 100 brightest stars, then you need to use ORDER BY<\/tt> sorting in ASC<\/tt>ending order:<\/p>\n
\r\ncreate table temp100 select name, ra, de, vmag from bsc ORDER BY vmag ASC limit 100;\r\n\r\nselect * from temp100 order by vmag asc limit 1;\r\n-- +------------+------------+-----------+-------+\r\n-- | name       | ra         | de        | vmag  |\r\n-- +------------+------------+-----------+-------+\r\n-- |   9Alp CMa | 06 45 08.9 | -16 42 58 | -1.46 |\r\n-- +------------+------------+-----------+-------+\r\n<\/pre>\n
Remove the tables:<\/p>\n
\r\ndrop table temp, temp100;\r\n<\/pre>\n
Recreate the temp<\/tt> table writing only the stars of the northern hemisphere and with a max magnitude of 3. Note that the decd<\/tt> column contains declination as fractional degrees (from −90 to +90):<\/p>\n
\r\ncreate table temp select * from bsc where decd > 0 and vmag < 3;\r\n<\/pre>\n
We know that the polar star has declination close to 90 degrees, so let's search it in the 88 - 90 degrees range:<\/p>\n
\r\nselect * from temp where decd between 88 and 90;\r\n<\/pre>\n
Let's see which stars are close to the galactic plane (latitude range + or − 2 degrees):<\/p>\n
\r\nselect * from temp where glat between -2 and 2;\r\n<\/pre>\n
Erase objects with magnitude less than 1 (first check the number):<\/p>\n
\r\nselect count(*) from prova where vmag < 1;\r\ndelete from temp where vmag < 1;\r\n<\/pre>\n
Use update<\/tt> to set a magnitude of 0 for the stars closer than 10 degrees to the north pole:<\/p>\n
\r\nupdate temp set vmag = 6 where decd > 0;\r\n<\/pre>\n
Perform more query at wish ...<\/p><\/blockquote>\n
Remove the table(s):<\/p>\n
\r\ndrop table temp;\r\n<\/pre>\n
Create a table selecting only the stars with a measure parallax, i.e. > 0. Only select coordinates, Vmag and parallax:<\/p>\n
\r\ncreate table parallaxes select rad, decd, vmag, parallax from bsc where parallax > 0;\r\n\r\n-- We have selected 3099 stars\r\n<\/pre>\n
Now we check if there is any relation between magnitude and parallax (on average).
\nWe select groups of stars in a non linear way because magnitudes do not decrease linearly with distance. Note that the use of the \"sub-query\", between ()<\/tt>, and the instruction limit<\/tt> with 1 or 2 parameters (N_skip, N_read<\/tt>). The first number is the number of rows to skip and the second those to read. So below we are asking to skip 100 rows and read the following 500.<\/p>\n
Consider the 100 brightest stars:<\/p>\n
\r\nselect avg(vmag), avg(parallax) from (select vmag, parallax from parallaxes order by vmag limit 100) p;\r\n\r\n-- result: 1.902800 |     0.0571600 \r\n<\/pre>\n
Read the next 500 brightest stars:<\/p>\n
\r\nselect avg(vmag), avg(parallax) from (select vmag, parallax from parallaxes order by vmag limit 100, 500) p;\r\n\r\n-- result: 3.725460 |     0.0327700\r\n<\/pre>\n
Read the next 2500 stars:<\/p>\n
\r\nselect avg(vmag), avg(parallax) from (select vmag, parallax from parallaxes order by vmag limit 500, 2500) p;\r\n\r\n-- result: 5.349928 |     0.0244048\r\n<\/pre>\n
We note that parallax \"on average\" decreases as the magnitude increases. Remove all the stars with parallax < 0.01:\n\n\n
\r\ndelete from parallaxes where parallax < 0.01;\r\n<\/pre>\n
Continue practicing with more queries, eventually creating new test tables ...<\/p><\/blockquote>\n
Remove table(s):<\/p>\n
\r\ndrop table parallaxes;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
Exercise with the BSC We assume that the dump files are in ~\/mpe2018\/Data otherwise change the file path as appropriate. Columns description […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":16,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-135","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/ross2.oas.inaf.it\/wp\/imprs18\/wp-json\/wp\/v2\/pages\/135"}],"collection":[{"href":"https:\/\/ross2.oas.inaf.it\/wp\/imprs18\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/ross2.oas.inaf.it\/wp\/imprs18\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/ross2.oas.inaf.it\/wp\/imprs18\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ross2.oas.inaf.it\/wp\/imprs18\/wp-json\/wp\/v2\/comments?post=135"}],"version-history":[{"count":16,"href":"https:\/\/ross2.oas.inaf.it\/wp\/imprs18\/wp-json\/wp\/v2\/pages\/135\/revisions"}],"predecessor-version":[{"id":139,"href":"https:\/\/ross2.oas.inaf.it\/wp\/imprs18\/wp-json\/wp\/v2\/pages\/135\/revisions\/139"}],"up":[{"embeddable":true,"href":"https:\/\/ross2.oas.inaf.it\/wp\/imprs18\/wp-json\/wp\/v2\/pages\/16"}],"wp:attachment":[{"href":"https:\/\/ross2.oas.inaf.it\/wp\/imprs18\/wp-json\/wp\/v2\/media?parent=135"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}