reduce measure precision

[smnorris]

Observations at measures such as 309.442m and 309.761m are in essentially the same spot. Try rounding values and perhaps storing downstream_route_measure as an integer

[smnorris]

Related to this, ensure that measures calculated for the observations are between a stream's upstream/downstream measures. In the pscis repo, this using the locatepoint/closestpoint functions similarly can produce measures very slightly greater than the stream's upstream measure https://github.com/smnorris/bcfishobs/blob/master/sql/03_create-prelim-table.sql#L71. When creating geoms no row will be returned (with no error) if a measure is outside of the m values on the stream - the point is lost.

[smnorris]

something like this should fix the issue:

with refpt AS 
(SELECT
  a.stream_crossing_id,
  s.downstream_route_measure,
  s.upstream_route_measure,
-- locate point on stream
  (ST_LineLocatePoint(s.geom, ST_ClosestPoint(s.geom, p.geom)) * s.length_metre) + s.downstream_route_measure as pt_measure
FROM whse_fish.pscis_stream_matching a
INNER JOIN whse_fish.pscis_points_all p
ON a.stream_crossing_id = p.stream_crossing_id
LEFT OUTER JOIN whse_basemapping.fwa_stream_networks_sp s
ON a.linear_feature_id = s.linear_feature_id)

SELECT
stream_crossing_id,
downstream_route_measure,
upstream_route_measure,
-- make measure an integer
-- (ensuring point measure is between stream's downtream measure and upstream measure)
CEIL(GREATEST(downstream_route_measure, FLOOR(LEAST(upstream_route_measure, pt_measure)))) as measure
FROM refpt;