[SOLVED] Solving for points within an sphere centered on a user defined point?

[mrln68]

I'm not quite sure how best to approach this beast.

 

I have a number of colors which are predefined (they are physical paints colors) and I want to be able to allow the user to enter an RGB color and find the paint colors which match most closely to that color - or alternately choose a paint color from one manufacturer and see similiar colors from other manufacturers.

 

To this end, I need to convert the RGB data to the Lab color space format (easy enough...simple math).  The Lab color space is a representation of a 3 dimensional grid where all the colors can be plotted.  If they choose an RGB color like #FEEA00, the color space location ends up being (91.6, -11.8, 90.46) - effectively XYZ in a 3D grid.  Now I want to return every color which is within 2 from that point (a sphere with a radius of 2).

 

I could run each color through a test to determine if it is within the area of interest...possibly using an additional function to automatically disqualify anything which is obviously out of range in order to prevent the full formula from being applied.  However with the current number of data points being around 450 and the final number to rise well above 2000 that seems like a lot of SQL calls which wouldn't yield much.

 

The other option which I had been toying with is to use a local program to predetermine which colors are comparable and store that in the db with the colors themselves.  However this would then limit users to only selecting existing colors, and it would require much larger changes to the db when a single datapoint is added/changed.

 

The colors are stored in a MySQL db, and the server currently has PHP 4.4.4...not sure if any new toys have been added in PHP 5 that would help this though...but I figured I would make that be known.

 

Are there any other options?  Anyone have a trick which might make the realtime calculations more effecient?

[calabiyau]

Are each of the X,Y,Z color space locations stored in a separate column in each row?

[calabiyau]

Cuz if they are there is a mysql SELECT QUERY that runs like this

 

SELECT * FROM table WHERE field BETWEEN $low AND $high

 

I'm sure you could build a query that would select only rows from the table that are +-2 from the x, y, and z.

[GingerRobot]

If i understood correctly, you should just be able to appply pythagoras in 3 dimensions:

 

<?php
$sql = "SELECT SQRT(POW(`x`-$x),2+POW(`y`-$y),2+POW(`z`-$z),2) as distance WHERE distance <= 2";
?>

 

 

Thats's given an x,y and z co-ordinate thats in the database, and a corresponding variable.

[mrln68]

Figures...

 

I need to take a closer look at what I can do with the actual SQL querry as opposed to doing it on the PHP side.  Right now the XYZ (or L.a.b. as the case may be) data is derived from the RGB data after it is called from the database, but it is easy enough to precalculate those values and store them in the database.  I'll need to double check to make sure that everything will work though.

[mrln68]

SELECT * 
FROM `color_data` 
WHERE SQRT(POW(`color_L`-25,2+POW(`color_a`-10,2)+POW(`color_b`-7,2)) <= 4

 

I took a stab at getting something working, but for some reason I am not getting any results (actually an SQL error...though according to what I can determine everything is correct).  I tried that querry both through PHP and directly as an SQL call through phpMyAdmin.  Any idea what might be foul?

[akitchin]

you're missing a closing parenthesis on the color_L square function.  by the way, that will grab anything within a radius 4, not 2 (as you're squaring the left side of the sphere equation).

[mrln68]

Thanks, for the obvious...Initially I was going to go with 2...but when I was double checking the actual math versus the colors...4 seemed to be the better option - once I get it up and running though, I may end up having the color range be user selectable though.