0

Background

I have an SQL database table set-up on "SQLite" with over 100,000 colour codes stored in the form of colour space parameters L, a, b (https://en.wikipedia.org/wiki/Lab_color_space).

Task at hand:

We want to match a randomly chosen colour (L_rand, a_rand and b_rand) to the closest colour in this large database.

The way we want to achieve this would be to use the Delta E CIE 2000 for calculating the difference between the randomly chosen colour and the database of colours. We would like to implement this calculation strategy into SQL (already implemented and shared by @Jaza previously) and there has been a discussion about this already in one of the posts (Performance of Delta E (CIE Lab) calculating and sorting in SQL) mainly by @Jaza and @hruske before.

Question:

Referring to the Best, but advanced solution section of @hruske 's post: Could you provide some hints on how can we implement the Delta E queries as K-Nearest-Neighbor searches with a custom GiST index? In other words, how can I use btree-gist PostGRE SQL extension I apologise as I am a novice in SQL and I wanted a direction to work with.

Thanks in advance.

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
Cassy
  • 11
  • 2
  • 3
    Stack Overflow is not a free code writing service. Please try to write some code before asking. Visit this website to learn more: [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) –  Jan 18 '18 at 09:03
  • Please check this section before posting a question : https://stackoverflow.com/help/how-to-ask – Fourat Jan 18 '18 at 09:07
  • What’s the bigger picture? Why is speed crucial? Why choose SQL if speed is important? – Mark Setchell Jan 18 '18 at 09:46
  • Are you using SQLite or PostgreSQL? – CL. Jan 18 '18 at 12:02
  • Thank you for replying, one and all. @_Steve - I guess, I was asking for a direction, not for any code. Kindly be considerate. Anyway, I shall take the comment seriously and have a code here soon. @_Fourat: thanks so much for your guidance. @_Mark - this is for a device that helps a consumer who wants to for example measure colour and he/she wants to find the closest match, then he/she would need to have this result within a matter of a second, I imagine. SQL is for storing the database of the vast colour range. @_CL: We are using SQLite I guess, but does that matter? Maybe syntax? – Cassy Jan 19 '18 at 01:16

0 Answers0