International Conference on
Spatial Analysis and ModelingSep. 2018, Tokyo, Japan엑셀을 사용한 도시 계획 지원 툴A Costing Spreadsheet
to Support City Planning
Decision Making
AI, Hisatoshi @ The University of TokyoKATAGIRI, Yukiko @ Tokyo Metropolitan University

Presented at CSIS-KRIHS international joint seminar, Kashiwa, JAPAN. Nov 14. 2018
Minor revision from ppt presented at ICSAM 2018, Tokyo, JAPANInternational Conference on
Spatial Analysis and ModelingSep. 2018, Tokyo, Japan

Introduction• Rapid aging and population decline in Japan

Population decreased from 2010 to 2015
MINUS population change ratio
総務省「平成27年国勢調査 人口等基本集計結果」
Ministry of Internal Affairs, Summary of the results of the 2015 population census• New city planning strategies and models needed• Key concept is to make cities more compact
Population increase
and Growth control

Population decline
and Compact cityCityCity

International Conference on
Spatial Analysis and ModelingSep. 2018, Tokyo, JapanMotivation• Many compact city models proposed in earlier studies• Models are designed for some certain city based on detailed
case study• Some cities have already developed compact city plan
• Compact city planning is urgent issue for all cities in Japan• Interactive tool for planning and monitoring is needed

Compact citymodel for our city?

City Planning with EXCEL?

International Conference on
Spatial Analysis and ModelingSep. 2018, Tokyo, Japan• Most municipality officials and local residents are unfamiliar
to GIS and spatial models• How about using EXCEL for city planning decision making?
• EXCEL is used for daily work
• But how?

?

International Conference on
Spatial Analysis and ModelingSep. 2018, Tokyo, Japan

Outline of the costing sheet• Urban area is represented with 60 by 60 cells• All steps from input to output complete on EXCEL• Automatic costing calculationInternational Conference on
Spatial Analysis and ModelingSep. 2018, Tokyo, Japan

Basic idea of the costing• Compact city enables the municipality to save annual cost
• To make the city compact, investment should be made at
the early stage to make sub centers attractive• In certain years, saved cost reaches first investment초기 투자Investment
for newurban coreFirst investment andcost saving balancecertain years later

Cost saving of annual
infrastructure
maintenance cost

City become compact감소한 비용

Subtotal of cost saving

International Conference on
Spatial Analysis and ModelingSep. 2018, Tokyo, Japan

How to use? Flow of costing Cell width Cell location, Lat/Long

Map settings
Input population
 Three categories
 Input function provided

Input parameters
 Population change ratio Unit price of municipality services

Future plan proposed

Customize the plan Excel proposes future planthat optimized to carry
future population
 User can change land use
plan manually

Future population

Costing output

 Considering current populationand future land use plan Future land use and population
 Year when cost balancesInput the population data

International Conference on
Spatial Analysis and ModelingSep. 2018, Tokyo, Japan• User have to input current population distribution• Population in 3 categories, e.g. young, productive, elderly• Input 3 x 3600 = 10,800 cells manually?
• Worksheet for easy data import provided

International Conference on
Spatial Analysis and ModelingSep. 2018, Tokyo, Japan

Population data import function• Accepts population data accompanied with mesh code
• Japanese mesh code represents its Lat/Long coordinate• Costing sheet calculates which mesh code corresponds towhich cell on the sheet
MESHCODE

Pop 1Pop 2Pop 3534030692

123420534030684

109413287534030792

35114

58

User:
Copy & paste mesh codeand population data set

EXCEL:
Assign the data into 60x60 cells
based on cell location and mesh code

International Conference on
Spatial Analysis and ModelingSep. 2018, Tokyo, Japan

Population data import function• Also accepts population data with Lat/Long coordinates• Costing sheet calculates which location falls into which cellon the sheet
X = Longitude

Y = Latitude

Pop 1

Pop 2

Pop 3

140.120313

35.7572863

123420140.123438

35.7572883

109413287

140.126563

35.7572883

35114

58

User:
Copy & paste Lat/Long coodinate
and population of the location

EXCEL:
Assign the data into 60x60 cells
based on cell location and mesh code

Parameter settings

International Conference on
Spatial Analysis and ModelingSep. 2018, Tokyo, Japan• User have to set the following parameters for the calculation• Cells for parameter are highlighted with yellow color
Cell width, e.g. scale of the map

Max. dist. of sub center from CBD
Population density
Population change ratio, strategy
Maintenance cost
Development cost
Population change, forecasted
Location, coordinates

International Conference on
Spatial Analysis and ModelingSep. 2018, Tokyo, Japan

Future plan proposition• From current population, EXCEL designates urbanized cell
• EXCEL also proposes which cells should be sub centers• Land use is expressed by following alphabets• Annual cost and population change differ by land use code
CodeExplanation

Proposed

Customized

X

Outside of target region

*

*

DCBD

*

*

H

Redevelopment of CBD with high rise houses*

R

Redevelopment of CBD with low rise houses*S

Proposed to be a sub center**

ITo be disengaged, close to CBD

*

*

O

To be disengaged, distant from CBD**AAgricultural land*

GGreen environment prevention area*

M

Planned to maintain current population*

Outcome of the costing

International Conference on
Spatial Analysis and ModelingSep. 2018, Tokyo, Japan• Future population distribution is shown as a map• Project duration calculated by comparing initial cost andannual cost savings

Calculation of future population

Customize land use plan

Year when initial investment and annual saving balanceUsage of recalculation

International Conference on
Spatial Analysis and ModelingSep. 2018, Tokyo, Japan• Recalculation is one of the powerful tool of EXCEL• If user changes the data in some cell, equations in othercells referring that cell will be automatically calculated again• This enables user to design future plan interactively

International Conference on
Spatial Analysis and ModelingSep. 2018, Tokyo, Japan

Costing outcome export function• Worksheet prepared for export is an array of Lat/Long
coordinates, population, land use, etc.
• User can plot this data as point data set on GIS
How the maplooks on EXCEL

Plotted as point
data on GISOverlaid onother layers

Conclusion

International Conference on
Spatial Analysis and ModelingSep. 2018, Tokyo, Japan• EXCEL based costing sheet targeting municipality officials
and local residents to design future plan of their city
• No GIS skills or spatial modeling knowledge requested• Recalculation enables interactive planning by the users• New functions equipped for smooth data input and output