Load GeoIP data

system echo '*** DROP DATABASE iploc ...'

DROP DATABASE if exists iploc;


system echo '*** CREATE DATABASE iploc ...'
CREATE DATABASE iploc;
GRANT all on iploc.* to root@'%';


USE iploc;


system echo '*** CREATE TABLE cityblocks ...'
CREATE TABLE cityblocks (
  startIpNum bigint NOT NULL,
  endIpNum bigint NOT NULL,
  locId int NOT NULL,
  KEY startIpNum (startIpNum,endIpNum)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


system echo '*** CREATE TABLE citylocation ...'
CREATE TABLE citylocation (
  locId int NOT NULL,
  country varchar(2) NOT NULL,
  region varchar(55) NOT NULL,
  city varchar(55) NOT NULL,
  postalCode varchar(10) NOT NULL,
  latitude decimal(7,4) NOT NULL,
  longitude decimal(7,4) NOT NULL,
  metroCode varchar(3) NOT NULL,
  areaCode varchar(3) NOT NULL,
  KEY locId (locId)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


system echo '*** LOAD data to cityblocks ...'
LOAD DATA LOCAL INFILE 'GeoIPCity-134-Blocks.csv'
INTO TABLE cityblocks
FIELDS TERMINATED BY ','
ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
IGNORE 2 LINES
(startIpNum, endIpNum, locId);


system echo '*** LOAD data to citylocation ...'
LOAD DATA LOCAL INFILE 'GeoIPCity-134-Location.csv'
INTO TABLE citylocation
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
IGNORE 2 LINES
(locId,country,region,city,postalCode,latitude,longitude,metroCode,areaCode);


system echo '*** CREATE TABLE blockslocation ...'
CREATE TABLE blockslocation (
  startIpNum bigint NOT NULL,
  endIpNum bigint NOT NULL,
  locId int NOT NULL,
  latitude decimal(7,4) NOT NULL,
  longitude decimal(7,4) NOT NULL,
  KEY startIpNum (startIpNum,endIpNum)
)ENGINE=MyISAM DEFAULT CHARSET=latin1;


system echo '*** INSERT data to blockslocation ...'

INSERT INTO blockslocation SELECT startIpNum, endIpNum, cityblocks.locId, latitude, longitude FROM cityblocks, citylocation WHERE cityblocks.locId = citylocation.locId;