TImeout for a query in Python client, TOPCAT, and query interface

Question

Dear whom it may concern,

    I have excuted a query (SELECT * FROM nsc_dr2.meas WHERE ra>=4.3683562 and ra<=4.3689118 and dec>=-73.4288328 and dec<=-73.4282772) in Python client, TOPCAT, and query interface and all of them are timeout without any result. Could you check it please? Thanks very much!

Answer 1

Hi, thanks for reaching out.

Direct spatial queries like the one you tried on a huge table such as nsc_dr2.meas will likely time out... There are 68 billion rows to go through, despite indexing on the DB backend etc.

But you can get the desired result by doing your spatial query on the much smaller object table, and then JOIN on the meas table using the object IDs. I've rewritten your query accordingly (this example I ran in a Jupyter notebook):

%%time
query = """
SELECT o.ra, o.dec, m.*
FROM nsc_dr2.object AS o
JOIN nsc_dr2.meas AS m ON o.id = m.objectid
WHERE (o.ra BETWEEN 4.3683562 and 4.3689118) AND (o.dec BETWEEN -73.4288328 and -73.4282772)
"""

result = qc.query(sql=query,fmt='pandas')
print(result.shape)

This returns 25 rows (34 colums) in 200 ms:

(25, 34)
CPU times: user 32.7 ms, sys: 2.94 ms, total: 35.6 ms
Wall time: 201 ms

Best regards,

Robert

Comments

Hi Robert,
Thanks very much for your reply! It is working~
BTW, I would like also point out two things. Firstly, I have installed the datalab client python package, which somehow destroys my anaconda environment. I have to reinstall the whole anaconda. Secondly, there seems to be some discrepancies between median (or mean) magnitudes derived from time-series data and the the weighted-average magnitude in the same filter (from nsc_dr2.object table). Is this because the of aperture size (I cannot find the aperture size used for the weighted-average magnitude, but assume it may be 2")? Thanks very much!~

---

There are various types of photometry in the nsc_dr2.meas table: MAG_AUTO, MAG_APER1, MAG_APER2, MAG_APER4, MAG_APER8.  The first one uses an automatic elliptical aperture set by the shape of the object that SExtractor measures.  This is the photometry that is used for the statistics in the object table.  The other four use fixed circular apertures with diameters of 1", 2", 4" and 8".  Hopefully that helps.
David

---

Got it~ Many thanks~