Untitled

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` );