How to retrieve ADMIN exonym and synonym names from RDF

Symptoms
--------

Customers may experience one or more of the following when working with RDF administrative data:

Need to retrieve all administrative (ADMIN) country, state, province, or regional names across available languages.
Need to distinguish between native names, exonyms, and synonyms stored in RDF.
Need to identify alternate spellings or localized names for administrative areas.
Unable to determine which records represent synonyms versus base names.
Require a query that can be executed directly in SQL Developer against RDF tables.

Answer
------

To retrieve ADMIN exonym names and synonym names for all ADMIN hierarchy levels, use the SQL query below. Synonym records are identified by NAME_TYPE = 'S', while exonym base names are identified by NAME_TYPE = 'B' and IS_EXONYM = 'Y'.

This query joins the administrative hierarchy with feature name tables and returns exonym names, synonym names, translated names, language information, and the corresponding native name for each administrative entity.

SQL Query
---------

SELECT ahr.ISO_COUNTRY_CODE,

ahr.ADMIN_ORDER,

fnsExo.IS_EXONYM,

fnsExo.NAME_TYPE,

fneExo.LANGUAGE_CODE,

fneExo.NAME,

fntExo.NAME AS NAME1,

fne.NAME AS Native

FROM RDF_ADMIN_HIERARCHY ahr

INNER JOIN RDF_FEATURE_NAMES fnsExo

ON ahr.ADMIN_PLACE_ID = fnsExo.FEATURE_ID

AND 'A' = fnsExo.OWNER

AND (

(fnsExo.NAME_TYPE = 'B'

AND fnsExo.IS_EXONYM = 'Y')

OR fnsExo.NAME_TYPE = 'S'

)

INNER JOIN RDF_FEATURE_NAME fneExo

ON fnsExo.NAME_ID = fneExo.NAME_ID

LEFT JOIN RDF_FEATURE_NAME_TRANS fntExo

ON fneExo.NAME_ID = fntExo.NAME_ID

INNER JOIN RDF_FEATURE_NAMES fns

ON ahr.ADMIN_PLACE_ID = fns.FEATURE_ID

AND 'A' = fns.OWNER

AND 'N' = fns.IS_EXONYM

AND 'B' = fns.NAME_TYPE

INNER JOIN RDF_FEATURE_NAME fne

ON fns.NAME_ID = fne.NAME_ID

LEFT JOIN RDF_FEATURE_NAME_TRANS fnt

ON fne.NAME_ID = fnt.NAME_ID;

Result Interpretation
---------------------

| Column | Description |
| --- | --- |
| ISO_COUNTRY_CODE | Country code associated with the administrative feature |
| ADMIN_ORDER | Administrative hierarchy level |
| IS_EXONYM | Indicates whether the name is an exonym (Y) or not (N) |
| NAME_TYPE | B = Base name, S = Synonym |
| LANGUAGE_CODE | Language of the returned name |
| NAME | Exonym or synonym name |
| NAME1 | Translated form of the name, when available |
| Native | Native base name of the administrative feature |

Expected Behavior
-----------------

Exonym names are returned when IS_EXONYM = 'Y' and NAME_TYPE = 'B'.
Synonym names are returned when NAME_TYPE = 'S'.
Native administrative names are returned in the Native column.
Results include data across available languages and administrative levels.

Notes
-----

The query is designed for execution in SQL Developer.
Administrative entities are identified through RDF_ADMIN_HIERARCHY.ADMIN_PLACE_ID.
Translation records are returned when matching entries exist in the translation tables.
* Missing translation records do not prevent the corresponding administrative name from being returned because LEFT JOINs are used.

Keywords
--------

RDF, ADMIN, exonym, synonym, administrative hierarchy, RDF_ADMIN_HIERARCHY, RDF_FEATURE_NAMES, RDF_FEATURE_NAME, RDF_FEATURE_NAME_TRANS, SQL Developer, NAME_TYPE, IS_EXONYM, native name, multilingual names, administrative data, country names, localization, language code.