RSMS Flows Processing Comparison

Overview

This document outlines the key differences between the original ORSANCO VM implementation and the current RSMS-specific version of the river flows processing system. The RSMS version introduces significant changes to support different data processing requirements and database architecture.

Core Differences

1. Database Architecture

Aspect	ORSANCO VM	RSMS Version
Database Type	PostgreSQL with TimescaleDB	SQL Server
Primary Tables	RiverFlow, DailyRiverFlow	HEC_RASFlow, HEC_RASFilesProcessed
Data Model	TimescaleDB hypertables	Standard SQL Server tables
Indexing	Composite (time, station)	Custom indexes for RSMS requirements

2. Data Processing Pipeline

Component	ORSANCO VM	RSMS Version
File Processing	download_process_hdf.py	process_hdf_rsms.py
Flow Processing	upsert_flows.py	upsert_flows_rsms.py
Data Transformation	Velocity-based calculations	Area-based calculations
Database Operations	Django ORM with bulk operations	SQL Server bulk upserts

3. Key Functional Changes

1. Data Calculation Changes

   • ORSANCO VM: Uses velocity measurements directly
   • RSMS: Calculates area from flow/velocity ratio
   • RSMS: Stores area measurements instead of velocity

2. Station Mapping

   • ORSANCO VM: Uses Django Station model
   • RSMS: Uses RivermileIndex table for station mapping
   • RSMS: Includes additional fields (RiverMile, TributaryMile)

3. File Processing

   • ORSANCO VM: Tracks processed files in Django model
   • RSMS: Uses dedicated HEC_RASFilesProcessed table
   • RSMS: Implements different file tracking mechanism

4. Data Storage

   • ORSANCO VM: Stores raw and aggregated data
   • RSMS: Focuses on raw flow data with area calculations
   • RSMS: Different schema for flow measurements

4. Technical Implementation Differences

1. Database Operations

   # ORSANCO VM (Django ORM)
   RiverFlow.objects.bulk_create(flows)
   
   # RSMS (SQL Server)
   df.to_sql('HEC_RASFlow', engine, if_exists='append', method=flows_upsert_sqlserver)

2. Data Processing

   # ORSANCO VM
   df = parse_flow_velocity_stage_datasets(datetime_dataset, flow_dataset, velocity_dataset, stage_dataset)
   
   # RSMS
   area_dataset = flow_dataset / velocity_dataset
   df = parse_flow_velocity_stage_datasets(datetime_dataset, flow_dataset, area_dataset, stage_dataset)

3. File Tracking

   # ORSANCO VM
   FlowFileProcessed.objects.create(name=x, date=datetime.now(timezone.utc), success=True)
   
   # RSMS
   flow_upserter.insert_flow_file_processed(name=x, date=datetime.now(timezone.utc), success=True)

5. Performance Considerations

1. Data Processing

   • ORSANCO VM: Optimized for TimescaleDB time-series queries
   • RSMS: Optimized for SQL Server bulk operations
   • RSMS: Implements chunked processing for large datasets

2. Storage Efficiency

   • ORSANCO VM: Uses TimescaleDB compression
   • RSMS: Relies on SQL Server table optimization
   • RSMS: Different indexing strategy

6. Security and Authentication

1. File Transfer

   • ORSANCO VM: SSH key-based SFTP
   • RSMS: Multiple authentication methods (key/password)
   • RSMS: Windows-compatible SFTP implementation

2. Database Access

   • ORSANCO VM: Django authentication
   • RSMS: SQL Server authentication
   • RSMS: Trusted connection support

Migration Considerations

1. Data Migration

   • Requires transformation of velocity to area calculations
   • Needs station mapping conversion
   • Requires file processing history migration

2. System Migration

   • Different database requirements
   • Modified processing pipeline
   • Updated authentication methods

3. Performance Impact

   • Different query optimization strategies
   • Modified data processing patterns
   • Updated storage requirements

Future Development

1. Planned Enhancements

   • Improved error handling
   • Enhanced data validation
   • Additional processing optimizations

2. Scalability Considerations

   • SQL Server performance tuning
   • Processing pipeline optimization
   • Storage efficiency improvements