Nipponese version

2MASS Catalog Server Kit

Supports 2MASS, WISE, USNO-B1.0, UCAC3, PPMXL, GSC-2.3, etc.

Sep. 2014 Version 2.3

Institutes/Observatories utilizing 2MASS Kit:
CFHT Observatory, Kiso Observatory of Tokyo Univ., ISAS/JAXA, NAOJ, Nagoya University, miniTAO

[HISTORY]
Sep.2014  V. 2.3  Supported SLLIB-1.4 or newer.
Nov.2012  V. 2.2  Supported WISE 3-Band Cryo Source.
May.2012  V. 2.1  Supported WISE All-Sky Release Catalog.
                  Small improvements.
Dec.2011  V. 2.0  A lot of improvements including design changes.
                  Supports USNO-B1.0, UCAC3, GSC-2.3.2, and PPMXL.
Sep.2011  V. 1.2  Supports fast box search.
Aug.2011  V. 1.1  Supports Tycho2, AKARI IRC, AKARI FIS and IRAS PSC.
                  Added a client tool: clients/sql2mass.php.
Nov.2010  V. 1.0  Supports 2MASS PSC only.

Yamauchi @ NAOJ/ISAS

PostgreSQL Powered

Special Thanks to: Dr. Satoshi Takita, Dr. Shinki Ooyabu (Linux tests), Dr. Norio Ikeda (Mac OS X port), and Dr. Yoshifusa Ita (document review). This document was translated by KOYOSHOUJI CO.,LTD.
Technical paper (2011PASP..123.1324Y) has been published.


Page Index:
HOME / Install Huge Catalogs / Install Small Catalogs / Information about table columns /

Index of Current Page:
Introduction / Supported Catalogs / Requirements / Download / Installation / Settings for PostgreSQL / Tuning / Usage / Stored Functions / Client Tools / FAQ


Introduction

2MASS Kit is an open source software for use in easily constructing a high performance search server for important astronomical catalogs. You can use our kit on your PCs with installing only necessary catalogs.

It is tuned for optimal coordinate search performance (Radial Search, Box Search, Rectangular Search) of huge catalogs, thus increasing the speed by more than an order of magnitude when compared to simple indexing on a single table. Optimal conditions enable more than 3,000 searches per second for radial search of 2MASS PSC.

The kit is best characterized by its flexible tuning. Each table index is registered in one of six table spaces (each resides in a separate directory), thus allowing only the essential parts to be easily moved onto fast devices. Given the terrific evolution that has taken place with recent SSDs in performance, a very cost-effective way of constructing high-performance servers is moving part of or all table indices to a fast SSD.

Potential users would mainly be observatories with unstable or narrowband networks, but it is also very deployable on personal use PCs, PCs in use at observatories, institutes, and data center servers.

The kit utilizes open source RDBMS, PostgreSQL-8.4, and therefore requires no software licensing fees.

Supported Catalogs

All-Sky Catalogs

Catalogs with certain sky coverage

Performance of Coordinate Search

Examples search speeds of a Radial Search, Box Search and Rectangular Search of 2MASS PSC under the best conditions are provided below. The elapsed time was measured under the same conditions three times, and then averaged.

Hardware
CPU: Intel(R) Xeon(R) E5640 @ 2.67GHz
Mainboard: SuperMicro X8STE
Memory: DDR3-1333 24 GB

Software
OS: CentOS 5.6 64-bit
RDBMS: PostgreSQL-8.4.7

Search CriteriaNumber of BodiesElapsed Time SQL Statements Used
Radial (1' of radius)20.001 sec SELECT count(*) FROM fTwomassGetNearbyObjEq(0, 0, 1);
Radial (60' of radius)51980.015 sec SELECT count(*) FROM fTwomassGetNearbyObjEq(0, 0, 60);
Radial (180' of radius)476320.102 sec SELECT count(*) FROM fTwomassGetNearbyObjEq(0, 0, 180);
Radial (360' of radius)1897840.368 sec SELECT count(*) FROM fTwomassGetNearbyObjEq(0, 0, 360);
Box (120'x120')66440.023 sec SELECT count(*) FROM fTwomassGetNearbyObjFromBoxCel('j2000', 0,0, 60,60);
Box (120'x2400')1365790.498 sec SELECT count(*) FROM fTwomassGetNearbyObjFromBoxCel('j2000', 0,0, 60,1200);
Rectangular (2x2deg)66440.006 sec SELECT count(*) FROM fTwomassGetObjFromRectEq(-1,1, -1,1);
Rectangular (10x10deg)1672660.107 sec SELECT count(*) FROM fTwomassGetObjFromRectEq(-5,5, -5,5);

Note: All the values result from the ideal condition where the tables and indices have been cached in the memory. Immediately after the machine has been turned on, in particular, much slower results will occur unless you use a fast SSD as nothing is cached in the memory and frequent disk I/Os will therefore take place. A RDBMS dedicated machine being used and operated for sufficiently long without being shut down will enable the search results to be a lot closer to the values shown above.


Requirements

We show some examples of additional hardwares for ultra fast search of huge catalogs:


Download

Download following package:

Data files for registration are available at HTTP.


Installation

Here I will walk through the installation procedure for Red Hat Enterprise Linux 6 or 5 (CentOS 6 or 5) and Mac OS X. With any other OS you will need to be slightly creative where appropriate.

Disable SELinux

If you are using Linux, edit /etc/sysconfig/selinux to disable SELinux.

SELINUX=disabled

Then, reboot your OS.

Shared Memory Settings

Set the maximum shared-memory segment to a sufficiently large value in /etc/sysctl.conf. It's completely valid to set the value larger than the amount of physical memory. This is not required on RHEL or CentOS.

64-bit Linux

# Controls the maximum shared segment size, in bytes
kernel.shmmax = 68719476736

32-bit Linux

# Controls the maximum shared segment size, in bytes
kernel.shmmax = 3221225472

Mac OS X 10.5 or later

Create /etc/sysctl.conf if it doesn't exist:

kern.sysv.shmmax=68719476736

If you are using Mac OS X 10.4 or earlier, edit /etc/rc. However, be aware that updating the OS will overwrite that setting.

After the parameter setting has been added or modified reboot the OS.

Installing PostgreSQL-8.4

On Linux (CentOS-6,RHEL-6) install postgresql, postgresql-libs, postgresql-devel, and postgresql-server:

$ su
# yum install postgresql
# yum install postgresql-devel
# yum install postgresql-server

On CentOS-5 and RHEL-5 install postgresql84, postgresql84-libs, postgresql84-devel, and postgresql84-server:

$ su
# yum install postgresql84
# yum install postgresql84-devel
# yum install postgresql84-server

On Mac OS X use macports like
(each binary will be placed in /opt/local/lib/postgresql84/bin/):

# port install postgresql84
# port install postgresql84-server

If PostgreSQL server is running stop it.

Linux

$ su
# /etc/rc.d/init.d/postgresql stop

Mac OS X

$ su
# pg_ctl stop

Preparing a DB directory

Create a directory for PostgreSQL to save its data in. In this kit the directory is assumed to be /db (you can override this by modifying Makefile). Allocate required disk space.

In normal circumstances you will have a large HDD partition for your home directories. If your home partition is large enough you can use the --bind mount option to separate /db from /home. Here is the case where /dev/sda4 is the home partition:

$ su
# mkdir -p /mnt/sda4
# mount /dev/sda4 /mnt/sda4
# mkdir -p /mnt/sda4/home
# mkdir -p /home
# mount --bind /mnt/sda4/home /home
# mkdir -p /mnt/sda4/db
# mkdir -p /db
# mount --bind /mnt/sda4/db /db

If you can allocate the entire HDD partition for /db, you can make a small performance optimization using the "-o noatime" mount option.

Here is your /etc/fstab:

/dev/sda4               /mnt/sda4               ext3    defaults        1 2
/mnt/sda4/home          /home                   none    bind            0 0
/mnt/sda4/db            /db                     none    bind            0 0

Feel free to use any own way other than outlined above as long as you can use required disk space for your home directory and /db.

The SSD is not in use at this point. The SSD is used after the DB has been built in accordance with what you wish to optimize.

Building 2MASS Kit and Initializing DB

Extract software package:

$ tar zxvf 2masskit-2.3.tar.gz
$ cd 2masskit-2.3

Choose an appropriate Makefile and create a symbolic link. There are several kinds of Makefiles available: Makefile.linux64 (for 64-bit Linux), Makefile.linux32 (for 32-bit Linux), Makefile.MacOSX64 (for 64-bit Mac OS X), Makefile.MacOSX32 (for 32-bit Mac OS X), and Makefile.solaris64 (for 64-bit Solaris Sparc). Next, edit the Makefile:

$ ln -s Makefile.linux64 Makefile
$ vi Makefile

If you save PostgreSQL data in /db and the OS is either RHEL-6(-5) or CentOS-6(-5) you don't need to modify any directory entries in Makefile. Otherwise, modify as appropriate for your environment.

Run make:

$ make

Install the necessary files and initialize the DB.

Linux:

$ su
# make install
# /sbin/service postgresql initdb

Mac OS X:

$ su
# make install
# initdb -D /db/pgsql/data

If any file exists in /db/pgsql/data initdb will fail. In this case empty /db, and start over again from make install.
If you still need help refer to "initdb fails!" in the FAQ section.

Setting Resources for PostgreSQL

Open /db/pgsql/data/postgresql.conf (created by initdb) in your favorite editor and modify the following parameters:

shared_buffers = 128MB
work_mem = 512MB
maintenance_work_mem = 128MB
checkpoint_segments = 10
effective_cache_size = 256MB

These parameters should be tuned for your purpose or environment. See also Tuning section. Note that setting work_mem is very important when retrieving large records for each query.

Starting PostgreSQL Server

Become superuser and start PostgreSQL server.

Linux:

$ su
# /etc/rc.d/init.d/postgresql start

Mac OS X:

$ su
# pg_ctl start -D /db/pgsql/data

Creating accounts and the database in PostgreSQL

Log in as user 'postgres' in UNIX and run psql:

$ su
# su postgres
% psql

Here accounts in PostgreSQL (called "roles" in PostgreSQL) can be set up and a database created.
'postgres' denotes a superuser in PostgreSQL, 'admin' can be used to register tables and storing functions, and 'guest' is for using the DB. Each 'xxxxxx' should be replaced by a respective password:

postgres=# ALTER ROLE postgres PASSWORD 'xxxxxx';
postgres=# CREATE ROLE admin LOGIN PASSWORD 'xxxxxx';
postgres=# CREATE ROLE guest LOGIN PASSWORD 'xxxxxx';
postgres=# CREATE DATABASE "2MASS";
postgres=# \q

In this document the database for this kit is called "2MASS", but you can use any other name through "CREATE DATABASE" above.

Modifying pg_hba.conf

Open /db/pgsql/data/pg_hba.conf in your favorite editor, and modify authentication method for local to md5.

local   all         all                               md5

Create start Configuration for PostgreSQL and Restart

Become superuser and create the start configuration for PostgreSQL server, and then restart PostgreSQL server.

Linux:

$ su
# /sbin/chkconfig --level 2345 postgresql on
# /etc/rc.d/init.d/postgresql restart

Mac OS X:

At the top of /opt/local/etc/LaunchDaemons/org.macports.postgresql84-server/postgresql84-server.wrapper add the location of the data storage for the DB, as in:

POSTGRESQL84DATA=/db/pgsql/data

Then:

$ su
# launchctl load -w /Library/LaunchDaemons/org.macports.postgresql84-server.plist
# pg_ctl restart -D /db/pgsql/data

Registering C stored functions

As a normal user in UNIX log in to the database "2MASS" as 'postgres' (superuser in PostgreSQL) to register the C stored functions and table spaces:

$ psql -U postgres 2MASS
Password for user postgres: xxxxxx
psql (8.4.5)
Type "help" for help.

2MASS=# \i create_c_function.sql
(Registered stored functions are listed)
2MASS=# \q

Registering Stored Functions for Astronomy

As a normal user in UNIX log in to the database "2MASS" as 'admin' to register stored functions for astronomy:

$ psql -U admin 2MASS
Password for user admin: xxxxxx
psql (8.4.5)
Type "help" for help.

2MASS=> \i create_function.sql

Installing Catalogs

If you want to install huge catalogs (2MASS PSC, WISE, USNO-B1.0, UCAC3, GSC-2.3.2, and PPMXL), jump to "Install Huge Catalogs".

If you want to install small catalogs (Tycho-2, AKARI IRC, AKARI FIS, and IRAS PSC), jump to "Install Small Catalogs".

See this page for support of WISE Preliminary catalog.


Settings for PostgreSQL

Allow Connections from PostgreSQL Clients on Other Machines

In order to allow connections from PostgreSQL clients (psql or Web applications) on other machines you will need to configure postgresql.conf and firewall as shown below.

Open /db/pgsql/data/postgresql.conf with your editor and set listen_addresses as in:

listen_addresses = '*'

Configure your firewall using the setup command (if your firewall is configured as Disabled you can skip this step):

$ su
# setup

In the Firewall configuration -> Customize by including "postgres:tcp" in the "Other ports" settings.
This then results in /etc/sysconfig/iptables being supplemented by:

-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

Restart iptables (if your firewall is configured as Disabled you can skip this step):

$ su
# /etc/rc.d/init.d/iptables restart

On Mac OS X you can do the same from System Preferences -> Sharing.

Next open /db/pgsql/data/pg_hba.conf with your editor, and specify the IP address of the client, denoted here by xxx.xxx.xxx.xxx, as in:

host  all     guest   xxx.xxx.xxx.xxx    255.255.255.255 md5

In the above example only guest role will be permitted. If you wish to allow all users use "all."

Restart PostgreSQL server:

$ su
# /etc/rc.d/init.d/postgresql restart

A client can now access the database on the server by typing "psql -h hostname ...."

Using psql without Passwords

Password prompts can sometimes be rather cumbersome, especially when used from your scripts.
In this case, you can specify the role name and the password in the file named .pgpass as in the follwing, which will result in no password being required when using psql.

*:*:*:guest:password

"*" means "everything is OK." You can specify any hostname:port:database:username:password, in that order, as needed.

Don't forget to set permissions to 600:

$ chmod 600 .pgpass

Tuning

BIOS Settings

In the case of Intel chipset motherboards disable the C1E (Enhanced HALT) power-saving feature as it will improve performance by 10% to 20% when compared to being enabled.
This setting has an impact on the performance of operations with frequent disk I/Os, as is the case with Cross-ID.

cpuspeed Settings

Disable dynamic CPU clocking for optimum performance:

/etc/rc.d/init.d/cpuspeed stop

hdparm Settings

You may ensure better performance by increasing the amount for disk read-ahead using the hdparm command. To do this add the line below to /etc/rc.d/rc.local:

/sbin/hdparm -a4096 /dev/sda

Tweaking Resources for PostgreSQL

Tweak these parameters in /db/pgsql/data/postgresql.conf as needed.

Generally, shared_buffers is important for OLTP (OnLine Transaction Processing), and OLAP (OnLine Analytical Processing) requires large value of work_mem.

Filesystem

You won't need to worry too much about this, modulo SSD. In our Cross-ID tests, used in conjunction with an SSD, ext4 performed slightly better (by 1 percent or so) than ext3 and xfs.

Using a High-performance SSD

With the 2MASS Kit all the table indices get stored in several distinct directories called table spaces.

For each huge catalog, there are six table spaces, as can be seen below:

# ls /db/pgsql/tablespace/twomass_*
twomass_eqi               twomass_eqi_index_xyzi16  twomass_main_index
twomass_eqi_index_radeci  twomass_main              twomass_main_pkey

Part or all the directories being copied onto an SSD, which in turn is mounted using the directories shown above, will result in greatly improved search performance. In this case use of the bind option is convenient, which mounts directories rather than devices.
These table spaces have different significances in contributing to optimized performance, and are listed from the most significant to the least:

If you seek optimized performance at a relatively low cost I would suggest buying a 256 GB PLEXTOR PX-M3 Pro SSD, and then copying the first four directories (from twomass_eqi_index_xyzi16 up to twomass_main_pkey) onto it.

Another method of speeding the process up Cross-ID would be copying twomass_eqi_index_xyzi16 onto a ramdisk, such as tmpfs (/dev/shm), and twomass_eqi onto SSD.

The following is a walk-through for speeding up Radial Search, Box Search and Cross-ID by copying some of the above onto a fast SSD (/dev/sdb).

$ su
# /etc/rc.d/init.d/postgresql stop
# mkdir -p /mnt/ssd_a
# mount -o noatime /dev/sdb1 /mnt/ssd_a
# mkdir -p /mnt/ssd_a/pgsql_tablespace
# chown -R postgres:postgres /mnt/ssd_a/pgsql_tablespace
# rsync -a --delete /db/pgsql/tablespace/twomass_eqi /mnt/ssd_a/pgsql_tablespace/.
# rsync -a --delete /db/pgsql/tablespace/twomass_eqi_index_xyzi16 /mnt/ssd_a/pgsql_tablespace/.
# mount --bind /mnt/ssd_a/pgsql_tablespace/twomass_eqi /db/pgsql/tablespace/twomass_eqi
# mount --bind /mnt/ssd_a/pgsql_tablespace/twomass_eqi_index_xyzi16 /db/pgsql/tablespace/twomass_eqi_index_xyzi16
# /etc/rc.d/init.d/postgresql start

Usage

Here we show examples for searching 2MASS PSC. Replace `Twomass' when searching other catalogs.

You should log on as guest with normal use priviledges to avoid any accidents with the tables:

$ psql -U guest 2MASS

For psql usage refer to the official page in English.

Radial Search

Use the stored function fTwomassGetNearbyObjEq() or fTwomassGetNearbyObjCel(). If you want the best search performance, use fTwomassGetNearbyObjEq(). fTwomassGetNearbyObjCel() is used to search objects with the positional conversion from J2000 to other coordinate systems.

fTwomassGetNearbyObjEq() takes R.A.(deg), Dec.(deg), and search radius (arcmin) as the arguments:

2MASS=> SELECT * FROM fTwomassGetNearbyObjEq(0, 0, 3);

fTwomassGetNearbyObjCel() takes coordinate system ('j2000', 'b1950', 'ecliptic' or 'galactic'), longitude(deg), latitude(deg), and search radius (arcmin) as the arguments:

2MASS=> SELECT * FROM fTwomassGetNearbyObjCel('j2000', 0, 0, 3);

In the case of fTwomassGetNearbyObjEq(), a table that consists of objid, lon, lat, and distance will result (the unit for distance is arcmin):

   objid   |    lon     |    lat    |     distance      
-----------+------------+-----------+-------------------
 793571660 | 359.982569 |  0.044046 |  2.84218284794452
 793571662 | 359.989615 |  0.012327 | 0.967104621472398
 793571661 |  359.98931 |  0.027802 |  1.78718165359994
 792913920 |   0.009564 | -0.000827 | 0.575981326971027
 792913922 |    0.01738 | -0.032945 |  2.23489923964895
 792913925 |   0.021541 | -0.023737 |  1.92324084931489
 792913927 |   0.030595 | -0.006287 |  1.87405693715596
 792913929 |   0.034844 | -0.028304 |  2.69347254510121
 793236078 |   0.038972 |  0.001536 |  2.34013544202409
 792913932 |   0.047375 | -0.006703 |  2.87081085523336

If you want corresponding records in the Main Table you can join the return value from the stored function to the Main Table using objid as a primary key. This example results in the coordinates, flux, flux error, and distance being retrieved:

SELECT o.ra,o.dec,o.j_m,o.h_m,o.k_m,o.j_msigcom,o.h_msigcom,o.k_msigcom,n.distance
FROM fTwomassGetNearbyObjEq(0,0,3) n, twomass o
WHERE n.objid = o.objid;

The result will be somewhat like this:

     ra     |    dec    |  j_m   |  h_m   |  k_m   | j_msigcom | h_msigcom | k_msigcom |     distance      
------------+-----------+--------+--------+--------+-----------+-----------+-----------+-------------------
 359.982569 |  0.044046 | 12.842 | 12.374 | 12.335 |     0.022 |     0.021 |     0.029 |  2.84218284794452
 359.989615 |  0.012327 |  13.75 | 13.194 |  13.14 |     0.026 |      0.03 |     0.037 | 0.967104621472398
  359.98931 |  0.027802 | 15.773 | 15.347 | 14.972 |     0.079 |      0.11 |      0.14 |  1.78718165359994
   0.009564 | -0.000827 | 16.422 | 16.112 |  15.21 |      0.11 |     0.227 |           | 0.575981326971027
    0.01738 | -0.032945 | 15.591 | 15.023 | 15.069 |     0.059 |     0.073 |     0.154 |  2.23489923964895
   0.021541 | -0.023737 | 17.025 | 15.841 | 17.233 |      0.22 |     0.159 |           |  1.92324084931489
   0.030595 | -0.006287 |  14.43 | 14.085 | 13.923 |     0.038 |     0.039 |     0.056 |  1.87405693715596
   0.034844 | -0.028304 | 15.644 | 15.355 | 15.129 |     0.069 |     0.096 |     0.143 |  2.69347254510121
   0.038972 |  0.001536 | 12.682 | 12.096 | 11.844 |     0.022 |     0.025 |     0.022 |  2.34013544202409
   0.047375 | -0.006703 | 13.514 | 13.189 | 13.167 |     0.029 |     0.031 |     0.033 |  2.87081085523336

The stored function fTwomassGetNearbyObjEq() does not perform sort by distance. If you do need it you can add "ORDER BY", as in:

SELECT o.ra,o.dec,o.j_m,o.h_m,o.k_m,o.j_msigcom,o.h_msigcom,o.k_msigcom,n.distance
FROM fTwomassGetNearbyObjEq(0,0,3) n, twomass o
WHERE n.objid = o.objid
ORDER BY n.distance;

Box Search

Use the stored function fTwomassGetNearbyObjFromBoxCel(). This function takes coordinate system ('j2000', 'b1950', 'ecliptic' or 'galactic'), longitude(deg), latitude(deg), search box half width (arcmin; 5400 maximum), search box half height (arcmin; 5400 maximum) as the arguments.

This example searches the region of width=6' and height=2' whose center is (l,b)=(12.0,34.0).

2MASS=> SELECT * FROM fTwomassGetNearbyObjFromBoxCel('galactic', 12,34, 3,1);

A table that contains objid, lon, and lat will be returned. lon and lat are longitude and latitude in the coordinate system specified in the first argument.

   objid   |       lon        |       lat        
-----------+------------------+------------------
 791409307 | 11.9461680949935 | 34.0096860074137
 791409308 | 11.9583757299871 |  34.015082591074
 791409311 | 11.9783906896371 | 34.0105966402837
 791409315 | 11.9505030961589 |  33.986455172412
 791409320 |  11.972281681199 | 33.9903131870409
 791409314 | 11.9812358007798 | 34.0060314550454
 791731741 | 12.0181080800759 | 34.0075228040278
 791409325 | 11.9916294535507 | 33.9913928836596
 791409328 | 11.9875644762627 | 33.9867387367315
 791731743 |  12.019301859339 | 34.0023027320602
 791731745 | 12.0481339981243 | 34.0058079804499
 791731746 |  12.046615046783 | 34.0033867068321
 791731752 | 12.0313248764534 | 33.9836666790509

If you want corresponding records in the Main Table you can join the return value from the stored function to the Main Table using objid as a primary key. This example results in the coordinates, flux, and flux error being retrieved:

SELECT o.ra,o.dec,n.lon as l, n.lat as b,
       o.j_m,o.h_m,o.k_m,o.j_msigcom,o.h_msigcom,o.k_msigcom
FROM fTwomassGetNearbyObjFromBoxCel('galactic', 12,34, 3,1) n, twomass o
WHERE n.objid = o.objid;

The result will be somewhat like this:

     ra     |    dec    |        l         |        b         |  j_m   |  h_m   |  k_m   | j_msigcom | h_msigcom | k_msigcom 
------------+-----------+------------------+------------------+--------+--------+--------+-----------+-----------+-----------
 243.573157 | -0.525274 | 11.9461680949935 | 34.0096860074137 | 16.137 | 15.373 |  15.25 |     0.104 |     0.138 |     0.216
 243.574301 | -0.513863 | 11.9583757299871 |  34.015082591074 | 15.281 | 14.835 | 14.586 |     0.056 |      0.07 |     0.117
 243.587256 | -0.502569 | 11.9783906896371 | 34.0105966402837 | 15.316 | 14.679 | 14.613 |     0.058 |     0.064 |     0.121
 243.594462 |  -0.53521 | 11.9505030961589 |  33.986455172412 | 15.913 | 15.168 | 14.912 |     0.092 |     0.094 |      0.15
 243.601299 | -0.518057 |  11.972281681199 | 33.9903131870409 | 16.041 | 15.671 | 15.173 |     0.113 |     0.149 |          
 243.592362 | -0.503147 | 11.9812358007798 | 34.0060314550454 | 15.935 | 15.398 | 15.408 |     0.093 |     0.125 |          
 243.608113 | -0.476909 | 12.0181080800759 | 34.0075228040278 | 16.299 | 15.204 | 14.744 |     0.137 |     0.098 |     0.153
 243.609321 | -0.504123 | 11.9916294535507 | 33.9913928836596 | 12.818 | 12.396 | 12.308 |     0.025 |     0.026 |     0.033
 243.611316 | -0.509512 | 11.9875644762627 | 33.9867387367315 |  16.43 | 15.689 | 16.224 |     0.147 |     0.133 |          
 243.613003 | -0.478987 |  12.019301859339 | 34.0023027320602 | 16.084 | 15.673 | 15.245 |     0.101 |     0.144 |     0.205
 243.623368 | -0.457166 | 12.0481339981243 | 34.0058079804499 | 15.955 | 15.405 | 15.527 |     0.096 |     0.102 |     0.242
 243.624682 | -0.459558 |  12.046615046783 | 34.0033867068321 | 16.816 |  15.84 | 15.839 |     0.201 |     0.159 |          
 243.634037 | -0.481054 | 12.0313248764534 | 33.9836666790509 | 15.595 |  15.18 | 15.008 |     0.071 |     0.098 |     0.157

Rectangular Search

Use the stored function fTwomassGetObjFromRectEq(). This function takes R.A.1(deg), R.A.2(deg), Dec.1(deg), and Dec.2(deg) as the arguments, in that order:

2MASS=> SELECT * FROM fTwomassGetObjFromRectEq(0,0.1, 1,1.1);

A table that contains objid, lon, and lat will be returned. lon and lat are R.A. and Dec. of J2000.

   objid   |   lon    |   lat    
-----------+----------+----------
 796325619 | 0.000857 | 1.024927
 796325620 | 0.000901 | 1.088982
 796325621 | 0.010481 | 1.091584
 796325622 | 0.012691 | 1.059668
 796325623 | 0.021387 |  1.06185
 796325624 | 0.022389 | 1.044861
 796325625 | 0.022943 | 1.076892
 796325626 | 0.026073 | 1.097096
 796325627 | 0.029074 | 1.012373
 796325628 | 0.044551 |  1.09418
 796325629 | 0.047209 | 1.004132
 796325630 |  0.04838 | 1.030788
 796325631 | 0.058135 |  1.01824
 796325632 | 0.063318 | 1.057747
 796325633 | 0.070774 | 1.089212
 796325634 | 0.071413 | 1.019535
 796325635 | 0.081828 | 1.000061
 796325636 | 0.084937 | 1.084082
 796325637 | 0.090331 | 1.089642
 796325638 | 0.091378 | 1.039778

If you want corresponding records in the Main Table you can join the return value from the stored function to the Main Table using objid as a primary key. This example results in the coordinates, flux, and flux error being retrieved:

SELECT o.ra,o.dec,o.j_m,o.h_m,o.k_m,o.j_msigcom,o.h_msigcom,o.k_msigcom
FROM fTwomassGetObjFromRectEq(0,0.1, 1,1.1) n, twomass o
WHERE n.objid = o.objid;

The result will be somewhat like this:

    ra    |   dec    |  j_m   |  h_m   |  k_m   | j_msigcom | h_msigcom | k_msigcom 
----------+----------+--------+--------+--------+-----------+-----------+-----------
 0.000857 | 1.024927 | 16.617 | 15.804 | 15.515 |     0.113 |     0.135 |      0.16
 0.000901 | 1.088982 |  8.219 |  8.063 |   7.95 |     0.027 |     0.046 |     0.023
 0.010481 | 1.091584 | 14.662 | 14.041 |  14.01 |      0.03 |     0.036 |     0.049
 0.012691 | 1.059668 | 14.633 |  14.23 | 14.207 |     0.035 |     0.041 |      0.05
 0.021387 |  1.06185 | 16.225 | 15.745 | 15.592 |     0.078 |     0.106 |     0.174
 0.022389 | 1.044861 | 14.637 | 14.105 | 13.822 |     0.036 |     0.033 |     0.045
 0.022943 | 1.076892 | 16.962 | 16.611 | 15.644 |     0.146 |     0.234 |     0.188
 0.026073 | 1.097096 | 16.645 | 15.905 | 15.457 |     0.134 |     0.162 |      0.19
 0.029074 | 1.012373 | 16.243 | 15.586 | 15.026 |     0.093 |     0.125 |     0.121
 0.044551 |  1.09418 | 15.534 | 14.906 | 14.484 |     0.051 |     0.067 |      0.08
 0.047209 | 1.004132 | 16.932 | 16.155 | 15.264 |     0.182 |     0.238 |     0.166
  0.04838 | 1.030788 | 16.468 | 15.803 | 14.991 |     0.129 |     0.206 |      0.13
 0.058135 |  1.01824 | 16.556 | 15.736 | 15.363 |     0.113 |     0.136 |     0.169
 0.063318 | 1.057747 | 16.131 | 15.499 | 15.535 |     0.096 |     0.122 |     0.192
 0.070774 | 1.089212 | 16.183 | 15.689 | 15.346 |           |     0.162 |          
 0.071413 | 1.019535 | 14.407 | 14.002 | 13.819 |     0.033 |     0.031 |     0.054
 0.081828 | 1.000061 |  14.45 | 13.841 | 13.682 |     0.032 |     0.033 |     0.049
 0.084937 | 1.084082 | 16.145 | 15.555 | 15.246 |      0.08 |     0.133 |     0.153
 0.090331 | 1.089642 | 16.741 | 16.307 | 15.551 |     0.157 |     0.246 |     0.194
 0.091378 | 1.039778 | 15.616 | 15.183 |  15.39 |     0.056 |     0.109 |     0.177

Table Columns

You can use "\d" to retrieve information about table columns.

2MASS=> \d twomass
 objid         | integer          | not null
 designation   | character(17)    | not null
 ra            | double precision | not null
 dec           | double precision | not null
 err_maj       | real             | not null
 err_min       | real             | not null
 err_ang       | smallint         | not null
 j_m           | real             | 
 j_cmsig       | real             | 
 j_msigcom     | real             | 
 j_snr         | real             | 
 h_m           | real             | 
 h_cmsig       | real             | 
 h_msigcom     | real             | 
 h_snr         | real             | 
 k_m           | real             | 
 k_cmsig       | real             | 
 k_msigcom     | real             | 
 k_snr         | real             | 
(Ommitted)

Cross-ID (Matches with Another Catalog)

You will first need to register the catalog as a table, which will then be matched with the database. Create the table through consulting the official documents of PostgreSQL, etc. If you are planning to use the table for a long time you may wish to create it using admin priviledges. Note that the table created should include R.A.(deg) and Dec.(deg) of J2000 in its columns.

The following example results in the catalog in my_catalog and the 2MASS catalog being matched using the radius of 0.25 arcmin, and returning the count:

SELECT count(fTwomassGetNearestObjIDEq(o.ra, o.dec, 0.25))
FROM ( SELECT * FROM my_catalog ORDER BY dec ) o;

In the following example all the records in my_catalog will be retrieved with the coordinates (R.A. and Dec.) and distance of 2MASS appended to the right of my_catalog:

SELECT p.*,
       q1.objid, q1.ra, q1.dec,
       (CASE (q1.objid IS NULL) WHEN true THEN null ELSE
       fDistanceArcminEq(p.ra,p.dec,q1.ra,q1.dec) END) as distance
FROM
(
  SELECT o.*
  FROM my_table o
  ORDER BY dec
) p
LEFT JOIN
  Twomass q1
ON 
  fTwomassGetNearestObjIDEq(p.ra, p.dec, 0.25) = q1.objid

If you wish to retrieve successfully matched lines only you could write a little more simply:

SELECT p.*,
       q1.objid, q1.ra, q1.dec,
       fDistanceArcminEq(p.ra,p.dec,q1.ra,q1.dec) as distance
FROM
(
  SELECT o.*
  FROM my_table o
  ORDER BY dec
) p
JOIN
  Twomass q1
ON 
  fTwomassGetNearestObjIDEq(p.ra, p.dec, 0.25) = q1.objid

Useful Stored Functions Specific to 2MASS Kit

Some stored functions, created for AKARI-CAS, are included.
For the functions provided by PostgreSQL visit this page of AKARI-CAS.

Angle Calculation

You can use fDistanceArcminEq(ra1, dec1, ra2, dec2) or fDistanceArcMinXYZ(cx1, cy1, cz1, cx2, cy2, cz2) to calculate the angle between two objects. Assigning NULL to any of the arguments will have undefined results.

Conversion from deg to sexagesimal(xx:xx:xx.x)

You can use fDeg2LonStr() and fDeg2LatStr() to convert the value in deg to sexagesimal(xx:xx:xx.x), as in:

SELECT fDeg2LonStr(o.ra) as ra, fDeg2LatStr(o.dec) as dec,
       o.j_m,o.h_m,o.k_m, n.distance
FROM fTwomassGetNearbyObjEq(0,0,3) n, twomass o
WHERE n.objid = o.objid
ORDER BY n.distance;

The result will be:

     ra      |     dec     |  j_m   |  h_m   |  k_m   |     distance      
-------------+-------------+--------+--------+--------+-------------------
 00:00:02.30 | -00:00:03.0 | 16.422 | 16.112 |  15.21 | 0.575981326971027
 23:59:57.51 | +00:00:44.4 |  13.75 | 13.194 |  13.14 | 0.967104621472398
 23:59:57.43 | +00:01:40.1 | 15.773 | 15.347 | 14.972 |  1.78718165359994
 00:00:07.34 | -00:00:22.6 |  14.43 | 14.085 | 13.923 |  1.87405693715596
 00:00:05.17 | -00:01:25.5 | 17.025 | 15.841 | 17.233 |  1.92324084931489
 00:00:04.17 | -00:01:58.6 | 15.591 | 15.023 | 15.069 |  2.23489923964895
 00:00:09.35 | +00:00:05.5 | 12.682 | 12.096 | 11.844 |  2.34013544202409
 00:00:08.36 | -00:01:41.9 | 15.644 | 15.355 | 15.129 |  2.69347254510121
 23:59:55.82 | +00:02:38.6 | 12.842 | 12.374 | 12.335 |  2.84218284794452
 00:00:11.37 | -00:00:24.1 | 13.514 | 13.189 | 13.167 |  2.87081085523336

Conversion from sexagesimal(xx:xx:xx.x) to deg

You can use the format sexagesimal(xx:xx:xx.x) to specify the coordinates of the search:

SELECT fDeg2LonStr(o.ra) as ra, fDeg2LatStr(o.dec) as dec,
       o.j_m,o.h_m,o.k_m, n.distance
FROM fTwomassGetNearbyObjEq(fLonStr2Deg('11:22:33.3'),fLatStr2Deg('22:33:44.4'),3) n, twomass o
WHERE n.objid = o.objid
ORDER BY n.distance;

The result will be:

     ra      |     dec     |  j_m   |  h_m   |  k_m   |     distance      
-------------+-------------+--------+--------+--------+-------------------
 11:22:31.39 | +22:33:04.7 | 16.137 | 15.803 | 15.808 | 0.794373037809949
 11:22:36.24 | +22:34:12.4 | 16.728 | 15.787 | 15.138 | 0.823298100602107
 11:22:34.17 | +22:34:33.9 | 16.658 | 15.962 | 15.287 |  0.84883116250746
 11:22:37.70 | +22:33:42.5 | 16.746 | 15.973 |  15.79 |  1.01713844965725
 11:22:34.57 | +22:32:44.2 | 16.729 | 15.875 |     17 |  1.04481387810461
 11:22:29.11 | +22:34:25.0 | 16.971 | 16.102 | 15.431 |  1.17931773570099
 11:22:27.70 | +22:33:04.7 | 16.197 | 15.689 | 15.014 |  1.45335381456842
 11:22:36.10 | +22:35:29.7 | 16.009 | 15.623 | 14.811 |  1.86948967595817
 11:22:22.99 | +22:33:16.5 | 16.656 | 16.009 | 15.635 |  2.42576801128236
 11:22:33.53 | +22:36:25.4 | 16.165 | 15.278 | 14.937 |  2.68444346193211
 11:22:21.60 | +22:34:34.4 |   16.5 | 15.154 | 15.144 |  2.82628524515026
 11:22:45.61 | +22:34:13.3 | 13.216 | 12.736 | 12.668 |  2.88203892253219
 11:22:31.09 | +22:36:37.8 | 16.659 | 16.118 | 15.246 |  2.93504711436251
 11:22:31.65 | +22:30:48.1 |  16.29 |  15.87 | 15.435 |  2.96366827957033

Coordinate Conversion (J2000 from/to B1950, Ecliptic, or Galactic)

I have prepared a number of stored functions, reusing part of the source code of WCSTools, for use in SQL statements to convert major coordinate systems into J2000, and vice versa.

The following are stored the functions used to convert B1950, Ecliptic, and Galactic into J2000:

Stored FunctionsArgumentsReturn Value
fB2Ra(ra,dec)B1950 coordinatesJ2000 R.A.
fB2Dec(ra,dec)B1950 coordinatesJ2000 Dec.
fE2Ra(lon,lat)Ecliptic coordinatesJ2000 R.A.
fE2Dec(lon,lat)Ecliptic coordinatesJ2000 Dec.
fG2Ra(lon,lat)Galactic coordinatesJ2000 R.A.
fG2Dec(lon,lat)Galactic coordinatesJ2000 Dec.

The following are the stored functions used to convert J2000 into B1950, Ecliptic, and Galactic:

Stored FunctionsArgumentsReturn Value
fJ2Ra1950(ra,dec)J2000 coordinatesB1950 R.A.
fJ2Dec1950(ra,dec)J2000 coordinatesB1950 Dec.
fJ2Lambda(ra,dec)J2000 coordinatesEcliptic Lon.
fJ2Beta(ra,dec)J2000 coordinatesEcliptic Lat.
fJ2L(ra,dec)J2000 coordinatesGalactic Lon.
fJ2B(ra,dec)J2000 coordinatesGalactic Lat.

The following is an example of performing a Radial Search with Galactic coordinate (0,0):

SELECT fJ2L(o.ra,o.dec) as l, fJ2B(o.ra,o.dec) as b,
       o.j_m,o.h_m,o.k_m, n.distance
FROM fTwomassGetNearbyObjEq(fG2Ra(0,0),fG2Dec(0,0),0.2) n, twomass o
WHERE n.objid = o.objid
ORDER BY n.distance;

The result will be:

          l           |           b           |  j_m   |  h_m   |  k_m   |      distance      
----------------------+-----------------------+--------+--------+--------+--------------------
 0.000238199212139686 |  0.000245096272298005 | 17.133 |  13.88 | 11.912 | 0.0205064415510798
     359.999429961829 |  -0.00149404269361704 | 17.089 | 13.623 | 12.001 | 0.0959458926395396
  0.00199983911961157 | -4.01722558191383e-05 | 16.407 | 12.665 | 10.888 |  0.120014534784677
     359.998106567874 |  0.000932992925588326 | 17.443 | 13.571 | 11.703 |  0.126649154498087
  0.00287219868966155 |  0.000670744958130914 | 16.467 | 12.758 | 10.874 |  0.176968660924733
  0.00261470909270918 |  -0.00204362165890217 | 16.831 | 13.607 | 12.482 |  0.199115956417241

Client Tools for 2MASS Kit server

sql2mass.php

client/sql2mass.php is an example program of 2MASS Kit client.

Before using this script, you have to install php and some libraries:

# yum install php
# yum install php-pgsql
# yum install php-pear
# pear install MDB2
# pear install pear/MDB2#pgsql

and, you should replace PASSWORD with actual one of $Dsn in sql2mass.php:

$Dsn = "pgsql://guest:PASSWORD@127.0.0.1:5432/2MASS";

Run sql2mass.php with setting an SQL statement on 1st argument:

$ php sql2mass.php "SELECT * FROM twomass LIMIT 10"

Warning: This script is not secure at all for Web applications that accept direct SQL input.


FAQ

Q: Is it available for casual use?

Yes. You can try searching the 2MASS catalog at SQL Search of AKARI-CAS.
(2MASS Kit reuses part of the source code of AKARI-CAS).

Q: initdb fails!

A: Check the owner and mode of the directories; for example, if you are creating a database in the directory /aaa/bbb/db/pgsql all of the directories /aaa, /aaa/bbb, and /aaa/bbb/db should be owned by root:root (your mileage may vary, depending on your OS) with the mode set to 755.

Q: md5sum of data files are correct, however, data registration fails!

A: Please use the same version of psql as your PostgreSQL server. Using older version of psql might cause errors that indicate internal problem of PostgreSQL server.

Q: Three days have already passed but the data still hasn't finished being registered in the Main Table!

A: Try a simple measurement of your HDD performance, as in:

$ su
# /sbin/hdparm -t /dev/sda

If the result of this test is below 100 MB/sec you may have problems with your S-ATA interface.
Some chipsets have several operational modes for the S-ATA interface, in which case you can usually tweak them using the BIOS settings. In addition, some manufacturers ship their PCs with BIOS settings not optimally suitable for Linux. DELL's PCs are particularly notorious for such problems, with many people traditionally having faced this problem.
If you only have Linux installed you can re-create initrd with a setting like "alias scsi_hostadapter ata_piix (in the case of Intel chipsets)" in the file /etc/modprobe.conf and set the BIOS S-ATA operational mode to "native", etc. for your S-ATA interface to have better performance.
If you are dual booting with Windows or you do not understand initrd you may very well be better off without an on-board S-ATA interface and instead buy an S-ATA interface card (e.g. LSI 9211-4i, ASUS U3S6, but you will need a free PCIe x4 or better slot) to connect HDDs to and then build a DB on them.

Q: What is objid?

A: objid does not exist in the original catalogs and was appended for the purpose of speeding up searches in this kit. objid is a four-byte or eight-byte integer defined as "offset + the line number", and is used as the primary key. You should not put these IDs on your paper, etc.

Q: Searches are somewhat slow immediately after booting up my machine!!

A: Unless your disk is super-fast the search speed immediately after booting will be slowed down by the overall speed of the disk. In order to solve this problem you will need to move all the tables and indices to a fast SSD. The 2MASS Kit creates all the tables and indices in the table spaces in an attempt to meet that demand. You can copy /db/pgsql/tablespace and all the data in it onto a fast SSD, which in turn is mounted as table spaces (using the bind option) to enable fast searches from just after the machine is booted.

Q: What are the licensing terms for this kit?

A: The kit is licensed under GPL V2. The dataset is under each original licensing.