Queries

River Basin Repository is queries are the ones that change based on River. These queries are in the ReadOnlyRiverBasinRepository.cs. There are 23 queries in this file.

Some of the Queries have Hardcoded rivers in them such as Ohio River and Des Moines River. There are also Queries that have 981 in them which is specific to Ohio river mainstem. These Queries need to be modified as well.

In addition there are some variable names that need to be fixed as well as they have Ohio River in them.

This query does not seem to be used any where. I am going to comment it out.

private const string GetOhioRiverBasinQuery =
@"select rtrim(River) Name,
case when max(TRM) = 0 then null else max(MRM) end MainStemIntersection,
case when max(TRM) = 0 then max(MRM) else max(TRM) end Length
from RivermileIndex where rtrim(River) <> 'Mississippi' AND NearestRiverStation IS NOT NULL
group by rtrim(River)";

This query has a few issues.

1. 2 different tables Ohio is using OhioRiverLatLong and Des Moines is using RiverLatLong
   1. Column names are also different between these two tables.
2. The river Flip. 981 - river mile in Ohio and just river mile in Des Moines.

    // @@@20181206JS - Ohio River in RiverLatLong is all negative, so this had to be changed.
    // There's another table (not sure when it came into play) called 'OhioRiverLatLong' that has Ohio River
    // miles defined as positive numbers. We could possibly use this table. For now, we will rewrite the existing query
    // so that it can successfully use the RiverLatLong table.
    private const string GetOhioRiverBasinTopologyQueryMainstem =
@"select RiverMile, Latitude, Longitude from RiverLatLong
where (RiverMile) between @min and @max
and (SELECT CAST((RiverMile * 10) AS int) % 10 AS Modulo) = 0";
// @"select RMIMile, Latitude, Longitude from OhioRiverLatLong
// where (981.8-RMIMile) between @min and @max
// and (SELECT CAST((RMIMile * 10) AS int) % 10 AS Modulo) = 0";

This query needs to be modified so that it takes the Mainstem from the Config as a parameter.

  private const string GetIENCCodesQuery = @"select IENCRiverCode from RSMSRiver where IENCRiverCode IS NOT NULL AND (River = 'Des Moines River' OR River = rtrim(@river))";
  // private const string GetIENCCodesQuery = @"select IENCRiverCode from RSMSRiver where IENCRiverCode IS NOT NULL AND (River = 'Ohio River' OR River = rtrim(@river))";