1

Suppose that I have this table in PostgreSQL 9.4:

CREATE TABLE trajs ( 
    id serial NOT NULL PRIMARY KEY, 
    obj_id integer,
    traj_id integer,
    geom geometry,
    t timestamp without time zone
);

Here is what the data looks like:

 obj_id, traj_id,  geom,     t
    1       1      point     '2016-04-29 09:35:00'
    1       1      point     '2016-04-29 09:40:00'
    1       1      point     '2016-04-29 09:55:00'
    2       1      point     '2016-04-29 09:35:00'
    2       1      point     '2016-04-29 09:42:00'
    2       1      point     '2016-04-29 09:50:00'
    3       1      point     '2016-04-29 09:35:00'
    3       1      point     '2016-04-29 09:41:00'
    3       1      point     '2016-04-29 09:43:00'

For each 'obj_id', can I iterate 'traj_id' depending on the 't' distance between the previous?

An example: for each 6 minutes of difference (between the first object with a time, like 3rd column and 1st in that table), this should be a new traj_id (+1) - and restart for a new obj_id. Here is what result should look like:

 obj_id, traj_id,  geom,     t
    1       1      point     '2016-04-29 09:35:00'
    1       1      point     '2016-04-29 09:40:00'
    1       2      point     '2016-04-29 09:55:00'
    2       1      point     '2016-04-29 09:35:00'
    2       2      point     '2016-04-29 09:42:00'
    2       3      point     '2016-04-29 09:50:00'
    3       1      point     '2016-04-29 09:35:00'
    3       2      point     '2016-04-29 09:41:00'
    3       2      point     '2016-04-29 09:43:00'

I started doing this, but my solution doesn't work. And I can't see how it can iterate by every time that the condition is met.

update trajs t
set traj_id = traj_id+1
where t.t > (select ant.t + interval '6 minutes' 
             from trajs ant 
             where  t.obj_id=ant.obj_id);

How to solve this?

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
Adelson Araújo
  • 227
  • 2
  • 3
  • 7
  • It should be obvious to provide the exact table definition (complete CREATE TABLE statement) and your version of Postgres with a question like this. And the question is not clear to me. Clarify by adding the expected result. Your explanation sounds line INSERT, but your code uses UPDATE? – Erwin Brandstetter Jun 22 '16 at 02:11
  • @ErwinBrandstetter I edited, hope that solved your questions. About the UPDATE , I was doing that way because I have to edit the traj_id column that already has values. When I said new traj_id values, I wanted to say that it have to change it, adding 1. Look to the result table. – Adelson Araújo Jun 22 '16 at 13:03

1 Answers1

2

For Postgres 9.4+:

UPDATE trajs t
SET    traj_id = upd.traj_id
FROM  (
   SELECT id, 1 + count(*) FILTER (WHERE t >= t0 + interval '6 min')
                           OVER (PARTITION BY obj_id ORDER BY t) AS traj_id
   FROM  (
      SELECT id, obj_id, t, lag(t) OVER (PARTITION BY obj_id ORDER BY t) AS t0
      FROM   trajs
      ) sub
   ) upd
WHERE t.id = upd.id
AND   t.traj_id IS DISTINCT FROM upd.traj_id;

Increases traj_id only if the previous row is 6 minutes or more earlier - exactly like requested.

About the last condition I added to optimize performance (last paragraph):

The way I formulated it, it also works for the corner case of the first row per obj_id, where lag() returns NULL.

About the aggregate FILTER I combined with the second window function:

Community
  • 1
Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • Thanks for the help. It works perfectly.. Do you have in mind if its possible update it comparing the actual time with the first time of a traj_id? Like, comparing not if the actual row is 6 minutes later than the previous, but if the actual is 6 minutes later than the first time of a new traj_id. – Adelson Araújo Jun 22 '16 at 14:13
  • That query would be slightly different, but shouldn't be a problem. Please start a new question for your new question. you can always link to this one to provide context. – Erwin Brandstetter Jun 22 '16 at 15:01