-
Notifications
You must be signed in to change notification settings - Fork 0
/
vo_cone_search.py
184 lines (172 loc) · 7.45 KB
/
vo_cone_search.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
# -*- coding: utf-8 -*-
"""
Created on Mon Dec 15 21:22:39 2014
@author: minz
"""
from .prettiesttable import from_db_cursor
from .globals import get_conn, JINJA
from .utils import dict_values_to_list
from astropy.coordinates import SkyCoord
from astropy import units as u
def build_key_constraint(conn, key, subkey_cond, condition):
"""
Create an advanced constraint on the key/subkey pair.
Might involve inequalities on the key value.
Example:
constraint = build_key_constraint(conn, 'z', 'subkey is null',
'> 0.5')
"""
cur = conn.cursor()
tables = cur.execute("""select reference_table, reference_column, uid_column
from reference_tables_keys k
join reference_tables t on t.table_name = k.reference_table
where key = '%s'
and %s""" % (key, subkey_cond)).fetchall()
conditions = []
for table in tables:
cond = """exists (select 1 from data_references dr
join [%s] x on dr.reference_uid = x.[%s]
where dr.cluster_uid = c.uid
and dr.reference_table = '%s'x
and x.[%s] %s)""" % (table[0], table[2],
table[0], table[1], condition)
conditions.append(cond)
return 'and (%s)' % ' or '.join(conditions)
def get_subkey_constraint(keysubkey):
"""
Get a proper subkey constrain from key-subkey string.
"""
if ',' in keysubkey:
key, subkey = keysubkey.split(',')
if subkey != 'all subkeys':
subkey_cond = "k.subkey ='%s'" % subkey
else:
subkey_cond = '1=1'
else:
key = keysubkey
subkey_cond = 'k.subkey is null'
return key, subkey, subkey_cond
def get_cone_search_constraint(args):
"""
Create a VO cone search constraint.
"""
ra = args['ra']
decl = args['decl']
if ':' in ra or ':' in decl:
coord = SkyCoord(ra, decl, unit=(u.hourangle, u.deg))
elif 'h' in ra:
coord = SkyCoord(ra, decl, unit=(u.hourangle, u.deg))
elif '.' in ra:
coord = SkyCoord(ra, decl, unit='deg')
else:
coord = SkyCoord(ra, decl, unit=(u.hourangle, u.deg))
ra = coord.ra.deg
decl = coord.dec.deg
radius = args['radius']
condition = """
and haversine(c.ra, c.dec, {0}, {1}) < {2}/60.""".format(ra, decl,
radius)
extra_column = """ haversine(c.ra, c.dec, {0}, {1}) as "_r (arcmin)",
""".format(ra, decl)
return condition, extra_column
def get_table_constraint(table, exists):
"""
Create a table filter.
"""
return """ and %s (select 1
from data_references dr
where dr.cluster_uid = c.uid
and dr.reference_table = '%s')""" % (table, exists)
def vo_cone_search(args):
"""
Performs a cluster search request.
Possible args keys are:
ra, decl - cone center in degrees;
radius - cone radius in arcmin;
fullsky - perform full-sky search. Cone constraints are ignored;
in_table - name of the table or list of table names,
constraint will be set on cluster presence/
absence in this table(s);
has_record - list of 'exists' or 'not exists' strings,
one for each value in in_table;
condition \
in_key |
constraint|
expression/
in_moc - name of MOC or list of MOCs,
constraint will be set on cluster entering/
not entering the MOC area;
has_moc - list of 'exists' or 'not exists' strings,
one for each value in in_moc;
"""
conn = get_conn()
conditions = []
if 'fullsky' not in args:
constraint, extra_columns = get_cone_search_constraint(args)
conditions.append(constraint)
else:
extra_columns = ''
if 'in_table' in args:
if isinstance(args['in_table'], str):
conditions.append(get_table_constraint(args['has_record'],
args['in_table']))
else:
for itable, table in enumerate(args['in_table']):
conditions.append(
get_table_constraint(args['has_record'][itable], table))
if 'condition' in args:
extra_counter = 0
args = dict_values_to_list(args, ['condition', 'in_key',
'constraint', 'expression'])
for icondition, condition in enumerate(args['condition']):
key, subkey, subkey_cond = \
get_subkey_constraint(args['in_key'][icondition])
if condition != 'extra':
conditions.append("""and %s (select 1
from reference_tables_keys k
join data_references dr on dr.reference_table = k.reference_table
where dr.cluster_uid = c.uid
and key = '%s'
and %s)""" % (condition, key, subkey_cond))
else:
expr = '%s %s' % (args['constraint'][extra_counter],
args['expression'][extra_counter])
conditions.append(build_key_constraint(conn, key, subkey_cond,
expr))
extra_counter = extra_counter + 1
if 'has_moc' in args:
args = dict_values_to_list(args, ['has_moc', 'in_moc'])
for cond, moc in zip(args['has_moc'], args['in_moc']):
conditions.append(""" and %s (select 1
from cluster_in_moc m
where m.uid = c.uid
and m.moc_name = '%s')
""" % (cond, moc))
if 'flag_name' in args:
args = dict_values_to_list(args, ['flag_name', 'flag_constraint',
'xid_values', 'obs_values'])
for iflag, flag in enumerate(args['flag_name']):
if flag == 'obs_flag':
value = args['obs_values'][iflag]
else:
value = args['xid_values'][iflag]
conditions.append("""
and %s %s %s""" % (flag, args['flag_constraint'][iflag], value))
t = JINJA.get_template('vo_cone_search.template')
sql = """select c.uid, %s ra, dec, c.source, source_id, xid_flag, obs_flag,
group_concat(distinct r.reference_table) as Tables
from clusters c
join data_references r on r.cluster_uid = c.uid
where 1=1
%s
group by c.uid, ra, dec, c.source, source_id
order by c.ra""" % (extra_columns, ' '.join(conditions))
print(sql)
t1 = from_db_cursor(conn.execute(sql))
t1.float_format['ra'] = '.5f'
t1.float_format['dec'] = '.5f'
html_data = {'table': t1.get_html_string(attributes={'border': 1,
'id': 'search',
'columns': 'IFFSIIIS'},
unescape=[('Tables')])}
return t.render(html_data)