Crossmatching various catalogues with various Datalab tables

Question

Hi,

I would like to crossmatch a user catalogue that contains about 6000 rows with ls_dr8.tractor and the resulting catalogue with various other SDSS and LS tables. Naively, I would have done something like this:

SELECT

/* Object properties */
CAT.name, CAT.ra, CAT.dec,

/* LS Photometry */

tractor.dered_mag_g, 

/* SDSS photometry */

sdss_phot.dered_g

FROM mydb://mycat AS CAT, ls_dr8.tractor AS tractor

INNER JOIN sdss_dr16.x1p5__specobj__ls_dr8__tractor_primary AS tractor_sdss ON tractor_sdss.id2 = tractor.ls_id
INNER JOIN sdss_dr16.photoplate AS sdss_phot ON sdss_phot.objid = sdss.bestobjid

WHERE 't' = q3c_radial_query(tractor.ra, tractor.dec, CAT.ra, CAT.dec, 0.001)

But this is inefficient and triggers a runtime error if I don't limit the query to a few objects. I tried to set up a nested query but didn't manage to set it up correctly. Could you give me a suggestion to make this query efficient?

Cheers,

Steve

Comments

Try a subquery like this: we select from the results of the xmatch and then inner join.  For a test mycat with a 1000 rows this query runs in less than a couple of seconds.

select match.ls_id, match.ra, match.dec, match.dered_mag_g, sdss_phot.dered_g
from (
    select tractor.ls_id,  CAT.ra, CAT.dec, tractor.dered_mag_g
    FROM mydb://mycat AS CAT, ls_dr8.tractor AS tractor
    WHERE 't' = q3c_radial_query(tractor.ra, tractor.dec, CAT.ra, CAT.dec, 0.001)
) match
INNER JOIN sdss_dr16.x1p5__specobj__ls_dr8__tractor_primary AS tractor_sdss ON tractor_sdss.id2 = match.ls_id
INNER JOIN sdss_dr16.photoplate AS sdss_phot ON sdss_phot.objid = sdss.bestobjid

Hope this helps,
Adam

---

Hi Adam,

Thanks for your help.

I noticed a strange behaviour. My catalogue has a bit more than 5000 entries. Running only the inner query leads to a time out. If I do instead "SELECT TOP 10000 tractor.ls_id,  CAT.ra, CAT.dec, tractor.dered_mag_g", the inner query is done within less than a second. This is odd. The output table will have exactly the same number of rows as the input table.

Do you know why this happens? Because of this behaviour, the refined nested query is not working.

Cheers,

Steve

---

Instead of 0.001 radius try using a smaller one 1/3600 (0.000277)  See how that works, the density of the tractor table  can get quite high I believe.

Answer 1

Thanks for reaching out Steve,  we will respond back here ASAP with suggestions or a query.