SQL Example: Using a background polygon to summarise asset information or simulation results

Recently I had a query asking if it was possible to summarise information based on a polygon loaded as a background layer. The specific information they wanted to summarise was the average pressure from a water supply network in InfoWorks WS. Here are the steps to do this.

  • Open the results that you want to analyse
  • Load in the background layer
  • Create a new SQL Group
  • Create a new SQL within this group and name it “01…” Maybe something like “01 Set Polygon ID”. This SQL will be used to set a temporary field for each customer point saying which polygon they lie within. This assumes that your polygon boundaries are accurate and you do not have any customer points that lie outside of the polygon boundaries or are in the incorrect one. It is run on the customer point table. It is important to set the GIS search Type to Inside and select the layer that contains the polygons. The first row sets a temporary field $polyID that stored which polygons the customer point is in, this is equal to the correct field in the GIS layer. The second line will just produce a table with all of the customer points and the value that has been set for $polyID. This is useful for testing but you can remove it when you are happy with the SQL group. The SQL you will need for this is shown below;

  • Create another new SQL within the same group and name it “02…”. Maybe something like “02 Average Pressure” This SQL will calculate the average pressure for the customer points in each polygon. Again it is run on the Customer Point table and will create a grid with the average average pressure for the customer points this is then grouped by the $polyID field. You could also include min and max pressure if you wanted to.

  • To produce the results you will need to drag the SQL group on to the Geoplan and the SQL’s will be run in alphabetical order, hence why the names are important.
Share this post!

    About Emily Stevens

    Emily Stevens is a Product Sector Leader with Innovyze in the United Kingdom, specialising in clean water modelling and operational modelling for water supply networks. She has 13 years experience in the water industry in the UK, and New Zealand. She is one of the key members of staff involved with the adoption and use of IWLive.
    This entry was posted in InfoNet, InfoWorks CS, InfoWorks ICM, InfoWorks RS, InfoWorks SD, InfoWorks WS and tagged . Bookmark the permalink.