CODEWITHSUNDEEP
sqlpostgresqlpostgis
vpgcloud
vpgcloud

Reputation: 1603

Group Points into Linestrings based on one column but only if sequential following order by other column

I have a table with a structure like this:

datetime            | the_geom                   | m_status
2017-01-01T00:00:00 | POINT(13.048133,57.798882) | status_1
2017-01-01T00:00:01 | POINT(14.048133,56.798882) | status_1
2017-01-01T00:00:02 | POINT(15.048133,55.798882) | status_1
2017-01-01T00:00:04 | POINT(17.048133,53.798882) | status_2
2017-01-01T00:00:05 | POINT(18.048133,52.798882) | status_2
2017-01-01T00:00:06 | POINT(19.048133,51.798882) | status_2
2017-01-01T00:00:07 | POINT(20.048133,50.798882) | status_1
2017-01-01T00:00:08 | POINT(21.048133,49.798882) | status_1
2017-01-01T00:00:09 | POINT(22.048133,48.798882) | status_3
2017-01-01T00:00:10 | POINT(23.048133,47.798882) | status_3

I would like to group sequential points by m_status into separate Linestrings but only if they are in sequential order following the datetime column.

Any constructive help is appreciated.

PostgreSQL version: PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

edit 1:

Expected output:

id | the_geom                                                                  | m_status
1  | LINESTRING(13.048133 57.798882, 14.048133 56.798882, 15.048133 55.798882) | status_1
2  | LINESTRING(17.048133 53.798882, 18.048133 52.798882, 19.048133 51.798882) | status_2
3  | LINESTRING(20.048133 50.798882, 21.048133 49.798882)                      | status_1
4  | LINESTRING(22.048133 48.798882, 23.048133 47.798882)                      | status_3

edit 2: I don't need the start and end dates for each linestring, however I need some sort of id to know their temporal order (see id in expected output above).

Upvotes: 1

Views: 355

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use a difference of row numbers approach to classify the groups (run the inner query to see group assignment) and then use ST_MakeLine to get all those points on to one row.

select ST_MakeLine(the_geom order by datetime) as the_geom, max(m_status) as m_status
from (select t.*,
      row_number() over(order by datetime)
      -row_number() over(partition by m_status order by datetime) as grp
      from tbl t
     ) t
group by grp

Edit: Based on OP's edit to include row numbers in the output, get the min or max time per group previously identified and use it for ordering.

SELECT row_number() over(ORDER BY min_time) AS id,
       ST_MakeLine(the_geom ORDER BY datetime) AS the_geom,
       max(m_status) AS m_status
FROM
  (SELECT t.*,
           min(datetime) over(partition BY grp,m_status) AS min_time
   FROM
     (SELECT t.*,
             row_number() over(ORDER BY datetime) -row_number() over(partition BY m_status ORDER BY datetime) AS grp
      FROM tbl t
   ) t 
) t
GROUP BY grp,min_time

Upvotes: 2

Related Questions