Crossmatching your stars, part 1

Many of you will have lists of targets and will want to know:

How do I crossmatch my targets with flemingdb?

The answer to that depends on

how many targets you have
what information you have (source ID, or coordinates)
which table you want to crossmatch with
if you are willing to write a program.

To cross-match an arbitrary number of targets in an elegant way requires a dbuser or personal account and some programming. The answers we provide here only work for a limited number of targets. These solutions require that you create a list of source IDs, or that you create an SQL file containing a query for each source position.

Crossmatches by ID
Crossmatches by position
Summary

Crossmatches by ID

All you basically need is a comma separated list of your IDs, either in row format, column format or any mixture of it. Row format:

id1, id2, id3, ...

Column format:

id1,
id2,
id3,
...

You can mix both formats freely, say 10 ids per row or so. Attention: string IDs have to be quoted using single quotes. Once you have this, copy

cp /home/mmtobs/local/pgtemplates/id_crossmatch.sql mycrossmatch.sql

and all you need to do is edit mycrossmatch.sql and replace terms in <> by what you need. So, let's see what is in there (remember: '--' is the comment sign in SQL, so everything behind it and being on the same line will be ignored:

copy
(
select * -- or list of columns you need
from <table_to_match_against>
-- join <another_table_to_match_against> on (<condition>)
where <idname> in (<yourlist>)
)
to stdout
with csv header delimiter ',';

We already used this kind of query in querying a single table. At that time we had a list of 3 gaiaid's in row format, we did not want to join any other table. To make it more interesting, let us assume, you got 2MASS designations and you want to know the Gaia magnitudes (if any). Here is what you do:

Open Schemas, Tables and Connections
Ctrl-F to search for "designation", be sure to activate "Highlight All"
Scroll to the gaia2 part of the Connections and see what we get: there is a promising "tmass_best_neighbour" tables which has the 2MASS designation in it, just as column "original_ext_source_id". And it has the gaia source_id.

So here is the plan:

we query tmass_best_neighbour to search for our 2MASS designation
with the source_id from that table we join gaiadr2 to get the magnitudes

Assume our list of designations is this (it does not play a role if we have 3, 300 or 1000 designations, for more stars you might want to create 1 command per 1000 targets):

'18473976+4246318', '23062928-0502285', '17151894+0457496'

Using your favorite editor you change the template and insert the list of IDs you got. In our case the result may look like this:

copy
(
select tmass_best_neighbour.source_id,
         tmass_best_neighbour.original_ext_source_id as tmass_name,
         ra, dec,
       phot_g_mean_mag as gaiamag,
       phot_bp_mean_mag as gaiabp,
       phot_rp_mean_mag as gaiarp
from tmass_best_neighbour
join gaiadr2 on (tmass_best_neighbour.source_id = gaiadr2.source_id)
where original_ext_source_id in
        ('18473976+4246318', '23062928-0502285', '17151894+0457496')
)
to stdout
with csv header delimiter ',';

If you do not want it as csv-file, take out the copy-part and just leave the select (remember to add a ';' at the end). Run with:

dbdemo -f mycrossmatch.sql

Doing that without the copy-part, the result looks like this:

source_id | tmass_name | ra | dec | gaiamag | gaiabp | gaiarp ---------------------+------------------+------------------+-------------------+---------+---------+--------- 4393265392167891712 | 17151894+0457496 | 258.831399084653 | 4.96067947299482 | 13.0089 | 14.9324 | 11.6962 2104839471773324032 | 18473976+4246318 | 281.915612790021 | 42.7755535258928 | 11.9438 | 12.2101 | 11.5306 2635476908753563008 | 23062928-0502285 | 346.626391869714 | -5.04346180220689 | 15.6451 | 18.9982 | 14.0967 (3 rows)

In case you want to know which stars that are: Kepler-144, Trappist-1 and GJ 1214.

Crossmatches by position

Assuming you have only coordinates, you can use the cone search from the Single Table tutorial. This is a rather clumsy approach because you will have to create a SQL-file with one query per position (you will see a much better solution in the programming tutorial). In addition I would highly recommend to add a counter for the positions, because you might get several matches per star, so counting the positions tells you wich result belongs to what position. In addition you would want to switch off the column headers and direct the output to a file of your choice. For our 3 stars from above the SQL-file looks like this (or get it from /home/mmtobs/local/sql/mycrossmatch_postions.sql):

-- set the output file
\out coordinates_res.txt

-- switch off column headers in output
\pset tuples_only on

-- perform 20 asec cone search around 3 positions

select 1 as counter,
       3600 * q3c_dist(ra, dec, 258.8314, 4.9607) as rdist,
       source_id, ra, dec,
       phot_g_mean_mag as gaiamag,
       phot_bp_mean_mag as gaiabp,
       phot_rp_mean_mag as gaiarp
from gaiadr2
where q3c_radial_query(ra, dec, 258.8314, 4.9607, 20.0 / 3600)
order by rdist;

select 2 as counter,
       3600 * q3c_dist(ra, dec, 281.9156, 42.7756) as rdist,
       source_id, ra, dec,
       phot_g_mean_mag as gaiamag,
       phot_bp_mean_mag as gaiabp,
       phot_rp_mean_mag as gaiarp
from gaiadr2
where q3c_radial_query(ra, dec, 281.9156, 42.7756, 20.0 / 3600)
order by rdist;

select 3 as counter,
       3600 * q3c_dist(ra, dec, 346.6264, -5.0435) as rdist,
       source_id, ra, dec,
       phot_g_mean_mag as gaiamag,
       phot_bp_mean_mag as gaiabp,
       phot_rp_mean_mag as gaiarp
from gaiadr2
where q3c_radial_query(ra, dec, 346.6264, -5.0435, 20.0 / 3600)
order by rdist;

Please note that I put the counter as first column in each select statement. It looks like this:

       1 | 0.0739701046850804 | 4393265392167891712 | 258.831399084653 | 4.96067947299482 | 13.0089 | 14.9324 | 11.6962
       1 |   9.68556079083502 | 4393265392167890176 | 258.833133712811 | 4.95863720122699 | 17.2153 | 17.6695 | 16.5356
       1 |   12.3376527927089 | 4393265392167889664 | 258.832898205065 | 4.95761498033342 | 16.2787 | 16.9049 | 15.5272
       2 | 0.170686304307056 | 2104839471773324032 | 281.915612790021 | 42.7755535258928 | 11.9438 | 12.2101 | 11.5306
       2 | 17.4640280578568 | 2104839467475751680 | 281.922184707418 | 42.776015583763 | 18.6452 | 18.9938 | 18.1179
       3 | 0.14056891759045 | 2635476908753563008 | 346.626391869714 | -5.04346180220689 | 15.6451 | 18.9982 | 14.0967

For the first position we get 3 results, for the second we get 2 results and for the third position just one.

What if you want to use COPY in order to get it into a csv-file? You will have to connect the single queries via a UNION-statement, just like so:

\pset tuples_only on

-- perform 20 asec cone search around 3 positions
copy
(
(select 1 as counter,
          3600 * q3c_dist(ra, dec, 258.8314, 4.9607) as rdist,
          source_id, ra, dec,
          phot_g_mean_mag as gaiamag,
          phot_bp_mean_mag as gaiabp,
          phot_rp_mean_mag as gaiarp
from gaiadr2
where q3c_radial_query(ra, dec, 258.8314, 4.9607, 20.0 / 3600)
order by rdist)
union
(select 2 as counter,
          3600 * q3c_dist(ra, dec, 281.9156, 42.7756) as rdist,
          source_id, ra, dec,
          phot_g_mean_mag as gaiamag,
          phot_bp_mean_mag as gaiabp,
          phot_rp_mean_mag as gaiarp
from gaiadr2
where q3c_radial_query(ra, dec, 281.9156, 42.7756, 20.0 / 3600)
order by rdist)
union
(select 3 as counter,
          3600 * q3c_dist(ra, dec, 346.6264, -5.0435) as rdist,
          source_id, ra, dec,
          phot_g_mean_mag as gaiamag,
          phot_bp_mean_mag as gaiabp,
          phot_rp_mean_mag as gaiarp
from gaiadr2
where q3c_radial_query(ra, dec, 346.6264, -5.0435, 20.0 / 3600)
order by rdist)
)
to stdout
with csv header delimiter ',';

Please note that the ':' after each "order by rdist" went away. It has to, because the whole thing is just one SQL statement.

It is unlikely that you will write a file like this by hand for more than say a dozen stars. But if you can create a file like this using your favorite programming language, this is a way to go. In the programming tutorial, we will use a different, much simpler way to cross-match stars by positions - but that requires programming.

Summary

We saw 2 different ways to crossmatch targets with FlemingDB tables: by ID and by position (coordinates).
We saw how joining tables may help if we do not have the ID we need for crossmatching (Gaia source_id), but some other ID. All that was needed is getting your IDs into a list-format. String IDs have to be put in single quotes.
Crossmatching by position requires one statement per position. Using COPY to produce a csv-output requires to UNION them into one query.
We saw how to direct the output to a file of your choice using "\out" and how to switch the column-headers off via "\pset". These are commands of the Postgres shell (psql), follow the link for a full documentation.

As usual: please send questions and comments to DBAdmin.

Crossmatching your stars, part 1

Crossmatches by ID

Crossmatches by position

Summary

OIRDB