fwa_networktraceagg error/inconsistency

[smnorris]

Querying two locations that are flow connected should return just one feature, but this returns 2, and one isn't being assigned an id:

 select 
   id, 
   from_blue_line_key, 
   from_measure, 
   to_blue_line_key, 
   to_measure
 from fwa_networktraceagg(356362521, 43756, 356362759, 98137);

 id | from_blue_line_key |   from_measure    | to_blue_line_key |    to_measure    
----+--------------------+-------------------+------------------+------------------
  1 |          356362521 |             43756 |        356362759 | 98896.7217189736
    |          356362759 | 98279.35995478695 |        356362759 |            98137

[smnorris]

It appears the result is disjoint because a single segment along the Nechako is skipped , linear_feature_id=702530182, from 98896 to 98279.

That record is present in the query to FWA_NetworkTrace without aggregation - on aggregation the record is lost because the records are not in the right order - sorting by watershed code / local code / measure doesn't give quite the result - the local code for that stream is incorrect:

  linear_feature_id | blue_line_key |   wscode   |     localcode     | downstream_route_measure | upstream_route_measure 
|-------------------+---------------+------------+-------------------+--------------------------+------------------------+
          702531349 |     356362759 | 100.567134 | 100.567134.195975 |         100286.252763188 |     100484.88760565323 |
          702530182 |     356362759 | 100.567134 | 100.567134.193752 |         98279.3598829651 |      98896.72177065989 |
          702531306 |     356362759 | 100.567134 | 100.567134.193259 |         99263.0681913489 |     100286.25274747585 |
          702530693 |     356362759 | 100.567134 | 100.567134.193259 |         98896.7217189736 |      99263.06800058839 |
          702529493 |     356362759 | 100.567134 | 100.567134.191236 |                    98137 |      98279.35995478695 |

[smnorris]

Fix is probably to post-process the results of an initial FWA_Downstream query and sort to make all endpoints touch. This would also resolve gaps in traces due to side channels.

Or just discard the codes and base all upstr/dnstr queries on a routing engine or recursive spatial queries...

[smnorris]

Post processing the results of FWA_Downstream with a recursive query is far too slow. Time for this example on the Nechako is ~1m20s.

Better to just fix the data as issues like this come up.