13. Geometries

The main extension of ADQL wrt SQL is addition of geometric functions. Unfortunately, these were not particularly well designed, but if you don’t expect too much, they’ll do their job.

Keep the crossmatch pattern somewhere handy (everything is in degrees):

SELECT TOP 5
  source_id, tgas.ra, tgas.dec, tm.raj2000,
tm.dej2000, hmag, e_hmag
FROM gaiadr1.tgas_source as tgas
JOIN twomass AS tm
ON 1=CONTAINS (
     POINT('ICRS', tm.raj2000, tm.dej2000),
     CIRCLE('ICRS', tgas.ra, tgas.dec, 1.5/3600))

In theory, you could use reference systems other than ICRS (e.g., GALACTIC, FK4) and hope the server converts the positions, but I’d avoid constructions with multiple systems – even if the server implements the stuff correctly, it’s most likely going to be slow.

When catalogs are on different epochs, you may need to account for proper motions to match faster stars. You should, however, not apply the proper motions in the primary selection. If you do that, the index cannot be used, and your query will waste a lot of CPU and disk bandwidth. Instead, decide about the maximum proper motion your objects might have (to get an idea, of the statistics, try selecting the fastest stars from ppmx.data – apart from the fact that the catalog got the fastest stars pretty wrong with two copies of some fast stars, there’s only a handful stars moving faster than four arcsecs per year).

Then multiply this with your epoch difference and make that your initial crossmatch radius. Then filter out the spurious matches with an extra where clause taking into account the proper motions. For moderate epoch differences, don’t worry about going into the tangential plane to apply proper motions and, for now, say something like

SELECT
   TOP 30
   *
   FROM ppmxl AS m
   JOIN gaiadr2.gaia_source AS g
   ON 1=CONTAINS(POINT('ICRS', m.raj2000, m.dej2000),
                 CIRCLE('ICRS', g.ra, g.dec, 30./3600.))
WHERE 1=CONTAINS(POINT('ICRS',
m.raj2000+m.pmra*COS(RADIANS(m.dej2000))*15,
m.dej2000+m.pmde*15),
  CIRCLE('ICRS', g.ra, g.dec, 0.5/3600.))

The 15 is because Gaia DR1 is on J2015, whereas PPMXL is on J2000. Also, be careful with the units – in many catalogs, positions and proper motions are given in different units.

Also note how the outer PM-based filter is just a WHERE-clause. Since JOIN is a combination of operators of the relational algebra, the result of a join is a relation again and thus can be treated like any other table.