Distance between coordinates (all compared against each other - iterate?)

[asfasf]

Hi guys, I'm been reading and looking for a solution for this but I'm struggling, so I decided to ask for help.

Imagine I have a list of coordinates and I would like to have the distance in meters from each other.
Example:
coordinate1 - distance to coordinate2, coordinate3, coordinate_n
coordinate2 - distance to coordinate1, coordinate 3, coordinate_n
and so on.

Some form of iteration between them?

I found some threads about this, the closest one: Need to compare Latitude & Longitude coordinates to find closest office
Although it gives the distance in km, I need in meters, so I adapted and considered the radius in meters.

Is this possible? The goal is then to filter by a max distance in meters the user should choose and check which coordinates fall inside the radius.
Something like a heatmap.

I have attached a sample with 4 pairs of coordinates, but my main project has 10k pairs to check against each other.

Can you help me? Thank you very much

Column A = ID
Column B = Longitude
Column C = Latitude

 

[DRSteele]

I put an Excel file in OneDrive for you to download to a desktop PC. It is a large file, and posting it here won't help. Anyway, it's the same formulas used in the link you cited.

I believe it does what you ask by reporting the distances between all locations to one another in a grid. Let us know if it helps.

 

[asfasf]

Thanks for sharing that file, really helpful
Although, Excel only supports 16k columns. What if I have 20k coordinates?

 

[Marc L]

Hi,​

​

as for huge data the smart way is any database software rather than Excel …​

 

[DRSteele]

Thanks for sharing that file, really helpful
Although, Excel only supports 16k columns. What if I have 20k coordinates?

You're welcome.

Perhaps you can split it between two or more worksheets. There must be some logical division in your data, like N S E or W, Province, City, something.

 

[asfasf]

Hi,​

​

as for huge data the smart way is any database software rather than Excel …​

Thanks. Any recommendation?

 

[Marc L]

​

Access, WinDev, whatever as any database software with some coding language …​