0

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)

  • Okay, so its a non starter that way then. I am thinking of a third approach, will update the question with it. – kmcodes Jan 30 at 12:17
  • Oh great. Will use this approach. Better implementation of my approach 3. – kmcodes Jan 30 at 12:22
3

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.

|improve this answer|||||

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.