I guess it would be order by v ASC, since 0 is a perfect match. order by v DESC gives you the least similar matches.
On Fri, Apr 11, 2014 at 1:37 PM, Dan Simpson <[email protected]> wrote: > If you have a table with 16 columns, q1, q2 ..., q16, could you do the > following? > > SELECT id, (abs(q1 - input1) + abs(q2 - input2) ... + abs(q16 - input16)) > AS v FROM table ORDER BY v DESC LIMIT 0,10; > > This way you can limit the size with a WHERE v > threshold, etc. > > --Dan > > > On Fri, Apr 11, 2014 at 12:59 PM, Chris McCann <[email protected]>wrote: > >> SD Ruby, >> >> I'm looking for a solution to a search problem and want to survey the >> community to see if anyone else has dealt with this type of search. >> >> The application I'm building supports an image processing system. We >> have a mathematical way of uniquely representing any particular image as a >> vector of 16 values, each ranging between 0 and 255. >> >> I need to implement a search mechanism that finds the closest matches to >> a given image, also represented as a 16 element vector. This is usually >> called a "vector space model" search, and it's implemented for full text >> search in Postgres as well as Lucene, and probably many other full text >> search systems. >> >> The problem I'm wrestling with is I'm not searching on text, I'm >> searching on integers. I basically need to search for the closest match >> like this: >> >> Say my search image has a vector with elements q(1) to q(16), [q(1) = >> 122, q(2) = 7, q(3) = 89,, ..., q(16) = 224]. >> >> To compare that vector against the image vectors in the database I need >> to calculate the "distance" between the query vector (q) and each of the >> database vectors (d): >> >> distance = square_root( (q(1) - d(1))^2 + (q(2) - d(2))^2 + ... (q(16) - >> d(16))^2) >> >> The lower the distance the closer the match, with dist == 0 being an >> exact match. >> >> My research hasn't led me to a direct implementation of this in Postgres >> or Lucene since they are designed for text searching, though the underlying >> principles are the exact same. Anyone ever tackle this type of search with >> numerical values? >> >> Thanks in advance, >> >> Chris >> >> -- >> -- >> SD Ruby mailing list >> [email protected] >> http://groups.google.com/group/sdruby >> --- >> You received this message because you are subscribed to the Google Groups >> "SD Ruby" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> For more options, visit https://groups.google.com/d/optout. >> > > -- -- SD Ruby mailing list [email protected] http://groups.google.com/group/sdruby --- You received this message because you are subscribed to the Google Groups "SD Ruby" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
