Some time ago I was involved in a project aiming to systemize a pandemonium of databases. Especially hard were the toponym ones. The source data was a deep XML tree with regions and cities at the top and streets and houses at the bottom.
| id | parent_id | name | | ---------- |------------|------------| | 1 | 2 | Kremlin | | 2 | | Moscow | | 3 | 2 | Red Square | | … (1M+ unsorted records) … |
I had to put this tree into PostgreSQL to allow queries. A typical query was, for example, to fetch all the streets of the cities within a specific region or to fetch the whole path from a particular street at the bottom up to the root node, etc.