iris.sql

CREATE DATABASE IF NOT EXISTS iris_db;

USE iris_db;

CREATE TABLE IF NOT EXISTS iris (
     vector BLOB,
     species VARCHAR(20)
);

INSERT INTO iris VALUES
(JSON_ARRAY_PACK('[5.1,3.5,1.4,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.9,3,1.4,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.7,3.2,1.3,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.6,3.1,1.5,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5,3.6,1.4,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.4,3.9,1.7,0.4]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.6,3.4,1.4,0.3]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5,3.4,1.5,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.4,2.9,1.4,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.9,3.1,1.5,0.1]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.4,3.7,1.5,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.8,3.4,1.6,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.8,3,1.4,0.1]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.3,3,1.1,0.1]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.8,4,1.2,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.7,4.4,1.5,0.4]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.4,3.9,1.3,0.4]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.1,3.5,1.4,0.3]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.7,3.8,1.7,0.3]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.1,3.8,1.5,0.3]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.4,3.4,1.7,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.1,3.7,1.5,0.4]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.6,3.6,1,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.1,3.3,1.7,0.5]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.8,3.4,1.9,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5,3,1.6,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5,3.4,1.6,0.4]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.2,3.5,1.5,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.2,3.4,1.4,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.7,3.2,1.6,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.8,3.1,1.6,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.4,3.4,1.5,0.4]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.2,4.1,1.5,0.1]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.5,4.2,1.4,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.9,3.1,1.5,0.1]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5,3.2,1.2,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.5,3.5,1.3,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.9,3.1,1.5,0.1]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.4,3,1.3,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.1,3.4,1.5,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5,3.5,1.3,0.3]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.5,2.3,1.3,0.3]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.4,3.2,1.3,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5,3.5,1.6,0.6]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.1,3.8,1.9,0.4]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.8,3,1.4,0.3]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.1,3.8,1.6,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.6,3.2,1.4,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5.3,3.7,1.5,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5,3.3,1.4,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[7,3.2,4.7,1.4]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6.4,3.2,4.5,1.5]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6.9,3.1,4.9,1.5]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.5,2.3,4,1.3]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6.5,2.8,4.6,1.5]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.7,2.8,4.5,1.3]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6.3,3.3,4.7,1.6]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[4.9,2.4,3.3,1]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6.6,2.9,4.6,1.3]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.2,2.7,3.9,1.4]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5,2,3.5,1]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.9,3,4.2,1.5]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6,2.2,4,1]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6.1,2.9,4.7,1.4]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.6,2.9,3.6,1.3]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6.7,3.1,4.4,1.4]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.6,3,4.5,1.5]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.8,2.7,4.1,1]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6.2,2.2,4.5,1.5]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.6,2.5,3.9,1.1]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.9,3.2,4.8,1.8]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6.1,2.8,4,1.3]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6.3,2.5,4.9,1.5]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6.1,2.8,4.7,1.2]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6.4,2.9,4.3,1.3]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6.6,3,4.4,1.4]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6.8,2.8,4.8,1.4]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6.7,3,5,1.7]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6,2.9,4.5,1.5]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.7,2.6,3.5,1]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.5,2.4,3.8,1.1]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.5,2.4,3.7,1]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.8,2.7,3.9,1.2]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6,2.7,5.1,1.6]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.4,3,4.5,1.5]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6,3.4,4.5,1.6]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6.7,3.1,4.7,1.5]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6.3,2.3,4.4,1.3]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.6,3,4.1,1.3]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.5,2.5,4,1.3]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.5,2.6,4.4,1.2]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6.1,3,4.6,1.4]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.8,2.6,4,1.2]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5,2.3,3.3,1]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.6,2.7,4.2,1.3]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.7,3,4.2,1.2]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.7,2.9,4.2,1.3]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6.2,2.9,4.3,1.3]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.1,2.5,3,1.1]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[5.7,2.8,4.1,1.3]'),'Iris-versicolor'),
(JSON_ARRAY_PACK('[6.3,3.3,6,2.5]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[5.8,2.7,5.1,1.9]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[7.1,3,5.9,2.1]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.3,2.9,5.6,1.8]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.5,3,5.8,2.2]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[7.6,3,6.6,2.1]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[4.9,2.5,4.5,1.7]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[7.3,2.9,6.3,1.8]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.7,2.5,5.8,1.8]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[7.2,3.6,6.1,2.5]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.5,3.2,5.1,2]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.4,2.7,5.3,1.9]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.8,3,5.5,2.1]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[5.7,2.5,5,2]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[5.8,2.8,5.1,2.4]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.4,3.2,5.3,2.3]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.5,3,5.5,1.8]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[7.7,3.8,6.7,2.2]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[7.7,2.6,6.9,2.3]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6,2.2,5,1.5]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.9,3.2,5.7,2.3]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[5.6,2.8,4.9,2]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[7.7,2.8,6.7,2]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.3,2.7,4.9,1.8]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.7,3.3,5.7,2.1]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[7.2,3.2,6,1.8]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.2,2.8,4.8,1.8]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.1,3,4.9,1.8]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.4,2.8,5.6,2.1]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[7.2,3,5.8,1.6]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[7.4,2.8,6.1,1.9]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[7.9,3.8,6.4,2]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.4,2.8,5.6,2.2]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.3,2.8,5.1,1.5]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.1,2.6,5.6,1.4]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[7.7,3,6.1,2.3]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.3,3.4,5.6,2.4]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.4,3.1,5.5,1.8]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6,3,4.8,1.8]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.9,3.1,5.4,2.1]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.7,3.1,5.6,2.4]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.9,3.1,5.1,2.3]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[5.8,2.7,5.1,1.9]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.8,3.2,5.9,2.3]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.7,3.3,5.7,2.5]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.7,3,5.2,2.3]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.3,2.5,5,1.9]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.5,3,5.2,2]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.2,3.4,5.4,2.3]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[5.9,3,5.1,1.8]'),'Iris-virginica');

SELECT species
FROM iris
WHERE EUCLIDEAN_DISTANCE(vector, JSON_ARRAY_PACK('[5.9,3,5.1,1.8]')) = 0;

SELECT EUCLIDEAN_DISTANCE(vector, JSON_ARRAY_PACK('[5.9,3,5.1,1.8]')) AS euclidean_distance, species
FROM iris
ORDER BY euclidean_distance
LIMIT 5;

SELECT EUCLIDEAN_DISTANCE(vector, JSON_ARRAY_PACK('[5.2,3.6,1.5,0.3]')) AS euclidean_distance, species
FROM iris
ORDER BY euclidean_distance
LIMIT 5;

SELECT species
FROM iris
ORDER BY EUCLIDEAN_DISTANCE(vector, JSON_ARRAY_PACK('[5.2,3.6,1.5,0.3]'))
LIMIT 1;