Importing IP2Location data into Cassandra and querying with PHP (IPv4)

3 min readDec 11, 2019

Intro

The guide will demonstrate how to import IP2Location data (DB11) in CSV form into Apache Cassandra and then query the data in a PHP page.

First of all, you will need to download the IP2Location DB11 CSV file.

Download Free LITE version at https://lite.ip2location.com/database-ip-country-region-city-latitude-longitude-zipcode-timezone

Download commercial version at https://ip2location.com/download?code=DB11CSV

Extract out the IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE.CSV file from the downloaded zipped file and store in the /mydata folder (our example, yours may differ).

Important Note

We will not cover the installation of Cassandra or PHP in this guide. We will assume you have already setup Cassandra and PHP on the localhost and are using PHP via Apache (also on the localhost). For this example, we are using a Debian machine.

You will also need to install the PHP Cassandra driver from https://pecl.php.net/package/cassandra

Pre-process the CSV data

Before we import the CSV data, we have to insert a dummy column into the data for the partition key. As we will be performing an ordered search, all of the rows will have the same partition key.

In Bash, run the following command to prefix every row in the CSV file with the dummy column and output the results into a new CSV file.

sed -e 's/^/"db11",/' /mydata/IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE.CSV > /mydata/IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE.CSV2

Importing the CSV data into Cassandra

In the cqlsh, run the following command to create the keyspace (equivalent of a database).

CREATE KEYSPACE IF NOT EXISTS ip2location WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 3};

After creating the keyspace, you need to select it by running the below command.

USE ip2location;

Next, run the following command to create the table.

DROP TABLE IF EXISTS ip2location_db11; CREATE TABLE IF NOT EXISTS ip2location_db11 ( dummy varchar, ip_from bigint, ip_to bigint, country_code varchar, country_name varchar, region_name varchar, city_name varchar, latitude float, longitude float, zip_code varchar, time_zone varchar, PRIMARY KEY (dummy, ip_to) ) WITH CLUSTERING ORDER BY (ip_to ASC);

Now that we have a table, we will commence the import of data from our CSV file into the table.

COPY ip2location_db11 (dummy, ip_from, ip_to, country_code, country_name, region_name, city_name, latitude, longitude, zip_code, time_zone) FROM '/mydata/IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE.CSV2';

Querying the data in PHP

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 = '8.8.8.8'; function queryIP2Location($myip) { $keyspace = 'ip2location'; $cluster = Cassandra::cluster()->build(); // localhost $session = $cluster->connect($keyspace); $myipnum = sprintf('%u', ip2long($myip)); $statement = new Cassandra\SimpleStatement('SELECT * FROM ip2location_db11 WHERE dummy = \'db11\' AND ip_to >= ' . $myipnum . ' ORDER BY ip_to LIMIT 1'); $future = $session->executeAsync($statement); $result = $future->get(); if ($result->count() == 0) die('No record found' . "<br>\n"); return $result[0]; } $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"; ?>

Originally published at https://blog.ip2location.com on December 11, 2019.