PostgreSQL select rows having same column values

You can use inner join:

select distinct t1.id
from table_name t1
inner join table_name t2
on t1.location_id <> t2.location_id 
and t1.lat = t2.lat
and t1.long = t2.long
where t1.speed = 0
and t2.speed = 0

or exists:

select t.id
from table_name t
where exists (
    select *
    from table_name it
    where t.location_id <> it.location_id 
    and t.lat = it.lat
    and t.long = it.long
    and it.speed = 0
)
and t.speed = 0

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

One more solution:

SELECT location_id 
FROM device_location 
WHERE (lat, long) IN (
  SELECT lat, long
  FROM device_location
  WHERE speed = 0.0
  GROUP BY lat, long
  HAVING COUNT(*) > 1
);

Test it on SQLize.online

If you want adjacent rows, you can just use lead() and lag() . . . but with a little trick by using locationid:

select t.*
from (select t.*,
             lag(locationid) over (order by locationid) as prev_locationid,
             lead(locationid) over (order by locationid) as next_locationid,
             lag(locationid) over (partition by lat, long order by locationid) as prev_locationid_ll,
             lead(locationid) over (partition by lat, long order by locationid) as next_locationid_ll
      from t
     ) t
where speed = 0 and
      (prev_locationid = prev_locationid_ll or
       next_locationid = next_locationid_ll
      );

The comparison is of the location ids only. One is calculated based on the location id along. The second is the previous or next based on the latitude and longitude. When these are the same then the values are the same on adjacent rows.