Matching Hex Values with SQLite Query like JavaScript

Question

I'm using a javascript that names an approximate/exact color depending on the hex value that it is given. This works really well, and I'm trying to create a similar thing except using a SQLite Query.

The problem is that the result from the SQLite query is different than what I get with the javascript. I don't have enough javascript experience to know what other matching takes place to get the right approximate color.

For example I have the colors:

["000000", "Black"],
["000080", "Navy Blue"],
["0000C8", "Dark Blue"],
["0000FF", "Blue"],
["000741", "Stratos"],
["011635", "Midnight"],
["011D13", "Holly"],
["0FFFFF", "Aqua Blue"],
["1E1609", "Karaka"],
["1E1708", "El Paso"],
["1E385B", "Cello"],
["1E433C", "Te Papa Green"],
["1E90FF", "Dodger Blue"],
["27504B", "Plantation"],
["FFFFF0", "Ivory"],
["FFFFFF", "White"]

Using #09F7FF as my input I use this query:

SELECT * FROM colors WHERE "Hex" >= '#0F97FF' ORDER BY "ROWID" COLLATE NOCASE ASC LIMIT 1

The color returned is supposed to be #1E90FF, Dodger Blue like the javascript does, but I get #1E1609 Karaka instead, which isn't correct. Would anyone be able to explain what I need to do in order to get the same result as the javascript?

var ntc = {

  init: function() {
    var color, rgb, hsl;
    for(var i = 0; i < ntc.names.length; i++)
    {
      color = "#" + ntc.names[i][0];
      rgb = ntc.rgb(color);
      hsl = ntc.hsl(color);
      ntc.names[i].push(rgb[0], rgb[1], rgb[2], hsl[0], hsl[1], hsl[2]);
    }
  },

  name: function(color) {

    color = color.toUpperCase();
    if(color.length < 3 || color.length > 7)
      return ["#000000", "Invalid Color: " + color, false];
    if(color.length % 3 == 0)
      color = "#" + color;
    if(color.length == 4)
      color = "#" + color.substr(1, 1) + color.substr(1, 1) + color.substr(2, 1) + color.substr(2, 1) + color.substr(3, 1) + color.substr(3, 1);

    var rgb = ntc.rgb(color);
    var r = rgb[0], g = rgb[1], b = rgb[2];
    var hsl = ntc.hsl(color);
    var h = hsl[0], s = hsl[1], l = hsl[2];
    var ndf1 = 0; ndf2 = 0; ndf = 0;
    var cl = -1, df = -1;

    for(var i = 0; i < ntc.names.length; i++)
    {
      if(color == "#" + ntc.names[i][0])
        return ["#" + ntc.names[i][0], ntc.names[i][1], true];

      ndf1 = Math.pow(r - ntc.names[i][2], 2) + Math.pow(g - ntc.names[i][3], 2) + Math.pow(b - ntc.names[i][4], 2);
      ndf2 = Math.pow(h - ntc.names[i][5], 2) + Math.pow(s - ntc.names[i][6], 2) + Math.pow(l - ntc.names[i][7], 2);
      ndf = ndf1 + ndf2 * 2;
      if(df < 0 || df > ndf)
      {
        df = ndf;
        cl = i;
      }
    }

    return (cl < 0 ? ["#000000", "Invalid Color: " + color, false] : ["#" + ntc.names[cl][0], ntc.names[cl][1], false]);
  },

  // adopted from: Farbtastic 1.2
  // http://acko.net/dev/farbtastic
  hsl: function (color) {

    var rgb = [parseInt('0x' + color.substring(1, 3)) / 255, parseInt('0x' + color.substring(3, 5)) / 255, parseInt('0x' + color.substring(5, 7)) / 255];
    var min, max, delta, h, s, l;
    var r = rgb[0], g = rgb[1], b = rgb[2];

    min = Math.min(r, Math.min(g, b));
    max = Math.max(r, Math.max(g, b));
    delta = max - min;
    l = (min + max) / 2;

    s = 0;
    if(l > 0 && l < 1)
      s = delta / (l < 0.5 ? (2 * l) : (2 - 2 * l));

    h = 0;
    if(delta > 0)
    {
      if (max == r && max != g) h += (g - b) / delta;
      if (max == g && max != b) h += (2 + (b - r) / delta);
      if (max == b && max != r) h += (4 + (r - g) / delta);
      h /= 6;
    }
    return [parseInt(h * 255), parseInt(s * 255), parseInt(l * 255)];
  },

  // adopted from: Farbtastic 1.2
  // http://acko.net/dev/farbtastic
  rgb: function(color) {
    return [parseInt('0x' + color.substring(1, 3)), parseInt('0x' + color.substring(3, 5)),  parseInt('0x' + color.substring(5, 7))];
  },

  names: [
["000000", "Black"],
["000080", "Navy Blue"],
["0000C8", "Dark Blue"],
["0000FF", "Blue"],
["000741", "Stratos"],
["011635", "Midnight"],
["011D13", "Holly"],
["0FFFFF", "Aqua Blue"],
["1E1609", "Karaka"],
["1E1708", "El Paso"],
["1E385B", "Cello"],
["1E433C", "Te Papa Green"],
["1E90FF", "Dodger Blue"],
["27504B", "Plantation"],
["FFFFF0", "Ivory"],
["FFFFFF", "White"]
]

}

ntc.init();

var n_match  = ntc.name("#0F97FF");
n_rgb        = n_match[0]; // RGB value of closest match
n_name       = n_match[1]; // Text string: Color name
n_exactmatch = n_match[2]; // True if exact color match

alert(n_match);

The javascript comes from: http://chir.ag/projects/name-that-color/#7996C8

My SQLite code can be found here: http://sqlfiddle.com/#!5/9eb07/21

your script compares numerical Hex values, `1E1609` is the next color in your list with a number greater than `0F97FF` so that's what it returns. Dodger Blue is `1E90FF`. Your script compares the Red value first, then if they are equal it compares the Green, then if equal checks the Blue. Maybe you want to use `splice` to split the hex value into three chunks - one per color? — Mousey, Sep 20 '15 at 22:59
@Mousey: Your explanation makes some sense, which is really what I was trying to figure out, but how is it determining the approximate value? The only value that actually matches is the last one `FF` so how does it decide on the the `1E` when my color input is `0F` to start with? And do you mean `splice` with sqlite?, the javascript won't be used, but more likely `C` code or something like that. Sorry for all the questions. — Yoshi Miro, Sep 20 '15 at 23:07
it's doing a mathematical comparison of Hex values, convert each Hex value into decimal and you will be able to follow it more easily, but basically you gave the color codes in ascending order of numerical value. It does not decide on '1E', it looks for anything else beginning `0F` and finds nothing, then goes to `10, `11` etc to `19` then `1A`, `1B`, `1C` etc. Letters A-F represent 11, 12, 13, 14, 15 in the space of a single character. — Mousey, Sep 20 '15 at 23:33
Hex = hexidecimal (counting in 16s), a slightly different method of counting compared to decimal (counts in 10s) and binary (counts in 2s). As the old joke goes, there are `10` types of people in this world: those that understand binary and those that don't. (Binary of `10` means the number 2). — Mousey, Sep 20 '15 at 23:37
splice is javascript, [SUBSTR](http://sqlite.awardspace.info/syntax/sqlitepg08.htm) would let you get a substring from the hex code within sqlite — Mousey, Sep 20 '15 at 23:48
@Mousey: Thanks for the info, I'll see what I can make of all that. At some point In my sqlite I tried using the integer of the hex value and still couldn't get it to match (but I was just using the entire value). So I'll try and wrap my head around slicing and dicing as you've hinted to. — Yoshi Miro, Sep 20 '15 at 23:57
@Mousey: The one thing that confuses me though is if I added the color `["0FFFFF", "Aqua Blue"],` to the list (which is closer to terms of integer value, but not color-wise the javascript still gets it right, returning `["1E90FF", "Dodger Blue"]`... I updated my question to include it. — Yoshi Miro, Sep 21 '15 at 00:08
if you include every possible hexidecimal color value in your database you won't need to do anything other than a direct match, there's probably a file of them online somewhere. The 256 websafe colors might be a good place to start. You could actually alter your database structure to represent color in 3 fields: Red, Blue, Green. Split the hex value into 3 before storing, and combine all 3 to get the color name (use a composite primary key - so the combination of the 3 colors must be unique). — Mousey, Sep 21 '15 at 00:11
@Mousey: I assume maybe it's summing the total of the three values (r, g, b) at the end? The difference in `0F97FF` in total value (splitting the r, g, b apart) is `-7` compared to `1E90FF`, vs. `+104` with `0FFFFF`. Re: including every single hex value... wouldn't the db be enormous? — Yoshi Miro, Sep 21 '15 at 00:14
re: Aqua Blue - I didn't notice that before. Could it be down to you order by `ROWID` and not by the value of `HEX`? If you added it after Karaka and are getting rowid automatically generated then that would explain it. Ideally `HEX` should be a Primary Key/Key Field since it has a unique value for each record (each row) — Mousey, Sep 21 '15 at 00:17
It didn't make any difference when I switched out `ROWID` with "Hex", same result. I think the javascript is likely doing what you said, taking each `r, g, b` subtracting them individually from the closest value in the list, and then summing the total to match it. — Yoshi Miro, Sep 21 '15 at 00:20

score 0 · Accepted Answer · edited May 23 '17 at 11:51

You are using TEXT as a data type for a Hexidecimal value, but the correct hex data format should begin 0x in sqlite, not # and:

"String variables that contain text formatted like hexadecimal integers are not interpreted as hexadecimal integers when coercing the string value into an integer due to a CAST expression or for a column affinity transformation or prior to performing a numeric operation or for any other run-time conversions. source

Editing your sqlite example to return more rows showed the leading 0 characters from the HEX field were ignored (and not displayed) - so Aqua Blue #0FFFFF was displayed as #FFFFF which probably isn't what you would want. Karaka was the first result found when sorting by ROWID in ascending order.

Given that your database includes considerable redundant data, you can easily use the Int field to do the comparison instead, by comparing the decimal values of the colors.

Change your query to work from the decimal value of the color code, and sort by decimal value (not hexidecimal). I've converted the hex value you gave in your example to decimal manually for this query. This solution will avoid the need to convert your HEX field into the x' format needed by sqlite-

SELECT * FROM colors WHERE "Int" >= '1021951' ORDER BY "Int" COLLATE NOCASE ASC LIMIT

If you do alter your data for sqlite the comparison should work - as long as you sort by the value of the color (HEX or Int) not the ROWID.

This answer about the hex() function and it's opposite may be helpful.

You really should consider normalizing your database since this will reduce the chance of errors considerably. The HEX field can easily be displayed by concatenating the R, G and B fields for instance. Essentially you have 5 fields which can be represented as a single field.

Thanks for the detailed answer; I switched the sqlite to use like in your answer but I still get the same result `#1E1609 Karaka`, am I doing something wrong? http://sqlfiddle.com/#!5/9eb07/43 — Yoshi Miro, Sep 21 '15 at 01:27
Since you helped me figure out how the result is ultimately determined I'll go ahead an mark the answer as correct. I'm still not sure why the sqllite query won't return the right result based off making the changes shown in your answer. If you have any ideas at some point that's great, otherwise thanks again. — Yoshi Miro, Sep 21 '15 at 04:32
The sqlite link didn't have Aqua Blue in it, so the correct result was Karaka. What result do you want? It can't return Dodger Blue if you are weighting of red more highly than green (and blue). The next highest shade of blue can be found comparing the value entered with column B (Blue) only- using SUBSTR to compare the 2 chars for blue only- but you would have to know that it was a shade of blue which was the closest match. A 4th generation database language (like sqlite/sql) isn't a direct replacement for a 3rd general language (like javascript or C) because they work differently — Mousey, Sep 21 '15 at 14:16

Matching Hex Values with SQLite Query like JavaScript

1 Answers1