How to get a guess on where your visitor come from using his IP address

Introduction

There is some free database at ip2location.com and the installation process is detailed here

scp IP2LOCATION-LITE-DB5.CSV.ZIP $USERNAME@$HOST:~/
ssh $USERNAME@$HOST
unzip IP2LOCATION-LITE-DB5.CSV.ZIP
rm *.{TXT,ZIP}

Install on Mysql

  • Push the data in the database 
    mysql -u $USER -p --enable-local-infile

CREATE DATABASE ip2location;
USE ip2location;
CREATE TABLE `i	p2location`(
    `ip_from` INT(10) UNSIGNED,
    `ip_to` INT(10) UNSIGNED,
    `country_code` CHAR(2),
    `country_name` VARCHAR(64),
    `region_name` VARCHAR(128),
    `city_name` VARCHAR(128),
    `latitude` DOUBLE,
    `longitude` DOUBLE,
    INDEX `idx_ip_from` (`ip_from`),
    INDEX `idx_ip_to` (`ip_to`),
    INDEX `idx_ip_from_to` (`ip_from`, `ip_to`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

USE ip2location;

LOAD DATA LOCAL
INFILE 'IP2LOCATION-LITE-DB5.CSV'
INTO TABLE
`ip2location`
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 0 LINES;

Install on Mongo

Import:

mongoimport --drop --db places --collection locations --type csv --file IP2LOCATION-LITE-DB5.CSV --fields ip_from,ip_to,country_code,country_name,region_name,city_name,lat,lng

Open mongo and create index:

mongo

# in mongo
use places
db.locations.ensureIndex({ip_to: 1})
db.locations.count()
exit

Usage

MYIP=curl -X GET ifconfig.co
mysql -u $USER -p ip2location -e "SELECT * FROM ip2location where ip_to < INET_ATON('$MYIP') LIMIT 1;"