Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
load geoip database into mysql
DROP TABLE IF EXISTS location;
CREATE TABLE IF NOT EXISTS `location` (
`locId` int(11) unsigned NOT NULL AUTO_INCREMENT,
`country` varchar(2) DEFAULT NULL,
`region` varchar(2) DEFAULT NULL,
`city` varchar(1000) DEFAULT NULL,
`postalCode` varchar(10) DEFAULT NULL,
`latitude` float DEFAULT NULL,
`longitude` float DEFAULT NULL,
`metroCode` int(11) DEFAULT NULL,
`areaCode` int(11) DEFAULT NULL,
PRIMARY KEY (`locId`),
KEY `city` (`city`(767))
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS blocks;
CREATE TABLE `blocks` (
`startIpNum` int(11) unsigned NOT NULL AUTO_INCREMENT,
`endIpNum` int(11) DEFAULT NULL,
`locId` int(11) DEFAULT NULL,
PRIMARY KEY (`startIpNum`)
) ENGINE=InnoDB AUTO_INCREMENT=3758096129 DEFAULT CHARSET=latin1;
DATABASE=geoip
DB_USER=root
mysql -u $DB_USER $DATABASE < db-setup.sql
wget http://geolite.maxmind.com/download/geoip/database/GeoLiteCity_CSV/GeoLiteCity-latest.zip
unzip -j GeoLiteCity-latest.zip
mv GeoLiteCity-Location.csv location.csv
mv GeoLiteCity-Blocks.csv blocks.csv
mysqlimport -u $DB_USER --ignore-lines=2 --fields-terminated-by=, --fields-optionally-enclosed-by='"' --local $DATABASE location.csv
mysqlimport -u $DB_USER --ignore-lines=2 --fields-terminated-by=, --fields-optionally-enclosed-by='"' --local $DATABASE blocks.csv
rm GeoLiteCity-latest.zip
@davidfkane

This comment has been minimized.

Show comment
Hide comment
@davidfkane

davidfkane May 26, 2015

Hi, Thanks for sharing this information, it helped me solve a problem. I was trying to import the New version of the Maxmind Geoip Database, which uses IP/CIDR notation instead of 'start' and 'end' IP addresses. This really helped. My solution is here: http://davidkane.net/installing-new-geoip-database-sql-database/

Many thanks.

Hi, Thanks for sharing this information, it helped me solve a problem. I was trying to import the New version of the Maxmind Geoip Database, which uses IP/CIDR notation instead of 'start' and 'end' IP addresses. This really helped. My solution is here: http://davidkane.net/installing-new-geoip-database-sql-database/

Many thanks.

@guruattimeinc

This comment has been minimized.

Show comment
Hide comment
@guruattimeinc

guruattimeinc May 2, 2017

I am trying to run through this code for translating ip to user agent, lat, long, zip code - but the join is slowing down overall processing. If we have 5M rows to process in hourly buckets. One option i am thinking is to run outer join ahead of my processing and use that combined data set for lookups, are there other options that could be considered? OR do you see any issues with the join planned?

-Thanks

I am trying to run through this code for translating ip to user agent, lat, long, zip code - but the join is slowing down overall processing. If we have 5M rows to process in hourly buckets. One option i am thinking is to run outer join ahead of my processing and use that combined data set for lookups, are there other options that could be considered? OR do you see any issues with the join planned?

-Thanks

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment