Suppose that I have this table in PostgreSQL 9.4:

    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?

  • 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 2: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

For Postgres 9.4+:

UPDATE trajs t
SET    traj_id = upd.traj_id
   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:

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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