The process of splitting an authority’s gazetteer in Cumbria

The process of splitting an authority is an enormous change for local authorities, and often causes a number of issues. Although it is encouraged that authorities involve their software suppliers from the outset to enable the smoothest possible transition, most often they are only involved towards the end of the process. This leaves software suppliers frantically trying to come up with a solution at the last minute. The most complicated instance is where you are splitting an authority into two, as in this case.

However, this process was efficient from the outset, with the combined efforts of both authority and software supplier creating a smooth and effective process that resulted in a successful split.

The Process

This process initially started where the software supplier and the Local Street Gazetteer (LSG) custodian began exploring options for the authority split at the beginning of July in 2022. Evidently, the broad span of time to plan an effective split before then undertaking it in April 2023 evidences the value in this planning time.

Initially, the software supplier created a polygon officially outlining the new defined areas using existing district polygons. Within this image, Carlisle, Allerdale and Copeland are encompassed by Cumberland, and Eden, South-Lakeland and Barrow-in-Furness combined to be Westmorland and Furness. These boundary lines are one of the most important steps in the process of dividing an authority in two and must not be left out. In being completed so successfully early within this case, the process was set up with the best chances of success from the start.

The next step was asking the software supplier to create a spatial query to display Street Type 1 & Type 2 ESUs that intersect these divisions to identify which streets are going to be affected. The 47 ESUs that ran through these cross boundaries were then displayed across a mapping layer of information for the LSG custodian to work from. The value in running this query as an automatic process will have saved hours of time to undertake this process manually whilst also providing a greater level of accuracy. This area of information gave the LSG custodian the toolset to then contact the relevant district address custodians, to initiate the process of determining new start/end co-ordinates for the USRNs within the new authority boundaries.

Discussions were undertaken as to how to efficiently split these datapoints. For instance, the custodian provided the boundary break information to neighbouring Carlisle and requested that they extend their USRN to a particular grid reference in order to meet the boundary. Likewise, the request was sent to Eden for them to adjust their gazetteer data to meet at the boundary. The majority of these instances were between Eden and Carlisle, and Eden and Allerdale. As such, this process valued the effective communication between not only the software supplier and the LSG custodian, but also the individual custodians that would then have to make these changes.

As a result of these discussions, changes were able to be made to both gazetteers and the Cumbria LSG. As well as this, new Type 3 USRNs were created and thus cross-referenced for each authority against relevant ESUs and street numbers.

When making these alterations to the data ahead of the authority split, the LSG custodian created new Type 3 streets for the main highways through the boundary split; in this instance this was the M6 and A66. Initially, the town name within the Type 3 records were under towns in Westmorland, whereas the new town names were listed under Cumberland towns. These also took into account the rules for Motorway sections. Any anomalies in terms of short sections around splitter islands and junctions were discussed with GeoPlace as to how they should be altered. It was decided these would be submitted as exceptions.

Following this, the software supplier then refreshed the LSG development environment, and began to split the authority data. In order to set which authority a record would be designated to, a new field was created. This field was ‘new_authority_uid’, and was added to a database table common to all LSG records. This would then be used to aid in the removal of authority data from each new system.

Early attempts at splitting the authority data were based on those records that crossed the boundary and intersected the polygons. It came as a suggestion from the custodian that the split come from the constructions record operational district field. This meant that any operational district field that fell under Carlisle, Allerdale or Copeland in the construction records would be deemed as Cumberland in the new field. This same logic was then applied to the Westmorland and Furness operational districts. This base of construction records provided a broader base to begin fitting other LSG records, including the ability to cross reference any Type 1 or Type 2 records on the already fitted construction records. This then also applies to Type 1 & 2 streets records, and Type 3 & 4 records which were fitted based on cross referenced ESUs. As a result, this produces a database column against each LSG record with a unique identifier for either Cumberland or Westmorland.

The correct identification for the USRN designation in each authority is important as the LSG is used for funding allocations. Having the correct data is crucial to receiving the correct funding.

Next, the software supplier created a Structured Query Language (SQL) script for the action of dividing the authorities. This script would essentially alter two copies of the previous Cumbria LSG into two new LSG databases for the new authorities. For this script to work efficiently, it needed to encompass a number of elements:

1. 1. Table changes needed to be configured. An example of this would be wherein any table including the naming ‘Cumbria’ or any relevant URL would need to be changed
2. 2. The main content of the script would need to remove the irrelevant LSG records for that newly formed authority; this includes any data belonging to the other side of the split. In order to achieve this, the new field previously created would be implemented in the table common to each LSG record, and a query was created to drop records from each LSG table. This field would thus be the last to have data removed
3. 3. Another consideration were lookup values. Lookups such as locality, town and authority could be removed if no street within the database is using the value
4. 4. Lastly, authority fields was needed to be updated the to the relevant value for type 15, 11 ,61, 62, 63, 64, and 66 records.

Here is an example of an ESU split based on construction records dated 23 August 2022.

Once there was a confident belief in this fitting process, the field was added to the production of the Cumbria LSG. The software supplier was then able to build the two new LSGs of the split authorities, completing numerous test runs of the splitting process. This consisted of running and tweaking scripts where appropriate, and establishing where the faults lay whether they were within the data or within the scripts themselves.

There were a few instances where it became apparent the construction operational district was incorrect and this was corrected by the LSG custodian. The software supplier refreshed the databases and tried these instances again. Eventually, there was then a comparison between a combined count from both new authority databases. So, the number of Type 15 records held within both Cumberland and Westmorland combined should match the initial total of Type 15 records held within the Cumbria LSG.

One element that was not initially considered but proved to be a challenge once the databases were split were NC17 Review Errors (streets split at former boundaries of merged authorities). These were identified after the initial successful live submissions for the new authorities, and were easily resolved after discussions amongst the custodians. The appropriate URSNs were retained, and the remaining were set with the Street State ‘permanently closed’, with the relevant ESUs moved to the live USRNs.

Summary

The success of split from Cumbria to Cumberland and Westmorland and Furness can be attributed not only to the hard work of those involved and high data quality, but also in the effective communication and planning across the teams involved.

In the collaborative efforts to divide these databases, GeoPlace, the LSG custodian, the software supplier and the district address custodians all play a vital role in the efficiency of this process. Decisions were able to be made quickly and collaboratively, leading to minimal disruptions or roadblocks. The extended timeframe enabled more developed planning and a more thorough testing process to ensure total accuracy. Any problems surrounding boundary issues were resolved and test submissions were provided to GeoPlace before the LSG went live.

Overall, this is a great example of how an authority split should be undertaken to be the most successful it can possibly be.