0

I have an exe which return an array of 16elements.I have to pass this array to Mysql using php to calculate the Euclidean distance.My table in MySQL is in the form.

id |img_id | features_1|features_2|features_3|features_4|features_5|features_6|features_7|...upto features_16
 1   1        0.389       0.4567     0.8981      0.2345
 2   2        0.9878      0.4567     0.56122     0.4532        
 3   3        
 4   4        
 ......................

So I have 16 features for each image and now I have 30,000 images that is img_id is upto 30,000. I have to calculate the euclidean distance of the array from exe which is passed through php with the datas in the database and return the img_id of the 6 images whose euclidean distance is minimum.

i.e. Suppose I have an array from exe A[0.458,0.234,0.4567,0.2398] I have to compute distance of each img_id with this array i.e for img_id=1 the distance will be ((0.458-0.389)^2+(0.234-0.4567)^2+(0.4567-0.8981)^2+(0.2398-0.2345)^2) and I have to repeat this process for all the 30,000 images and return the 6 img_id who has least distance. I wrote the following stored proc but it only returns img_id NULL.

if there is any fast way to perform this please mention it.

create procedure sp_euclideandistance(feature1 double,feature2 double,feature3 double,feature4 double,feature5 double,feature6 double,feature7 double,feature8 double,feature9 double,feature10 double,feature11 double,feature12 double,feature13 double,feature14 double,feature15 double,feature16 double)
 Begin
  Declare i int;
  Declare distance double;
  Declare img_id int;
  Declare features_1 double;
  Declare features_2 double;
  Declare features_3 double;
  Declare features_4 double;
  Declare features_5 double;
  Declare features_6 double;
  Declare features_7 double;
  Declare features_8 double;
  Declare features_9 double;
  Declare features_10 double;
  Declare features_11 double;
  Declare features_12 double;
  Declare features_13 double;
  Declare features_14 double;
  Declare features_15 double;
  Declare features_16 double;
  Set i=92303;
  Set distance=0.000;

  create temporary table temptbl_dist(img_id int,distance int);

  while(i<124232)
  do

    select img_id,features_1,features_2,features_3,features_4,features_5,features_6,features_7,features_8,features_9,features_10,features_11,features_12,features_13,features_14,features_15,features_16 INTO img_id,features_1,features_2,features_3,features_4,features_5,features_6,features_7,features_8,features_9,features_10,features_11,features_12,features_13,features_14,features_15,features_16  from tbl_features where img_id=i;

    Set distance=((feature1-features_1)^2)+((feature2-features_2)^2)+((feature3-features_3)^2)+((feature4-features_4)^2)+((feature5-features_5)^2)+((feature6-features_6)^2)+((feature7-features_7)^2)+((feature8-features_8)^2)+((feature9-features_9)^2)+((feature10-features_10)^2)+((feature11-features_11)^2)+((feature12-features_12)^2)+((feature13-features_13)^2)+((feature14-features_14)^2)+((feature15-features_15)^2)+((feature16-features_16)^2);

    insert into temptbl_dist values (i,distance);

    Set i=i+1;


  end while;
  select img_id from temptbl_dist order by distance asc limit 6;
  drop table temptbl_dist;

End
Passerby
  • 9,715
  • 2
  • 33
  • 50
user1583647
  • 1,227
  • 2
  • 24
  • 48
  • Any particular reason for the `php` tag? – Mark Baker Apr 15 '13 at 06:39
  • Yes if this same process can be done in php will it be faster? – user1583647 Apr 15 '13 at 06:43
  • I doubt if PHP can be faster - you still need to compute and sort, and sorting data is one of the main job for database softwares. – Passerby Apr 15 '13 at 07:26
  • I'm not familiar with MySQL, but can you reduce your procedure to simply `SELECT img_id FROM (SELECT img_id,((feature1-feature_1^2)+...) "distance" FROM feature_tbl WHERE img_id>=92303 AND img_id<124232) ORDER BY distance ASC LIMIT 6`? – Passerby Apr 15 '13 at 07:34
  • I dont think so we can do this. – user1583647 Apr 15 '13 at 07:50
  • SQL Error (1690): BIGINT UNSIGNED value is out of range in '(((((((((((((0.44666 - `fashion`.`tbl_features`.`features_1`) ^ 2) + ((0.89222 - `fashion`.`tbl_features`.`features_2`) ^ 2)) + ((0.44443 - `fashion`.`tbl_features`.`features_3`) ^ 2)) + ((0.9812 - `fashion`.`tbl_features`.`features_4`) ^ 2)) + ((0.0012 - `fashion`.`tbl_features`.`features_5`) ^ 2)) + ((0.1234 - `fashion`.`tbl_features`.`features_6`) ^ 2)) + ((0.3592 - `fashion`.`tbl_features`.`features_7`) ^ 2)) + ((0.9977 - `fashion`.`tbl_features`.`features_8`) */ – user1583647 Apr 15 '13 at 08:09

0 Answers0