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?
