The ASCC2.5 catalogue

We assume that the dump files are in ~/mpe2018/Data otherwise change the file path as appropriate.
As usual, the mysql> prompt is omitted. The database is always mpe2018db.

It is not mandatory, but we assume the database test exists. If not you can create it:
mysql> create database test;
or use another database of your choice or use the mpe2018db itself. Remember to change the queries accordingly.
Then from the MySQL terminal run:

use test;
source ascc25_initial.sql

show create table ascc25_initial; -- note the ENGINE
select count(*) from ascc25_initial; -- note time
select * from ascc25_initial limit 6; -- note FLAGvar
select count(*) from ascc25_initial where FLAGvar = 1;
select MASTERhpx6, runningnumber from ascc25_initial limit 10;
alter table ascc25_initial change FLAGvar FLAGvar tinyint(1) NOT NULL DEFAULT 0;
-- note execution time for the last three queries

Let’s add an index on the ascc25_initial table and perform some queries:

alter table ascc25_initial add PRIMARY KEY (`MASTERhpx6`,`runningnumber`);
select MASTERhpx6, runningnumber from ascc25_initial limit 10;
select MASTERhpx6, count(*) as num_objects from ascc25_initial group by MASTERhpx6 limit 10;
-- perform more queries

Now create a new table ascc25 with some differences with respect to the original one. We’ll use both ascc25_initial and ascc25 in the course:

\u mpe2018db

CREATE TABLE `ascc25` (
  `RAmas` int(10) unsigned NOT NULL DEFAULT '0',
  `DECmas` int(11) NOT NULL DEFAULT '0',
  `EP00000c` mediumint unsigned NOT NULL DEFAULT '0',
  `RAPMdmas` smallint NOT NULL DEFAULT '0',
  `DECPMdmas` smallint NOT NULL DEFAULT '0',
  `Bmm` mediumint NOT NULL DEFAULT '0',
  `Vmm` mediumint NOT NULL DEFAULT '0',
  `FLAGvar` smallint NOT NULL DEFAULT '0',
  `MASTERhpx6` smallint unsigned NOT NULL DEFAULT '0',
  `runningnumber` int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`MASTERhpx6`,`runningnumber`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii; -- note MyISAM

Note: we have chosen the MyISAM storage engine and made the field runningnumber to be AUTO_INCREMENT.
Let’s populate the table making use of the SID/DIF function HEALPLookup to compute MASTERhpx6:

insert into ascc25 select RAmas,DECmas,9125000,RAPMdmas,DECPMdmas,Bmm,Vmm,FLAGvar, HEALPLookup(1,6,RAmas/3.6e6,DECmas/3.6e6), 0 from test.ascc25_initial;

Note the 0 input value given for the field runningnumber. Let’s compare execution times wrt InnoDB

select count(*) from ascc25;
select * from ascc25 limit 10;
show index from ascc25;
select count(*) from ascc25 where FLAGvar=1;
alter table ascc25 change FLAGvar FLAGvar tinyint(1) NOT NULL DEFAULT 0;
-- ...

Quite impressive differences. Think about why.
Some queries (note that the table will be modified):

select ramas/3.6e6 as RAdeg, decmas/3.6e6 as DECdeg, RAPMdmas/10 as RApm, DECPMdmas/10 as DECpm from ascc25 limit 3;
select Vmm/1000 as Vmag, (Bmm-Vmm)/1000 as color from ascc25 limit 10;
alter table ascc25 drop MASTERhpx6, drop runningnumber;

Let’s add a field with the B-V star color:

alter table ascc25 add color float not null default 0;
-- update the color column
update ascc25 set color=Bmm/1000 - Vmm/1000;

select * from ascc25 limit 3;

select count(*) from ascc25 where color >4;
select count(*) from ascc25 where color >-4;

Notice the large number! This is because there are flagged magnitudes:

select * from ascc25 where Vmm<-10000 limit 3;
select * from ascc25 where Bmm<-10000 limit 3;

select count(*) from ascc25 where Bmm=-99990;
select count(*) from ascc25 where Vmm=-99990;

So let’s fix the values:

update ascc25 set color=Bmm/1000 - Vmm/1000 where Bmm != -99990 and Vmm != -99990;
update ascc25 set color=-900 where Bmm = -99990 or Vmm = -99990;

-- see min, max and average color
select min(color), max(color), avg(color) from ascc25 where color != -900;

-- drop the 'color' field
alter table ascc25 drop color;

In order to perform quick sky coordinates queries, let’s now use the SID/DIF provided features.

Sky indexing

We are going to add two sky indices: a depth 6 HTM index and an order 10 HEALPix index (nested schema).

If you have SID installed:

ALTER TABLE ascc25 ADD COLUMN htm6 SMALLINT UNSIGNED NOT NULL;
UPDATE ascc25 SET htm6 = HTMLookup(6, RAmas/3.6e6, DECmas/3.6e6);
ALTER TABLE ascc25 ADD KEY (htm6);
ALTER TABLE ascc25 ADD COLUMN healp10 INT UNSIGNED NOT NULL;
UPDATE ascc25 SET healp10 = HEALPLookup(1, 10, RAmas/3.6e6, DECmas/3.6e6);
ALTER TABLE ascc25 ADD KEY (healp10);

Some example queries making use of SID tools:

CALL SID.SelectCircleHTM  ('', '*', 'mpe2018db.ascc25', 'htm6'   ,  6, 'RAmas/3.6e6', 'DECmas/3.6e6', 30, -20, 30, '');
CALL SID.SelectCircleHEALP('', '*', 'mpe2018db.ascc25', 'healp10', 10, 'RAmas/3.6e6', 'DECmas/3.6e6', 30, -20, 30, 'LIMIT 10');
CALL SID.SelectCircleHEALP('myregion', '*', 'mpe2018db.ascc25', 'healp10', 10, 'RAmas/3.6e6', 'DECmas/3.6e6', 30, -20, 30, '');
select * from myregion;
select ramas/3.6e6 as radeg, decmas/3.6e6 as decdeg, Vmm/1000 as Vmag, (Bmm-Vmm)/1000 as color from myregion;

-- perform more queries: use database() to get the current DB

drop table myregion;
CALL SID.SelectCircleHEALP('', '*', CONCAT(database(), '.', 'ascc25'), 'healp10', 10, 'RAmas/3.6e6', 'DECmas/3.6e6', 30, -20, 30, 'where Vmm<12000');
-- ...

If you have DIF installed:
In this case the previous commands can be performed by the dif script with these two shell commands:

shell> dif --index-htm mpe2018db ascc25 6 "RAmas/3.6e6" "DECmas/3.6e6"
shell> dif --index-healpix-nested mpe2018db ascc25 10 "RAmas/3.6e6" "DECmas/3.6e6"

Some example queries making use of DIF tools:

select * from ascc25_htm_6 where dif_circle(10, 20, 15);
select ramas/3.6e6,decmas/3.6e6 from ascc25_htm_6 where dif_circle(30, -20, 30);
select ramas/3.6e6,decmas/3.6e6 from ascc25_healp_10 where dif_circle(30, -20, 30);
select ramas/3.6e6 as radeg, decmas/3.6e6 as decdeg from ascc25_htm_6 where dif_circle(30, -20, 30);
select ramas/3.6e6 as radeg, decmas/3.6e6 as decdeg from ascc25_htm_6 where dif_rect(30, -20, 30);
select ramas/3.6e6 as radeg, decmas/3.6e6 as decdeg, Vmm/1000 as Vmag, (Bmm-Vmm)/1000 as color from ascc25_htm_6 where dif_circle(30, -20, 60) and Vmm<12000;
SELECT table_name FROM INFORMATION_SCHEMA.TABLES where table_schema='mpe2018db' AND table_type = 'View';
-- ...