4.1. Example Queries

4.1.1. Cone Search

Count objects classified morphologically as “deVauc” galaxies by Tractor in Legacy Surveys DR 9, lying within a radius of 0.1 deg of Ra and Dec. We are using the q3c style spatial indexing to support this query.

SELECT COUNT(*)
  FROM  ls_dr9.tractor
 WHERE type = 'DEV'
       AND
       q3c_radial_query(ra, dec, 286.602228, 43.780606, 0.1)

Returns 14 rows in ~ 13 ms.

In this survey adding the additional conditions shown below will find unique, unresolved (point-like) sources with reasonably clean photometry.

SELECT COUNT(*)
FROM ls_dr9.tractor
WHERE brick_primary = 1
      AND type = 'PSF'
      AND allmask_g = 0 and allmask_r = 0 and allmask_z = 0
      AND fracflux_g < 0.05 and fracflux_r < 0.05 and fracflux_z <  0.05
      AND flux_g > 0 and flux_r > 0 and flux_z > 0
      AND q3c_radial_query(ra, dec, 286.602228, 43.780606, 1.0)

Returns 1,296 rows in ~ 300 ms.

Notice we changed type = ‘PSF’ and expanded the radius to 1.0 degrees.

4.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 of cross-matching. The other thing to notice is the smaller 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 AS d,
       gaia_dr1.gaia_source AS g
 WHERE q3c_join(d.ra_j2000, d.dec_j2000, g.ra, g.dec, .001);

Returns 1,307,402 rows in 179 sec.