Next Previous Up Contents
Next: Matching Rows Within a Table
Up: Joins and Matches
Previous: Concatenating Tables

5.2 Matching Rows Between Tables

When joining two tables side-by-side you need to identify which row(s) in one correspond to which row(s) in the other. Conceptually, this is done by looking at each row in the first table, somehow identifying in the second table which row "refers to the same thing", and putting a new row in the joined table which consists of all the fields of the row in the first table, followed by all the fields of its matched row in the second table. The resulting table then has a number of columns equal to the sum of the number of columns in both input tables.

In practice, there are a number of complications. For one thing, each row in one table may be matched by zero, one or many rows in the the other. For another, defining what is meant by "referring to the same thing" may not be straightforward. There is also the problem of actually identifying these matches in a relatively efficient way (without explicitly comparing each row in one table with each row in the other, which would be far too slow for large tables).

A common example is the case of matching two object catalogues - suppose we have the following catalogues:

    Xpos       Ypos        Vmag
    ----       ----        ----
   1134.822    599.247     13.8
    659.68    1046.874     17.2
    909.613    543.293      9.3
    x           y          Bmag
    -           -          ---- 
   909.523     543.800     10.1
   1832.114    409.567     12.3
   1135.201    600.100     14.6
    702.622   1004.972     19.0
and we wish to combine them to create one new catalogue with a row for each object which appears in both tables. To do this, you have to specify what counts as a match - in this case let's say that a row in one table matches (refers to the same object as) a row in the other if the distance between the positions indicated by their X and Y coordinates matches to within one unit (sqrt((Xpos-x)2 + (Ypos-y)2)<=1)). Then the catalogue we will end up with is:
    Xpos       Ypos        Vmag    x           y          Bmag
    ----       ----        ----    -           -          ---- 
   1134.822    599.247     13.8   1135.201    600.100     14.6
    909.613    543.293      9.3    909.523    543.800     10.1
There are a number of variations on this however - your match criteria might involve sky coordinates instead of Cartesian ones (or not be physical coordinates at all), you might want to match more than two tables, you might want to identify groups of matching objects in a single table, you might want the output to include rows which don't match as well...

The Match Window allows you to specify

and to start the matching operation. Depending on the type of match chosen, some additional columns may be appended to the resulting table giving additional details on how the match went. Usually, the 'match score' is one of these; The exact value and meaning of this column depends on the match, but it typically gives the distance between the matched points in some sensible units; the smaller the value, the better the match. You can find out exactly what this score means by examining the column's description in the Columns Window. Columns in the resulting table retain their original names unless that would lead to ambiguity, in which case a disambiguating suffix "_1" or "_2" is added to the column name.

To match two tables, use the Pair Match () button in the Control Window; to match more tables than two at once, use the other options on the Control Window's Join menu.

Next Previous Up Contents
Next: Matching Rows Within a Table
Up: Joins and Matches
Previous: Concatenating Tables

TOPCAT - Tool for OPerations on Catalogues And Tables
Starlink User Note253
TOPCAT web page:
Author email:
Mailing list: