3.1. Example Queries

3.1.2. Cross Match

Cross matching is a resource-intensive operation, so expect it to run quite long. However, you can get really good performance if you have one table significantly small in number of rows.

Here we have the Dark Energy Survey Science Verification first Annual survey cross-matching against the GAIA DR1 survey within .001 degrees.

Note here we do not use the JOIN clause in SQL which is one reason it is a resource-intensive query. This is the time you want a cartesian join due to the nature cross-matching. The other thing to notice is the smallest table by number of rows is listed first in the q3c_join() clause (i.e. d.ra_j200 and d.dec_j2000).

SELECT count(*)
  FROM des_sva1.gold_catalog d,
       gaia_dr1.gaia_source g
 WHERE q3c_join(d.ra_j2000, d.dec_j2000, g.ra, g.dec, .001);

Returns 1,307,402 in 179 sec.