UMLS Database Query Diagrams:
How to perform searches in the mapping files

This diagram shows how to perform searches in the mapping files.

Map sets are represented in the UMLS as map set concepts whose preferred atom has TTY = 'XM'. The map set concept itself carries some metadata information as attributes (found in MRSAT.RRF). Searching for the map set CUI in MRSAT will yield this information.

The mappings (MRMAP.RRF) are aggregated by a MAPSETCUI field. All mappings sharing this value are part of the same map set. The representation of a "mapping" is actually a denormalization of what could be represented in three parts:

- mapping
- "map from" object
- "map to" object

Thus, the "FROMXXX" or "TOXXX" of MRMAP.RRF may not be unique across the whole file. Mappings in the UMLS relate one expression (FROMEXPR) to another expression (TOEXPR). These expressions may be single identifiers, lists of identifiers, or Boolean expressions of identifiers. The map set metadata (and type fields) indicate what values are used. Furthermore, mappings (other than ATX map sets) relate expressions of source identifiers to each other, rather than expressions of UMLS identifiers (like AUI, CUI, etc.). For example, the SNOMED CT to ICD9CM map set relates SNOMED CT concept identifiers (SCUI) to lists of ICD9CM codes (CODE). If you are willing to tolerate some inaccuracy (due to versioning issues), it is possible to extract the individual source asserted identifiers of the expressions and search for them in MRCONSO.RRF to determine the actual AUI and CUI values involved.

Corresponding Oracle Queries:

1. Find XM atom for map set concept.

SELECT * FROM mrconso
WHERE cui = 'C1321851'
     AND tty = 'XM';

2. Find semantic type of map set concept. The join on MRCONSO.RRF is not necessary, but it provides access to fields of the atom (like the SAB) that may be useful.

SELECT * FROM mrconso a, mrsty b
WHERE a.cui = 'C1321851'
     AND a.tty = 'XM'
     AND a.cui = b.cui;

3. Find map set attributes (e.g., FROMVSAB, TOVSAB, MAPSETVSAB, MAPSETNAME, etc.)

SELECT b.* FROM mrconso a, mrsat b
WHERE a.cui = 'C1321851'
     AND a.tty = 'XM'
     AND a.aui = b.metaui;

4. Find the mappings. The join on MRCONSO.RRF is not necessary, but it provides access to fields of the atom (like the SAB) that may be useful.

SELECT b.* FROM mrconso a, mrmap b
WHERE a.cui = 'C1321851'
     AND a.tty = 'XM'
     AND a.cui = b.mapsetcui;

5. Find the atoms corresponding to the "map from" objects that contain single CUI values. Note: this may not always be possible to do, given that the FROMVSAB may be out of sync.

SELECT b.* FROM mrmap a, mrconso b, mrsat c
WHERE a.mapsetcui = 'C1321851'
     AND a.fromtype = 'SCUI'
     AND a.mapsetcui = c.cui
     AND c.atn = 'FROMRSAB'
     AND a.fromexpr = b.scui
     AND b.sab = c.atv;

6. Find the atoms corresponding to the "map to" objects that contain single CODE values. Note: this may not always be possible to do, given that the TOVSAB may be out of sync.

SELECT b.* FROM mrmap a, mrconso b, mrsat c
WHERE a.mapsetcui = 'C1321851'
     AND a.totype = 'CODE'
     AND a.mapsetcui = c.cui
     AND c.atn = 'TORSAB'
     AND a.toexpr = b.code
     AND b.sab = c.atv;

Last Reviewed: July 29, 2016