I have several matrices of distances between objects, like this:

Method 1
          A         B         C         D ...
A 0.0000000         .         .         .
B 0.5458965 0.0000000         .         .
C 0.9758267 0.9673959 0.0000000         .
D 0.9714434 0.9589730 0.5715958 0.0000000

Method 2
          A         B         C         D ...
A 0.0000000         .         .         .
B 0.1063699 0.0000000         .         .
C 0.4997017 0.4857148 0.0000000         .
D 0.4968287 0.4752771 0.0639429 0.0000000

Method 3

To give a sense of scale, each matrix is about 10,000 x 10,000 and I have five different methods of distance calculation.

I also have a Postgres database that has a table that stores information about objects A, B, C, D, etc., so I'm looking at SQL solutions specifically.

Let's say a user selects object B in my app. I then display a table of the closest objects to B. Since the distance matrices are on different scales, I convert the values to ranks and use the average of the ranks to sort the results.

What is an efficient way to store this data? The obvious way would be like this:

method obj1 obj2         d
     1    A    B 0.5458965
     1    A    C 0.9758267

Then, I just query for obj1='B' OR obj2='B', group by method, calculate rank(d), and aggregate the ranks by mean. Is this a reasonable schema for this problem?

  • Background: Each object is a document in a corpus and the distances between objects are estimated from various topic modeling algorithms. Since these are symmetric matrices, there is a many-to-many relationship.
    – Devin
    Apr 23, 2018 at 18:06


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.