Functions for Working with Embedded Dictionaries

### regionToTopContinent[​](#regiontotopcontinent "Direct link to regionToTopContinent")

Finds the highest continent in the hierarchy for the region.

**Syntax**

```
regionToTopContinent(id[, geobase])
```

**Parameters**

- `id` — Region ID from the geobase. [UInt32](/docs/sql-reference/data-types/int-uint).
- `geobase` — Dictionary key. See [Multiple Geobases](#multiple-geobases). [String](/docs/sql-reference/data-types/string). Optional.

**Returned value**

- Identifier of the top level continent (the latter when you climb the hierarchy of regions).[UInt32](/docs/sql-reference/data-types/int-uint).
- 0, if there is none.

**Example**

Query:

```
SELECT regionToName(number::UInt32, 'en'), regionToTopContinent(number::UInt32) AS id, regionToName(id, 'en') FROM numbers(13);
```

Result:

```
┌─regionToName(CAST(number, 'UInt32'), 'en')─┬─id─┬─regionToName(regionToTopContinent(CAST(number, 'UInt32')), 'en')─┐
│                                            │  0 │                                                                  │
│ World                                      │  0 │                                                                  │
│ USA                                        │  9 │ America                                                          │
│ Colorado                                   │  9 │ America                                                          │
│ Boulder County                             │  9 │ America                                                          │
│ Boulder                                    │  9 │ America                                                          │
│ China                                      │ 11 │ Eurasia                                                          │
│ Sichuan                                    │ 11 │ Eurasia                                                          │
│ Chengdu                                    │ 11 │ Eurasia                                                          │
│ America                                    │  9 │ America                                                          │
│ North America                              │  9 │ America                                                          │
│ Eurasia                                    │ 11 │ Eurasia                                                          │
│ Asia                                       │ 11 │ Eurasia                                                          │
└────────────────────────────────────────────┴────┴──────────────────────────────────────────────────────────────────┘
```

### regionToPopulation[​](#regiontopopulation "Direct link to regionToPopulation")

Gets the population for a region. The population can be recorded in files with the geobase. See the section ["Dictionaries"](/docs/sql-reference/statements/create/dictionary/embedded). If the population is not recorded for the region, it returns 0. In the geobase, the population might be recorded for child regions, but not for parent regions.

**Syntax**

```
regionToPopulation(id[, geobase])
```

**Parameters**

- `id` — Region ID from the geobase. [UInt32](/docs/sql-reference/data-types/int-uint).
- `geobase` — Dictionary key. See [Multiple Geobases](#multiple-geobases). [String](/docs/sql-reference/data-types/string). Optional.

**Returned value**

- Population for the region. [UInt32](/docs/sql-reference/data-types/int-uint).
- 0, if there is none.

**Example**

Query:

```
SELECT regionToName(number::UInt32, 'en'), regionToPopulation(number::UInt32) AS id, regionToName(id, 'en') FROM numbers(13);
```

Result:

```
┌─regionToName(CAST(number, 'UInt32'), 'en')─┬─population─┐
│                                            │          0 │
│ World                                      │ 4294967295 │
│ USA                                        │  330000000 │
│ Colorado                                   │    5700000 │
│ Boulder County                             │     330000 │
│ Boulder                                    │     100000 │
│ China                                      │ 1500000000 │
│ Sichuan                                    │   83000000 │
│ Chengdu                                    │   20000000 │
│ America                                    │ 1000000000 │
│ North America                              │  600000000 │
│ Eurasia                                    │ 4294967295 │
│ Asia                                       │ 4294967295 │
└────────────────────────────────────────────┴────────────┘
```

### regionIn[​](#regionin "Direct link to regionIn")

Checks whether a `lhs` region belongs to a `rhs` region. Returns a UInt8 number equal to 1 if it belongs, or 0 if it does not belong.

**Syntax**

```
regionIn(lhs, rhs\[, geobase\])
```

**Parameters**

- `lhs` — Lhs region ID from the geobase. [UInt32](/docs/sql-reference/data-types/int-uint).
- `rhs` — Rhs region ID from the geobase. [UInt32](/docs/sql-reference/data-types/int-uint).
- `geobase` — Dictionary key. See [Multiple Geobases](#multiple-geobases). [String](/docs/sql-reference/data-types/string). Optional.

**Returned value**

- 1, if it belongs. [UInt8](/docs/sql-reference/data-types/int-uint).
- 0, if it doesn't belong.

**Implementation details**

The relationship is reflexive – any region also belongs to itself.

**Example**

Query:

```
SELECT regionToName(n1.number::UInt32, 'en') || (regionIn(n1.number::UInt32, n2.number::UInt32) ? ' is in ' : ' is not in ') || regionToName(n2.number::UInt32, 'en') FROM numbers(1,2) AS n1 CROSS JOIN numbers(1,5) AS n2;
```

Result:

```
World is in World
World is not in USA
World is not in Colorado
World is not in Boulder County
World is not in Boulder
USA is in World
USA is in USA
USA is not in Colorado
USA is not in Boulder County
USA is not in Boulder
```

### regionHierarchy[​](#regionhierarchy "Direct link to regionHierarchy")

Accepts a UInt32 number – the region ID from the geobase. Returns an array of region IDs consisting of the passed region and all parents along the chain.

**Syntax**

```
regionHierarchy(id\[, geobase\])
```

**Parameters**

- `id` — Region ID from the geobase. [UInt32](/docs/sql-reference/data-types/int-uint).
- `geobase` — Dictionary key. See [Multiple Geobases](#multiple-geobases). [String](/docs/sql-reference/data-types/string). Optional.

**Returned value**

- Array of region IDs consisting of the passed region and all parents along the chain. [Array](/docs/sql-reference/data-types/array)([UInt32](/docs/sql-reference/data-types/int-uint)).

**Example**

Query:

```
SELECT regionHierarchy(number::UInt32) AS arr, arrayMap(id -> regionToName(id, 'en'), arr) FROM numbers(5);
```

Result:

```
┌─arr────────────┬─arrayMap(lambda(tuple(id), regionToName(id, 'en')), regionHierarchy(CAST(number, 'UInt32')))─┐
│ []             │ []                                                                                           │
│ [1]            │ ['World']                                                                                    │
│ [2,10,9,1]     │ ['USA','North America','America','World']                                                    │
│ [3,2,10,9,1]   │ ['Colorado','USA','North America','America','World']                                         │
│ [4,3,2,10,9,1] │ ['Boulder County','Colorado','USA','North America','America','World']                        │
└────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────┘
```

- [Multiple Geobases](#multiple-geobases)
  - [regionToName](#regiontoname)- [regionToCity](#regiontocity)- [regionToArea](#regiontoarea)- [regionToDistrict](#regiontodistrict)- [regionToCountry](#regiontocountry)- [regionToContinent](#regiontocontinent)- [regionToTopContinent](#regiontotopcontinent)- [regionToPopulation](#regiontopopulation)- [regionIn](#regionin)- [regionHierarchy](#regionhierarchy)

Was this page helpful?