0

India has around 19100 pin codes. For an application, I want to calculate and store the distance between any two pin codes (this is not the challenge, will use google maps API to find it). On calculating this is around 182 million records (used sum of n numbers formula).

How can I store this data in such a way that it is easily retrieved and optimally stored?

One option I can think of is storing it as a table with aroun 8500 columns and 8500 rows, but that seems wrong to me. Is there a better way or library I can use?

I use python and postgresql.

Improve this question

2 Answers 2

Reset to default
4

I would start by keeping it super simple, create a table with start_pin, end_pin, and distance. Compoind index on the start and end.

(If storage us a big problem, potentially optimise by using a smallint to represent each pin code and store those in the lookup table.... but check first that this is necessary)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

The best way is to use PostGIS to store the locations pf the 19100 points (I don't know what a "pin code" subject to geographical measurements is, but I assume it is a point).

Then use PostGIS to calculate the distances whenever you need them.

Storing the n * (n - 1) / 2 distances for n points would be overkill.

Improve this answer

3 Comments

The challenge is that PostGIS gives you access to geographical distance, while I am looking for driving distance. P.S. Pin codes are postal codes like Zip codes in USA (they are basically descriptors of an area, but their centroid is taken as an indicative point)
@kmcodes: "driving distance" isn't a single value. The driving distance for a car will be different to the one for a truck and that again will be different for a bicycle. Maybe you want something like pgrouting.org ?
Sorry, I was simplifying for brevity, I meant driving distance by car only. I will check out pgrouting, but does that mean there is no ideal way to store this information pre-calculated (google maps API will be my source)?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.