Grouping latitude and longitude pairs in sequence

Question

I've a table in which every x seconds a position is being written to. The table consists of the following columns:

lat  | lon  | time  | .....

The ultimate goal is to make the track visible on a map with on the points where the device halted displaying a image. On the points where there are more than one entries over time the rows should be added together to display a row with a 'timeSpent' at the location which is the difference of the different rows at the same location. So:

lat  | lon  | timeFirst | timeSpent

For instance this is the 'raw' table:

x1,y1, 13:00:12  // Point 1
x1,y2, 13:01:34  // Point 2
x3,y3, 13:02:01  // Point 3
x3,y3, 13:03:03  // Point 3
x3,y3, 13:04:34  // Point 3
x3,y3, 13:05:12  // Point 3
x4,y6, 13:06:23  // Point 4

On point 3 there are multiple entries. Which should be joined together to the following table:

    x1,y1, 13:00:12, null
    x1,y2, 13:01:34, null
    x3,y3, 13:02:01, 13:05:12
    x4,y6, 13:06:23, null

I would really love to have a native MySQL query which makes the result table. But if it's necessary I could use PHP to parse the source table.

Can anyone help me to get the wanted table?

score 2 · Accepted Answer · answered Mar 31 '12 at 18:05

The problem that it is not clear in your example is that if you add another row to it with values:

x3,y3, 13:59:59  // Point 3

It will still be Point 3, but you'll be expecting it to be considered as another point in the sequence. In order to do so you should have a query that would prepare the data like this (notice x3,y3 values):

+---------+-----+-----+
| COUNTER | LAT | LON |
+---------+-----+-----+
|       1 | x1  | y1  |
|       2 | x1  | y2  |
|       3 | x3  | y3  |
|       3 | x3  | y3  |
|       3 | x3  | y3  |
|       3 | x3  | y3  |
|       4 | x4  | y6  |
|       5 | x3  | y3  |
+---------+-----+-----+

Once you have this, you can easily group by. The query that would help you do this is the following:

select
  if(@lat = lat and @lon = lon, @counter, @counter := @counter + 1) counter,
  @lat := lat lat, @lon := lon lon
from t, (select @lat := '', @lon := '', @counter := 0) init
order by time

So, all you need to do with that query is group it by and get the min and max values (and null them if they're equal). So in order to get the result you're looking for you should run the following query:

select
  if(@lat = lat and @lon = lon, @counter, @counter := @counter + 1) counter,
  @lat := lat lat, @lon := lon lon, min(time) timeFirst,
  nullif(max(time), min(time)) timeSpent
from t, (select @lat := '', @lon := '', @counter := 0) init
group by counter, lat, lon
order by time

NOTE: This will also return the counter column. You can get rid of it by wrapping the previous query into another select statement such as:

select lat, lon, timeFirst, timeSpent from ([previous query]) as FinalQuery

But that will decrease considerably performance. So, my advice would be just not to use that column in your application.

Result with the counter column removed:

+-----+-----+-----------+-----------+
| LAT | LON | TIMEFIRST | TIMESPENT |
+-----+-----+-----------+-----------+
| x1  | y1  | 13:00:12  |           |
| x1  | y2  | 13:01:34  |           |
| x3  | y3  | 13:02:01  | 13:05:12  |
| x4  | y6  | 13:06:23  |           |
| x3  | y3  | 13:59:59  |           |
+-----+-----+-----------+-----------+

A possible variation on the `counter` expression: `@counter := @counter + (@lat <> lat or @lon <> lon) counter`. — Andriy M, Mar 31 '12 at 18:51

score 1 · Answer 2 · answered Mar 31 '12 at 17:38

1

SELECT lat,
       lon
       MIN(time) AS timeArrived,
       MAX(time) AS timeSpent
  FROM table
 GROUP BY lat,
          lon
 ORDER BY 3

Might be a starting point

If the lat,lon is only logged once, then timeArrived and timeLeft will have the same value

answered Mar 31 '12 at 17:38

Mark Baker

209,507
32
346
385

1

Hmm, that's a huge restriction, because this assumes the device, after leaving a point, won't come back to it later. – Mosty Mostacho Mar 31 '12 at 17:43

score 0 · Answer 3 · answered Mar 31 '12 at 17:47

0

I think this will do the trick:

SELECT lat, lon, MIN(`time`) AS start_time, MAX(`time`) AS end_time, 
TIMEDIFF( MAX(`time`), MIN(`time`) ) AS timeSpent FROM coordinates
GROUP BY lat, lon ORDER BY NULL

Tell me how it goes.

answered Mar 31 '12 at 17:47

georgepsarakis

1,927
3
20
24

Grouping latitude and longitude pairs in sequence

3 Answers3