четверг, 29 октября 2009 г.

Работа с GeoIP с помощью SQLite

Воспользуемся известной базой ip-адресов от MaxMind. Описание формата и технологии преобразования ip адреса к целому числу и обратно смотреть по ссылке.

There is popular geoip database provided by the MaxMind company.
http://www.maxmind.com/app/csv

Кроме того, можно найти уже готовую базу в формате SQLite здесь
http://alt.textdrive.com/nanoki/
На момент написания статьи актуальная база доступна по ссылке ниже.

The MaxMind dataset in SQLite database format is available by link below.
http://alt.textdrive.com/assets/public/Nanoki/IPLocation.20090201.tar.bz2

Воспользовавшись функцией ip2int() из моего расширения INET, легко получить простой запрос определения местоположения по ip-адресу.

The geolocation query can be writed easy by using the ip2int() function from my extension INET


select location.start as start,
location.end as end,
city.name as city,
region.name as region,
region.code as region_code,
country.name as country,
country.code as country_code
from location
join city on city.id = location.city_id
join region on region.id = city.region_id
join country on country.id = region.country_id
where location.start <= ip2int('195.122.250.12')
order by location.start desc
limit 1;

3279616000|3279616679|Nizhni Novgorod|Nizhegorod|51|Russian Federation|RU


Если посмотреть план выполнения запроса, мы можем увидеть, что производится индексный поиск по всем таблицам, обеспечивая высокое быстродействие.

The SQL query plan is optimal because it uses indexes by all tables.

0|0|TABLE location WITH INDEX location_start ORDER BY
1|1|TABLE city USING PRIMARY KEY
2|2|TABLE region USING PRIMARY KEY
3|3|TABLE country USING PRIMARY KEY


Как видим, задача решается быстро и элегантно. Для выполнения запроса к базе можно использовать любой удобный вам интерфейс к SQLite.

Enjoy!

Комментариев нет:


(C) Alexey Pechnikov aka MBG, mobigroup.ru