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 · Accepted Answer · 2017-07-12T10:31:39.310

0

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

edited Jul 12 '17 at 10:31

answered Jul 12 '17 at 09:34

Xingzhou Liu

1,507
8
12

similar with this https://dba.stackexchange.com/questions/162401/comparing-previous-next-record-to-calculate-an-average-speed i just want calculate the row that i selected, for example (id69 to id75) , and the end total up all the distances from id69 to id75 . – Amir Khan Jul 12 '17 at 09:55
your query are quite close! but this query will select all my database id, i need the result that id are selected by me – Amir Khan Jul 12 '17 at 10:05
Check the answer edit I did. filter within the parenthesized derive tables, dont put an on or where statement after cross join. – Xingzhou Liu Jul 12 '17 at 10:29
If you're looking for a one to many (distance from point 69 to all other points), you can also use a subquery in your select clause. – Xingzhou Liu Jul 12 '17 at 10:41
can i know what is `predicate` on your query? – Amir Khan Jul 13 '17 at 04:38
Basically any "where clause" condition for choosing which pairs of coordinates you want to compare. A filtered list of id's also works. – Xingzhou Liu Jul 13 '17 at 06:55

Calculate all latitude and longitude distance in result using MySQL

1 Answers1