SQL Emojis - 📊 Understanding Array Fields and SQL Functions

 📊 Understanding Array Fields and SQL Functions

Array fields are dynamic data containers often presented in grids on property sheets, representing various details like CCTV survey specifics, manhole survey inlets, and outlets within InfoAsset Manager. Each row in these arrays corresponds to a specific data point within these surveys or objects, akin to the way objects themselves have named fields.

📍 Flag Arrays: Flags associated with an object can also be treated as arrays. They contain two essential fields - "value" and "name" (where "name" is read-only).

SQL provides several ways to access and work with the fields within these arrays.

🔍 1. Detecting Data in the Array: To check if the array contains any data, you can use the function ANY with the array's name followed by '.'. For example, ANY(details.) will return true if there are any records in the array, otherwise, it returns false.

📏 2. Counting Rows in the Array: Counting the number of rows in the array is done using the COUNT function with the array's name followed by '.'. For instance, COUNT(details.) calculates the number of rows in the "details" array.

📊 3. Aggregate Functions: SQL offers various aggregate functions that evaluate an expression for each row in the array and then perform a collective action with all the results. These functions can be applied to numeric, date, and string fields. Some key aggregate functions include:

• ANY: Returns true if the expression is true for any row.
• ALL: Returns true if the expression is true for all rows.
• COUNT: Returns the count of records that satisfy the expression.
• MAX: Retrieves the maximum value from the array.
• MIN: Retrieves the minimum value from the array.
• AVG: Computes the average of non-NULL values.
• FIRST and LAST: Capture values from the first and last records in the array.

📦 Using 'Bare' Array Field Values: You can also use values from array fields outside of aggregate functions in expressions. The expression is evaluated for each record within the array. Keep in mind that when using expressions, it's crucial to precisely define your query, especially when dealing with tests other than equality.

📝 Setting Values in Array Fields: You can set values in array fields by including them in an assignment clause. This allows you to modify data in multiple records at once. The SET clause can be used with or without a WHERE clause, and you can use array fields to specify the conditions under which the SET clause is applied.

🔁 Interaction Between 'Bare' Array Fields and Aggregate Functions: When using array field values in both the WHERE and SET clauses, the aggregate functions in the SET clause are only evaluated for records that meet the conditions specified in the WHERE clause.

For example, if you use the query SET user_number_1 = COUNT(details.*) WHERE details.code = 'DES', it will set user_number_1 to the number of "details" records with the code "DES." The same effect can be achieved with SET user_number_1 = COUNT(details.code='DES').

An alternate way to calculate this without using a 'bare' array field would be SET user_number_1 = SUM(IIF(details.code='DE', details.service_score, 0)).

However, if an aggregate function is used in the WHERE clause, it will be applied to all records within the array field.

🔗 In Summary: SQL's array handling capabilities provide a powerful means to manipulate and analyze complex data structures efficiently. Using aggregate functions and 'bare' array field values wisely can help you unlock the full potential of your data.

🎉 Welcome to ICM InfoWorks! 🎉 -Emoji Version

 🎉 Welcome to ICM InfoWorks! 🎉

Engage with a user interface that’s as intuitive as it is innovative, designed meticulously to mesh well with your thought process and provide an effortless user experience. 🌐🖱️

• Multi-user Version Control System: Manage and track the evolution of your projects with grace and ease, ensuring every change is logged and accessible. The essence of collaborative excellence! 👥🔄
• Full Support for 64-bit Operating Systems (Windows® 10 & 11): Revel in the seamless integration and optimized performance on modern operating systems, enabling you to tackle complex simulations with agility and precision. 🖥️💫
• Multi GIS Platform Support (ArcGIS from ESRI and MapXtreme from Precisely): Expand your horizons by interfacing effortlessly with leading GIS platforms, weaving the spatial magic into your models. 🌍🔗
• Tree View: Navigate the structural hierarchy of your projects in a visually intuitive tree view, providing a clear, organized view of your data. 🌲👀
• Recycle Bin: A safety net for your data, ensuring nothing gets lost in the shuffle. 🗑️🛡️
• Flexible Grouping Structure: Organize your assets and data with a flexible grouping structure, tailoring the organization to your project needs. 🗂️💼
• Enhanced GeoPlan: Dive into an enriched geospatial experience with Enhanced GeoPlan, crafting a spatial narrative for your models. 🌐🖼️
• Powerful Themes Capability: Unleash the aesthetic and functional potential of your models with robust theming capabilities, replete with anti-aliasing and transparency support. 🎨🎭
• Labels: Tag and identify elements with descriptive labels, keeping your models organized and easily understandable. 🏷️💡
• Editing Tools: Sculpt and refine your models with a suite of powerful editing tools, honing the details to perfection. ✂️🛠️
• Property Editor for Network Objects: Delve into the details with a dedicated property editor for network objects, accompanied by in-line validation for real-time feedback. 📝✔️

💧 Long Section: 💧

• Customization (Colors/Fonts): Tailor the appearance of your long section views with customizable colors and fonts, making the visual representation as unique as your project. 🎨✍️
• River Features: Incorporate river features seamlessly, enriching the hydrological context of your models. 🏞️🌊
• 3D Rendering: Explore your models in a whole new dimension with 3D rendering, providing a deeper understanding and visually impactful representation of your data. 🌐🔍

🔄 Tabbed Windows: 🔄

• Group Dockable Windows Together: Organize your workspace by grouping dockable windows together, creating a neat and efficient layout for your projects. 🖥️🔲
• Easy Switching Between Views: Transition between different views effortlessly with a tabbed interface, keeping your workflow smooth and focused. 🔄👓

🌐 3D View: 🌐

• Display of Rivers in 3D: Witness the majestic flow of rivers in a captivating 3D view, adding a layer of realism and understanding to your models. 🏞️🔍

🔧 Admin Functions Without The Need For An Administrator Program: 🔧

• Relish the empowerment of having admin functions at your disposal directly within ICM InfoWorks, streamlining the management process. 🛠️🔄

🔄 Close Integration Between InfoWorks ICM On-premise Databases and InfoAsset Manager Asset Databases: 🔄

• Experience the tight-knit integration between on-premise databases and asset databases, creating a cohesive data ecosystem for your projects. 🗃️🔗

The realm of ICM InfoWorks is a blend of cutting-edge technology and user-centric design, aiming to provide an enriched, efficient, and enjoyable experience as you venture through the challenges and successes of hydraulic modeling and analysis! 🎉🚀

🎯 Improving Pump Simulation in SWMM 5

 🎯 Improving Pump Simulation in SWMM 5

• Goal: Enhance the simulation of variable speed pumps in SWMM 5 for better accuracy.
• Current Setup: 🔄 Users currently adjust three pumps (Pump1, Pump2, and Pump3) based on the depth in the Wet Well. This method relies on three distinct pump curves.
• Proposed Solution: 🧠 Use SWMM 5's Real-Time Control (RTC) feature to simulate pump settings with a single control curve, streamlining the process.
• How it Works:
  • 📊 The pump flow is determined using the Pump Curve (refer to Figure 2).
  • 🛠️ This flow is then fine-tuned according to the Pump Setting (detailed in Figure 3).
  • 💡 Each pump operates with its unique control curve. These curves dictate when the pump activates or deactivates based on the Wet Well's depth (Check Figures 4, 5, and 6 for the respective pumps).
• Benefits: 🌊 This method ensures a more consistent flow (visualized in Figures 7 and 8). Moreover, it offers flexibility to simulate varying pump speeds using a singular pump curve, depending on the active pump.

Conclusion: 🚀 Utilizing the RTC feature in SWMM 5 paves the way for a more precise and streamlined simulation of variable speed pumps with just one foundational pump curve.

🌍 Global Dry Weather Flow Reduction in InfoSWMM 🌍

 🌍 Global Dry Weather Flow Reduction in InfoSWMM 🌍

🌟 Discover a fantastic tip to efficiently reduce the overall dry weather flow in InfoSWMM without altering the mean dry weather flow! 🌟

1️⃣ First, navigate to the Operations tab of the Attribute Browser 🖥️ and craft a Reduction or Increase Flow Pattern 🌀. 2️⃣ For instance, aiming for 85% of the flow? Simply input a value of 0.85 🔢. 3️⃣ Next, head to the Node DWF DB Table 📊 and apply the pattern you just designed 🎨. 4️⃣ Voilà! ✨ Now, during your simulation, all the flows recorded in the DWF table will witness a reduction by a neat 15% 📉.

Harness this tip and optimize your workflow with InfoSWMM! 🚀🌊👩‍💻👨‍💻

🚀 Dive into the Depths of ICM SWMM's Pumping Summary Table! 🚀

 🚀 Dive into the Depths of ICM SWMM's Pumping Summary Table! 🚀

Engage with a meticulously crafted tableau, the "Pumping Summary" table, a mirror reflecting the heartbeat of your hydraulic system's pump performance. The table is a blend of precise metrics and insightful parameters, designed to provide a holistic picture of how each pump fares in the turbulent tides of hydraulic operations. 🌊💡

• Pump Identification 🎛️: Each knight in your pump battalion is named and famed. Know who stands valiant in the face of hydraulic adversities.

• Percent Utilized 📊: Behold the efficiency arena where each pump showcases its utilization prowess, presented as a percentage that sings the tunes of operational efficiency.

• Number of Start-Ups 🔄: Count the calls to arms each pump receives. A metric that whispers tales of operational demand and readiness.

• Flow Metrics (Min, Avg, Max Flow) 📏: Traverse the flow spectrum with the minimum, average, and maximum flow rates, measured in the gallant units of cubic feet per second (CFS). These metrics are the narrators of each pump's flow tales, recounting the chapters of capabilities and limitations.

• Total Volume 🌊: Stand in awe as the total volume of water pumped is unveiled, measured in the grand units of millions of gallons. A testament to the endurance and workload of each pump.

• Power Usage ⚡: Witness the energy saga with the total power consumed by each pump, measured in the steadfast units of kilowatt-hours (Kw-hr). A metric that unveils the veils of energy efficiency.

• % Time Off Pump Curve (Low & High) 📈📉: Delve into the nuances of pump performance with the percentage of time each pump strays off its ideal performance curve, at both the low and high extremes. A key to unlocking insights into potential wear and tear or the whispers of underlying pump performance issues.

ICM SWMM’s "Pumping Summary" table is not just a summary; it's an epic narrating the operational odyssey of each pump in your hydraulic system. It's a blend of numbers and narratives, each parameter is a chapter, and each value is a verse in the saga of your system's hydraulic performance. Engage with it, and let the numbers narrate the tales of pump performance, whispering the secrets to optimizing your hydraulic system's heartbeats! 🌐🎉

🌐 Introduction to InfoSurge 🌐 - Emoji Version

 🌐 Introduction to InfoSurge 🌐

 

Welcome to the realm of InfoSurge, a beacon of light in the turbulent waters of water distribution systems! 🌊 When pressure surges aka waterhammer knock at the doors, the tranquility of the system trembles. These uninvited guests bring along a troupe of troubles - failing pumps, pipe ruptures, and the notorious backflow of dirty water (pathogen intrusion) into the kingdom of clean water! 🚿 Pump startups and shutdowns are like transient storms, leaving trails of increased leakage and a dent in the reliability of our water guardians. 🌪️

A dreaded scenario - uncontrolled pump shutdown (downsurge) may summon the dreaded water-column separation, a precursor to catastrophic pipeline failures due to the ensuing severe pressure rises following the collapse of the vapor cavity. 💥

 

🚀 Rise of InfoSurge 🚀

 

Fear not, for the InfoSurge is here, a result of meticulous efforts to craft a comprehensive modeling application. It stands as a vigilant knight, ready to be wielded by engineers and scientists to combat the transient operation demons haunting the pipe systems. 🛡️ Vapor cavitation and liquid column separation, the dark forces, are explicitly modeled, thus letting the pressure surges due to vapor cavity collapse be evaluated rightfully. 💫 This mighty program is designed to quickly and accurately compute pressures and flows throughout the piping kingdom during transient operations, and evaluate alternate operational scenarios and surge protection schemes like a wise sage. 🧙

 

💫 Journey into the Abyss of Hydraulic Transients 💫

 

Hydraulic transients are akin to disturbances in the peaceful fluid realm, caused during the transition from one steady-state to another. 🔄 The principal components of these disturbances are pressure changes triggered by the propagation of pressure waves throughout the distribution system. 🌊 These pressure waves, akin to messages from the deep, continue to travel with the velocity of sound until they are dissipated down to the level of the new steady-state by the action of damping or friction forces. 🎶 Only if the flow is regulated with a gentle touch, it's possible to transition smoothly from one steady-state to another without inviting the wrath of large fluctuations in pressure head or pipe velocity. 🔄

 

🌪️ The Five Horsemen of Disturbances 🌪️

 

Disturbances in the water realm can be stirred by:

1. Pump startup or shutdown: The awakening or the slumber of the pump giants! 🎛️
2. Valve opening or closing (variation in flow area): The gatekeepers’ dance! 🕺
3. Changes in boundary pressures (reservoir level, pressure tank, etc.): The shifting sands of pressure landscapes! 🏜️
4. Rapid changes in demand conditions (e.g., hydrant flushing): The thirst of the land! 🚰
5. Changes in transmission conditions (e.g., pipe break): The breaking point! 💔

These disturbances can morph into serious tsunamis for water utilities if not recognized and addressed with proper analysis and design considerations. 🌊 Our hydraulic systems must be fortified castles, designed to endure both normal and abnormal operations, and shielded to withstand adversities like power failure, pipeline fracture, and more. 🏰

 

🛡️ The Arsenal of Transient Mitigation 🛡️

 

Here are the three sacred design considerations to ward off transient conditions:

1. Alteration of pipeline characteristics (e.g., pipe diameter): Shaping the veins of our water body! 🔄
2. Improvement in valve and pump control procedures: Mastering the rhythm of flow! 🎶
3. Design and installation of surge protection devices: Crafting the shields against surges! 🛡️

InfoSurge unveils a treasure chest of devices to control pressure and flow transients, including but not limited to open surge tanks, closed surge vessels, bladder tanks, hybrid tanks, bypass lines, check valves, feed tanks, air release/vacuum valves, pressure relief valves, and surge anticipation valves. 🗝️

 

🌌 Embark on a Quest with InfoSurge 🌌

 

InfoSurge is your loyal companion on a quest to explore your water distribution system's response to pump station power failures, valve closures, pump speed changes, and more! 🌌 It helps you devise special protection measures to mend the leaks, evade breaks, scrutinize control actions and strategies, and enhance the water quality in your kingdom. 🏞️

InfoSurge is not a replacement for the wise counsel of good engineering judgment. It's a tool forged to assist water utilities in navigating the stormy seas of transient conditions. 🧭 It won’t make design or operating decisions but will provide precious insights, which if harnessed and interpreted rightly, will empower utilities in making informed decisions. 🗝️ For any queries or guidance, the noble InfoSurge support team is at your beck and call! 📞

 

Our relentless quest for excellence in transient flow analysis is a tale of high-level, state-of-the-art research, and development. We are on a voyage to continually refine InfoSurge, reflecting the strides of progress, and are elated to pioneer this sophisticated computer modeling technology, advancing it to unparalleled levels of reliability, versatility, ease of use, and performance. 🚀 Your journey with InfoSurge is bound to be an adventure filled with discoveries and triumphs in the realm of transient flow analysis! 🎉

Suggestion for Mike Urban Import to ICM InfoWorks

 Question on LI that I thought you could use about Mike Urban to ICM: 🤔 Could you suggest how we might convert a Mike Urban model into InfoWorks ICM? I've considered converting it to MOUSE first and then importing it into ICM. However, this method seems to omit many attributes when importing into ICM.

Answer: 🌟 Great query! Did you know that ICM can directly import MEX, UND, and HGF files from Mike Urban? These files are all in CSV format. 📁 What I typically do is tweak the MEX file, creating two versions: UND for the network 🌐 and HGF for catchments 🏞️. Here's a little secret: while both files contain similar data, ICM selectively reads specific sections from the UND and others from the HGF. 🔍

One challenge you've pinpointed is that ICM interprets the 2010 version of Mike Urban. But, the silver lining here is that since these files are in CSV format, you can harness the power of ICM's Open Data Import Center (ODIC)🚀 to integrate the omitted data from the CSVs. You simply load a CSV file and map the Mike Urban data to the corresponding ICM data. 🗺️

So, here's my two-fold recommendation: First, use ICM's import function 🔄, and then leverage the ODIC to incorporate any missing data. I sincerely hope this strategy streamlines your process! ✨

UND for the network 🌐 and HGF for catchments in the Import Dialog