SQL: Select duplicate LRS events, including near-duplicate FROM_MEAS and TO_MEAS

[Bud]

This post might interest linear referencing folks — as a way to find possible duplicate LRS events:

Select duplicates (including near-duplicate numbers)

Summary:

I want to select rows where the ROAD_ID, YEAR_, STATUS, FROM_MEASURES, and TO_MEASURES are duplicated.

In the case of FROM_MEASURES and TO_MEASURES, I want to use a 5 metre tolerance. For example, these TO_MEASURES would be considered duplicates: 200.6, 199.3, and 201.

PROJ_ID    ROAD_ID      YEAR_ STATUS   FROM_MEASURE TO_MEASURE
---------- ---------- ---------- -------- ------------ ----------
       100          1       2022 APPROVED         null      100.1 --duplicates (other than PROJ_ID); null is to be treated as zero
       101          1       2022 APPROVED            0      100.1

       102          1       2022 APPROVED            0      200.6 --duplicates: TO_MEASURES are approximately the same (within a 5 metre tolerance)
       103          1       2022 APPROVED            0      199.3
       104          1       2022 APPROVED            0        201

       105          2       2023 PROPOSED            0         50 --not duplicates: FROM_MEASURES are different and TO_MEASURES are different
       106          2       2023 PROPOSED           75        100

       107          3       2024 DEFERRED            0        100 --not duplicates: YEARS are different and TO_MEASURES are different
       108          3       2025 DEFERRED            0        110

       109          4       2026 PROPOSED            0       null  --not duplicates: STATUSES are different
       110          4       2026 DEFERRED            0       null

Solution:

select * from road_projects rp
where exists (
  select null
  from road_projects rp2
  where rp2.proj_id != rp.proj_id
  and rp2.road_id = rp.road_id
  and rp2.year_ = rp.year_
  and rp2.status = rp.status
  and abs(coalesce(rp2.from_measure, 0) - coalesce(rp.from_measure, 0)) < 5
  and abs(coalesce(rp2.to_measure, 0) - coalesce(rp.to_measure, 0)) < 5
)
order by proj_id

fiddle