Как получить национальное имя из geonames базы данных?

Question

[pavel_ms]

При работе над web-приложением столкнулся с проблемой: необходимо сделать автокомплит в поле ввода города.
Например, я начинаю вводить "Пе" и вижу в suggest "Пермский край -> Пермь", "Пензенская область -> Пенза".

При этом также важна возможность определения города по ip c помощью maxMind. MaxMind возвращает geonameid для базы geoname (www.geonames.org/), по которому можно сопоставить ip с записью из таблицы.

Однако, в базе geoname русские имена для населенных пунктов есть только в поле alternatenames, отдельной колонки с русскими именами населенных пунктов нет.

Вопрос: как получить национальное (русское) имя населенного пункта в отдельной колонке?
Возможно, есть какие-то готовые решения?

Answer 1

[Vladimir Goncharov]

Я использую такой запрос для автокомплита:
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 'моск%' and fclass='P'
GROUP BY geonames.geonameid
ORDER BY geonames.population desc, geonames.name=geo_alternatives.alternateName desc
LIMIT 10

И такой запрос для получения идентификатора города по данным из maxmind:
SELECT geo_cities.geonameid as id
FROM geonames
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

[pavel_ms]

Огромное спасибо. Суть я уловил. Возникли два вопроса:
1. Что за таблица region ? Ее нет в dump, который предоставляет geonames.org
2. Не ли проблемы с производительностью? У меня Ваши запросы отрабатывают очень долго. Планирую использовать sphinx.

Comments

[Vladimir Goncharov]

1) это admin1CodesASCII
2) для первого запроса нужны индексы как минимум на поля: alternatives.alternateName и geonames.geonameid

[pavel_ms]

Получилось из Вашего сделать подходящий мне вариант. Спасибо большое.

[Vladimir Goncharov]

пожалуйста