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

The aim of this guide is to demonstrate how to import IP2Location data (DB11) in csv form into CouchDB 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 CouchDB or PHP in this guide. We will assume you have already setup CouchDB and PHP on the localhost and are using PHP via Apache (also on the localhost). For this example, we are using an Amazon EC2 instance running Debian Linux.

More info can be found at the following URLs if you need assistance with installations:
PHP: http://php.net/manual/en/install.unix.debian.php
CouchDB: http://couchdb.apache.org/

Importing the csv data into CouchDB

Create a new PHP file called couchdb.php and paste the following code into it:

<?php
class CouchDB {
	private $username;
	private $password;

	function __construct($db, $host = 'localhost', $port = 5984, $username = null, $password = null) {
		$this->db = $db;
		$this->host = $host;
		$this->port = $port;
		$this->username = $username;
		$this->password = $password;
	}

	static function decode_json($str) {
		return json_decode($str);
	}

	static function encode_json($str) {
		return json_encode($str);
	}
	
	function create_db() {
		return $this->db_action('PUT');
	}
	
	function delete_db() {
		return $this->db_action('DELETE');
	}
	
	function import_db($data = NULL) {
		return $this->db_action('POST', $data);
	}
	
	function db_action($method = 'PUT', $data = NULL) {
		$url = '/' . $this->db . (($method == 'POST') ? '/_bulk_docs' : '');
		$request = new CouchDBRequest($this->host, $this->port, $url, $method, $data, $this->username, $this->password);
		return $request->send();
	}
	
	function db_query($ipnum) {
		$url = '/' . $this->db . '/_all_docs?include_docs=true&startkey="' . $ipnum . '"&limit=1';
		$request = new CouchDBRequest($this->host, $this->port, $url, 'GET', NULL, $this->username, $this->password);
		return $request->send();
	}
}

class CouchDBRequest {
	static $VALID_HTTP_METHODS = array('DELETE', 'GET', 'POST', 'PUT');

	private $method = 'GET';
	private $url = '';
	private $data = NULL;
	private $sock = NULL;
	private $username;
	private $password;

	function __construct($host, $port = 5984, $url, $method = 'GET', $data = NULL, $username = null, $password = null) {
		$method = strtoupper($method);
		$this->host = $host;
		$this->port = $port;
		$this->url = $url;
		$this->method = $method;
		$this->data = $data;
		$this->username = $username;
		$this->password = $password;

		if(!in_array($this->method, self::$VALID_HTTP_METHODS)) {
			throw new Exception('Invalid HTTP method: '.$this->method);
		}
	}

	function getRequest() {
		$req = "{$this->method} {$this->url} HTTP/1.0\r\nHost: {$this->host}\r\n";

		if($this->username || $this->password)
			$req .= 'Authorization: Basic '.base64_encode($this->username.':'.$this->password)."\r\n";

		if($this->data) {
			$req .= 'Content-Length: '.strlen($this->data)."\r\n";
			$req .= 'Content-Type: application/json'."\r\n\r\n";
			$req .= $this->data."\r\n";
		} else {
			$req .= "\r\n";
		}

		return $req;
	}

	private function connect() {
		$this->sock = @fsockopen($this->host, $this->port, $err_num, $err_string);
		if(!$this->sock) {
			throw new Exception('Could not open connection to '.$this->host.':'.$this->port.' ('.$err_string.')');
		}
	}

	private function disconnect() {
		fclose($this->sock);
		$this->sock = NULL;
	}

	private function execute() {
		fwrite($this->sock, $this->getRequest());
		$response = '';
		while(!feof($this->sock)) {
			$response .= fgets($this->sock);
		}
		$this->response = new CouchDBResponse($response);
		return $this->response;
	}

	function send() {
		$this->connect();
		$this->execute();
		$this->disconnect();
		return $this->response;
	}

	function getResponse() {
		return $this->response;
	}
}

class CouchDBResponse {
	private $raw_response = '';
	private $headers = '';
	private $body = '';

	function __construct($response = '') {
		$this->raw_response = $response;
		list($this->headers, $this->body) = explode("\r\n\r\n", $response);
	}

	function getRawResponse() {
		return $this->raw_response;
	}

	function getHeaders() {
		return $this->headers;
	}

	function getBody($decode_json = false) {
		return $decode_json ? CouchDB::decode_json($this->body) : $this->body;
	}
}
?>

Next, create a new PHP file called import.php and paste the following code into it:

<?php
require("couchdb.php");

$db = 'db11'; // must be all in lower case
$filename = 'IPV6-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE.CSV';
$itemsperbatch = 5000;
$padzero = 40; // need to pad the ip numbers because key comparison is done as a string

$couchdb = new CouchDB($db);

//delete old database
$result = $couchdb->delete_db();

//create database
$result = $couchdb->create_db();

//read data file
$handle = fopen($filename, "r");
$contents = '';
$dataarr = array();
$counter = 0;
while (!feof($handle)) {
	$line = fgets($handle, 8192);
	$dataarr[] = $line;
	
	if (count($dataarr) == $itemsperbatch) {
		echo "Importing row " . $counter . "\n";
		doImport($dataarr);
		$dataarr = array(); //reset
	}
}
fclose($handle);
if (count($dataarr) > 0) {
	doImport($dataarr);
	$dataarr = array(); //reset
}

function doImport($dataarr) {
	global $couchdb;
	global $counter;
	global $padzero;
	
	$mainarr = array();
	foreach ($dataarr as $data) {
		$data = rtrim($data); // clear EOL
		
		if (preg_match('/^"[^"]+","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)"$/', $data, $matches) == 1) {
			$counter++;
			$ipto = $matches[1];
			$countrycode = $matches[2];
			$countryname = $matches[3];
			$regionname = $matches[4];
			$cityname = $matches[5];
			$lat = $matches[6];
			$long = $matches[7];
			$zipcode = $matches[8];
			$timezone = $matches[9];
			$itemarr = array();
			$itemarr["_id"] = str_pad($ipto, $padzero, '0', STR_PAD_LEFT);
			$itemarr["COUNTRY_CODE"] = $countrycode;
			$itemarr["COUNTRY_NAME"] = $countryname;
			$itemarr["REGION_NAME"] = $regionname;
			$itemarr["CITY_NAME"] = $cityname;
			$itemarr["LATITUDE"] = $lat;
			$itemarr["LONGITUDE"] = $long;
			$itemarr["ZIP_CODE"] = $zipcode;
			$itemarr["TIME_ZONE"] = $timezone;
			
			$mainarr[] = json_encode($itemarr);
		}
	}
	
	$jsonstr = '{"docs": [' . implode(',', $mainarr) . ']}';
	$result = $couchdb->import_db($jsonstr);
}
?>

Run the PHP script by calling the below command in command prompt:
php import.php

Querying the IP2Location data from a PHP web page

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
require("couchdb.php");

$db = 'db11'; // must be all in lower case

// ip address to test
$ip = "8.8.8.8";

// need to pad the ip numbers because key comparison is done as a string
$padzero = 40;

$couchdb = new CouchDB($db);

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 $couchdb;
	global $padzero;
	
	// 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 = str_pad($ipnum, $padzero, '0', STR_PAD_LEFT);

	$result = $couchdb->db_query($ipnum);
	$resultarr = json_decode($result->getBody(), true);
	$resultarr = $resultarr["rows"][0]["doc"];
	return $resultarr;
}

$myresult = queryIP2Location($ip);

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.