PostgreSQL select rows having same column values

Question

  | location_id  |  lat  |  long  |  speed  |
    ------------- ------- -------- --------- 
      101241        0.12    1.1       0.0    
    ------------- ------- -------- --------- 
      101242        0.12    1.1       0.0
    ------------- ------- -------- --------- 
      101243        0.12    1.1       0.0
    ------------- ------- -------- --------- 
      101244        1.25    0.74      7.4
    ------------- ------- -------- ---------

I want to select all locations where speed = 0 and lat && long are same

So from above example answer should be::

   | location_id  |
    --------------
        101241     
    --------------
        101242     
    --------------
        101243     
    --------------

Note:: Speed is constant 0 but lat and long depend on previous rows value

Answer 1

I actually read this as a gaps-and-islands problem, where you want adjacent rows that have the same latitude and longitude, and a speed of 0.

You could approach this with window functions: the difference between row numbers gives you the islands: you can then compute the lenght of each islands, and filter on those lenght is greater than 1 and whose speed is 0:

select *
from (
    select t.*, count(*) over(partition by lat, long, speed, rn1 - rn2) cnt
    from (
        select t.*, 
            row_number() over(order by location_id) rn1,
            row_number() over(partition by lat, long, speed order by location_id) rn2
        from mytable t
    ) t
) t
where speed = 0 and cnt > 1

Demo on DB Fiddle