Как определить по IP-адресу город посетителя (GEO IP)

Определение города (географического положения) посетителя по его IP-адресу иногда бывает полезно для крупных проектов, порталов и, вообщем, везде, где требуется геотаргетинг. Собственно, чтобы что-то определить необходима база связей IP-город. До недавних пор нормальной и бесплатной базы в свободном доступе не было. Когда у меня возникла задача определения города посетителя я обратил свое внимание на руцентровский IpGeoBase. База постоянно пополняется и определяет город достаточно точно. Одно плохо — существующие примеры скриптов (для использования БД) на том же PHP какие-то тяжелые и неадекватные решаемой задаче :). Пришлось немного преобразовать данные и написать свой скрипт:

  • собственно, SQL-дамп базы (MySQL).
  • скрипт, для определения города по IP-адресу (1 запрос к БД, входной параметр $dblink — результат, возвращаемый функцией mysql_connect):

    $dblink = mysql_connect('localhost', 'mysql_user', 'mysql_password');
    mysql_select_db('dbname');

    function geo_city_by_ip($dblink)
    {
    $ra = (isset($_SERVER['REMOTE_ADDR']))? $_SERVER['REMOTE_ADDR'] : '';
    if ($ra=='') return '';
    $ra = mysql_query("SELECT city, (ip2-ip1) as dst FROM geo WHERE INET_ATON('".$ra."')>=ip1 AND INET_ATON('".$ra."')<=ip2 ORDER BY dst ASC LIMIT 1", $dblink);
    if (mysql_num_rows($ra)>0)
    {
    $cn = mysql_fetch_array($ra);
    $cn = trim($cn['city']);
    }
    return $cn;
    }

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

  1. Alex пишет:

    А насколько реальная БД?

  2. Николай Яровой пишет:

    По дате поста.

  3. Павел пишет:

    а где можно постоянно обновлять эту базу? Откуда-нибудь можно скачивать? В SQL.

  4. ksav пишет:

    В SQL-запросе «INET_ATON(‘».$ra.»‘)» лучше убрать, что сильно ускорит выборку.

    Само преобразование IP->long сделать до SQL-запроса:
    $IP = ip2long($ra);
    if ($IP < 0) $IP += pow(2,32);

    сам SQL-запрос будет выглядеть:
    SELECT city, (ip2-ip1) as dst FROM Geo WHERE ip1 =’$IP’ ORDER BY dst ASC LIMIT 1

    To Alex, Павел: — реальную БД легко скачать с офф. сайта GeoIP, занести её в MySQL дело 5 минут, тем более, что формат базы данных виден из SQL-дампа базы Николая (NickSpring).

    За идею «красивого» запроса к БД через «(ip2-ip1) as dst», спасибо, я сам как-то не догадался, что можно сделать так элементарно просто.

  5. Евгений пишет:

    А как сделать дам в SQL такой большой базы..!? У меня комп виснет, одним словом и в разы меньше дам не получается, а здесь говорите дело пяти минут …

  6. Николай Яровой пишет:

    Евгений, а Вы попробуйте не на экран дамп выводить, а послать в виде файла на скачку.

  7. Никита пишет:

    Здравствуйте
    А у вас нет дампа свежей базы?
    Было бы очень хорошо)

  8. SofTie пишет:

    ipgeobase.ru/cgi-bin/Archive_dop.cgi
    берем тут и всысываем, вот конвертилка в CSV:

  9. SofTie пишет:

    $handle = fopen(«./block_coord.db», «r»);
    $handle2 = fopen(«./block_coord_.db», «w»);
    while (!feof($handle)) {
    $buffer = fgets($handle, 4096);
    $buffer = str_replace(‘»‘, «‘», $buffer);
    $buffer = str_replace(‘ ‘, ‘»;»‘, $buffer);
    $buffer = str_replace(‘ — ‘, ‘»;»‘, $buffer);
    $buffer = trim($buffer);
    $buffer = ‘»‘.$buffer.’»‘;
    fwrite($handle2, $buffer.»\n»);
    }
    fclose($handle);
    fclose($handle2);
    echo «done!»;

  10. SEO Блог – оптимизация, раскрутка сайта. » Сервисы статистики на стороне интернет-магазинов пишет:

    [...] Как сделать определение страны/города по ip – здесь. [...]

  11. дядя вася пишет:

    А нахрена в конце запроса $dblink???

  12. дядя вася пишет:

    попробовал поставить параметр в запрос, и перестало работать.. данные неверные пишет …можа я чего низнамо.

  13. дядька лама пишет:

    Листал дамп, смущает, что регионы перекосячены для областей. Как выковыривать из дампа именно область а не город ???
    У меня есть база с регионами в порядке как на автомобильных номерах. Как бы их слепить воедино, кто даст умный совет?

  14. Бесплатное создание сайтов пишет:

    Необходимо выдернуть ip областей и вставить в тот скрипт — минуя города (сократить)

  15. sverel пишет:

    Очевидно же, что вместо (ip2-ip1) лучше создать столбец `delta` и во время загрузки дампа его один раз вычислить. Иначе MySQL будет вычислять его по всей таблице при каждом запросе!!! А в таблице на данный момент 150к записей. По `delta` можно будет создать индекс и запрос превратится в совершенно примитивный.

  16. sverel пишет:

    Упс. ошибся по поводу дельты — не до конца разобрался. Тем не менее, запрос всё же оптимизировал так:

    $ip = sprintf(‘%u’, ip2long($ip));
    $query = ‘SELECT * FROM `geo` WHERE ‘. $ip .’ BETWEEN `ip_from` AND `ip_to` ORDER BY (`ip_to`-`ip_from`) ASC LIMIT 1′;

  17. Dain пишет:

    Т.к. на данный момент на IpGeoBase нет непосредственно sql-дампа, обновляемого регулярно, а есть .txt-файлы, то вот моё решение:

    1). берем отсюда ipgeobase.ru/cgi-bin/Archive.cgi актуальный архив (я брал geo_files.tar.gz, датированный 05-07-2011, что не может не радовать), распаковываем, имеем 2 файла:

    - cidr_optim.txt. В нем строки, состоящие из:
    — ip-адрес «от» (в int’е),
    — ip-адрес «до» (в int’е),
    — интервал ip-адресов (в IPv4-формате),
    — зона,
    — id города;
    все разделено табуляцией;

    - cities.txt. В нем строки, состоящие из:
    — id города,
    — название города,
    — название региона,
    — название округа,
    — широта,
    — долгота;
    и тут все разделено табуляцией;

    2). создаем в mysql’е две таблицы:

    CREATE TABLE `geo_ips` (
    `ip_from` int(10) unsigned NOT NULL,
    `ip_to` int(10) unsigned NOT NULL,
    `zone` varchar(2) NOT NULL,
    `city_id` smallint(6) DEFAULT NULL,
    KEY `city_id` (`city_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    — мне нафиг был не нужен столбец «интервал ip-адресов в IPv4″, так что без него обошлось
    — P.S.: обратите внимание на KEY `city_id` (`city_id`) — ибо в результате записей в таблице будет около 130 000, а это малость многовато. Этот индекс нужен чтоб у вас потом ничё не тупило при джойне обеих таблиц по id-города

    CREATE TABLE `geo_cities` (
    `id` smallint(6) NOT NULL,
    `city_title` varchar(255) NOT NULL,
    `region_title` varchar(255) NOT NULL,
    `district_title` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    — а тут мне нафиг были не нужны столбцы «широта» и «долгота», так что обошлось и без них. Ну и cool

    3). пишем скрипт-парсер. Вот что я наШкодил:

    <?php
    // тут недурственно бы подключиться к базе; если не знаете, как, то дальше лучше не читать…

    // очистим имеющиеся таблицы; полезно, если будете этот скрипт юзать регулярно, скармливая ему новые файлы (данные-то у IpGeoBase обновляются, так-то!)
    mysql_query('TRUNCATE TABLE `geo_cities`');
    mysql_query('TRUNCATE TABLE `geo_ips`');

    // теперь заполним одну таблицу (какую — не принципиально)
    $strings = file('cidr_optim.txt'); // долго не парясь, всасываем файл построчно в массив
    $strings_count = sizeof($strings);
    $rows_inserted = 0;
    for ($i = 0; $i < $strings_count; $i++) {
    // ну и побежали…
    $cols = explode("\t", $strings[$i]);
    // а вот тут пришлось сделать тупо построчные инсерты, отдельно для каждой записи, ибо одним запросом 130 000 элементов моё двигло впихивать категорически отказалось :(
    $sql = 'INSERT INTO `geo_ips` (`ip_from`, `ip_to`, `zone`, `city_id`) VALUES ('.(int) $cols[0].', '.(int) $cols[1].', "'.$cols[3].'", '.( is_numeric(trim($cols[4])) ? (int) $cols[4] : 'null' ).')'; // у многих записей в ip-таблице нет городов, вместо них стоят прочерки, которые нам какбэ нах не упали, т.к. нам потом JOIN'ы юзать; пустое значение тоже не подойдет; остается null
    if ( !mysql_query($sql) ) {
    echo 'ERROR (inserting row #'.$i.'): '.mysql_errno().' — '.mysql_error().'’;
    } else {
    $rows_inserted++;
    }
    }
    echo ‘rows inserted (geo_ips): ‘.$rows_inserted.»; // ну это так, для наглядности :)

    // теперь заполним, соответственно, другую таблицу
    $strings = file(‘cities.txt’);
    $strings_count = sizeof($strings);
    $rows_inserted = 0;
    for ($i = 0; $i < $strings_count; $i++) {
    $cols = mysql_real_escape_string($strings[$i]);
    $cols = explode("\t", $cols);
    $sql = 'INSERT INTO `geo_cities` (`id`, `city_title`, `region_title`, `district_title`) VALUES ('.(int) $cols[0].', "'.$cols[1].'", "'.$cols[2].'", "'.$cols[3].'")';
    if ( !mysql_query($sql) ) {
    echo 'ERROR (inserting row #'.$i.'): '.mysql_errno().' — '.mysql_error().'’;
    } else {
    $rows_inserted++;
    }
    }
    echo ‘rows inserted (geo_cities): ‘.$rows_inserted.»;

    // узё
    // у меня скрипт срабатывает примерно за 20 секунд; многовато, но что делать…
    ?>

    4). изначально у меня задача получать название города посетителя, так что в итоге у меня получился вот такой вот запрос:

    <?php
    $user_ip = sprintf("%u\n", ip2long($_SERVER['REMOTE_ADDR']));

    $sql = '
    SELECT
    `gc`.`city_title`,
    `gc`.`region_title`
    FROM `geo_cities` AS `gc`
    INNER JOIN `geo_ips` AS `gi`
    ON `gi`.`city_id` = `gc`.`id`
    WHERE `gi`.`ip_from` = ‘.$user_ip;

    // …
    // ну дальше уж сами ;)
    ?>

  18. Dain пишет:

    \t (проверка табуляции)

  19. Dain пишет:

    проверка табуляции: \tраз-два, \tраз-два…

  20. Яр пишет:

    Dain или разбирающиеся!!!
    Помогите!

    Как закончить запрос:

    $sql = ‘
    SELECT
    `gc`.`city_title`,
    `gc`.`region_title`
    FROM `geo_cities` AS `gc`
    INNER JOIN `geo_ips` AS `gi`
    ON `gi`.`city_id` = `gc`.`id`
    WHERE `gi`.`ip_from` = ‘.$user_ip………………………….???????????????

  21. Gaer пишет:

    Dain, спасибо за подробный пример!
    Возможно ли использовать таблицы innoDB и можно ли изменить таблицы с данными в из MyISAM в innoDB?

  22. Артем пишет:

    Рабочий вариант

    $dblink = mysql_connect(‘localhost’, ‘mysql_user’, ‘mysql_password’);
    mysql_select_db(‘dbname’);
    $user_ip = sprintf(«%u\n», ip2long($_SERVER['REMOTE_ADDR']));
    $ra = mysql_query(«SELECT gc.city_title, gc.region_title FROM geo_cities AS gc INNER JOIN geo_ips AS gi ON gi.city_id = gc.id WHERE gi.ip_from = ‘».$user_ip.»‘»);

    if (mysql_num_rows($ra)>0)
    {
    $cn = mysql_fetch_array($ra);
    $cn = $cn['city_title'];
    echo $cn;
    }

  23. Артем пишет:

    $user_ip = sprintf(«%u\n», ip2long($_SERVER['REMOTE_ADDR']));

    $ra = mysql_query(«SELECT gc.city_title, gc.region_title FROM geo_cities AS gc INNER JOIN geo_ips AS gi ON gi.city_id = gc.id WHERE gi.ip_from = ‘».$user_ip.»‘»);

    if (mysql_num_rows($ra)>0)
    {
    $cn = mysql_fetch_array($ra);
    $cn = $cn['city_title'];
    echo $cn;
    }

  24. Артем пишет:

    Модератор, тут режет код и он неправильный публикуется

Оставьте свой отзыв!