MODEL | completestreets

The Model was created through many steps. They include: Draft Excel Model Creation, ArcGIS Implementation, and Python Script Creation.

Complete Streets Model

Draft Excel Model

Master Table

Scott Barbacki, the python instructor at Niagara College, was consulted in the design of the complete streets model. He provided the team with a template in Excel to start with. The team developed a draft model and manually populated tables to get a starting place for and an understanding of the final model. The draft model was made up of three tables: the Master Table, the Attribute Existence Table, and the Attribute Weighting Table.

The master table (Seen in Table 1) separates each road segment into its own row. Within the row, it specifies the road segment typology, and whether each complete street attribute exists, how many it may contain within a 150 metre buffer. The very last column on the right is the completeness percentage of the road segment. This calculation is derived from the next two tables to be described below. Table 1 shows only a small sample of road segments from the study area.

Table 1 Draft Master Table in Excel

Attribute Existence Table

The existence of attributes table (Seen in Table 2) takes the same format as the master table but defines the attributes by a binary score of 1 for existent and 0 for nonexistent. This is later used to calculate the completeness score in the master table.

Table 2 Attribute Existence Table in Excel

Attribute Weighting Table

The attribute weighting table (Seen in Table 3) weighs the importance of each complete streets attribute based on the typology in which they appear. The weighting process was wholly subjective and up to the project team to decide. This method leaves the user in complete control of which attributes they choose to include and the weightings assigned to each, however, the total must always sum up to 100%.

Table 3 Attribute Weighting Table in Excel

The calculation for the completeness for each road segment takes the matching attributes from the existence table and the weighting table and multiplies them together to get the percentage influence of each complete streets attribute individually. It then adds all the percentages together to get the total completeness percentage for that road segment. It is to be noted that the weighting scores used must match the typology that road segment is associated with.

ArcGIS Implementation

To create a working model, parts of the draft model had to be implemented into ArcGIS. The Attribute Weighting Table was manually created in the geodatabase containing all the collected data (See Figure 1)

Python Script

By creating a python script that reads the collected data from the geodatabase, the 150 metre buffer was generated around the study area feature class, the Attribute Existence Table was created in ArcGIS, and the completeness percentages were calculated within the study area feature class attribute table in ArcGIS using the Attribute Weight Table and the Attribute Existence Table.

Create 150 Metre Buffer

The Python script first starts off by creating a 150 metre buffer around the study area (See Figure 3).

The typologies were manually added to each road segment by creating a new field called 'Typology' within the study area feature class attribute table in ArcGIS (See Figure 2)

Figure 1 Attribute Weighting Table in ArcGIS

Figure 2 Typology Field in Study Area Feature Class

Python Attribute Existence Table

Figure 3 Create 150 metre buffer around study area

The next part of the python script creates the Attribute Existence Table. It does this by listing the complete street attribute feature classes (specified in the Attribute weighting table) within the geodatabase and excluding feature classes such as the study area, road network, zoning, etc. It then spatially joins the listed feature classes to the buffer to determine the quantity of each attribute within 150 metres of the road segment. It then outputs this count to a new table called by the feature class name plus 'total' and changes the name of the SUM field to the attribute name plus 'SUM' (See Figure 4).

The script then joins all of the 'total' tables together to get a table including the total number of all attributes within the 150 metre buffer. It renames the table to Exists_Table (See Figure 5).

Figure 4 Spatially Joins Complete Streets Attributes to Buffer to get SUM of Attributes within Buffer

Before the Attribute Existence table is complete, the SUM values had to be changed to binary values (0 or 1), for easier calculation of Completeness. The values above 0 were changed to a 1 and the values at 0 stayed the same (See Figure 6)

The last step was to clean up the geodatabase of any extra feature classes or tables previous created in the script. (See Figure 7).

Figure 5 Joins all tables together showing the SUM and renames the table 'Exists_Table'

Figure 6 Changes SUM Values to Binary (0 or 1)

Figure 7 Cleans up geodatabase from extra tables and feature classes

Python Calculate Completeness

With the Attribute Existence and Weighting tables completed, the last step of the model is to calculate the completeness of the study area. This step joins the Attribute Existence and Weighting tables to the study area feature class by their similar fields (object ID for Existence and Typology for Weighting). It then creates a new field called 'Completeness'. Lastly, it calculates the value for the 'Completeness' field by multiplying the matching attributes from the existence and the weighting fields to get the percentage influence of each complete streets attribute individually. It then adds all the percentages together and multiplies everything by 100 to get the total completeness percentage for that road segment (See Figure 8).

Figure 8 Calculates final completeness

Final Results