Calculate distances between many points (large data)
Basically I have all functions two calculate distances between two data points, but I have a problem because I have large data set and my server get 100% cpu and 100% Ram because of large data set.
There are 2 two tables:
1st is member_addresses table, where are 2 millions unique rows:
MemberId, Lat, lng
10001, 56.95, 24.25
10002, 56.96, 24.24
2nd table is store location addresses, where are 20'000 unique stores
Storeid, lat, lng
5001, 60.12, 24.86
5002, 60.13, 24.85
I need to find out for each member nearest 5 stores, when I try to join both table together, it creates 2'000'000* 20*000 rows, and it freezes my server. I know, it's not efficient, but can you help to suggest which will be the best way to calculate it?