PSCIS 199306 not being linked to crossing 13900028

[smnorris]

Via @lucy-schick - this linkage is enforced in the fix table, but currently stream_crossing_id is null for modelled crossing 13900028 in streams_vw

There is no other reference to these ids in the fix tables so this seems like a bug?

[smnorris]

Issue is somewhere in the initial PSCIS processing - the crossing is not coming through into bcfishpass.pscis:

select * from bcfishpass.pscis where stream_crossing_id = 199306
 stream_crossing_id 
--------------------
(0 rows)

[smnorris]

The record is being silently dropped on load -
The query loading the matched records to bcfishpass.pscis gives this:

 stream_crossing_id | modelled_crossing_id | blue_line_key | downstream_route_measure 
--------------------+----------------------+---------------+--------------------------
             199239 |             13905537 |     356294487 |                     2060
             199306 |             13900028 |     356294487 |                     2060
(2 rows)

The closest point on the stream to each PSCIS crossing is exactly the same: at measure 2060m.
But the output table has a constraint that blue_line_key / downstream_route_measure must be unique - we don't want more than one crossing in the same spot - and the load enforces this constraint by dropping duplicates.

Best fix would probably be to allow setting of desired downstream_route_measure in the PSCIS fix table, or perhaps allow shifting the PSCIS coordinates. This would give some flexibility for other tricky crossings (ie FWA stream or DRA road location does not match reality or PSCIS GPS coordinates are wrong).

[smnorris]

Obviously, records coming from the fix table shouldn't be dropped (silently or not) - their location has been QA'ed by a person. But the unique constraint is a good one, crossings cannot be at the same location.

The PSCIS load process could be triggered on PR to the fix table - and any records with duplicate locations could result in a failed build. But there would have to be a mechanism to fix!