InfoNet SQL: Counting The Connection Nodes on a Pipe

Question: How many connection nodes are attached to my pipe?
Answer: Use an SQL.

CCTV Surveys encode the location of lateral pipe connections, in the UK, the MSCC standard has codes CN (Connection), JN (Junction) etc. (image below).

UK's Standard MSCC CCTV Codes

UK’s Standard MSCC CCTV Codes

Other standards use codes which differ from those used in the UK. Irrespective of this it is possible to use these to create lateral connection nodes and connection pipes using built in InfoNet functionality.

Update Network From CCTV Data

Update Network From CCTV Data

The lateral nodes, once created, can be viewed in a pipe’s property dialog box.

Pipe Property Dialog Box - Lateral Nodes

Pipe Property Dialog Box – Lateral Nodes

The following SQL will generate a table showing the Pipe ID against the number of lateral connections nodes associated with that pipe. The pipes are then ordered by number of lateral connections nodes on the pipe in descending order.

SQL - Connection Nodes on Pipes

SQL – Connection Nodes on Pipes

SET lateral_pipe.$count = 0;
SET lateral_pipe.$count = lateral_pipe.$count + 1;
SELECT DISTINCT(lateral_pipe.us_node_id + ‘.’ + lateral_pipe.ds_node_id + ‘.’ + lateral_pipe.link_suffix) AS ‘Pipe ID’, lateral_pipe.$count AS ‘Number Of Connections’ ORDER BY lateral_pipe.$count DESC

Note that the default object in the SQL is ‘connection node’ which may seem an odd starting point from which to begin since the query we require is on the pipe object. The reason for this is that there isn’t an inbuilt association between pipes to connection nodes. Admittedly were this present then the SQL would be simpler. All this means is that our challenge is that much more interesting.

Let’s begin by looking at the first couple of lines of the SQL:

SET lateral_pipe.$count = 0;
SET lateral_pipe.$count = lateral_pipe.$count + 1;

We create a variable ($count) on the connection node’s associated pipe and in the first line and initiate it with a value zero. The second line iterates through every lateral node and adds the value one to this variable on the pipe. Therefore each pipe now has a temporary variable that holds the number of lateral connection nodes attached to it.

The following line creates a table of pipes and the number of connection nodes on the pipes showing the pipe with most first and then in descending order.

SELECT DISTINCT(lateral_pipe.us_node_id + ‘.’ + lateral_pipe.ds_node_id + ‘.’ + lateral_pipe.link_suffix) AS ‘Pipe ID’, lateral_pipe.$count AS ‘Number Of Connections’ ORDER BY lateral_pipe.$count DESC

The trick here is to use DISTINCT keyword, if this isn’t used then what is returned is a table displaying the pipes repeated the same number of times for the same number of connection nodes associated with it, which clearly isn’t what we want.

Share this post!

    About Idris Nujjoo

    Idris is the Product Sector Leader for InfoNet and InfoMaster and has been working for Innovyze (and its earlier incarnations) since 2001 and specifically working on InfoNet and Asset Management for the past decade.
    This entry was posted in InfoNet. Bookmark the permalink.