GeoIP Location

I am a fan of free data and sharing cool information

So I give props to maxmind.com for their free lite geo IP data base.
The database can be found at : http://dev.maxmind.com/geoip/geoip2/geolite2/ They are CSV Files so you need to import them.

here is how I import them into MySQL:
Create the first table:

CREATE TABLE `geocityblocks` (
  `network_start_ip` varchar(45) DEFAULT NULL,
  `network_prefix_length` bigint(20) DEFAULT NULL,
  `geoname_id` bigint(20) DEFAULT NULL,
  `registered_country_geoname_id` bigint(20) DEFAULT NULL,
  `represented_country_geoname_id` bigint(20) DEFAULT NULL,
  `postal_code` varchar(45) DEFAULT NULL,
  `latitude` float(10,6) DEFAULT NULL,
  `longitude` float(10,6) DEFAULT NULL,
  `is_anonymous_proxy` int(11) DEFAULT NULL,
  `is_satellite_provider` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

now import the very large 198meg CSV file. I run this command in MySQL Workbench:

load data local infile 'C:/Users/mell/Downloads/GeoLite2-City-CSV/GeoLite2-City-CSV_20141202/GeoLite2-City-Blocks.csv'
into table `test`.`geoCityBlocks`
fields terminated by ','
OPTIONALLY enclosed by '"'
lines terminated by '\n'
ignore 1 lines
(network_start_ip,network_prefix_length,geoname_id,registered_country_geoname_id,represented_country_geoname_id,
postal_code,latitude,longitude,is_anonymous_proxy,is_satellite_provider)

It should be around 3,080,877 rows

---

Now lets work on the other table:

CREATE TABLE `geocitylocations` (
  `geoname_id` varchar(100) DEFAULT NULL,
  `continent_code` varchar(100) DEFAULT NULL,
  `continent_name` varchar(100) DEFAULT NULL,
  `country_iso_code` varchar(100) DEFAULT NULL,
  `country_name` varchar(100) DEFAULT NULL,
  `subdivision_iso_code` varchar(100) DEFAULT NULL,
  `subdivision_name` varchar(100) DEFAULT NULL,
  `city_name` varchar(100) DEFAULT NULL,
  `metro_code` varchar(100) DEFAULT NULL,
  `time_zone` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Now import the data to that table. I run this command in MySQL Workbench:

load data local infile 'C:/Users/mell/Downloads/GeoLite2-City-CSV/GeoLite2-City-CSV_20141202/GeoLite2-City-Locations.csv'
into table `test`.`geoCityLocations`
fields terminated by ','
OPTIONALLY enclosed by '"'
lines terminated by '\n'
ignore 1 lines
(geoname_id,continent_code,continent_name,country_iso_code,country_name,subdivision_iso_code,subdivision_name,
city_name,metro_code,time_zone) 

should be around 86,470 rows


for the US this is how an entry looks

geoname_id=4700168
continent_code=NA
continent_name=North America
country_iso_code=US
country_name=United States
subdivision_iso_code=TX
subdivision_name=Texas
city_name=Irving
metro_code=623
time_zone=America/Chicago


and finally some code to use the data:
PHP Code: https://github.com/maxmind/geoip-api-php/blob/master/src/geoip.inc
About the CSV files: http://dev.maxmind.com/geoip/geoip2/geoip2-csv-databases/

So lets make this stuff more useful. Since everything is stored in ipv6 as strings we need to add 2 more fields:
ipv6_binary as varbinary(16)
ipv6_binary_top as as varbinary(16)
So run this is MySQL workbench: its going to take a few minutes

ALTER TABLE `test`.`geocityblocks` ADD COLUMN `ipv6_binary` VARBINARY(16) NULL  AFTER `is_satellite_provider` ;
ALTER TABLE `test`.`geocityblocks` ADD COLUMN `ipv6_binary_top` VARBINARY(16) NULL  AFTER `ipv6_binary` ;

This is going to store the ipv6 address as binary in ipv6_binary. e.g. 4.28.169.0 = ffff 041c a900
Then we will compute the top range and add that as well in ipv6_binary_top:255 allowed address FFFF 041C A9FF
-- The below line to set the binary for all the address. it takes awhile to run and you will need a version of MySQL that supports INET6_ATON, (run time about 3-5 minutes) :)

update test.geoCityBlocks set ipv6_binary=INET6_ATON(network_start_ip);

now increment the ipv6_binary_top: INET6_ATON(network_start_ip) + getIpRange(network_prefix_length)
You will need to use the function I made list at the bottom of the page.

matching the IP:
You will need to pad the hex up to 16:
select * from test.geoCityBlocks where ipv6_binary>=x'00000000000000000000ffff041ca900' && ipv6_binary_top<=x'00000000000000000000ffff041ca900'

---

Functions:

DELIMITER $$

CREATE FUNCTION `test`.`getIpRange` (network_prefix INT)
RETURNS INTEGER
BEGIN
	DECLARE x INT;
    DECLARE j INT;
    DECLARE y INT;
    SET x = 1;
	SET y = 1;
    SET j = 129-network_prefix;

	 sloop: LOOP
		
		SET x = x + 1;
		IF x > j THEN
            LEAVE sloop;
        END IF; 
		SET y = y + y;
		ITERATE sloop;
	END LOOP sloop;


RETURN y;
END