SDSS sample of galaxies

Exercise with a list of SDSS galaxies

We assume that the abell2065_2deg_radius.csv file is in ~/mpe2018/Data otherwise change the file path as appropriate. Can download it from here.
You can download any additional data set querying the SDSS Skyserver.
Columns description:

objid      => unique identifier (numeric) of the object
rad        => Right Ascension (fractional degrees)
decd       => Declination (fractional degrees)
u g r i z  => magnitude of the object in these filters
redshift   => z
specobjid  => unique spectrum ID

As usual, the mysql> prompt is omitted. The database is always mpe2018db.
Have a look to the file with a text editor, or e.g. just
head -5 ~/mpe2018/Data/abell2065_2deg_radius.csv

Manually create the table to host the catalogue:

use mpe2018db;

CREATE TABLE sdss_sample (
  objid bigint NOT NULL,
  rad   double NOT NULL,
  decd  double NOT NULL,
  u     float DEFAULT NULL,
  g     float DEFAULT NULL,
  r     float DEFAULT NULL,
  i     float DEFAULT NULL,
  z     float DEFAULT NULL,
  redshift float DEFAULT NULL,
  specobjid bigint NOT NULL
) engine=MyISAM;

Load the CSV file into the table via LOAD DATA (we use explicit clauses for clarity – see the MySQL reference page), describe the content, count the number of rows (galaxies) in the catalogue, print the first five rows:

load data local infile '~/mpe2018/Data/abell2065_2deg_radius.csv'
  into table sdss_sample
  FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\'
  LINES TERMINATED BY '\n' STARTING BY ''
  IGNORE 2 LINES;

describe sdss_sample;

select count(*) from sdss_sample;
select * from sdss_sample limit 5;

Let’s preform some queries. Here we investigate the extreme redshift ranges.

select * from sdss_sample where redshift > 3.;
-- 3 found
select * from sdss_sample where redshift > 1. and redshift < 3;
-- 7 found
select * from sdss_sample where redshift < 0.00001;
-- 4 found

select objid, r, u-r as color from sdss_sample order by color asc limit 3; 
select objid, r, u-r as color from sdss_sample order by color desc limit 3; 
select objid, r, g-r as color from sdss_sample order by color desc limit 3; 

Exercise with more queries of your choice. I suggest to perform queries to identify the number of objects in r magnitude ranges and (g - r) color. We'll see these data in a graphical way too.