PostgreSQL – How to Store a Very Large Matrix

postgresql

I have a list of 19100+ postal codes(with their associated centroid lat/long). Now I am planning to calculate the driving distances between each of them and store it in a database as a ready reckoner for use in another application. I am facing two problems:

  1. Is storing this as a 19100 columns X 19100 rows table feasible (seems wrong in my gut) a viable option?
  2. Since A->B is the same as B->A I can store maybe reduce it to a 9550 X 9550 elements table, which reduces the problem but still seems like optimisation of an incorrect approach.

[ 1 & 2 seem to be non starters as I am told max columns is 1300 in a table]

3.Approach three is storing the distance as key/value pairs. For example to store distance between 400703 and 400705, we store it as

{"400703400705":15"}

Is this the only option?

What's the best way to store this? It will be a write once operation, all future uses will be read only, if that helps in any way. Is there a better approach? (I cannot use Haversine distance formula because it is driving distance I need, not geographical distance)

Best Answer

I would create a table with three columns:

create table distances
(
  from_zip_code int not null,
  to_zip_code int not null,
  distance numeric not null, 
  primary key (from_zip_code, to_zip_code)
);

If you want, you can prevent storing the reversed combination through a unique index.

If your ZIP codes aren't numbers, then obviously you need to use text or varchar for those two columns.

While the table is moderately large (9550 * 9550 rows), access through the primary key is typically pretty much constant in time.

Related Question