Edition 25 - ICM SQL Functions in ICM SWMM and ICM InfoWorks Networks

This newsletter edition covers several SQL-related topics for working with ICM InfoWorks and ICM SWMM networks. The first section provides SQL code to create a usage table for the Sustainable Drainage Systems (SUDS) or Low Impact Development (LID) features on subcatchments. The second sections cover SQL for working with buckets and the RINDEX in ICM InfoWorks network data, as well as using aggregate functions for ICM InfoWorks links. Following are sections on SQL for ICM SWMM options and how to delete all SUDS or LIDs from an ICM network. Finally, the newsletter includes SQL code for retrieving upstream and downstream results for time series reporting (TSR) in ICM InfoWorks networks, as well as a similar SQL query for ICM SWMM network link TSR results.

1/ SQL for make a Usage Table for the SUDS or LIDS on the Subcatchments

2/ SQL Buckets and RINDEX for ICM InfoWorks Network Data

3/ SQL Aggregate Functions for ICM InfoWorks Links

4/ SQL for ICM SWMM Options

5/ How to Delete all SUDS or LIDS from your Network

ICM InfoWorks and ICM SWMM Networks do not have easy-to-use grids for viewing all of the SuDS or LID data.? You need to use Ruby to view or edit the data globally. Here is how you can delete all of the SuDS data.???

This Ruby script is designed to reset Sustainable Drainage Systems (SUDS) controls in a network of subcatchments. Here's a summary of what it does:

1. It starts by importing the necessary libraries: CSV for handling CSV files and date for handling dates.
2. It retrieves the current network using the?WSApplication.current_network?method.
3. It records the start time of the operation for tracking purposes.
4. It begins a transaction on the network. Transactions ensure that changes to the network are done atomically, meaning all changes are applied, or none are.
5. It initializes a counter to keep track of the number of SUDS controls that get reset.
6. It iterates over each subcatchment in the network. For each subcatchment, it checks if there are any SUDS controls. If there are, it resets them by setting the size of the?suds_controls?array to 0, writes the changes back to the?suds_controls?and the subcatchment, and increments the reset counter.
7. After iterating over all subcatchments, it commits the transaction, making all changes to the network permanent.
8. Finally, it outputs the number of reset SUDS controls to the console.

# Import required libraries
require 'CSV'
require 'date'

# Get the current network
on = WSApplication.current_network

# Record the start time
start_time = Time.now

# Begin a transaction on the network
on.transaction_begin

# Initialize a counter for the number of reset SUDS controls
reset_count = 0

# Iterate over each subcatchment in the network
on.row_objects('_subcatchments').each do |ro|
  # If the subcatchment has any SUDS controls, reset them
  if ro.suds_controls.size > 0
    ro.suds_controls.size = 0
    ro.suds_controls.write
    ro.write
    # Increment the reset counter
    reset_count += 1
  end
end

# Commit the transaction
on.transaction_commit

# Output the number of reset SUDS controls
puts "Number of reset SUDS controls: #{reset_count}"        

6/ SQL for ICM InfoWorks Network Link Upstream and Downstream results

Here is what the code does for TSR or time series report data - The SQL code is performing a series of aggregate operations on a dataset, grouped by the oid field. Here's a summary of what it does:

• Object: The script operates on a model run that was dragged to the ICM GeoPlan
• Group By: The script groups the data by the oid field.
• Select: The script selects the following aggregate operations for each oid: MAX, MIN, AVG, SUM, and COUNT of tsr

SELECT 
    oid, 
    MAX(tsr.us_depth) AS max_us_depth, 
    AVG(tsr.us_depth) AS avg_us_depth,
    SUM(tsr.us_depth) AS sum_us_depth,
    MIN(tsr.us_depth) AS min_us_depth,
    COUNT(tsr.us_depth) AS count_us_depth,
    MAX(tsr.us_flow) AS max_us_flow, 
    AVG(tsr.us_flow) AS avg_us_flow,
    SUM(tsr.us_flow) AS sum_us_flow,
    MIN(tsr.us_flow) AS min_us_flow,
    COUNT(tsr.us_flow) AS count_us_flow,
    MAX(tsr.ds_depth) AS max_ds_depth, 
    AVG(tsr.ds_depth) AS avg_ds_depth,
    SUM(tsr.ds_depth) AS sum_ds_depth,
    MIN(tsr.ds_depth) AS min_ds_depth,
    COUNT(tsr.ds_depth) AS count_ds_depth,
    MAX(tsr.ds_flow) AS max_ds_flow, 
    AVG(tsr.ds_flow) AS avg_ds_flow,
    SUM(tsr.ds_flow) AS sum_ds_flow,
    MIN(tsr.ds_flow) AS min_ds_flow,
    COUNT(tsr.ds_flow) AS count_ds_flow,
    MAX(tsr.us_froude) AS max_us_froude, 
    AVG(tsr.us_froude) AS avg_us_froude,
    SUM(tsr.us_froude) AS sum_us_froude,
    MIN(tsr.us_froude) AS min_us_froude,
    COUNT(tsr.us_froude) AS count_us_froude,
    MAX(tsr.ds_froude) AS max_ds_froude, 
    AVG(tsr.ds_froude) AS avg_ds_froude,
    SUM(tsr.ds_froude) AS sum_ds_froude,
    MIN(tsr.ds_froude) AS min_ds_froude,
    COUNT(tsr.ds_froude) AS count_ds_froude,
    MAX(tsr.us_vel) AS max_us_vel, 
    AVG(tsr.us_vel) AS avg_us_vel,
    SUM(tsr.us_vel) AS sum_us_vel,
    MIN(tsr.us_vel) AS min_us_vel,
    COUNT(tsr.us_vel) AS count_us_vel,
    MAX(tsr.ds_vel) AS max_ds_vel, 
    AVG(tsr.ds_vel) AS avg_ds_vel,
    SUM(tsr.ds_vel) AS sum_ds_vel,
    MIN(tsr.ds_vel) AS min_ds_vel,
    COUNT(tsr.ds_vel) AS count_ds_vel        

7/ SQL for ICM SWMM Network Link TSR Results

Almost the same SQL can be used for ICM SWMM networks but since SWMM5 has only ONE flow per link the use of US and DS are not needed and some parameters have a different name.

SELECT 
    oid, 
    MAX(tsr.depth) AS max_depth, 
    AVG(tsr.depth) AS avg_depth,
    SUM(tsr.depth) AS sum_depth,
    MIN(tsr.depth) AS min_depth,
    COUNT(tsr.depth) AS count_depth,
    MAX(tsr.flow) AS max_flow, 
    AVG(tsr.flow) AS avg_flow,
    SUM(tsr.flow) AS sum_flow,
    MIN(tsr.flow) AS min_flow,
    COUNT(tsr.flow) AS count_flow,
    MAX(tsr.capacity) AS max_capacity, 
    AVG(tsr.capacity) AS avg_capacity,
    SUM(tsr.capacity) AS sum_capacity,
    MIN(tsr.capacity) AS min_capacity,
    COUNT(tsr.capacity) AS count_capacity,
    MAX(tsr.velocity) AS max_velocity, 
    AVG(tsr.velocity) AS avg_velocity,
    SUM(tsr.velocity) AS sum_velocity,
    MIN(tsr.velocity) AS min_velocity,
    COUNT(tsr.velocity) AS count_velocity        

Closing Note: Thank you so much for journeying with me through this content. This space is reserved for future updates and insights. Your engagement and time are truly appreciated. Until next time! You can also see my past articles on LinkedIn (91 in 2023). The next goal is 133 or 17*19, in FY2025 (which is the calendar year 2024 and the start of 2025 in Autodesk terms).

Why 1729: The factors of 1729 are 1, 7, 13, 19, 91, 133, 247, and 1729 itself. A bit of history about 1729: It's famously known as the Hardy-Ramanujan number after a story involving two great mathematicians, G.H. Hardy and Srinivasa Ramanujan. According to the anecdote, Hardy visited Ramanujan in the hospital and mentioned that he arrived in a taxi numbered 1729, which he found to be a rather uninteresting number. Ramanujan immediately responded that 1729 is actually very interesting because it is the smallest number expressible as the sum of two cubes in two different ways: 1729=1^3+12^3=9^3+10^3. This property makes 1729 a significant figure in the world of mathematics, showcasing Ramanujan's extraordinary intuitive grasp of numbers.