# Store cities in your database

- The [terms](/terms) let you store the data that the API returns, with no restrictions.
- The ID of a city does not come from a source record. If a source replaces or merges the record of a city, the city keeps its ID.
- Onde never gives an ID to a different place.
- An ID still answers after its city leaves the dataset. [A city that leaves the dataset](#a-city-that-leaves-the-dataset) tells what Get a city returns.

## Store the ID and the fields that you show

Store the ID with a copy of the fields that your pages show. Then a page shows the city with no request to the API, and with no cost.

```sql
ALTER TABLE users
  ADD COLUMN city_id text,
  ADD COLUMN city_name text,
  ADD COLUMN region_name text,
  ADD COLUMN country_code text;
```

The names are in the `language` of the request. If your app has one language, store the names in this language.

## Group and count by city

Two people who select Kyoto get the same ID, also if one types `京都` and the other types `Kyoto`. Group and count by `city_id`, and not by the name.

```sql
SELECT city_id, min(city_name) AS city_name, count(*) AS users
FROM users
GROUP BY city_id
ORDER BY users DESC;
```

To filter by country, use `country_code`. It is the ISO 3166-1 alpha-2 code, such as `JP`.

## Show the city in another language

If your app has more than one language, send the ID to [Get a city](/docs/api#getCity). Get a city returns `names`, with the name in each language that City API supports. `region` and `country` also have `names`.

```json
{
  "id": "city_39sy1dm2dj",
  "name": "Kyoto",
  "names": {
    "en": "Kyoto",
    "de": "Kyōto",
    "es": "Kioto",
    "fr": "Kyōto",
    "it": "Kyoto",
    "nl": "Kyoto",
    "pt": "Quioto",
    "ru": "Киото",
    "ja": "京都市",
    "ko": "교토 시",
    "zh": "京都",
    "ar": "كيوتو",
    "hi": "क्योटो"
  }
}
```

Store `names`, and the app shows the city in each language with no more requests.

## Read a city again

A new dataset can correct the name, the region, the coordinates, or the population of a city. Most apps do not need these corrections. If your app does, read each stored city again with Get a city.

Each response gives the dataset version in `meta.dataset`, such as `2026-11-17`. Store the version with each city. [Get dataset details](/docs/api#getMeta) gives the current version, with no key and no cost.

```bash
curl "https://api.onde.earth/v1/meta"
```

Read only the cities with an older version. Each read costs one unit, and the [limit of requests per second](/docs/usage-and-limits#requests-per-second) applies, so read the cities in a background job.

```js
const CITY_URL = 'https://api.onde.earth/v1/cities/'

// Returns the current city, or null if the city has left the dataset.
async function readCity(id) {
  const response = await fetch(CITY_URL + encodeURIComponent(id), {
    headers: { Authorization: `Bearer ${process.env.ONDE_KEY}` }
  })
  const body = await response.json()
  if (response.status === 410) {
    return null
  }
  if (!response.ok) {
    throw new Error(body.detail)
  }
  return body.data
}
```

## A city that leaves the dataset

Rarely, a city leaves the dataset. For example, the source has two records for one city, and Onde merges them. The ID of the city keeps working, and it gives the same response in all later datasets:

- If the city has a replacement, Get a city returns the replacement with status 200. `data.id` is the ID of the replacement, and `meta.retiredId` is the ID that you sent. Replace the stored ID with `data.id`.
- If the city has no replacement, Get a city returns status 410 with the error `city_retired`. Keep the stored fields, and do not read this ID again.

`readCity` above returns the replacement in the first case and `null` in the second case.