Importing IP2Location data into MongoDB and querying with PHP (IPv6)

The aim of this guide is to demonstrate how to import IP2Location data (DB11) in csv form into MongoDB and then query the data in a PHP web page.

First of all, you will need to download the IP2Location DB11 csv file.
Download Free LITE version at http://lite.ip2location.com/database-ip-country-region-city-latitude-longitude-zipcode-timezone
Download commercial version at http://ip2location.com/download?code=DB11IPV6

Extract out the IPV6-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE.CSV file from the downloaded zipped file.

Important Note

We will not cover installation of MongoDB, Perl and PHP in this guide. We will assume you have already setup MongoDB, Perl and are using PHP via Apache on a Linux or Windows platform.

Preparing the csv data for import

We will be using a Perl script to create an index field in the CSV data to speed up queries later.

Create a new Perl file called createindex.pl and paste the following code into it:

use strict;

my $filename = "IPV6-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE.CSV";
my $filename2 = "INDEXED.CSV";
my $padchar = "0";
my $padlen = 40;

open IN, "<$filename" or die "Cannot open input file";
open OUT, ">$filename2" or die "Cannot open output file";
while (<IN>)
{
	my $line = $_;
	
	if ($line =~ /^"[^"]+","([^"]+)",/)
	{
		my $ipnum = $1;
		my $result = $padchar x ($padlen - length($ipnum));
		$result .= $ipnum;
		print OUT '"A' . $result . '",' . $line;
	}
}
close OUT;
close IN;

Run the Perl script by calling the below command in command prompt:
perl createindex.pl

Importing the csv data into MongoDB

Linux
At the command prompt, run the following command:
mongoimport --drop --db ip2location --collection db11 --type csv --file "/var/www/TestMongoPHP/INDEXED.CSV" --fields ip_to_index,ip_from,ip_to,country_code,country_name,region_name,city_name,latitude,longitude,zip_code,time_zone

My csv file folder is /var/www/TestMongoPHP while your folder may be different so remember to change to the folder where you have extracted the csv file earlier.

Windows
At the command prompt, run the following command:
C:\mongodb\bin\mongoimport --drop --db ip2location --collection db11 --type csv --file "C:\inetpub\wwwroot\TestMongoPHP\INDEXED.CSV" --fields ip_to_index,ip_from,ip_to,country_code,country_name,region_name,city_name,latitude,longitude,zip_code,time_zone

My MongoDB is installed in C:\mongodb but if your installation folder is different, remember to change the example above. Same with the csv file folder; mine is C:\inetpub\wwwroot\TestMongoPHP\ so remember to change to the folder where you have extracted the csv file earlier.

Create index to speed up queries

Linux
Next, create a text file called buildindex and paste the following code into it:
use ip2location
db.db11.ensureIndex({ip_to: 1})
exit

Save the buildindex and then run the following in command prompt:
mongo < buildindex

Windows
Next, create a text file called buildindex.txt and paste the following code into it:
use ip2location
db.db11.ensureIndex({ip_to: 1})
exit

Save the buildindex.txt and then run the following in command prompt:
C:\mongodb\bin\mongo.exe < buildindex.txt

Again, my MongoDB is installed in C:\mongodb but if your installation folder is different, remember to change the example above.

Querying the IP2Location data from a PHP web page

Note: You will need to install the PHP driver for MongoDB. Please see the link below for more info: Go to http://docs.mongodb.org/ecosystem/drivers/php/ for more info.

Now, create a PHP file called test.php in your website.

Paste the following PHP code into it and then run it in the browser:

<?php
// ip address to test
$ip = "8.8.8.8";

// connect to Mongo
$m = new MongoClient();

// select a database
$db = $m->ip2location;

// select a collection (analogous to a relational database's table)
$collection = $db->db11;

function ip62long($ipv6) {
        $ip_n = inet_pton($ipv6);
        $bits = 15;
        $ipv6long = 0;

        while ($bits >= 0) {
                $bin = sprintf('%08b',(ord($ip_n[$bits])));

                if ($ipv6long) {
                        $ipv6long = $bin . $ipv6long;
                }
                else {
                        $ipv6long = $bin;
                }
                $bits--;
        }
        return gmp_strval(gmp_init($ipv6long, 2), 10);
}

function queryIP2Location($myip) {
        global $collection;

        // convert IP address to IP number
        if (filter_var($myip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4)) {
                $myip = '::FFFF:' . $myip;
        }
        if (filter_var($myip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV6)) {
                $ipnum = ip62long($myip);
        }

        // pad ipnum to 40 digits with zeroes in front so we can do string comparison
        $ipnum = 'A' . str_pad($ipnum, 40, '0', STR_PAD_LEFT);

        // filter those ip_to which are greater than our IP number
        $query = array('ip_to_index' => array('$gte' => $ipnum));

        // query the database and limit to only 1 result with sort ip_to in ascending order
        $cursor = $collection->find($query)->sort(array('ip_to_index' => 1))->limit(1);

        // convert from iterator to array form
        $retarr = iterator_to_array($cursor);
        $retarrfinal = array();

        // getting down to the sub-array which holds the actual data
        foreach ($retarr as $retarr2) {
                $retarrfinal = $retarr2;
        }

        // remove the Mongo auto id field
        unset($retarrfinal['_id']);

        // remove the index field
        unset($retarrfinal['ip_to_index']);

        return ($retarrfinal);
}

// call the function to get IP2Location information on the ip address
$myresult = queryIP2Location($ip);

// output the result
echo 'ip_from: ' . $myresult["ip_from"] . "<br>\n";
echo 'ip_to: ' . $myresult["ip_to"] . "<br>\n";
echo 'country_code: ' . $myresult["country_code"] . "<br>\n";
echo 'country_name: ' . $myresult["country_name"] . "<br>\n";
echo 'region_name: ' . $myresult["region_name"] . "<br>\n";
echo 'city_name: ' . $myresult["city_name"] . "<br>\n";
echo 'latitude: ' . $myresult["latitude"] . "<br>\n";
echo 'longitude: ' . $myresult["longitude"] . "<br>\n";
echo 'zip_code: ' . $myresult["zip_code"] . "<br>\n";
echo 'time_zone: ' . $myresult["time_zone"] . "<br>\n";
?>

Do you like this article? Share it with others by clicking the social media buttons below. We will write more articles related to this topic.