Installing DIF

DIF is a collection of tools and libraries specifically created for managing data identifiable by spherical coordinates and stored in MySQL tables. See the GitHub site for more information about installation and reference manual.
If you have SID installed, then this installation is optional, though they can co-exist.

The source code is on the GitHub repository. You can download it either via the tar archive or via git with:

git clone https://github.com/lnicastro/DIF.git

User manual:
https://github.com/lnicastro/DIF/tree/master/doc

Configuration options:
./configure --help

Example configuration:

tar zxvf dif-0.5.5.tar.gz
cd dif-0.5.5
touch configure aclocal.m4 Makefile.in src/config.h.in
./configure --with-mysql-source=$HOME/Downloads/mysql-5.7.22
make
sudo make install

The touch command is needed if you dowloaded DIF via git clone.

Remember to install the Perl DBI/DBD-mysql modules if not present on your system. To check if you have them use, for example, perldoc:

perldoc -l DBI
perldoc -l DBD::mysql

If you receive the message “No documentation found for …”, then you need to install them. Depending on your Perl installation (system default, MacPorts installed, manual installation) you might need to use one of the following procedures.

OS specific command:
On Debian, Ubuntu and variants:

sudo apt-get install libdbd-mysql-perl

On Red Hat, Fedora, centOS, and variants:

sudo yum install "perl(DBD::mysql)"

On openSUSE

sudo zypper install perl-DBD-mysql

Using port (for Mac OS):

sudo port install p5-dbd-mysql

Using cpan:

sudo cpan DBD::mysql

Manual installation:
Download the source code from the above mentioned links (DBI here and DBD-mysql here) and run the commands (DBD-mysql case):

tar ztvf DBD-mysql-4.027.tar.gz
cd DBD-mysql-4.027
perl Makefile.PL
make
sudo make install

To actually enable the DIF facilities, you need to run the installation command:

dif --install

You’ll be asked the MySQL root password to complete this task.
Note: dif is a Perl script used to perform various DIF-related tasks. If you use tcsh you might need to run rehash to have the command visible in an existing terminal. See the manual for a full description or run:

dif --help

Assuming the command executes successfully, you now need to restart the MySQL server to make the new DIF storage engine working.
Depending on how you started the server, you could need to use one of these commands:

sudo /etc/init.d/mysql.server restart

or

sudo /usr/local/mysql/bin/mysqladmin -u root -p shutdown
sudo /usr/local/mysql/bin/mysqld_safe --user=mysql &

Use cases

Assume you have the catalogues simpleBSC, ascc25 and ucac2_initial in the mpe2018db database and want to index them at HTM depth 6 (which means with a pixel size of about 1.2 square degrees):

dif --index-htm mpe2018db simpleBSC 6 RAdeg DECdeg
dif --index-htm mpe2018db ascc25 6 "RAmas/3.6e6" "DECmas/3.6e6"
dif --views-only --index-htm mpe2018db ucac2_initial 6 "RAmas/3.6e6" "DECmas/3.6e6"
  1. The first operation performed by dif is to add (if not there already) an extra column htmID_6 with the integer pixel ID where each object falls, followed by the creation of an index on this column.
  2. The second operation that dif performs is the creation of a table view needed to perform all the DIF specific queries. For example for simpleBSC the view simpleBSC_htm_6 is created and ascc25_htm_6 for the ascc table.
    Note the last two parameters: they refer to the operation needed on the columns with RA/Dec in order to get them in degrees.
    In the first case they are already in degrees, therefore we just report the column names. On the other hand in ascc25 and in ucac2_initial we have stored them as milli-arcsec. That’s why we need to give the conversion formula to dif.
  3. The third operation performed is to insert/update the info for the given catalogue into the table DIF.tbl.

Moreover, in the case of ucac2_initial we use the option --views-only.
This is because the column with the HTM id at depth 6 (default name is htmID_6) is already present in that catalogue, as well as the index using it. Therefore it is not necessary to act on the table, but it is enough to create the view(s) and update the DIF.tbl.

Let’s also add an order 10 HEALPix index:

dif --index-healpix-nested mpe2018db ascc25 10 "RAmas/3.6e6" "DECmas/3.6e6"

In this case the table view ascc25_healp_10 is created.

Note: whenever in the WHERE clause of the query a DIF function is used, then the “table view” must be used instead of the original table!

Some queries on circular and rectangular regions:
Enter the MySQL client terminal, mysql -u mpeusr -p mpe2018db, then:

-- all the info for objects in a circle or radius 18 arcmin around RA=30, Dec=30
  SELECT * FROM ascc25_htm_6 WHERE dif_Circle(30,30,18);

-- as above, but returning coordinates and magnitudes only in standard format
  SELECT RAmas/3.6e6 as RA, DECmas/3.6e6 as Decl, Bmm/1000 as B, Vmm/1000 as V
    FROM ascc25_htm_6
    WHERE dif_Circle(30,30,18);

-- only magnitudes V less than 11 for objects in a square with side 33 arcmin
  SELECT Bmm/1000 as B, Vmm/1000 as V
     FROM ascc25_htm_6
     WHERE dif_Rect(100,-20,33) and Vmm < 11000;

-- a query using the HEALPix indexing
  SELECT ramas/3.6e6 as radeg, decmas/3.6e6 as decdeg, Vmm/1000 as Vmag, (Bmm-Vmm)/1000 as color 
     FROM ascc25_healp_10
     WHERE dif_Circle(30,-20,30);