# Make a list of parallaxes for all NSS sources 782290 GCNS_full_NSSsolutions.fits
select g.source_id, g.nss_solution_type, g.parallax, g.parallax_error, g.pmra, g.pmra_error, g.pmdec, g.pmdec_error
       from gaiadr3.nss_two_body_orbit as g
union all
select g.source_id, g.nss_solution_type, g.parallax, g.parallax_error, g.pmra, g.pmra_error, g.pmdec, g.pmdec_error
       from gaiadr3.nss_acceleration_astro as g
union all
select g.source_id, g.nss_solution_type, g.parallax, g.parallax_error, g.pmra, g.pmra_error, g.pmdec, g.pmdec_error
       from gaiadr3.nss_vim_fl  as g;

# Make output for all union of gaiasource for all possible targets 1961005, table in GACS  user_rsmart.nssplus8massourceid
select g.* from gaiadr3.gaia_source as g, gaiadr3.nss_two_body_orbit as n where(n.source_id = g.source_id)
union
select g.* from gaiadr3.gaia_source as g, gaiadr3.nss_acceleration_astro as n where(n.source_id = g.source_id)
union
select g.* from gaiadr3.gaia_source as g, gaiadr3.nss_vim_fl as n where(n.source_id = g.source_id)
union
select g.* from gaiadr3.gaia_source as g where(g.parallax > 8);

# So the 1211740 gaiadr3.gaia_source as g where(g.parallax > 8) and 782290 NSS have 33025 overlap

# Get external catalog data for  user_rsmart.nssplus8massourceid
SELECT n.source_id, xmatch.original_ext_source_id, xmatch.angular_distance, c.*
FROM user_rsmart.nssplus8massourceid as n
JOIN gaiadr3.allwise_best_neighbour AS xmatch USING (source_id)
JOIN gaiadr1.allwise_original_valid AS c ON xmatch.original_ext_source_id = c.designation ;

SELECT n.source_id, xmatch.original_ext_source_id, xmatch.angular_distance, c.*
FROM user_rsmart.nssplus8massourceid as n
JOIN gaiadr3.panstarrs1_best_neighbour AS xmatch USING (source_id)
JOIN gaiadr2.panstarrs1_original_valid AS c ON xmatch.original_ext_source_id = c.obj_id ;

SELECT n.source_id, xmatch.original_ext_source_id, xmatch.angular_distance, c.*
FROM user_rsmart.nssplus8massourceid as n
JOIN gaiadr3.tmass_psc_xsc_best_neighbour AS xmatch USING (source_id)
JOIN gaiadr1.tmass_original_valid AS c ON xmatch.original_ext_source_id = c.designation ;



select j.source_id, j.original_ext_source_id, j.angular_distance
       from user_rsmart.nssplus8massourceid as t, gaiadr3.allwise_best_neighbour as j where(t.source_id = j.source_id);
# save as        allwisejoin
select j.source_id, j.angular_distance, p.* from gaiadr1.allwise_original_valid as p, user_rsmart.allwisejoin as j
       where(p.designation = j.original_ext_source_id);

SELECT t.source_id, p.* FROM user_rsmart.nssplus8massourceid AS t
JOIN gaiadr3.panstarrs1_best_neighbour AS xmatch USING (source_id)
JOIN gaiadr3.panstarrs1_join AS xjoin USING (clean_panstarrs1_oid)
JOIN gaiadr2.panstarrs1_original_valid AS p ON
   xjoin.original_ext_source_id = p.obj_id

SELECT gaia.source_id, a.*
FROM user_rsmart.nssplus8massourceid AS gaia
JOIN gaiadr3.allwise_best_neighbour AS xmatch USING (source_id)
JOIN gaiadr3.allwise_join AS xjoin USING (clean_allwise_oid)
JOIN gaiadr2.allwise_original_valid AS t ON
   xjoin.original_ext_source_id = t.designation



# Test
select top 10000 source_id from gaiadr3.gaia_source ;
# save as a user table test
SELECT t.source_id, p.* FROM user_rsmart.test AS t
JOIN gaiadr3.panstarrs1_best_neighbour AS xmatch USING (source_id)
JOIN gaiadr3.panstarrs1_join AS xjoin USING (clean_panstarrs1_oid)
JOIN gaiadr2.panstarrs1_original_valid AS p ON
   xjoin.original_ext_source_id = p.obj_id
# provides 9825 matches

select j.source_id, j.original_ext_source_id, j.angular_distance
       from user_rsmart.test as t, gaiadr3.panstarrs1_best_neighbour as j where(t.source_id = j.source_id);
# provides 9745 matches
# save as user table testjoin
select j.source_id, j.angular_distance, p.* from gaiadr2.panstarrs1_original_valid as p, user_rsmart.testjoin as j where(p.obj_id = j.original_ext_source_id);


# Make list of sourceids for all possible objects with parallaxes > 8.
select source_id from gaiadr3.nss_two_body_orbit where parallax > 8 
UNION
select source_id from gaiadr3.nss_acceleration_astro where parallax > 8 
UNION
select source_id from gaiadr3.nss_vim_fl where parallax > 8 
UNION
select source_id from gaiadr3.gaia_source where(parallax > 8.) ;

# Save as user table GCNS_fullsourceid

select g.* from gaiadr3.gaia_source as g, user_rsmart.gcns_fullsourceid as r where(r.source_id = g.source_id);

select g.source_id, g.nss_solution_type, g.parallax, g.parallax_error, g.pmra, g.pmra_error, g.pmdec, g.pmdec_error
       from gaiadr3.nss_two_body_orbit as g,user_rsmart.gcns_fullsourceid as r where(r.source_id = g.source_id and  g.parallax > 0.)
union all
select g.source_id, g.nss_solution_type, g.parallax, g.parallax_error, g.pmra, g.pmra_error, g.pmdec, g.pmdec_error
       from gaiadr3.nss_acceleration_astro as g,user_rsmart.gcns_fullsourceid as r where(r.source_id = g.source_id and  g.parallax > 0.)
union all
select g.source_id, g.nss_solution_type, g.parallax, g.parallax_error, g.pmra, g.pmra_error, g.pmdec, g.pmdec_error
       from gaiadr3.nss_vim_fl as g,user_rsmart.gcns_fullsourceid as r where(r.source_id = g.source_id and  g.parallax > 0.);

########################################
select c.source_id, c.original_ext_source_id, c.angular_distance
       from user_rsmart.gcns_fullsourceid as u, gaiadr3.allwise_best_neighbour as c where(u.source_id = c.source_id);

select c.source_id, c.original_ext_source_id, c.angular_distance
       from user_rsmart.gcns_fullsourceid as u, gaiadr3._best_neighbour as c where(u.source_id = c.source_id);

select c.source_id, c.original_ext_source_id, c.angular_distance
       from user_rsmart.gcns_fullsourceid as u, gaiadr3.panstarrs1_best_neighbour as c where(u.source_id = c.source_id);

# Save as user tables

############## Make external catalogs
select c.*, t.j_m, t.j_msigcom, t.h_m, t.h_msigcom, t.ks_m, t.ks_msigcom, t.ext_key, t.j_date
from gaiadr1.tmass_original_valid as t, user_rsmart.gcns_full_tmass as c where(t.designation = c.original_ext_source_id);

select c.*, t.g_mean_psf_mag, t.g_mean_psf_mag_error, t.r_mean_psf_mag ,
t.r_mean_psf_mag_error, t.i_mean_psf_mag, t.i_mean_psf_mag_error ,
t.z_mean_psf_mag, t.z_mean_psf_mag_error, t.y_mean_psf_mag ,
t.y_mean_psf_mag_error, t.epoch_mean
from gaiadr2.panstarrs1_original_valid as t, user_rsmart.gcns_full_ps1 as c where(t.obj_id = c.original_ext_source_id);

select c.*, t.w1mpro, t.w1mpro_error, t.w2mpro, t.w2mpro_error, t.w3mpro,
t.w3mpro_error, t.w4mpro, t.w4mpro_error
from gaiadr1.allwise_original_valid as t, user_rsmart.gcns_full_allwise as c
where(t.designation = c.original_ext_source_id);

# Download tables as fits




