Categorizing Conduit Lengths Using Bucketing in SQL for ICM InfoWorks and SWMM Networks

Analyzing Conduit Characteristics with InfoWorks ICM

As a hydraulic modeling professional, one of the key tasks is to understand the characteristics of the conduit network in our models. InfoWorks ICM's powerful SQL capabilities allow you and me to perform in-depth analyses and categorize conduits based on various attributes.

In this post, I share a SQL script that groups conduits into buckets based on their length, width, upstream invert level, capacity, and gradient. This analysis can provide valuable insights for network optimization, maintenance planning, and design validation.

By leveraging the RINDEX function and user-defined list variables, we can categorize conduits into meaningful buckets based on their attributes. The TITLE function ensures that the grouped results display the actual bucket values instead of indexes, enhancing readability. Note - all of this is on our open source GitHub which you can find using this ICM Hub link.

1. Define the Buckets (in meters, cms or mm)

This line defines a list of buckets in the appropriate units (you need to know the units from the ICM Interface). The script will categorize the conduit lengths based on these predefined buckets.

LIST $buckets =  0, 100, 200, 300, 400, 500, 600, 700, 800, 900, 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000, 10000;

LIST $bucketsmm =  0, 100, 200, 300, 400, 500, 600, 700, 800, 900, 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000, 10000;

LIST $bucketsinv =  1, 10, 100, 250, 500, 1000;

LIST $bucketscap=  0, 0.1, 0.5, 1, 2, 5, 10, 20;

LIST $bucketsgrad =  0, 0.01, 0.05, 0.1, 0.2, 0.5, 1, 2, 5, 10, 20, 50, 100;         

2. Query for Bucket Categorization with SQL RINDEX

SELECT count(*) AS 'Count' GROUP BY TITLE(RINDEX(conduit_length, $buckets), $buckets) AS 'Length (m)';

SELECT count(*) AS 'Count' GROUP BY TITLE(RINDEX(conduit_width, $bucketsmm), $bucketsmm) AS 'Width (mm)';

SELECT count(*) AS 'Count' GROUP BY TITLE(RINDEX(us_invert, $bucketsinv), $bucketsinv) AS 'US Invert (m)';

SELECT count(*) AS 'Count' GROUP BY TITLE(RINDEX(capacity, $bucketscap), $bucketscap) AS 'Capacity';

SELECT count(*) AS 'Count' GROUP BY TITLE(RINDEX(gradient, $bucketsgrad), $bucketsgrad) AS 'Gradient';        

count(*): This function counts the number of conduits in each length bucket.

• GROUP BY: This clause groups the conduit lengths based on the defined buckets.
• TITLE(RINDEX(conduit_length etc, $buckets), $buckets): This expression categorizes the conduit_length values into the appropriate length buckets using the RINDEX function, which returns the index of the largest bucket value that is less than or equal to the conduit_length. The TITLE function is used to display the bucket values as the column header.
• AS 'Length (meters)': This aliases the grouped column with a user-friendly name, "Length (meters)"

3. What it looks like in the SQL ICM Interface

4. All of the SQL code together

/* 
// Object Type: All Links
// Spatial Search: blank
*/

LIST $buckets =  0, 100, 200, 300, 400, 500, 600, 700, 800, 900, 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000, 10000;
SELECT count(*) AS 'Count' GROUP BY TITLE(RINDEX(conduit_length, $buckets), $buckets) AS 'Length (m)';

LIST $bucketsmm =  0, 100, 200, 300, 400, 500, 600, 700, 800, 900, 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000, 10000;
SELECT count(*) AS 'Count' GROUP BY TITLE(RINDEX(conduit_width, $bucketsmm), $bucketsmm) AS 'Width (mm)';

LIST $bucketsinv =  1, 10, 100, 250, 500, 1000;
SELECT count(*) AS 'Count' GROUP BY TITLE(RINDEX(us_invert, $bucketsinv), $bucketsinv) AS 'US Invert (m)';

LIST $bucketscap=  0, 0.1, 0.5, 1, 2, 5, 10, 20;
SELECT count(*) AS 'Count' GROUP BY TITLE(RINDEX(capacity, $bucketscap), $bucketscap) AS 'Capacity';

LIST $bucketsgrad =  0, 0.01, 0.05, 0.1, 0.2, 0.5, 1, 2, 5, 10, 20, 50, 100;
SELECT count(*) AS 'Count' GROUP BY TITLE(RINDEX(gradient, $bucketsgrad), $bucketsgrad) AS 'Gradient';        

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 calendar year 2024 and the start of 2025 in Autodesk terms).

The articles form the backbone of the newsletter. Seven articles make up One Newsletter edition. There will be a summary edition once 19 editions are published, or approximately every 133 articles. The far reaching goal is 1729 articles, 247 editions, and 20 summary editions.

Why 1729: The number 1729 has 8 factors, which 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.