Emojis - SQL in ICM InfoWorks - 5 Assignment 📝

 5 Assignment 📝

As well as selecting ✅, deselecting ❎, and deleting 🗑️ objects, it is possible to use SQL to set values for fields in objects in the InfoWorks ICM networks 🌐. If you wish to set values for fields for objects in the default table 📊, this may be done with the SET 🛠️ keyword.

It is possible but not necessary to have a WHERE 📍 sub-clause. As with selection, deselection, and deletion, values will either be set for all objects or only for selected objects depending on the setting of the check-box ☑️.

If you do not have a WHERE 📍 sub-clause then the assignment will be done for all objects or all selected objects, otherwise it will only be performed for the objects for which the WHERE 📍 sub-clause is true.

It is possible to perform more than one assignment at once by separating them with commas.

Examples:

SET user_number_1 = 123 🔢 SET user_number_2 = user_number_1 / width ➗

SET user_number_1 = 123 WHERE x > 10000 AND y > 12000 🔍

SET user_number_1 = x, user_number_2 = y 🔄

SET user_text_1 = 'XXX' + node_id, user_text_2 = asset_id WHERE width > 200 📄 When you have more than one assignment in a clause separated by commas, they are performed from left to right, so it is safe to assume that the value of a field in one assignment will be the value that it would be after performing the previous assignments in that clause.

It is possible to override the default table and the default selection behaviour. In both cases, this is achieved by using the UPDATE 🔄 keyword.

To override the default table, the clause should begin with UPDATE 🔄 followed by the table name followed by the keyword SET 🛠️ and the rest of the clause as above e.g.

UPDATE [All Links] SET asset_id = '' 🔗

UPDATE [All Links] SET asset_id = '', user_number_1 = 0.0 WHERE width > 200 🔗

To override the default selection behaviour, begin the clause with UPDATE ALL 🔄 or UPDATE SELECTED 🔄 as appropriate.

To override both, these two mechanisms should be combined e.g.

UPDATE SELECTED Node SET user_number_1 = 123.45 🔄

It is possible to set fields to the NULL value described above e.g.

SET ground_level = NULL ❓

Ruby for CSV file Import using ODIC in WS Pro

 # Reference to the GitHub script: https://github.com/modelcreate/infoworks-ruby-scripts/blob/master/scripts/open_data_import_centre/import_model.rb

# Import the necessary library for file utilities

require 'FileUtils'

# Get the current open network in InfoWorks

open_net = WSApplication.current_network

# Prompt the user to select a folder for importing files

shp_dir = WSApplication.folder_dialog 'Select a folder to import files', true 

# The configuration (CFG) files are kept in the same directory as this Ruby script

cfg_dir = File.dirname(WSApplication.script_file) 

# Define paths for the error file and the script file

err_file = shp_dir + '\errors.txt'

script_file = cfg_dir + '\odic_script.bas'

# Define the layers and their respective configuration and shape file paths

layers = {

    "hydrant" => { "cfg" => cfg_dir + '\hydrant.cfg', "shp" => shp_dir + '\hydrants.shp'},

    "valve" => { "cfg" => cfg_dir + '\valve.cfg', "shp" => shp_dir + '\valves.shp'},

    "polygons" => { "cfg" => cfg_dir + '\polygons.cfg',  "shp" => shp_dir + '\dma.shp' },

    "pipe" => { "cfg" => cfg_dir + '\pipe.cfg', "shp" => shp_dir + '\mains.shp' }

}

# Iterate through each layer and import the data

layers.each do |layer, config|

    puts "Importing IW layer: #{layer}"

    # Skip the import if the shape file doesn't exist

    if File.exist?(config["shp"]) == false

        puts "Shape file not found for #{layer} layer - #{config["shp"]}"

        next

    end

    # Define additional options for the import

    options = {

        "Error File" => err_file,    # Location to save error logs

        "Set Value Flag" => 'IG',   # Import Flag

        "Script File"=> script_file # Import Script (ICM .rb / WS .bas)

    }

    # Execute the import function

    open_net.odic_import_ex(

        'shp',            # Source Type: Shapefile

        config["cfg"],    # Field mapping configuration

        options,          # Additional options in Ruby Hash format

        layer,            # InfoWorks Layer to import to

        config["shp"]     # Location of the shape file

    )

    # Append the error log with the completion status of each layer

    File.write(err_file, "\n End of #{layer} import \n", File.size(err_file))

end

# Read and output the content of the error log to the console

File.readlines(err_file).each do |line|

    puts line

end

# Print a completion message

puts "Finished Import"

Emojis - SQL in ICM InfoWorks - 📋1 Basic Object Selection 📋

 1 Basic Object Selection 📋

1.1 Introduction to basic arithmetic and comparison 🔢 SQL can be used in its simplest form to select objects for which an expression holds true. This section focuses on basic arithmetic and string comparisons. Arithmetic operators available are:

• ➕ : Addition

• ➖ : Subtraction

• ✖️ : Multiplication / ➗ : Division ^ 📈 : Exponent (e.g. width^2, area^0.5) % 🌀 : Modulus

A 'unary minus' ➖ is used to negate a number. These operators follow the standard precedence rules.

Strings can also be joined using the '+' operator. For instance, combining 'X12' with 'Y34' yields 'X12Y34'. However, other operators don't hold special meaning for strings.

Comparison operators include:

= ✅ : Equality

= ↗️ : Greater than or equal to ⬆️ : Strictly greater than <= ↘️ : Less than or equal to < ⬇️ : Strictly less than <> ❌ : Not equal to

Comparison operators have a higher precedence than arithmetic ones.

In InfoWorks ICM, string comparison is case insensitive. When using comparison operators, behavior depends on the language set in Windows. Strings are always trimmed of leading or trailing spaces or 'tab' characters.

Fields and constants are the primary elements manipulated using arithmetic operators. Fields are categorized into:

Current table fields (e.g., node_id, x, y) Results fields (e.g., sim) Joined table fields (either one-to-one or one-to-many links) Structure blob fields

Constants are of five types: Numbers, Strings, Boolean, Dates, and NULL.

1.2 NULL field values and associated operators ❓ In InfoWorks ICM, numerical fields can be blank, represented as 'NULL' in SQL. Fields can be checked for NULL values using 'IS NULL' or 'IS NOT NULL'.

1.3 Logical operators 🔄 Expressions can be combined using logical operators AND, OR, and NOT.

1.4 String operators 🧵 LIKE and MATCHES are operators used for string tests. LIKE uses special characters '?' and '*' for matching, whereas MATCHES uses regular expressions.

1.5 Data types and type conversion rules 🔄 1.5.1 Data Types 📊 InfoWorks ICM databases contain several data types including numerical types, string, Boolean, and date.

1.5.2 Mixing Data Types in Binary Operators 🔄 Binary operators like +, -, *, and AND have rules when mixing data types.

1.5.3 Conversion to strings 🔄 Conversions include turning Boolean values to '1' (true) or '0' (false), numbers to strings, and dates to strings.

1.5.4 Conversion to Boolean 🔄 Strings with characters, dates, and non-zero numbers are considered true.

1.5.5 Conversion to numbers 🔢 Dates, strings, and Boolean values have specific rules for conversion to numbers.

1.5.6 Unary operators ➖ Unary operators include unary minus and NOT.

1.5.7 Determining which objects to select based on SQL expressions ✅ Objects are selected based on the evaluation of the SQL expression.

1.6 Simulation Results 📊 InfoWorks ICM allows running SQL queries incorporating simulation results.

1.7 One to one links 🔗 It's possible to include values for linked objects in SQL expressions. In InfoWorks ICM, this is usually done automatically through "implicit joins".

The table showcases the implicit one-to-one joins within the InfoWorks ICM Network:

From 📌 To 📍 Name 🏷️ CCTV Conduit 📺 Conduit 🚿 joined Link 🔗 Node 📌 us_node Link 🔗 Node 📌 ds_node Subcatchment 🌧️ Node 📌 node Subcatchment 🌧️ Lateral link ➡️ drains_to

This formatted structure provides a clearer understanding and easier navigation through the content. 📘

Emojis - SQL in ICM InfoWorks - 📥4 Insertion 📥

 4 Insertion 📥

It is possible to insert objects into the network and insert rows into blobs. 🗃️

To add individual objects the syntax is: 📝

INSERT INTO📋<table name> (field1, field 2,... fieldn) VALUES (val1,val2,... valn) 🖊️

INSERT INTO node (node_id,x,y) VALUES ('N1', 123, 456) 📍

The number of values must match the number of fields. All the values can be scalar expressions e.g. scalar variables, expressing including scalar variables. 📈

To add individual rows into an individual object's structured blob the syntax is: 📜

INSERT INTO 📋<table name>.<blob name> (field1,field,2... fieldn) VALUES (val1,val2,... valn) 📌

The field names must be either: 🔍

• a key field name of the table 🔑
• the array name followed by a . followed by a field in the blob 📊

All key field names of the table must be specified and objects into which lines in the structure blob are being inserted must exist. 📁

To insert values into a table from a SELECT statement, the syntax is: 📖

INSERT INTO 📋<table name> (field1,field2,... fieldn) SELECT <select statement> 📚

To insert values into a structure blob from a SELECT statement the syntax is: 📘

INSERT INTO 📋<table name>.<blob name>(field1,field2,... fieldn) SELECT <select statement> 📙

As with the values insert statement, the field names must be either:

• a key field name of the table 🔑
• the array name followed by a . followed by a field in the blob 📊

All key field names of the table must be specified and the objects into which lines are being inserted must exist. 📁

The number of items being selected must match the number of fields being set in the INSERT. ✅

The select statement can include WHERE and ORDER BY but not GROUP BY or HAVING and can include selection of SCENARIO, TOP and BOTTOM and SELECTED etc. 🔄

It is possible to add objects to a scenario by adding the scenario details after the list of fields e.g. 🎬

INSERT INTO node(node_id,x,y) IN BASE SCENARIO VALUES ('N2',3,4) 🎥

INSERT INTO node(node_id,x,y) IN SCENARIO 'SC1' VALUES ('N3,4,5) 📽️

5 Assignment 🖋️ As well as selecting, deselecting and deleting objects, it is possible to use SQL to set values for fields in objects in the InfoWorks ICM networks. If you wish to set values for fields for objects in the default table, this may be done with the SET keyword. 🛠️

It is possible but not necessary to have a WHERE sub-clause. As with selection, deselection and deletion, values will either be set for all objects or only for selected objects depending on the setting of the check-box. ✅❌

If you do not have a WHERE sub-clause then the assignment will be done for all objects or all selected objects, otherwise it will only be performed for the objects for which the WHERE sub-clause is true. 🌟

It is possible to perform more than one assignment at once by separating them with commas. 🖍️

Examples:

SET user_number_1 = 123 💼 SET user_number_2 = user_number_1 / width 📏

SET user_number_1 = 123 WHERE x > 10000 AND y > 12000 🗺️

SET user_number_1 = x, user_number_2 = y 🧮

SET user_text_1 = 'XXX'+node_id, user_text_2 = asset_id WHERE width > 200 🎨 When you have more than one assignment in a clause separated by commas they are performed from left to right, so it is safe to assume that the value of a field in one assignment will be the value that it would be after performing the previous assignments in that clause. ➡️

It is possible to override the default table and the default selection behaviour. In both cases this is achieved by using the UPDATE keyword. 🔄

To override the default table, the clause should begin with UPDATE followed by the table name followed by the keyword SET and the rest of the clause as above e.g. 🔄📋

UPDATE [All Links] SET asset_id = '' 📌

UPDATE [All Links] SET asset_id = '', user_number_1 = 0.0 WHERE width > 200 📐

To override the default selection behaviour, begin the clause with UPDATE ALL or UPDATE SELECTED as appropriate. 🌐

To override both these two mechanisms should be combined e.g. 🔄➕

UPDATE SELECTED Node SET user_number_1 = 123.45 🖍️

It is possible to set fields to the NULL value described above e.g. ❓

SET ground_level = NULL ❌