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