mod geoip

Often it is desireable to Geotarget your web content. This is a way we do geotargetting using MaxMinds geoip database.

This document is a stepwise explanation of how to
1. obtain the relevant csv files from GeoIP
2. create the tables
3. import the data
4. use this data to figure out where your visitors are coming from

It is relevant when you use the MySQL based solution combined with PHP. There’s a simpler method to achieve this as well please use the contact form or post a comment here to discuss that.

-by Imtiaz A. Khan -230906 khanimtiaz@gmail.com

Step 1. -Obtain the relevant csv
================================
GeoIP provides a free version of there database that can be used for pretty much all the functionality
of geotargetting The csv version of the database is available at
http://www.maxmind.com/download/geoip/database/GeoLiteCity_CSV/GeoLiteCity_20060901.zip

For the latest version Please visit : http://www.maxmind.com/app/geolitecity

Extract the compressed files after download completes. You should have two files namely..
GeoLiteCity-Blocks.csv and GeoLiteCity-Location.csv

Rename these to locip.csv and lookup.csv respectively. Remove all double qoutes from locip.csv.

The data in these files is under the following heads:
locip.csv: startIpNum,endIpNum,locId
lookup.csv: locId,country,region,city,postalCode,latitude,longitude,dmaCode,areaCode

We’re going to import these to the database in the next step. Before that remove
the header rows and any copyright information from the files as we need just the data.

Step 2. -create the database and the tables
============================================
We need a database for the two tables that will be used. And a table structure for the data.

login to your MySQL server and run the following commands:

CREATE DATABASE geoip;

USE geoip;
CREATE TABLE locip (
start_ip INT UNSIGNED NOT NULL,
end_ip INT UNSIGNED NOT NULL,
locId INT UNSIGNED NOT NULL
);

CREATE TABLE lookup (
locId INT UNSIGNED NOT NULL,
country CHAR(15) NOT NULL,
region CHAR(2) NOT NULL,
city CHAR(30) NOT NULL,
postalcode CHAR(15) NOT NULL,
latitude CHAR(15) NOT NULL,
longitude CHAR(15) NOT NULL,
dmaCode CHAR(15) NOT NULL,
areaCode CHAR(15) NOT NULL
);

Step 3. -import the data
========================
Next we do the actual import. From the command line run the following
commands to import the data in csv files into the MySQL tables

mysqlimport –delete –local –fields-terminated-by=’,’
–fields-optionally-enclosed-by='”‘
–lines-terminated-by=’n’ –user=’user’
–password=’password’ geoip locip.csv

mysqlimport –delete –local –fields-terminated-by=’,’
–fields-optionally-enclosed-by='”‘ –lines-terminated-by=’n’
–user=’user’ –password=’password’ geoip lookup.csv

NOTE: The names of files and the qoutes are important.

Check the data in the database by running random selects just to make sure all got in fine.

Step 4. -check your work
========================
You can take a look at GeoIP in action using the following php script.

< ?php global $link; $link= mysql_connect("localhost","username","password"); if( !$link ) die("could not connect to MySQL"); mysql_select_db("geoip",$link) or die("Could't open organizer::" .mysql_error() ); $a=split(".", $REMOTE_ADDR); $ip=$a[0] . "." . $a[1] . "." . $a[2]. "." . $a[3]; $ip_num=16777216*$a[0] + 65536*$a[1] + 256*$a[2] + $a[3]; $sql_getlocid="SELECT locId FROM locip WHERE $ip_num >= start_ip AND $ip_num< = end_ip"; $result=mysql_query($sql_getlocid,$link); $row = mysql_fetch_assoc($result); $locid=$row["locId"]; $sql_getreg="SELECT * FROM lookup WHERE locId=" . $locid; $result=mysql_query($sql_getreg,$link); $row = mysql_fetch_assoc($result); $region=$row["region"]; echo "IP--->” . $REMOTE_ADDR . “”;
echo “Country—>” . $row[“country”] . “”;
echo “Region—>” . $row[“region”] . “”;
echo “City—>” . $row[“city”] . “”;
echo “Longitude—>” . $row[“longitude”] . “”;
echo “Latitude—>” . $row[“latitude”] . “”;
echo “Area Code—>” . $row[“areaCode”] . “”;
echo “Postal Code—>” . $row[“postalcode”] . “”;

?>

This script when run properly should give you details of your own machines IP.
If the GeoIP database doesn’t have correct information about your IP then it’ll
not show some of the data.

To see different results as in how it would behave if someone else from someplace
else tried to access your machine try changing the proxy settings on your system and
accessing the script: Use the proxy list available here: http://www.samair.ru/proxy/

Remember the free database is 97% accurate for IPs in the USA and approximately 60%
for the rest of the world, which means even though it’ll tell you the country information
correct it might not be able to give you the finer details like city or region.

Please contact Imtiaz at khanimtiaz@gmail.com in case you need assistance with your GeoIP setup/usage.