Ipv6 creating table load data and retriving in mysql using ip_poly

Question

CREATE TABLE `ipv6_table` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ip_poly` POLYGON NOT NULL,
  `start_network` BIGINT(20) NOT NULL DEFAULT '0',
  `end_network` BIGINT(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  SPATIAL KEY `idx_ip_poly` (`ip_poly`)
) ENGINE=Innodb; 

LOAD DATA LOCAL INFILE '/var/lib/mysql-files/19225.csv'
INTO TABLE ipv6_table
FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
(
  @start_network,
  @end_network )
SET
  id = NULL,
    ip_poly = ST_GeomFromText(CONCAT(
      'POLYGON((',
      @start_network - 1, ' -1, ',
      @end_network + 1, ' -1, ',
      @end_network + 1, ' 1, ',
      @start_network - 1, ' 1, ',
      @start_network - 1, ' -1))'
    )),
  start_network = @start_network,
  end_network = @end_network ;  

SELECT * FROM ipv6_table WHERE ST_Intersects(ip_poly, ST_GEOMFROMTEXT(concat('POINT(', 2306128953120655672, ' 0)')));

I have raw data in csv file like this

2306128950956392448,2306128951224827903
2306128951224827904,2306128951241605119
2306128951241605120,2306128951493263359
2306128951493263360,2306128951510040575
2306128951510040576,2306128952030134271
2306128952030134272,2306128952046911487

Like this I'm trying to create table for Ipv6 data. and I'm loading data from csv file into it and I'm trying to fetch ipv6 data using start_network or end_network or range between them but it is returning multiple data. can anyone help me in this.

score 0 · Answer 1 · answered Feb 27 '25 at 05:40

You use BIGINT as IPv6 address. When the polygon is created from your data then this BIGINT value which contains 19 significant digits is converted to NUMERIC one which is not accurate enough. So you have excess output rows.

Create a table

CREATE TABLE `ipv6_table` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ip_poly` POLYGON NOT NULL,
  `start_network` BIGINT(20) NOT NULL DEFAULT '0',
  `end_network` BIGINT(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  SPATIAL KEY `idx_ip_poly` (`ip_poly`)
) ENGINE=Innodb;

Fill it with provided data

CREATE TRIGGER set_poly_on_insert
BEFORE INSERT ON ipv6_table
FOR EACH ROW
SET NEW.ip_poly = ST_GeomFromText(CONCAT(
      'POLYGON((',
      NEW.start_network - 1, ' -1, ',
      NEW.end_network + 1, ' -1, ',
      NEW.end_network + 1, ' 1, ',
      NEW.start_network - 1, ' 1, ',
      NEW.start_network - 1, ' -1))'
    ));
INSERT INTO ipv6_table (start_network, end_network) VALUES
(2306128950956392448,2306128951224827903),
(2306128951224827904,2306128951241605119),
(2306128951241605120,2306128951493263359),
(2306128951493263360,2306128951510040575),
(2306128951510040576,2306128952030134271),
(2306128952030134272,2306128952046911487);
SELECT id, ST_AsText(ip_poly), start_network, end_network
FROM ipv6_table;

Check inserted data

id	ST_AsText(ip_poly)	start_network	end_network
1	POLYGON((2.3061289509563924e18 -1,2.306128951224828e18 -1,2.306128951224828e18 1,2.3061289509563924e18 1,2.3061289509563924e18 -1))	2306128950956392448	2306128951224827903
2	POLYGON((2.306128951224828e18 -1,2.306128951241605e18 -1,2.306128951241605e18 1,2.306128951224828e18 1,2.306128951224828e18 -1))	2306128951224827904	2306128951241605119
3	POLYGON((2.306128951241605e18 -1,2.3061289514932634e18 -1,2.3061289514932634e18 1,2.306128951241605e18 1,2.306128951241605e18 -1))	2306128951241605120	2306128951493263359
4	POLYGON((2.3061289514932634e18 -1,2.3061289515100406e18 -1,2.3061289515100406e18 1,2.3061289514932634e18 1,2.3061289514932634e18 -1))	2306128951493263360	2306128951510040575
5	POLYGON((2.3061289515100406e18 -1,2.3061289520301343e18 -1,2.3061289520301343e18 1,2.3061289515100406e18 1,2.3061289515100406e18 -1))	2306128951510040576	2306128952030134271
6	POLYGON((2.3061289520301343e18 -1,2.3061289520469115e18 -1,2.3061289520469115e18 1,2.3061289520301343e18 1,2.3061289520301343e18 -1))	2306128952030134272	2306128952046911487

Select the subnet which the address 2306128951241605190 belongs to, only subnet #3 must be returned

SELECT id, start_network, end_network, ST_AsText(ip_poly), ST_AsText(criteria.ipv6_address)
FROM ipv6_table 
CROSS JOIN (SELECT ST_GEOMFROMTEXT(concat('POINT(', 2306128951241605190, ' 0)')) AS ipv6_address) AS criteria
WHERE ST_Intersects(ip_poly, criteria.ipv6_address);

Check the output. Excess row #2 is returned. Check for the geometry addresses which are "rounded" after the datatype convertion.

id	start_network	end_network	ST_AsText(ip_poly)	ST_AsText(criteria.ipv6_address)
2	2306128951224827904	2306128951241605119	POLYGON((2.306128951224828e18 -1,2.306128951241605e18 -1,2.306128951241605e18 1,2.306128951224828e18 1,2.306128951224828e18 -1))	POINT(2.306128951241605e18 0)
3	2306128951241605120	2306128951493263359	POLYGON((2.306128951241605e18 -1,2.3061289514932634e18 -1,2.3061289514932634e18 1,2.306128951241605e18 1,2.306128951241605e18 -1))	POINT(2.306128951241605e18 0)

fiddle

Ipv6 creating table load data and retriving in mysql using ip_poly

1 Answers1