GalenaCraftpine Inc.

Kitchener service lines with no recorded material.

The City of Kitchener publishes its water service pipes as open data. Most of the records carry the code for unknown material. This sheet maps those records, draws a random sample for crews to check, reads their results back into an estimate, and turns the estimate into a replacement programme. It all runs in this browser.

--Active service records with no material--

  1. 1 RecordsWhere the unknown lines are
  2. 2 SampleWhich lines crews check
  3. 3 FieldWhat crews found
  4. 4 EstimateHow many are lead
  5. 5 ProgrammeWhen replacement ends
Fig. 1

Survey plan, City of Kitchener, service line records by cell

List cells →Plan

The plan opens at 1024 px and wider. Fig. 7 carries the same data.

Grid UTM 17N, EPSG:26917

City of Kitchener open data

Fig. 1. Service line records of the City of Kitchener, counted in H3 cells.

    Fig. 2

    Active service lines by install decade

    Fig. 2. Active lines by the decade of the install date in the record.

      Fig. 3

      Sample for field checks

      Step 2

      Crews cannot dig every unknown line. A random sample tells you, with a stated margin, how many of them are lead. Every line in the pool has the same chance of being picked, and the same seed always draws the same lines.

      Jurisdiction

      Pool

      Write the seed down. With the same records and seed, anyone can repeat the draw.

      Fig. 3. The sample design.

        Fig. 4

        Field results

        Step 3

        Record what the crew found at each pick. If a pick cannot be reached, mark it so and check the next spare in order. Never swap in a neighbouring line, because that breaks the random draw.

        Share with crews

        The file has the pick order, the line id, the cell and blank columns for the result, the check method and the date.

        A CSV with the columns id and result. Results read: lead, galvanized, not lead or not reached. Method and date are kept when present.

        Try it without a crew
        %

        Simulated results are made up from the share you set. They are marked as simulated everywhere they appear. They say nothing about Kitchener's pipes.

        Fig. 4. Picks in draw order, then the spares.

        Fig. 5

        Estimate from the field results

        Step 4

        Fig. 5. The share of checked picks that were lead, with its 95 % interval, and the count it implies for the pool.

          Fig. 6

          Replacement programme

          Step 5

          Fig. 6. The year replacement ends at the pace set above, for the estimate and for each end of its interval.

            Fig. 7

            Cells on the plan, by lines with no recorded material

            Schedule 1

            Fig. 7. Every cell drawn on the plan in Fig. 1.

            Fig. 8

            Material codes in the layer

            Schedule 2

            Fig. 8. Every material code in the City of Kitchener layer.

              1Notes

              Each service line is placed at the average of its mapped points. Lines are counted in H3 cells at resolution 8, about 0.7 square kilometres each. Only lines the city marks active are counted. A cell with fewer than ten lines is left out so that small clusters are not shown, and the note under Fig. 1 gives what was left out.

              No material means the record carries the city's unknown code or no code at all. A material code is the city's own record. Nobody checked these pipes in the field for this sheet, and the sheet makes no prediction about any line.

              The sample is a simple random sample. Every line in the pool gets the key SHA-256 of the seed, a colon and its id, and the lines with the lowest keys are picked. Each pick's chance of selection is the sample size over the pool size. The sample size follows the table in the United States rule at 40 CFR 141.84(b)(5)(ii). In Canada no rule sets a size, so the same table is used as a statistical design.

              The estimate counts checked picks only. A pick that was not reached is left out of the count and reported beside it. The interval is the Wilson score interval at 95 %. The count for the pool is the pool size times the share, and the same for each end of the interval.

              Line ids on this sheet are hashed. Each is "k" and 16 letters: the first 16 hex digits of the SHA-256 of the text "kitchener-water-service-pipes:", the layer's last edit time, a colon, the city's asset id, a colon and a count of earlier rows with the same asset id, with the digits 0 to f written as the letters a to p. Anyone with the public layer can match a pick back to its record. No address, owner, parcel or coordinate is on this sheet. A utility running Galena on its own records gets its own line ids in the sample file.

              Roads, water, rail, parks, municipal boundaries and water mains of 300 mm and larger come from the city's own open data and are drawn for context.

              Field results you enter stay in this browser. Nothing is sent anywhere. Download them as a file to keep or share them.