Calculate all latitude and longitude distance in result using MySQL

Question

this is my current query to calculate between two latitude and longitude, but how do i using this or similar formula to calculate all the latitude and longitude in result?

SELECT ROUND(6353 * 2 * ASIN(SQRT( POWER(SIN((a.GPSLat -
      abs(b.GPSLat)) * pi()/180 / 2),2) + COS(a.GPSLat * pi()/180 ) * COS(
      abs(b.GPSLat) *  pi()/180) * POWER(SIN((a.GPSLon - b.GPSLon) *  
      pi()/180 / 2), 2) )), 2) as "Total(KM)"
from table1 a
      inner join table1 b on a.ID = 70 and b.ID = 71;

This is my database Lat and Lon example

The result count by id70 and id71

Xingzhou Liu Xingzhou Liu · Accepted Answer · 2017-07-12T09:34:26

Not 100% clear on what you mean but are you saying you want to calculate distances for all pairs of coordinates? If so, you're trying to do a cross join:

SELECT a.id, b.id , ...(your formula) from 
  (select id from Table1 
      [some join with filtered table]
       where [some predicate] ) a 
  CROSS JOIN 
  ( ... similar query to above to filter your right hand set... )b

Sorry on iPad now and typing out everything is exasperating.

Do not apply any on or where to the cross join, as that will turn it into an inner join.

This will give you distances between all pairs.

See for some ways to optimize this : Cross Join without duplicate combinations

Calculate all latitude and longitude distance in result using MySQL

1 Answers