Locked History Actions

LSST Galaxy Catalog Access

Install FreeTDS (I got v0.91) to provide the driver needed by ODBC to talk to the remote Microsoft SQL Server. On OS X Mountain Lion, I used:

./configure
make
sudo make install

Append the following to /usr/local/etc/freetds.conf:

[lsst]
    host = fatboy.npl.washington.edu
    port = 1433
    tds version = 7.0

Install the python ODBC library (I got v3.0.6). On OS X Mountain Lion, I just used the following after downloading the package:

python setup.py build
sudo python setup.py install

Install Cython (0.17.1), which is a pre-requisite for pymssql:

python setup.py build
sudo python setup.py install

Install pymssql (2.0.0b1). First, replace setup.py with this version, then follow the usual steps:

python setup.py build
sudo python setup.py install

Interactive test from the cmd-line (does not use pyodbc layer):

tsql -H fatboy.npl.washington.edu -p 1433 -U LSST-2 -P L$$TUser
tsql -S lsst -U LSST-2 -P L$$TUser

Interactive test in python, following this guide:

import pyodbc
cnxn = pyodbc.connect('DRIVER=/usr/local/lib/libtdsodbc.so;SERVER=fatboy.npl.washington.edu;UID=LSST-2;PWD=L$$TUser;DATABASE=LSST;PORT=1433;')

Interactive test in python using pymssql following this guide:

import pymssql
conn = pymssql.connect(host='fatboy.npl.washington.edu', user='LSST-2', password='L$$TUser', database='LSST', port=1433)

Interactive test in python using _mssql following this guide:

import _mssql
conn = _mssql.connect(server='fatboy.npl.washington.edu', user='LSST-2', password='L$$TUser', database='LSST', port=1433)
print conn.tds_version
conn.execute_query("Select name from sysobjects where type like 'u'")
for row in conn: print row['name']
conn.execute_query("select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'galaxy'")
for row in conn: print row[0]
conn.execute_scalar("select count(*) from galaxy")
conn.close()

The version reported is 8.0. The list of tables produced by this code is:

bh_microlensvar
St4_50813_four
stars_partition_11600000000000_sansM
cepheid_stage
testResults
starsMdwarf_11600000000000
galaxy_keyid
S0_50813
galaxy_agn
cepheidstarsold
S2_50813
Sc_four
cepheidvarold
Sc_50813
SS_four
SR_50813
ST_four
St6_50813_bbox
galaxy_bulge
SS_50813
ST_50813
St6_four
St4_50813
St7_four
St5_50813
St8_four
obshistid_y5_rerun
St6_50813
St7_50813
stars_sedfilename
St8_50813
Sc_bbox
St7_50813_bbox
SS_bbox
stars_partition_14000000000000
ST_bbox
St5_bbox
dwarfGalaxies
SSMTableNames_y5
St6_bbox
St7_bbox
St8_bbox
galaxy_old_0415
tiles
S0_49383
S0_50813_bbox
S0_staging_50813
S2_staging_50813
Sc_staging_50813
SR_staging_50813
starsOldSelectTop100000
SS_staging_50813
St8_50813_bbox
ST_staging_50813
starsSelectTop10000
St4_staging_50813
starsCenteredp80m10
St5_staging_50813
St6_staging_50813
St7_staging_50813
St8_staging_50813
output_opsim3_61
stars_partition_8700000000000_circle80m10
LightcurveDecRange
density_map_norm_starsWD
galaxy_oldTable_UntilDec2010
S2_50813_test
density_map_norm_starsMSRGB
mdvartmp
stars_binned_floor_by_r
starsStaging
density_map_norm_starsMSRGB_by_rmag
stars_partition_8700000000000_sansM
orbits
starsMdwarf_8700000000000
stars_partition_11000000000000_sansM
stars_partition_11000000000000
stars_partition_8700000000000
S2_50813_bbox
Sc_50813_bbox
stars_partition_11600000000000
stars_partition_12000000000000
galaxy_staging
stars_partition_12500000000000
starsBHB
stars
starsRRLy
SSMOrbits_y5
starsWD
SR_50813_bbox
S0_49353_bbox
S0_49383_bbox
variabilityMap
image
lens
imagevar
SS_50813_bbox
ST_50813_bbox
SsmLookup_y5
St4_50813_bbox
galaxy_old_0407
galaxy
St5_50813_bbox
Sc
gal_cat
orbits_50813
SS
orbits_50843
ST
orbits_50873
St5
orbits_50903
starsMdwarf_11000000000000
stars_partition_11800000000000
St6
mdwarfvar
orbits_50933
St7
orbits_50963
sedIdMap
St8
orbits_50993
bh_mlvartmp
S0_49353_four
ebvar_stage
orbits_51023
mlvartmp
RRly_lc
S0_49383_four
ebstarsold
orbits_51053
starsrrlyvar
orbits_51083
agnvar
ebvarold
orbits_51113
microlens_stage
orbits_51143
SSMTableNames
microlensvar
orbits_51173
ebstars
ebvar
AstromEasterEggs
imageold
lensold
stars_partition_12200000000000
AstromEasterEggsold
S0_50813_four
cepheidstars
S2_50813_four
Sc_50813_four
cepheidvar
SR_50813_four
amcvnvar
bh_mlens_stage
sysdiagrams

The number of entries in galaxy is 17428284, and the list of columns is:

htmid
galid
ra
dec
redshift
rad_vel
u_ab
g_ab
r_ab
i_ab
z_ab
y_ab
sedid_bulge
sedid_disk
sedid_agn
av_b
rv_b
ext_model_b
av_d
rv_d
ext_model_d
glon
glat
pa_bulge
pa_disk
inc_bulge_deg
inc_disk_deg
a_b
b_b
bulge_n
bra
bdec
a_d
b_d
disk_n
dra
ddec
agnra
agndec
versionid
fluxnorm_bulge
fluxnorm_agn
absmag_r_total
gal_type
mass_stellar
mass_gas
mass_halo
ug_total_rest
gr_total_rest
ri_total_rest
iz_total_rest
zy_total_rest
isagn
agn_tau
agn_sfu
agn_sfg
agn_sfr
agn_sfi
agn_sfz
agn_sfy
fluxnorm_disk
magnorm_bulge
magnorm_disk
magnorm_agn
cx
cy
cz
geopoint
sedname_agn
sedname_bulge
sedname_disk
varsimobjid
varParamStr
type
mass_bulge
BulgeMinusDiskLSSTr
log10BulgeToTotLSSTrFlux
AbsiMag
AbsiMag0_1
z0_1umg
z0_1gmr
z0_1rmi
z0_1imz
z0_1zmy
ba_disk
ba_bulge
DiskHalfLightRadius
DiskLSSTu
DiskLSSTg
DiskLSSTr
DiskLSSTi
DiskLSSTz
DiskLSSTy
BulgeLSSTu
BulgeLSSTg
BulgeLSSTr
BulgeLSSTi
BulgeLSSTz
BulgeLSSTy
AGNID
BulgeHalfLightRadius
t0_agn
myid
id

The columns needed to produce a GalSim image are:

ra
dec
u_ab
g_ab
r_ab
i_ab
z_ab
y_ab
pa_bulge
pa_disk
inc_bulge_deg
inc_disk_deg
DiskHalfLightRadius
BulgeHalfLightRadius
magnorm_bulge
magnorm_disk

Notes on the current db contents:

  • inc_%s_deg is always -1000
  • %sra and %sdec are SQL NULL
  • pa_bulge == pa_disk (or else one is zero) and units are deg 0-360
  • fluxnorm_%s is always zero
  • magnorm_%s and sedname_%s are SQL NULL when component is not present
  • sedid_%s is -1 when component is not present
  • sedid can be different for the same sedname
  • %sHalfLightRadius is equal to a_%s
  • Magnorm is the normalization of the galaxy spectrum at lambda=5000 (prior to redshifting) that will produce the magnitudes defined in the database
  • Bulge and disk do not necessarily have the same sedname and sedid can differ from sedname

The gcat.py script takes about 50s to extract 853,717 rows covering 1 sq. deg. at ra x dec = (-1,0) x (0,1). Convert to ROOT using:

text2root -i gcat.dat -o 'gcat.root[gcat]' --verbose

I updated the script to use the stored procedure described here on 14 July 2013.