Pleiades 2MASS cross-match tutorial

1./ GACS cross-match with hosted table: Raul

Pros: everything within GACS (can be stored, shared, ...)
Cons: no coordinate conversion right now (equinox, proper motion)


2./ Use of pre-computed 2MASS cross-match best neighbour

Pros: fast!
Pros: everything within GACS (can be store, shared, ...)
Pros: physical cross-match (equinox, proper motion, ad-hoc metrics in the future)
Cons: no control on best neighbour, although OK for most purposes
Cons: limited to hosted tables, although many (2MASS, UCAC4, SDSS, RAVE, ...)

select * from public.gaia_source
inner join public.tmass_best_neighbour
  on public.tmass_best_neighbour.source_id = public.gaia_source.source_id
inner join public.tmass_original_valid
  on public.tmass_best_neighbour.tmass_oid = public.tmass_original_valid.tmass_oid
WHERE CONTAINS(POINT('ICRS',public.gaia_source.ra,public.gaia_source.dec),CIRCLE('ICRS',56.75,24.1167,2))=1

Name: m452massBestNeighbour.vot
Rows: 50124


3./ Use of pre-computed 2MASS cross-match neighbourhood

Pros: fast!
Pros: everything within GACS (can be store, shared, ...)
Pros: physical cross-match (equinox, proper motion, ad-hoc metrics in the future)
Pros: full control on results
Cons: multiple matches, needs subsequent filtering
Cons: limited to hosted tables, although many (2MASS, UCAC4, SDSS, RAVE, ...)

select * from public.gaia_source
inner join public.tmass_neighbourhood
  on public.tmass_neighbourhood.source_id = public.gaia_source.source_id
inner join public.tmass_original_valid
  on public.tmass_neighbourhood.tmass_oid = public.tmass_original_valid.tmass_oid
WHERE CONTAINS(POINT('ICRS',public.gaia_source.ra,public.gaia_source.dec),CIRCLE('ICRS',56.75,24.1167,2))=1

Name: m452massNeighbourhood.vot
Rows: 50163

Sources with multiple cross-match candidates:

SELECT count(*) as n, public.gaia_source.source_id FROM public.gaia_source
inner join public.tmass_neighbourhood on public.tmass_neighbourhood.source_id = public.gaia_source.source_id
WHERE CONTAINS(POINT('ICRS',public.gaia_source.ra,public.gaia_source.dec),CIRCLE('ICRS',56.75,24.1167,2))=1
group by public.gaia_source.source_id
having count(*) > 1

Rows: 39 = 0.078% of sources within search radius


4./ Use of pre-computed 2MASS cross-match neighbourhood and ad-hoc filter function

Pros: fast!
Pros: everything within GACS (can be store, shared, ...)
Pros: physical cross-match (equinox, proper motion, ad-hoc metrics in the future)
Pros: full control on results
Cons: limited to hosted tables, although many (2MASS, UCAC4, SDSS, RAVE, ...)

Filter function: minimise G - J colour difference

select *
from public.gaia_source s
inner join (
	SELECT min(abs(public.tmass_original_valid.j_m - public.gaia_source.phot_g_mean_mag)) as g_j_diff, public.gaia_source.source_id FROM	public.gaia_source
	inner join public.tmass_neighbourhood on public.tmass_neighbourhood.source_id = public.gaia_source.source_id
	inner join public.tmass_original_valid on public.tmass_neighbourhood.tmass_oid = public.tmass_original_valid.tmass_oid
	WHERE CONTAINS(POINT('ICRS',public.gaia_source.ra,public.gaia_source.dec),CIRCLE('ICRS',56.75,24.1167,2))=1
	group by public.gaia_source.source_id
) g on s.source_id = g.source_id
inner join public.tmass_neighbourhood n on n.source_id = s.source_id
inner join public.tmass_original_valid t on n.tmass_oid = t.tmass_oid
where abs(t.j_m - s.phot_g_mean_mag) = g.g_j_diff

Name: m452massAdHocFilter.vot
Rows: 50124


5./ Cross-match against user table

Pros: full control on input tables and results
Pros: results within GACS (can be store, shared, ...)
Cons: slow, needs user table generation and upload
Cons: coordinate conversion, equinox, proper motion correction done by the user

Pleiades UCAC4: m45Ucac4.csv
Count: 16166
input name: m45Ucac4
Mark RA/DEC columns AFTER ingestion, but not before
ra: RAJ2000
dec: DEJ2000
cross-match with gaia_source. Radius: 2"
Select query after xmatch ends and execute

Number of rows: 16132
name: m45Ucac4CrossMatch.vot



6./ Cross-match against external TAP service

Pros: Many additional tables readily available
Pros: better control on results
Cons: medium speed. TAP is fast, but upload/download may be slow
Cons: may need substantial internet upload/download bandwidth
Cons: loose control on coordinate conversion, equinox, proper motion correction
Cons: results not within GACS (cannot be store, shared, ...)

Pleiades UCAC4
Download and massage Vizier table
Load UCAC4 table: m45Ucac4.csv
In my case "t1" (first table), rename as needed

xMatch against gaia_source using Topcat
TAP server (GACS ESAC):
http://gaia.esac.esa.int/tap-server/tap/
Query:
DO NOT EXECUTE!!

select * from public.gaia_source as gaia
inner join TAP_UPLOAD.t1 as ucac4
on 1=contains(
	point('ICRS', gaia.ra, gaia.dec),
	 circle('ICRS', ucac4.raj2000, ucac4.dej2000, 2/60)
)

VERY very slow query => no data base optimisation => USE GACS!!


7./ Baade's window

NGC6522 RA DEC (J2000)
CIRCLE('ICRS',270.892,-30.033972,2))=1
Caveat: no

select * from public.gaia_source
inner join public.tmass_best_neighbour
  on public.tmass_best_neighbour.source_id = public.gaia_source.source_id
inner join public.tmass_original_valid
  on public.tmass_best_neighbour.tmass_oid = public.tmass_original_valid.tmass_oid
WHERE CONTAINS(POINT('ICRS',public.gaia_source.ra,public.gaia_source.dec),CIRCLE('ICRS',270.892,-30.033972,2))=1

select * from public.gaia_source
inner join public.tmass_best_neighbour
  on public.tmass_best_neighbour.source_id = public.gaia_source.source_id
inner join public.tmass_original_valid
  on public.tmass_best_neighbour.tmass_oid = public.tmass_original_valid.tmass_oid
WHERE CONTAINS(POINT('ICRS',public.gaia_source.ra,public.gaia_source.dec),CIRCLE('ICRS',270.892,-30.033972,2))=1
and public.gaia_source.phot_g_mean_mag - public.tmass_original_valid.j_m <= 0
and public.tmass_original_valid.j_m - public.tmass_original_valid.k_m >= 1

Gaia source count: 30,908,854
Gaia 2mass cross-match count: 3,496,056
Name: baadeSWindow2MassBestNeighbour
First query crashes: too little quota
Restricted query count: 813
Name: baadeSWindow2MassBlueStarsInfraredExcess




Topcat tutorial

1.- Cone search query:
SELECT * 
FROM public.gaia_source  
WHERE CONTAINS(POINT('ICRS',public.gaia_source.ra,public.gaia_source.dec),CIRCLE('ICRS',56.75,24.1167,2))=1

Rename as m45

2.- Filter by proper motion quality
SELECT * 
FROM public.gaia_source  
WHERE CONTAINS(POINT('ICRS',public.gaia_source.ra,public.gaia_source.dec),CIRCLE('ICRS',56.75,24.1167,2))=1
AND abs(pmra_error/pmra)<0.10
AND  abs(pmdec_error/pmdec)<0.10
AND pmra IS NOT NULL AND abs(pmra)>0
AND pmdec IS NOT NULL AND abs(pmdec)>0

Rename as m45PmFilter


3.- Select cluster members by proper motion different wrt background stars
SELECT * 
FROM public.gaia_source  
WHERE CONTAINS(POINT('ICRS',public.gaia_source.ra,public.gaia_source.dec),CIRCLE('ICRS',56.75,24.1167,2))=1
AND abs(pmra_error/pmra)<0.10
AND  abs(pmdec_error/pmdec)<0.10
AND pmra IS NOT NULL AND abs(pmra)>0
AND pmdec IS NOT NULL AND abs(pmdec)>0
AND pmra BETWEEN 15 AND 25
AND pmdec BETWEEN -55 AND -40

Rename as m45cluster


4.- Compute average parallax
SELECT avg(parallax) as avg_parallax FROM public.gaia_source  
WHERE CONTAINS(POINT('ICRS',public.gaia_source.ra,public.gaia_source.dec),CIRCLE('ICRS',56.75,24.1167,2))=1
AND abs(pmra_error/pmra)<0.10
AND  abs(pmdec_error/pmdec)<0.10
AND pmra IS NOT NULL AND abs(pmra)>0
AND pmdec IS NOT NULL AND abs(pmdec)>0
AND pmra BETWEEN 15 AND 25
AND pmdec BETWEEN -55 AND -40

Result: 8.176976744186046