MaxMind GeoIP

Aus Weis nix

Setup and use of MaxMind's GeoIP City database:

Inhaltsverzeichnis 1 Datafile Retrieval 2 Container Databases 3 Data Import 4 Query

Datafile Retrieval

The CSV files containing the data was obtained by:

• accessing the MaxMind Member Center
• downloading the CSV file as ZIP (approx 22MB)
• unzipping the archive

Container Databases

The following SQL statements create a MySQL database that can receive the CSV data:

DROP TABLE IF EXISTS geoip_blocks;
DROP TABLE IF EXISTS geoip_location;
CREATE TABLE geoip_blocks (
 startip int(10) unsigned NOT NULL default '0',
 stopip int(10) unsigned NOT NULL default '0',
 locid int(10) unsigned NOT NULL default '0',
 KEY startip (startip)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE geoip_location (
 locid int(10) unsigned NOT NULL default '0',
 country char(2) NOT NULL default ,
 region char(2) NOT NULL default ,
 city varchar(32) NOT NULL default ,
 zipcode varchar(8) NOT NULL default ,
 latitude float NOT NULL default '0',
 longitude float NOT NULL default '0',
 dmacode int(11) NOT NULL default '0',
 areacode int(11) NOT NULL default '0',
 KEY locid (locid)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Data Import

The CSV data can be imported into the tables using:

LOAD DATA INFILE '/root/GeoIP/GeoIP-134_20060306/GeoIPCity-134-Blocks.csv' INTO TABLE geoip_blocks 
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;
LOAD DATA INFILE '/root/GeoIP/GeoIP-134_20060306/GeoIPCity-134-Location.csv' INTO TABLE geoip_location 
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;

Query

• Convert incomping IP quad to integer IP (i.e. see IP Converter) i.e. ?=3273049628 for IP 195.22.198.28
• Sample perl code with input checking:

my $ip_match = qr/^(\d|[01]?\d\d|2[0-4]\d|25[0-5])\.
(\d|[01]?\d\d|2[0-4]\d|25[0-5])\.
(\d|[01]?\d\d|2[0-4]\d|25[0-5])\.
(\d|[01]?\d\d|2[0-4]\d|25[0-5])$/o;
if (@x=($ip =~ $ip_match)) {
 $ipnumber = 256*(256*(256*$x[0] + $x[1]) + $x[2]) + $x[3];
}

• Note that the simplistic query below will be very slow due to the work involved in combining the two large sets formed by the WHERE clause (regardless of index availability).

SELECT country FROM geoip_blocks,geoip_location 
  WHERE ?>=startip AND ?<=stopip AND geoip_blocks.locid=geoip_location.locid

• A much faster SQL Query using a single index:

SELECT (?<=stopip) AS flag, geoip_location.*
FROM geoip_blocks 
LEFT JOIN geoip_location ON geoip_location.locid=geoip_blocks.locid
WHERE startip<=? 
ORDER BY startip DESC 
LIMIT 1;

and use data if (flag==1)