Skip to content

Instantly share code, notes, and snippets.

@diogenese

diogenese/nopaste

Created April 13, 2019 08:14
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save diogenese/c41305b539cdfda9aa41436912047d56 to your computer and use it in GitHub Desktop.
Save diogenese/c41305b539cdfda9aa41436912047d56 to your computer and use it in GitHub Desktop.
Download ZIP
GeoLite2 import from csv files
Raw
nopaste
USE `geoipdb`;
SELECT "Creating functions and procedures";
DELIMITER ;;
DROP PROCEDURE IF EXISTS `ip2city`;;
CREATE PROCEDURE `ip2city`(
fp_ipstr VARCHAR(15)
)
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE v_ip INT unsigned DEFAULT 0;
SET v_ip = ip4str_to_int( fp_ipstr );
SELECT CONCAT(l.city,IF(LENGTH(city), ', ', ''),
r.region,IF(LENGTH(r.region), ', ', ''),c.country)
FROM `geo_iprange` i JOIN `geo_blocks` b ON i.id = b.rangeid
JOIN `geo_locations` l ON i.locid=l.id
JOIN `geo_countries` c ON c.iso=l.iso
JOIN `geo_regions` r ON r.iso=l.iso AND r.admin=l.admin
WHERE i.ipstart<=v_ip AND i.ipend>=v_ip AND geoblock=(v_ip>>16)<<16;
END ;;
CREATE FUNCTION `ip4str_to_int`(
fp_ipstr VARCHAR(255)
) RETURNS int(10) unsigned
NO SQL
DETERMINISTIC
BEGIN
DECLARE v_part, v_rem VARCHAR(15) DEFAULT "";
DECLARE v_ret,v_pos INT unsigned DEFAULT 0;
SET v_rem = fp_ipstr;
WHILE LENGTH( v_rem ) DO
SET v_pos = LOCATE( '.', v_rem );
IF v_pos > 0 THEN
SET v_part = SUBSTRING( v_rem, 1, v_pos - 1);
SET v_rem = SUBSTRING( v_rem, v_pos + 1);
SET v_ret = ( v_ret << 8 ) + v_part;
ELSE
SET v_ret = ( v_ret << 8 ) + v_rem;
SET v_rem = '';
END IF;
END WHILE;
RETURN v_ret;
END ;;
DELIMITER ;
SELECT "Createing temp tables";
DROP TABLE IF EXISTS `geo_csv_city_blocks_ipv4`;
CREATE TABLE `geo_csv_city_blocks_ipv4` (
`network` VARCHAR(20) NOT NULL DEFAULT "",
`ipbyte1` INT(3) unsigned NOT NULL,
`ipbyte2` INT(3) unsigned NOT NULL,
`ipbyte3` INT(3) unsigned NOT NULL,
`ipbyte4` INT(3) unsigned NOT NULL,
`netmask` INT(3) unsigned NOT NULL,
`geoname_id` INT(10) unsigned NOT NULL,
`rgcgeoid` INT(10) unsigned NOT NULL,
`rpcgeoid` INT(10) unsigned NOT NULL,
`is_anon` TINYINT(1) unsigned NOT NULL,
`is_sat` TINYINT(1) unsigned NOT NULL,
`postal_code` VARCHAR(32) NOT NULL DEFAULT "",
`latitude` DECIMAL(8,4) NOT NULL DEFAULT "0.0",
`longitude` DECIMAL(8,4) NOT NULL DEFAULT "0.0",
`accuracy_radius` INT(6) unsigned NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_csv_city_blocks_ipv6`;
CREATE TABLE `geo_csv_city_blocks_ipv6` (
`network` VARCHAR(32) NOT NULL DEFAULT "",
`ipword1` VARCHAR(4) NOT NULL DEFAULT "",
`ipword2` VARCHAR(4) NOT NULL DEFAULT "",
`ipword3` VARCHAR(4) NOT NULL DEFAULT "",
`ipword4` VARCHAR(4) NOT NULL DEFAULT "",
`netmask` INT(3) unsigned NOT NULL,
`geoname_id` INT(10) unsigned NOT NULL,
`rgcgeoid` INT(10) unsigned NOT NULL,
`rpcgeoid` INT(10) unsigned NOT NULL,
`is_anon` TINYINT(1) unsigned NOT NULL,
`is_sat` TINYINT(1) unsigned NOT NULL,
`postal_code` VARCHAR(32) NOT NULL DEFAULT "",
`latitude` DECIMAL(8,4) NOT NULL DEFAULT "0.0",
`longitude` DECIMAL(8,4) NOT NULL DEFAULT "0.0",
`accuracy_radius` INT(6) unsigned NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_csv_country_blocks_ipv4`;
CREATE TABLE `geo_csv_country_blocks_ipv4` (
`network` VARCHAR(20) NOT NULL DEFAULT "",
`ipbyte1` INT(3) unsigned NOT NULL,
`ipbyte2` INT(3) unsigned NOT NULL,
`ipbyte3` INT(3) unsigned NOT NULL,
`ipbyte4` INT(3) unsigned NOT NULL,
`netmask` INT(3) unsigned NOT NULL,
`geoname_id` INT(10) unsigned NOT NULL,
`rgcgeoid` INT(10) unsigned NOT NULL,
`rpcgeoid` INT(10) unsigned NOT NULL,
`is_anon` TINYINT(1) unsigned NOT NULL,
`is_sat` TINYINT(1) unsigned NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_csv_country_blocks_ipv6`;
CREATE TABLE `geo_csv_country_blocks_ipv6` (
`network` VARCHAR(32) NOT NULL DEFAULT "",
`ipword1` VARCHAR(4) NOT NULL DEFAULT "",
`ipword2` VARCHAR(4) NOT NULL DEFAULT "",
`ipword3` VARCHAR(4) NOT NULL DEFAULT "",
`ipword4` VARCHAR(4) NOT NULL DEFAULT "",
`netmask` INT(3) unsigned NOT NULL,
`geoname_id` INT(10) unsigned NOT NULL,
`rgcgeoid` INT(10) unsigned NOT NULL,
`rpcgeoid` INT(10) unsigned NOT NULL,
`is_anon` TINYINT(1) unsigned NOT NULL,
`is_sat` TINYINT(1) unsigned NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_csv_asn_blocks_ipv4`;
CREATE TABLE `geo_csv_asn_blocks_ipv4` (
`network` VARCHAR(20) NOT NULL DEFAULT "",
`ipbyte1` INT(3) unsigned NOT NULL,
`ipbyte2` INT(3) unsigned NOT NULL,
`ipbyte3` INT(3) unsigned NOT NULL,
`ipbyte4` INT(3) unsigned NOT NULL,
`netmask` INT(3) unsigned NOT NULL,
`as_number` INT(10) unsigned NOT NULL,
`as_org` VARCHAR(256) NOT NULL DEFAULT ""
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_csv_asn_blocks_ipv6`;
CREATE TABLE `geo_csv_asn_blocks_ipv6` (
`network` VARCHAR(32) NOT NULL DEFAULT "",
`ipword1` VARCHAR(4) NOT NULL DEFAULT "",
`ipword2` VARCHAR(4) NOT NULL DEFAULT "",
`ipword3` VARCHAR(4) NOT NULL DEFAULT "",
`ipword4` VARCHAR(4) NOT NULL DEFAULT "",
`netmask` INT(3) unsigned NOT NULL,
`as_number` INT(10) unsigned NOT NULL,
`as_org` VARCHAR(256) NOT NULL DEFAULT ""
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_csv_country_locations`;
CREATE TABLE `geo_csv_country_locations` (
`geoname_id` INT(10) unsigned NOT NULL,
`locale_code` VARCHAR(4) NOT NULL DEFAULT "",
`cont_code` VARCHAR(4) NOT NULL DEFAULT "",
`cont_name` VARCHAR(32) NOT NULL DEFAULT "",
`cntr_iso` VARCHAR(4) NOT NULL DEFAULT "",
`cntr_name` VARCHAR(128) NOT NULL DEFAULT "",
`is_euro` TINYINT(1) unsigned NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_csv_city_locations`;
CREATE TABLE `geo_csv_city_locations` (
`geoname_id` INT(10) unsigned NOT NULL,
`locale_code` VARCHAR(4) NOT NULL DEFAULT "",
`cont_code` VARCHAR(4) NOT NULL DEFAULT "",
`cont_name` VARCHAR(20) NOT NULL DEFAULT "",
`cntr_iso` VARCHAR(4) NOT NULL DEFAULT "",
`cntr_name` VARCHAR(128) NOT NULL DEFAULT "",
`sub1_code` VARCHAR(4) NOT NULL DEFAULT "",
`sub1_name` VARCHAR(80) NOT NULL DEFAULT "",
`sub2_code` VARCHAR(4) NOT NULL DEFAULT "",
`sub2_name` VARCHAR(80) NOT NULL DEFAULT "",
`city_name` VARCHAR(128) NOT NULL DEFAULT "",
`metro_code` VARCHAR(8) NOT NULL DEFAULT "",
`time_zone` VARCHAR(80) NOT NULL DEFAULT "",
`is_euro` TINYINT(1) unsigned NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_txt_timezones`;
CREATE TABLE `geo_txt_timezones` (
`cntr_iso` VARCHAR(4) NOT NULL DEFAULT "",
`time_zone` VARCHAR(80) NOT NULL DEFAULT "",
`gmt_offset` DECIMAL(5,2) NOT NULL DEFAULT "0.0",
`dst_offset` DECIMAL(5,2) NOT NULL DEFAULT "0.0",
`raw_offset` DECIMAL(5,2) NOT NULL DEFAULT "0.0"
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
SELECT "Loading Data";
LOAD DATA LOCAL INFILE "/storage/geoip/data/GeoLite2-CSV/GeoLite2-City-Blocks-IPv4.csv"
INTO TABLE `geo_csv_city_blocks_ipv4` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/storage/geoip/data/GeoLite2-CSV/GeoLite2-City-Blocks-IPv6.csv"
INTO TABLE `geo_csv_city_blocks_ipv6` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/storage/geoip/data/GeoLite2-CSV/GeoLite2-Country-Blocks-IPv4.csv"
INTO TABLE `geo_csv_country_blocks_ipv4` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/storage/geoip/data/GeoLite2-CSV/GeoLite2-Country-Blocks-IPv6.csv"
INTO TABLE `geo_csv_country_blocks_ipv6` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/storage/geoip/data/GeoLite2-CSV/GeoLite2-City-Locations-en.csv"
INTO TABLE `geo_csv_city_locations` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/storage/geoip/data/GeoLite2-CSV/GeoLite2-Country-Locations-en.csv"
INTO TABLE `geo_csv_country_locations` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/storage/geoip/data/timeZones.txt"
INTO TABLE `geo_txt_timezones` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/storage/geoip/data/GeoLite2-CSV/GeoLite2-ASN-Blocks-IPv4.csv"
INTO TABLE `geo_csv_asn_blocks_ipv4` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/storage/geoip/data/GeoLite2-CSV/GeoLite2-ASN-Blocks-IPv6.csv"
INTO TABLE `geo_csv_asn_blocks_ipv6` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
SELECT "Create new tables";
DROP TABLE IF EXISTS `geo_city_blocks_ipv4`;
CREATE TABLE `geo_city_blocks_ipv4` (
`network` VARCHAR(32) NOT NULL DEFAULT "",
`ipbyte1` INT(3) unsigned NOT NULL,
`ipbyte2` INT(3) unsigned NOT NULL,
`ipbyte3` INT(3) unsigned NOT NULL,
`ipbyte4` INT(3) unsigned NOT NULL,
`netmask` INT(3) unsigned NOT NULL,
`ipstart` BIGINT(20) unsigned NOT NULL,
`ipend` BIGINT(20) unsigned NOT NULL,
`geoname_id` INT(10) unsigned NOT NULL,
`rgcgeoid` INT(10) unsigned NOT NULL,
`rpcgeoid` INT(10) unsigned NOT NULL,
`is_anon` TINYINT(1) unsigned NOT NULL,
`is_sat` TINYINT(1) unsigned NOT NULL,
`postal_code` VARCHAR(32) NOT NULL DEFAULT "",
`latitude` DECIMAL(8,4) NOT NULL DEFAULT "0.0",
`longitude` DECIMAL(8,4) NOT NULL DEFAULT "0.0",
`accuracy_radius` INT(6) unsigned NOT NULL,
PRIMARY KEY ( `network` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_city_blocks_ipv6`;
CREATE TABLE `geo_city_blocks_ipv6` (
`network` VARCHAR(32) NOT NULL DEFAULT "",
`ipword1` INT(5) unsigned NOT NULL,
`ipword2` INT(5) unsigned NOT NULL,
`ipword3` INT(5) unsigned NOT NULL,
`ipword4` INT(5) unsigned NOT NULL,
`netmask` INT(3) unsigned NOT NULL,
`ipstart` BIGINT(20) unsigned NOT NULL,
`ipend` BIGINT(20) unsigned NOT NULL,
`geoname_id` INT(10) unsigned NOT NULL,
`rgcgeoid` INT(10) unsigned NOT NULL,
`rpcgeoid` INT(10) unsigned NOT NULL,
`is_anon` TINYINT(1) unsigned NOT NULL,
`is_sat` TINYINT(1) unsigned NOT NULL,
`postal_code` VARCHAR(32) NOT NULL DEFAULT "",
`latitude` DECIMAL(8,4) NOT NULL DEFAULT "0.0",
`longitude` DECIMAL(8,4) NOT NULL DEFAULT "0.0",
`accuracy_radius` INT(6) unsigned NOT NULL,
PRIMARY KEY ( `network` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_country_blocks_ipv4`;
CREATE TABLE `geo_country_blocks_ipv4` (
`network` VARCHAR(20) NOT NULL DEFAULT "",
`ipbyte1` INT(3) unsigned NOT NULL,
`ipbyte2` INT(3) unsigned NOT NULL,
`ipbyte3` INT(3) unsigned NOT NULL,
`ipbyte4` INT(3) unsigned NOT NULL,
`netmask` INT(3) unsigned NOT NULL,
`ipstart` BIGINT(20) unsigned NOT NULL,
`ipend` BIGINT(20) unsigned NOT NULL,
`geoname_id` INT(10) unsigned NOT NULL,
`rgcgeoid` INT(10) unsigned NOT NULL,
`rpcgeoid` INT(10) unsigned NOT NULL,
`is_anon` TINYINT(1) unsigned NOT NULL,
`is_sat` TINYINT(1) unsigned NOT NULL,
PRIMARY KEY ( `network` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_country_blocks_ipv6`;
CREATE TABLE `geo_country_blocks_ipv6` (
`network` VARCHAR(32) NOT NULL DEFAULT "",
`ipword1` INT(5) unsigned NOT NULL,
`ipword2` INT(5) unsigned NOT NULL,
`ipword3` INT(5) unsigned NOT NULL,
`ipword4` INT(5) unsigned NOT NULL,
`netmask` INT(3) unsigned NOT NULL,
`geoname_id` INT(10) unsigned NOT NULL,
`rgcgeoid` INT(10) unsigned NOT NULL,
`rpcgeoid` INT(10) unsigned NOT NULL,
`is_anon` TINYINT(1) unsigned NOT NULL,
`is_sat` TINYINT(1) unsigned NOT NULL,
PRIMARY KEY ( `network` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_asn_blocks_ipv4`;
CREATE TABLE `geo_asn_blocks_ipv4` (
`network` VARCHAR(20) NOT NULL DEFAULT "",
`ipbyte1` INT(3) unsigned NOT NULL,
`ipbyte2` INT(3) unsigned NOT NULL,
`ipbyte3` INT(3) unsigned NOT NULL,
`ipbyte4` INT(3) unsigned NOT NULL,
`netmask` INT(3) unsigned NOT NULL,
`ipstart` BIGINT(20) unsigned NOT NULL,
`ipend` BIGINT(20) unsigned NOT NULL,
`as_number` INT(10) unsigned NOT NULL,
`as_org` VARCHAR(128) NOT NULL DEFAULT "",
PRIMARY KEY ( `network` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_asn_blocks_ipv6`;
CREATE TABLE `geo_asn_blocks_ipv6` (
`network` VARCHAR(32) NOT NULL DEFAULT "",
`ipword1` INT(5) unsigned NOT NULL,
`ipword2` INT(5) unsigned NOT NULL,
`ipword3` INT(5) unsigned NOT NULL,
`ipword4` INT(5) unsigned NOT NULL,
`netmask` INT(3) unsigned NOT NULL,
`ipstart` BIGINT(20) unsigned NOT NULL,
`ipend` BIGINT(20) unsigned NOT NULL,
`as_number` INT(10) unsigned NOT NULL,
`as_org` VARCHAR(128) NOT NULL DEFAULT "",
PRIMARY KEY ( `network` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_city_locations`;
CREATE TABLE `geo_city_locations` (
`geoname_id` INT(10) unsigned NOT NULL,
`locale_code` VARCHAR(4) NOT NULL DEFAULT "",
`cont_code` VARCHAR(4) NOT NULL DEFAULT "",
`cont_name` VARCHAR(20) NOT NULL DEFAULT "",
`cntr_iso` VARCHAR(4) NOT NULL DEFAULT "",
`cntr_name` VARCHAR(80) NOT NULL DEFAULT "",
`sub1_code` VARCHAR(4) NOT NULL DEFAULT "",
`sub1_name` VARCHAR(80) NOT NULL DEFAULT "",
`sub2_code` VARCHAR(4) NOT NULL DEFAULT "",
`sub2_name` VARCHAR(80) NOT NULL DEFAULT "",
`city_name` VARCHAR(80) NOT NULL DEFAULT "",
`metro_code` VARCHAR(8) NOT NULL DEFAULT "",
`time_zone` VARCHAR(80) NOT NULL DEFAULT "",
`is_euro` TINYINT(1) unsigned NOT NULL,
PRIMARY KEY ( `geoname_id` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_country_locations`;
CREATE TABLE `geo_country_locations` (
`geoname_id` INT(10) unsigned NOT NULL,
`locale_code` VARCHAR(4) NOT NULL DEFAULT "",
`cont_code` VARCHAR(4) NOT NULL DEFAULT "",
`cont_name` VARCHAR(32) NOT NULL DEFAULT "",
`cntr_iso` VARCHAR(4) NOT NULL DEFAULT "",
`cntr_name` VARCHAR(80) NOT NULL DEFAULT "",
`is_euro` TINYINT(1) unsigned NOT NULL,
PRIMARY KEY ( `geoname_id` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_timezones`;
CREATE TABLE `geo_timezones` (
`cntr_iso` VARCHAR(4) NOT NULL DEFAULT "",
`time_zone` VARCHAR(80) NOT NULL DEFAULT "",
`gmt_offset` DECIMAL(5,2) NOT NULL DEFAULT "0.0",
`dst_offset` DECIMAL(5,2) NOT NULL DEFAULT "0.0",
`raw_offset` DECIMAL(5,2) NOT NULL DEFAULT "0.0",
PRIMARY KEY ( `time_zone` ),
KEY `isokey` ( `cntr_iso` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
SELECT "Fill new tables";
-- Clean out any duplicates and create keys
INSERT IGNORE `geo_city_blocks_ipv4` ( network,ipbyte1,ipbyte2,ipbyte3,ipbyte4,netmask,ipstart,ipend,geoname_id,
rgcgeoid,rpcgeoid,is_anon,is_sat,postal_code,latitude,longitude,accuracy_radius )
SELECT network,ipbyte1,ipbyte2,ipbyte3,ipbyte4,netmask,@ipv:=(ipbyte1<<24)+(ipbyte2<<16)+(ipbyte3<<8)+ipbyte4,@ipv+(1<<(32-netmask))-1,
geoname_id,rgcgeoid,rpcgeoid,is_anon,is_sat,postal_code,latitude,longitude,accuracy_radius FROM `geo_csv_city_blocks_ipv4`;
INSERT IGNORE `geo_country_blocks_ipv4` ( network,ipbyte1,ipbyte2,ipbyte3,ipbyte4,netmask,ipstart,ipend,geoname_id,rgcgeoid,rpcgeoid,
is_anon,is_sat )
SELECT network,ipbyte1,ipbyte2,ipbyte3,ipbyte4,netmask,@ipv:=(ipbyte1<<24)+(ipbyte2<<16)+(ipbyte3<<8)+ipbyte4,@ipv+(1<<(32-netmask))-1,
geoname_id,rgcgeoid,rpcgeoid,is_anon,is_sat FROM `geo_csv_country_blocks_ipv4`;
INSERT IGNORE `geo_asn_blocks_ipv4` ( network,ipbyte1,ipbyte2,ipbyte3,ipbyte4,netmask,ipstart,ipend,as_number,as_org )
SELECT network,ipbyte1,ipbyte2,ipbyte3,ipbyte4,netmask,@ipv:=(ipbyte1<<24)+(ipbyte2<<16)+(ipbyte3<<8)+ipbyte4,@ipv+(1<<(32-netmask))-1,
as_number,as_org FROM `geo_csv_asn_blocks_ipv4`;
INSERT IGNORE `geo_city_blocks_ipv6` ( network,ipword1,ipword2,ipword3,ipword4,netmask,ipstart,ipend,geoname_id,rgcgeoid,rpcgeoid,
is_anon,is_sat,postal_code,latitude,longitude,accuracy_radius )
SELECT network,@ipv1:=conv(ipword1,16,10),@ipv2:=conv(ipword2,16,10),@ipv3:=conv(ipword3,16,10),@ipv4:=conv(ipword4,16,10),netmask,
@ipv:=(@ipv1<<48)+(@ipv2<<32)+(@ipv3<<16)+@ipv4,@ipv+(1<<(64-netmask))-1,
geoname_id,rgcgeoid,rpcgeoid,is_anon,is_sat,postal_code,latitude,longitude,accuracy_radius FROM `geo_csv_city_blocks_ipv6`;
INSERT IGNORE `geo_country_blocks_ipv6` (network,ipbyte1,ipbyte2,ipbyte3,ipbyte4,netmask,ipstart,ipend,geoname_id,rgcgeoid,rpcgeoid,
is_anon,is_sat )
SELECT network,@ipv1:=conv(ipword1,16,10),@ipv2:=conv(ipword2,16,10),@ipv3:=conv(ipword3,16,10),@ipv4:=conv(ipword4,16,10),netmask,
@ipv:=(@ipv1<<48)+(@ipv2<<32)+(@ipv3<<16)+@ipv4,@ipv+(1<<(64-netmask))-1,
geoname_id,rgcgeoid,rpcgeoid,is_anon,is_sat FROM `geo_csv_country_blocks_ipv6`;
INSERT IGNORE `geo_asn_blocks_ipv6` (
network,ipword1,ipword2,ipword3,ipword4,netmask,ipstart,ipend,as_number,as_org )
SELECT network,@ipv1:=conv(ipword1,16,10),@ipv2:=conv(ipword2,16,10),@ipv3:=conv(ipword3,16,10),@ipv4:=conv(ipword4,16,10),netmask,
@ipv:=(@ipv1<<48)+(@ipv2<<32)+(@ipv3<<16)+@ipv4,@ipv+(1<<(64-netmask))-1,
as_number,as_org FROM `geo_csv_asn_blocks_ipv6`;
INSERT IGNORE `geo_city_locations` SELECT * FROM `geo_csv_city_locations`;
INSERT IGNORE `geo_country_locations` SELECT * FROM `geo_csv_country_locations`;
SELECT "Add table keys";
ALTER TABLE `geo_city_blocks_ipv4`
ADD UNIQUE KEY `ipnumkey` ( `ipstart`,`ipend` ),
ADD KEY `geoidkey` ( `geoname_id` ),
ADD KEY `regidkey` ( `rgcgeoid` ),
ADD KEY `repidkey` ( `rpcgeoid` ),
ADD KEY `anonkey` ( `is_anon` ),
ADD KEY `satkey` ( `is_sat` ),
ADD KEY `latkey` ( `latitude` ),
ADD KEY `lonkey` ( `longitude` );
ALTER TABLE `geo_city_blocks_ipv6`
ADD UNIQUE KEY `ipnumkey` ( `ipstart`,`ipend` ),
ADD KEY `geoidkey` ( `geoname_id` ),
ADD KEY `regidkey` ( `rgcgeoid` ),
ADD KEY `repidkey` ( `rpcgeoid` ),
ADD KEY `anonkey` ( `is_anon` ),
ADD KEY `satkey` ( `is_sat` ),
ADD KEY `latkey` ( `latitude` ),
ADD KEY `lonkey` ( `longitude` );
ALTER TABLE `geo_country_blocks_ipv4`
ADD UNIQUE KEY `ipnumkey` ( `ipstart`,`ipend` ),
ADD KEY `geoidkey` ( `geoname_id` ),
ADD KEY `regidkey` ( `rgcgeoid` ),
ADD KEY `repidkey` ( `rpcgeoid` ),
ADD KEY `anonkey` ( `is_anon` ),
ADD KEY `satkey` ( `is_sat` );
ALTER TABLE `geo_country_blocks_ipv6`
ADD UNIQUE KEY `ipnumkey` ( `ipstart`,`ipend` ),
ADD KEY `geoidkey` ( `geoname_id` ),
ADD KEY `regidkey` ( `rgcgeoid` ),
ADD KEY `repidkey` ( `rpcgeoid` ),
ADD KEY `anonkey` ( `is_anon` ),
ADD KEY `satkey` ( `is_sat` );
ALTER TABLE `geo_asn_blocks_ipv4`
ADD KEY `asnkey` ( `as_number` );
ALTER TABLE `geo_asn_blocks_ipv6`
ADD KEY `asnkey` ( `as_number` );
ALTER TABLE `geo_city_locations`
ADD KEY `lockey` ( `locale_code` ),
ADD KEY `contkey` ( `cont_code` ),
ADD KEY `cisokey` ( `cntr_iso` ),
ADD KEY `cnamkey` ( `cont_name`,`cntr_name`,`city_name` ),
ADD KEY `rnamkey` ( `city_name`,`cntr_name`,`cont_name` ),
ADD KEY `eurokey` ( `is_euro` );
ALTER TABLE `geo_country_locations`
ADD KEY `lockey` ( `locale_code` ),
ADD KEY `contkey` ( `cont_code` ),
ADD KEY `cisokey` ( `cntr_iso` ),
ADD KEY `eurokey` ( `is_euro` ),
ADD KEY `cnamkey` ( `cont_name`,`cntr_name` );
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment