Supports 2MASS, WISE, USNO-B1.0, UCAC3, PPMXL, GSC-2.3, etc.
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.
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
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.
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 Criteria | Number of Bodies | Elapsed Time | SQL Statements Used |
---|---|---|---|
Radial (1' of radius) | 2 | 0.001 sec | SELECT count(*) FROM fTwomassGetNearbyObjEq(0, 0, 1); |
Radial (60' of radius) | 5198 | 0.015 sec | SELECT count(*) FROM fTwomassGetNearbyObjEq(0, 0, 60); |
Radial (180' of radius) | 47632 | 0.102 sec | SELECT count(*) FROM fTwomassGetNearbyObjEq(0, 0, 180); |
Radial (360' of radius) | 189784 | 0.368 sec | SELECT count(*) FROM fTwomassGetNearbyObjEq(0, 0, 360); |
Box (120'x120') | 6644 | 0.023 sec | SELECT count(*) FROM fTwomassGetNearbyObjFromBoxCel('j2000', 0,0, 60,60); |
Box (120'x2400') | 136579 | 0.498 sec | SELECT count(*) FROM fTwomassGetNearbyObjFromBoxCel('j2000', 0,0, 60,1200); |
Rectangular (2x2deg) | 6644 | 0.006 sec | SELECT count(*) FROM fTwomassGetObjFromRectEq(-1,1, -1,1); |
Rectangular (10x10deg) | 167266 | 0.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.
We show some examples of additional hardwares for ultra fast search of huge catalogs:
Download following package:
Data files for registration are available at HTTP.
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.
If you are using Linux,
edit /etc/sysconfig/selinux
to disable SELinux.
SELINUX=disabled
Then, reboot your OS.
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.
# Controls the maximum shared segment size, in bytes kernel.shmmax = 68719476736
# Controls the maximum shared segment size, in bytes kernel.shmmax = 3221225472
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.
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
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.
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.
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.
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
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.
Open
/db/pgsql/data/pg_hba.conf
in your favorite editor, and modify authentication method for local to md5.
local all all md5
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
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
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
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.
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 ...
."
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
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.
Disable dynamic CPU clocking for optimum performance:
/etc/rc.d/init.d/cpuspeed stop
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
Tweak these parameters in
/db/pgsql/data/postgresql.conf
as needed.
shared_buffers
work_mem
Generally, shared_buffers
is important for
OLTP (OnLine Transaction Processing), and
OLAP (OnLine Analytical Processing) requires large value of
work_mem
.
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.
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
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.
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;
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
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
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)
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
Some stored functions, created for AKARI-CAS, are included.
For the functions provided by PostgreSQL visit
this page of AKARI-CAS.
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.
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
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
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 Functions | Arguments | Return Value |
---|---|---|
fB2Ra(ra,dec) | B1950 coordinates | J2000 R.A. |
fB2Dec(ra,dec) | B1950 coordinates | J2000 Dec. |
fE2Ra(lon,lat) | Ecliptic coordinates | J2000 R.A. |
fE2Dec(lon,lat) | Ecliptic coordinates | J2000 Dec. |
fG2Ra(lon,lat) | Galactic coordinates | J2000 R.A. |
fG2Dec(lon,lat) | Galactic coordinates | J2000 Dec. |
The following are the stored functions used to convert J2000 into B1950, Ecliptic, and Galactic:
Stored Functions | Arguments | Return Value |
---|---|---|
fJ2Ra1950(ra,dec) | J2000 coordinates | B1950 R.A. |
fJ2Dec1950(ra,dec) | J2000 coordinates | B1950 Dec. |
fJ2Lambda(ra,dec) | J2000 coordinates | Ecliptic Lon. |
fJ2Beta(ra,dec) | J2000 coordinates | Ecliptic Lat. |
fJ2L(ra,dec) | J2000 coordinates | Galactic Lon. |
fJ2B(ra,dec) | J2000 coordinates | Galactic 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/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.
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).
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.
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.
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.
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.
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.
A: The kit is licensed under GPL V2. The dataset is under each original licensing.