GeoIP MySQL database creation and CSV loading script

If you need to create a GeoIPCity database and load the CSV files from GeoIP, just run this SQL script (make sure you put the CSV files in the right place).

After loading the DB, just run the following SQL command:

SELECT * FROM geoip_blocks JOIN geoip_loc ON geoip_blocks.locId = geoip_loc.locId
WHERE BETWEEN startIpNum AND endIpNum


CREATE TABLE `geoip_blocks` (
`startIpNum` BIGINT NOT NULL ,
`endIpNum` BIGINT NOT NULL ,
`locId` BIGINT NOT NULL
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_bin;

CREATE TABLE `geoip_loc` (
`locId` BIGINT NOT NULL ,
`country` VARCHAR( 2 ) NULL ,
`region` VARCHAR( 3 ) NULL ,
`city` VARCHAR( 100 ) NULL ,
`postalCode` VARCHAR( 10 ) NULL ,
`latitude` FLOAT NOT NULL ,
`longitude` FLOAT NOT NULL ,
`metroCode` INT NULL ,
`areaCode` INT NULL ,
PRIMARY KEY ( `locId` )
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_bin;

LOAD DATA INFILE '/root/geoip/GeoLiteCity-Blocks.csv' INTO TABLE geoip_blocks FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY ',' IGNORE 2 LINES;

LOAD DATA INFILE '/root/geoip/GeoLiteCity-Location.csv' INTO TABLE geoip_loc FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY ',' IGNORE 2 LINES;