title: manydimentional distance in postgres
body: <p>Postgres / Postgis support distance calculations between points, sorting by distance from a given point and so on, I'm trying to abuse this facility :)</p>
  
  <p>This is an example, just to illustrate what I'm trying to do.</p>
  
  <pre><code>       Width    Height  Thickness   Hue Lightness   Saturation     
  Item1  220      157     100         270 60          75     
  Item2  221      199     105         280 60          75     
  Item3  210      150     105         100 40          75   
  </code></pre>
  
  <p>Ignoring these capabilities for now, to find close matching items from the table below that are similar in physical dimensions you could do:</p>
  
  <pre><code>SELECT * FROM items
  ORDER BY sqrt((i1.width-i2.height)*(i1.width-i2.height)
              + (i1.width-i2.height)*(i1.width-i2.height)
              + (i1.thickness-i2.thickness)*(i1.thickness-i2.thickness))
  </code></pre>
  
  <p>(call that pseudosql :) )</p>
  
  <p>We can consider the 4th-6th properties to be dimensions such that a similar (or "close") color has a similar value for each of those properties. And we can extend the above query by adding in those fields.</p>
  
  <p>Now, Postgres seems to be able to do some pretty great things with indexing and such that would speed up the above query massively, but the data types only seem to go up to 4 dimensions.</p>
  
  <p>What would be a good solution for calculating, and more often sorting records similarity of 10 scales (or "dimensions") for millions of records?</p>
  
  <p>I'm currently thinking of 3 4D points and sorting by the distance of the 3 points.</p>
  
  <p>Without the Postgis thing I guess the question would be:-</p>
  
  <p>In a table with items as follows, what would be the fastest way to sort by the sum of the difference between 10 values input (one per val field), taking advantage of whatever possible, bounding boxes, spacial or other indexes etc.</p>
  
  <p>eg.</p>
  
  <pre><code>SELECT * FROM items
  ORDER BY ((item_val1-xxxx)*(item_val1-xxxx)
          * (item_val2-yyyy)*(item_val2-yyyy)
          * ......
          * (item_val10-zzzz)*(item_val10-zzzz))
  </code></pre>
  
  <p>where xxxx,yyyy, zzzz represent values input in the front end application</p>
  
  <pre><code>item_name: string
  item_val1: int
  item_val2: int
  item_val3: int
  item_val4: int
  item_val5: int
  item_val6: int
  item_val7: int
  item_val8: int
  item_val9: int
  item_val10: int
  </code></pre>
  
  <p>Any ideas / alternatives greatly appreciated.</p>