custom datatype - rgb color

From: gabriel munteanu <jajalinux(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: custom datatype - rgb color
Date: 2009-09-04 05:46:21
Message-ID: 93de83770909032246y12c557c3r37278eec92057b9e@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,
I have a products table, with id, name, price columns.
the products are shoes.
now, they have a color , and in the UI we have a color palette so the
user can search the shoes for colors.
example: the user searches for red - [255,0,0], so i must give him all
shoes close to red in color.
i add the color column , with a custom datatype named color - should i
use array? or does anybody has a better approach to it?
anyway, by computing the RGB color distance in this way [pseudocode]:
dist=SQRT(POW(R1-R2,2)+POW(G1-G2,2)+POW(B1-B2,2)) , i am can provide
him OK results.
so, the select sql would be:

select * from products where color_distance (color,[255,0,0]] < 10;
i don't know how to create an efficient index and operator class for
such column so that the select would run as fast as possible.

the problem would be much simpler if i would have an integer
representation of colors, but i could find such thing.
has anybody encountered such a situation and can help me with
guidance, or pointing me to some online docs?

Thanks for help,

--
jgabios
http://bash.editia.info

Browse pgsql-general by date

  From Date Subject
Next Message Guillaume Lelarge 2009-09-04 05:53:02 Re: How to stop a query
Previous Message Chris 2009-09-04 05:44:23 Re: libpq performance