How to get national name from geonames database?

Question

Geolocation

While working on a web application, I ran into a problem: it is necessary to autocomplete in the city input field.
For example, I start typing "Pe" and see in suggest "Perm region -> Perm", "Penza region -> Penza".
At the same time, the ability to determine the city by ip using maxMind is also important. MaxMind returns a geonameid for the geoname base ( www.geonames.org/ ) that can be used to match an ip to an entry in the table.
However, in the geoname database, Russian names for localities are only in the alternatenames field, there is no separate column with Russian names of localities.
Question: how to get the national (Russian) name of the settlement in a separate column?
Perhaps there are some ready-made solutions?

Answer 1

I use this autocomplete query:
SELECT geonames.geonameid as id, geonames.name, countries.name as country, regions.name as region
FROM geonames
JOIN alternatives ON (alternatives.geonameid = geonames.geonameid)
WHERE alternatives.alternateName LIKE 'mosk %' and fclass='P'
GROUP BY geonames.geonameid ORDER BY geonames.population desc, geonames.name
=geo_alternatives.alternateName desc
LIMIT 10 JOIN countries ON (geonames.country = countries.iso_alpha2) JOIN regions ON (concat(geonames.country, '.', geonames.admin1) = regions.code)
JOIN alternatives ON (alternatives.geonameid = geonames.geonameid)
WHERE geonames.country = :country AND geo_alternatives.alternateName = :city and fclass='P'
GROUP BY geonames.geonameid
ORDER BY geonames.population desc, geo_cities.name=geo_alternatives. alternateName desc
LIMIT 1

Answer 2

Thanks a lot. I got the gist. Two questions arose:
1. What is the region table? It's not in the dump provided by geonames.org
2. Are there any performance issues? Your requests are taking a very long time for me. I plan to use sphinx.