1

With PostgreSQL 13.6 have a table of multiple GPS points already group by track_id (HEX number not sequential), and from where ST_Makeline is made to build a line.

Some of these tracks have a time gap between them bigger than a given value (say 10 minutes). Need to split the track_id where these gaps exits, to a track_id (before 1st gap) and track_id+1 (after 1st gap) ... track_id+n (after n_st gap).

The track_id is a non-sequential large intervals HEX number, so no issues on adding a few additional units (1 to 2 gaps per track) to it.

The actual location geom is irrelevant in the analysis, since the points in the gap can be close (time spent on the same position) or far apart (loose of reception).

This is only to clean and organize data, before any further analysis.

Table example:

track_id geom time
123 aaaa 10:01
123 aaab 10:02
123 aaac 10:05
123 aaad 10:45
123 aaae 10:47
123 aaaf 10:49
234 aaab 10:52
234 aaac 10:55
234 aaaa 10:58

to build, new column 'new_track_id`:

track_id geom time new_track_id
123 aaaa 10:01 123
123 aaab 10:02 123
123 aaac 10:05 123
123 aaad 10:45 124
123 aaae 10:47 124
123 aaaf 10:49 124
234 aaab 10:52 234
234 aaac 10:55 234
234 aaaa 10:58 234
create table public.testtime
        (id SERIAL primary key,
        track_id varchar,
        time1 time);

--Test data:

    insert into public.testtime (track_id, time1)
        values (123,    '10:01:00'),
               (123,    '10:02:00'),
               (123,    '10:05:00'),
               (123,    '10:45:00'),
               (123,    '10:47:00'),
               (123,    '10:49:00'),
               (234,    '10:52:00'),
               (234,    '10:55:00'),
               (234,    '10:58:00');

EDIT:

to verify that time_dif is bigger than treshold:

UPDATE table SET
chk_dif=(CASE WHEN time_dif>'00:10' THEN TRUE END) 

to populate the row after the true : not working

UPDATE table SET
chk_dif2=(CASE WHEN chk_dif=TRUE THEN TRUE
               WHEN chk_dif IS NULL THEN (SELECT LAG(chk_dif2 OVER (PARTITION BY track_id ORDER BY time))
               ELSE FALSE END)

It copies the TRUE from chk_dif to chk_dif2, but not repeat the chk_dif2 on the next rows, has applicable.

To change the track_id (simplify for now, change the last digit to 'x' : is not more an HEX, and only allows for a gap) looking for better

UPDATE table SET
new_track_id =
CASE WHEN chk_dif2=TRUE THEN CONCAT(LEFT(track_id,LENGTH(track_id)),'X') END)

EDIT2:

The problem is on the second step, to copy the TRUE for the next rows, as applicable to the second part of the segment.

3
  • What is the data type of the time field
    – BERA
    Aug 26, 2022 at 6:43
  • is a 'timestamp without time zone' : not included the portion of YYYYMMDD on the tables above for clarity
    – domiho1
    Aug 26, 2022 at 6:52
  • 1
    I would calculate a time diff column first, see: stackoverflow.com/questions/24691462/…. But I dont know how to increment after that
    – BERA
    Aug 26, 2022 at 7:08

1 Answer 1

1

This looks like a pure SQL question, so it might be a better fit for dba.stackexchange.com, but then again, it looked like a bit of fun for a saturday morning. :-)

This is hideous, but it looks like it works:

SELECT
    *,
    SUM(increment) OVER (PARTITION BY track_id ORDER BY time1 ROWS UNBOUNDED PRECEDING) AS idoffset
FROM (SELECT
        *,  
        CASE
          WHEN time1 - LAG(time1) OVER (PARTITION BY track_id ORDER BY time1) > '00:10' THEN 1
          ELSE 0
        END AS increment
      FROM testtime) tt1
;

Output

 id | track_id |  time1   | increment | idoffset
----+----------+----------+-----------+----------
  1 | 123      | 10:01:00 |         0 |        0
  2 | 123      | 10:02:00 |         0 |        0
  3 | 123      | 10:05:00 |         0 |        0
  4 | 123      | 10:45:00 |         1 |        1
  5 | 123      | 10:47:00 |         0 |        1
  6 | 123      | 10:49:00 |         0 |        1
  7 | 234      | 10:52:00 |         0 |        0
  8 | 234      | 10:55:00 |         0 |        0
  9 | 234      | 10:58:00 |         0 |        0
(9 rows)

First we use a window function, as suggested in a comment, to compute a column increment which is 1 for the first sample in every new group, and 0 everywhere else. Then, for each row, we sum the increments of all preceding rows in the group, which gives the idoffset to add to the id. We can’t use a window function in a window function call, so let’s go for a sub-select.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.