Allocating Forecast Data to the Landscape

Karen A. Larson

The Metro Data Resource Center produces 20-year forecasts of population, households and employment for use in its solid waste, land-use and transportation planning programs. These forecasts are created at the TAZ (Transportation Analysis Zone) level. This paper addresses the process used to further disaggregate these data to quarter acre grid cells based upon multiple criteria, including whether the cell represents developed or vacant land, current zoning, observed refill rates and theoretical upzone densities.

Metro’s Data Resource Center produces a four-county forecast of population, households and employment at place of work. The county-level forecast is then allocated to TAZ for use in the regional transportation model. There are times when forecast data are needed for geographies other than the original TAZ allocations, for example, when the regional transportation model changes zone systems. Re-aggregations of the forecast data are also used in Metro’s Growth Management and by Regional Environmental Management for forecasting and program evaluation. The Data Resource Center also produces aggregations at custom geographies for our local government partners and for fee-for-service clients.

In order to successfully disaggregate the data from the TAZ to quarter-acre grid cells we make use of the extensive GIS databases maintained by the Data Resource Center and Clark County, Washington’s Department of Assessment and GIS. These database include the base information necessary to allocate the TAZ-level forecast data to quarter acre grid cells. In addition, the Data Resource Center has conducted research on the rate of residential infill and redevelopment, which we term ‘refill’, and on employment density. The results of these studies provide us with a better basis upon which to build an allocation structure.

Step One – Build an allocation grid

The first step in disaggregating the data is the creation of an allocation grid using the data stored in Metro’s GIS (RLIS) and Clark County, Washington’s GIS. These polygon data are converted to individual grids with quarter-acre cells, then combined to make a single unified allocation grid. The coverages with alphanumeric attribute data, namely Metro’s zoning coverage, were converted to grids using ArcView’s Spatial Analyst extension. All other coverages were converted in ArcInfo GRID 7.1.2 .

The steps for conversion from polygon to grid are as follows:

• Convert the polygon coverages to grids.
• Set all cells with a nondata value to a dummy integer value to enable grid combination.
• Add 1000 to all Oregon zoning values. This differentiates the Oregon zoning values from those in Clark County when the two grids are added together. Nodata values in Oregon are set to 0.
• Add the Clark County and Metro zoning grids together.
• Join zoning attribute data back to the grid.
• Join the parks and water grids.
• Invert the new parks and water grid to be used as a mask.
• Combine the zoning, undeveloped land and estimate grids using the parks and water grid as a mask.
• Rejoin the attributes to the new grid.
• Combine the Region 2040 Design Types and TAZ grids.
• Combine these two resultant grids and rejoin the attributes to the new grid.
• Combine this grid with the base year estimate grid and rejoin the attributes to the new grid.
• Export the resulting grid’s value attribute table to a delimited text file.

Step Two – Produce a cell-level allocation in Excel

The cell-level allocation calculations are fairly involved, and are much easier to accomplish in a Microsoft Excel workbook using Visual Basic for Applications than in an Arc/Info AML. Additionally, the allocation of population and households is done in a separate workbook from the allocation of employment, due to the size of the allocation grid’s value attribute table.

Before the VBA macros can be run it is necessary to set up the input data in the workbook as follows:

• Create a tab in the allocation workbook with the necessary lookup tables:

• Create a tab in the workbook for the allocation grid's value attribute table. Import the delimited text file containing those data and populate the tab.

• Create a tab in the workbook for the base year estimates and the TAZ level forecast data.

• Calculate differences between the base year estimates and forecast at the TAZ level.

• Add the following items to the allocation tab and populate them as described below:

Once the setup is complete the first round of allocation calculations may be run. The function used for this is named CalcFirstRound and may be found in the appendix. Both example functions in the appendix were used for calculating employment allocations. The functions used in the population and household allocations are the same, except for the variable names.

Next we check for any unallocated growth as follows:

• Multiply the individual cell values by the count of those cells, then sum those totals by TAZ
• Populate a new column with the difference between the forecast amount to be allocated and the amount allocated by the CalcFirstRound

The second function, CalcFinal, allocates the remaining amount to the cells in the TAZ based upon current zoning.

Finally, we perform a final check against the control totals by TAZ:

• Multiply the individual cell values by the count of those cells, then sum those totals by TAZ
• Populate a new column with the difference between the forecast amount to be allocated and the amount allocated by the CalcFinal.

At this point we are ready to export the allocations to a tab delimited ASCII file. The only columns exported from the allocation spreadsheet are the value item linking the data back to the allocation grid and the final cell values.

Step Three – Rejoin the allocations to the allocation grid

The final step in the allocation process is reassembling the allocations and the allocation grid:

• Open the allocation grid in ArcView Spatial Analyst and open its value attribute table.
• Export the results of the allocation process to a tab delimited file.
• Open the tab delimited file in the ArcView session and join it to the allocation grid’s value attribute table on the ‘value’ item.
• Export the allocation grid to a new grid.

Allocating zonal data of any kind to a raster system can be fraught with uncertainty and frustration. There is an inevitable trade-off between accuracy and feasibility. In the case of population and employment forecast data we found that having detailed base data to work from, and an explicit design for the future development of the region to be invaluable.

In the ten months since the completion of this project we have completed an inventory of the nearly 700 local zoning designations and their allowed densities. This allowed us to create a more accurate grid of the base year estimates being used in the forecast currently under development. It is our expectation that when the time comes to disaggregate the TAZ allocations for the new forecast having a better base to use as a jumping-off point will allow us to create an even more reliable representation of the forecast ‘on the ground’.

Function CalcFirstRound(VacDev, EshregExt, EshregFor, EshzoneExt, EshzoneFor, Diffin, RefillRate, TheorMax, Countin)
      
   Dim x1 As Integer
   
   Dim Vac_Dev As Variant
   Dim Eshreg_Ext As Variant
   Dim Eshreg_For As Variant
   Dim Eshzone_Ext As Variant
   Dim Eshzone_For As Variant
   Dim Diff As Variant
   Dim Refill_Rate As Variant
   Dim Theor_Max As Variant
   Dim Count As Variant
   Dim CellTotal As Variant
   Dim Growth As Variant
   
   x1 = 1
   
   Vac_Dev = VacDev                 ' 1 = Vacant, 99999 = Developed
   Eshreg_Ext = EshregExt
   Eshzone_Ext = EshzoneExt
   Eshreg_For = EshregFor
   Eshzone_For = EshzoneFor
   Refill_Rate = RefillRate
   Count = Countin
   Theor_Max = TheorMax
   Diff = Diffin
   
   Growth = 0
   CellTotal = "Oops"
        
   ' Test for change in TAZ values
   If Diff = 0 Then
      CellTotal = 0
      
   ' Employment Gain
   ElseIf Diff > 0 Then
   
      'Developed Land
      If Vac_Dev > 1 Then
         Growth = Diff * Refill_Rate
         CellTotal = (Eshreg_Ext * (Growth / Eshzone_Ext)) / Count
         
      'Vacant Land
      ElseIf Vac_Dev = 1 Then
         
         'Compare growth to theoretical max
         If (Diff * (1 - Refill_Rate)) < Theor_Max Then
            Growth = Diff * (1 - Refill_Rate)
            CellTotal = (Eshreg_For * (Growth / Eshzone_For)) / Count
         ElseIf (Diff * (1 - Refill_Rate)) > Theor_Max Then
            Growth = Theor_Max
            CellTotal = (Eshreg_For * (Growth / Eshzone_For)) / Count
         Else: CellTotal = "Err-gain/vac/theormax"
         End If
      
      Else: CellTotal = "Err-gain/vac"
      
      End If
      
   'Employment Loss
   ElseIf Diff < 0 Then
   
      'Developed Land
      If Vac_Dev > 1 Then
         Growth = Diff
         CellTotal = (Eshreg_Ext * (Growth / Eshzone_Ext)) / Count
      'Vacant Land
      Else: CellTotal = 0
      End If
      
      
   Else: CellTotal = Diff
   
   End If
   
      CalcEmp1 = CellTotal
    
End Function

Function CalcFinal(EshregExt, EshzoneExt, Diffin, Emp96, EmpFor, Countin)

   Dim Eshreg_Ext As Variant
   Dim Eshzone_Ext As Variant
   Dim Diff As Variant
   Dim cv96 As Variant
   Dim cv_forecast As Variant
   Dim Count As Variant
   Dim CellTotal As Variant

   Eshreg_Ext = EshregExt
   Eshzone_Ext = EshzoneExt
   Diff = Diffin
   cv96 = Emp96
   cv_forecast = EmpFor
   Count = Countin

If Diff <> 0 Then

   CellTotal = (cv96 + cv_forecast) + ((Eshreg_Ext * (Diff / Eshzone_Ext)) / Count)
    
ElseIf Diff = 0 Then
   
   CellTotal = cv96 + cv_forecast
    
Else: CellTotal = "Oops"
   
End If

CalcEmp2 = CellTotal

End Function

Metro; Regional Framework Plan, December 11, 1997, pp 11 -23

Karen A. Larson, GIS Analyst

Metro

600 NE Grand Ave., Portland, OR 97232 USA

Phone: 503-797-1581, Fax: 503-797-1909

larsonk@metro.dst.or.us