場所割り当ての最適化
この例では、オープンソースの Python を使用して、大規模な割り当てと場所の割り当ての問題にどのようにアプローチするかを示します。このワークフローでは匿名化されたデータを使用し、モデルの入力を準備し、起点と終点のマトリックスを構築し、最適化を解決し、GIS ソフトウェアで確認できるように出力を GeoJSON にエクスポートする方法を示します。
計画に関する質問は単純ですが有益です。各近隣または郊外の人口、各施設の収容力、および各施設がサービスを提供できる最大距離を考慮すると、全人口にサービスを提供するのに十分な施設があるか、そうでない場合、現在の制約の下でどのエリアが割り当てられていないままになるかということです。
モデルの機能
これは割り当てと場所の割り当てモデルです。各需要場所は六角形で表され、各施設は利用可能な供給を表し、モデルはサービス距離と容量のルールを尊重しながら、すべての六角形を単一の最適な施設に割り当てます。同じモデリング パターンは、サービス範囲、サービス エリア、アクセスのギャップを理解する必要がある医療施設、学校、銀行、小売店、またはその他のサービス ネットワークにも使用できます。
出力は単なる割り当てのテーブルではありません。また、サービス エリア レイヤーや商圏レイヤーに変換して、各施設がどの場所をカバーできるか、どの需要エリアが未割り当てのフォールバックにプッシュされるか、容量、距離、または目的が調整されたときに結果がどのように変化するかを示すこともできます。
目的関数
この例の目的は、すべての需要ヘキサゴンからその割り当てられた施設までの合計距離を最小化することです。これにより、現在の仮定の下で最も効率的な割り当てが得られます。ビジネス上の質問が変更された場合は、同じフレームワークを異なる目的関数で再実行できます。たとえば、距離ではなく運営コストを最小限に抑えたり、直線距離ではなく移動時間を最小限に抑えたり、高コストの施設がその運営プロファイルを正当化するのに十分な量を獲得できるように需要を再調整したりすることができます。
主要な制約
- 各施設には最大サービス距離があり、その範囲外のデマンドを割り当てることはできません。
- 各施設には最小人口制限と最大人口制限があるため、それに割り当てられた総需要はその収容力制限内に収まる必要があります。
- 各需要ヘキサゴンは 1 つの施設に割り当てる必要があります。
- 六角形を実際の施設に割り当てることができない場合は、ダミーの
unassigned施設に割り当てることができるため、モデルの実行可能性が維持され、カバレッジ ギャップが表示されます。 - この例では、高速プロトタイピングのために六角形の重心と施設の間のユークリッド距離を使用していますが、必要に応じて同じワークフローを実際の走行可能なルートに切り替えることができます。
ワークフロー
ワークフローには 3 つの主要な段階があります。
- 対象エリアを六角形のテッセレーションに変換し、各六角形の人口を導出します。
- 各需要ヘキサゴンと各施設間の出発地-目的地マトリックスを生成します。
- 最適化モデルを解決し、結果を GeoJSON にエクスポートして、QGIS、ArcGIS Pro、またはその他のマッピング ソフトウェアで確認できるようにします。
実装スタックには、shapely、pyproj、sqlite3、pyomo、CBC ソルバー、および GeoJSON 出力を備えたオープンソース Python が含まれています。データ準備パターンは意図的にモジュール化されているため、需要レイヤーの置き換え、施設の制約の変更、目標の交換、追加のビジネス ルールによるモデルの拡張が容易になります。
実装メモ- 当初は PuLP がテストされましたが、はるかに大きなモデルをより確実に処理できるため、代わりに pyomo が選択されました。
- モデルはオープンソースの CBC ソルバーを使用して解決され、このアプローチは、そのセットアップで 1 時間以内に 5,000 万を超える決定変数に拡張されました。
- さらに大規模なインスタンスの場合、ライセンスで許可されている場合は、グロビを検討できます。
- 大規模な出力を GeoJSON に書き込むには、モデル自体を解決するよりも時間がかかる可能性があるため、大規模な実稼働環境では、データベースに直接書き込む方が効率的になる可能性があります。
- このようなモデルを構築する実際的な方法は、制約をテストしながら大きな六角形と高速ユークリッド距離から始めて、モデルの動作が検証されたら、より細かいテッセレーションとより現実的なルート コストに切り替えることです。
- 制約を段階的に追加できますが、ビジネス ルールが追加されるたびにモデルが実行不可能になるリスクが高まるため、慎重に導入する必要があります。
ソースコードの例
以下のコードは、このページに直接エンドツーエンドのワークフローを示しています。
1.generate_hexagons.py
import json
import math
import os
import pyproj
from shapely.geometry import shape
# for converting the coordinates to and from geographic and projected coordinates
TRAN_4326_TO_3857 = pyproj.Transformer.from_crs("EPSG:4326", "EPSG:3857")
TRAN_3857_TO_4326 = pyproj.Transformer.from_crs("EPSG:3857", "EPSG:4326")
# the area of interest used for generating the hexagons
input_geojson_file = "input/area_of_interest.geojson"
# load the area of interest into a JSON object
with open(input_geojson_file) as json_file:
geojson = json.load(json_file)
# the area of interest coordinates (note this is for a single-part / contiguous polygon)
geographic_coordinates = geojson["features"][0]["geometry"]["coordinates"]
# create an area of interest polygon using shapely
aoi = shape({"type": "Polygon", "coordinates": geographic_coordinates})
# get the geographic bounding box coordinates for the area of interest
(lng1, lat1, lng2, lat2) = aoi.bounds
# get the projected bounding box coordinates for the area of interest
[W, S] = TRAN_4326_TO_3857.transform(lat1, lng1)
[E, N] = TRAN_4326_TO_3857.transform(lat2, lng2)
# the area of interest height
aoi_height = N - S
# the area of interest width
aoi_width = E - W
# the length of the side of the hexagon
l = 200
# the length of the apothem of the hexagon
apo = l * math.sqrt(3) / 2
# distance from the mid-point of the hexagon side to the opposite side
d = 2 * apo
# the number of rows of hexagons
row_count = math.ceil(aoi_height / l / 1.5)
# add a row of hexagons if the hexagon tessallation does not fully cover the area of interest
if(row_count % 2 != 0 and row_count * l * 1.5 - l / 2 < aoi_height):
row_count += 1
# the number of columns of hexagons
column_count = math.ceil(aoi_width / d) + 1
# the total height and width of the hexagons
total_height_of_hexagons = row_count * l * 1.5 if row_count % 2 == 0 else 1.5 * (row_count - 1) * l + l
total_width_of_hexagons = (column_count - 1) * d
# offsets to center the hexagon tessellation over the bounding box for the area of interest
x_offset = (total_width_of_hexagons - aoi_width) / 2
y_offset = (row_count * l * 3 / 2 - l / 2 - aoi_height - l) / 2
# create an empty feature collection for the hexagons
feature_collection = { "type": "FeatureCollection", "features": [] }
oid = 1
hexagon_count = 0
for i in range(0, column_count):
for j in range(0, row_count):
if(j % 2 == 0 or i < column_count - 1):
x = W - x_offset + d * i if j % 2 == 0 else W - x_offset + apo + d * i
y = S - y_offset + l * 1.5 * j
coords = []
for [lat, lng] in [
TRAN_3857_TO_4326.transform(x, y + l),
TRAN_3857_TO_4326.transform(x + apo, y + l / 2),
TRAN_3857_TO_4326.transform(x + apo, y - l / 2),
TRAN_3857_TO_4326.transform(x, y - l),
TRAN_3857_TO_4326.transform(x - apo, y - l / 2),
TRAN_3857_TO_4326.transform(x - apo, y + l / 2),
TRAN_3857_TO_4326.transform(x, y + l)
]:
coords.append([lng, lat])
hexagon = shape({"type": "Polygon", "coordinates": [coords]})
# check if the hexagon is within the area of interest
if aoi.intersects(hexagon):
hexagon_count += 1
if(hexagon_count % 1000 == 0):
print('Generated {} hexagons'.format(hexagon_count))
population = 0
hexagon_names = []
# open the geojson file with the population data
with open("input/population_areas.geojson") as json_file:
geojson = json.load(json_file)
for feature in geojson["features"]:
polygon = shape(
{
"type": "Polygon",
"coordinates": feature["geometry"]["coordinates"]
}
)
# check if hexagon is within the polygon and derive the population for that intersected part of the hexagon
if hexagon.intersects(polygon):
if not feature["properties"]["Name"] in hexagon_names:
hexagon_names.append(feature["properties"]["Name"])
population += (
hexagon.intersection(polygon).area
/ polygon.area
* feature["properties"]["Population"]
)
hexagon_names.sort()
f = {
"type": "Feature",
"properties": {
"id": oid,
"name": ', '.join(hexagon_names),
"population": population
},
"geometry": {
"type": "Polygon",
"coordinates": [coords]
}
}
# add the hexagon to the feature collection
feature_collection['features'].append(f)
oid += 1
print('Generated {} hexagons'.format(hexagon_count))
# output the feature collection to a geojson file
with open("output/hexagons.geojson", "w") as output_file:
output_file.write(json.dumps(feature_collection))
# Play a sound when the script finishes (macOS)
for i in range(1, 2):
os.system('afplay /System/Library/Sounds/Glass.aiff')
# Play a sound when the script finishes (Windows OS)
# import time
# import winsound
# frequency = 1000
# duration = 300
# for i in range(1, 10):
# winsound.Beep(frequency, duration)
# time.sleep(0.1)2.generate_origin_destination_matrix.py
import json
import math
import os
import pyproj
import sqlite3
def getDistance(x1,y1,x2,y2):
distance = math.sqrt((x2-x1)**2+(y2-y1)**2)
return int(distance)
def getHexagonCentroid(hexagon):
coordinates = hexagon['geometry']['coordinates'][0]
# remove the last pair of coordinates in the hexagon
coordinates.pop()
lat = sum(coords[1] for coords in coordinates) / 6
lng = sum(coords[0] for coords in coordinates) / 6
return lat, lng
# for converting the coordinates to and from geographic and projected coordinates
TRAN_4326_TO_3857 = pyproj.Transformer.from_crs("EPSG:4326", "EPSG:3857")
TRAN_3857_TO_4326 = pyproj.Transformer.from_crs("EPSG:3857", "EPSG:4326")
# create a sqlite database for the results
db = 'output/results.sqlite'
# delete the database if it already exists
if(os.path.exists(db)):
os.remove(db)
# create a connection to the sqlite database
conn = sqlite3.connect(db)
# create cursors for the database connection
c1 = conn.cursor()
c2 = conn.cursor()
# create the facilities table
c1.execute('''
CREATE TABLE facilities (
facility_id INT,
facility_x REAL,
facility_y REAL,
trade_area_distance_constraint REAL,
min_population_constraint INT,
max_population_constraint INT
);
''')
c1.execute('''
CREATE TABLE od_matrix (
facility_id INT,
hexagon_id INT,
facility_x REAL,
facility_y REAL,
hexagon_x REAL,
hexagon_y REAL,
distance INT,
optimal INT
);
''')
# the geojson for the facilities
input_geojson_file = "input/facilities.geojson"
# load the area of interest into a JSON object
with open(input_geojson_file) as json_file:
geojson = json.load(json_file)
facilities = geojson['features']
for facility in facilities:
facility_id = facility['properties']['OID']
trade_area_distance_constraint = facility['properties']['trade_area_dist_constraint']
min_population_constraint = facility['properties']['min_population_constraint']
max_population_constraint = facility['properties']['max_population_constraint']
[lng, lat] = facility['geometry']['coordinates']
[x, y] = TRAN_4326_TO_3857.transform(lat, lng)
sql = '''
INSERT INTO facilities (
facility_id,
facility_x,
facility_y,
trade_area_distance_constraint,
min_population_constraint,
max_population_constraint
)
VALUES ({},{},{},{},{},{});
'''.format(facility_id, x, y, trade_area_distance_constraint, min_population_constraint, max_population_constraint)
c1.execute(sql)
# create an empty feature collection for the hexagons
feature_collection = { "type": "FeatureCollection", "features": [] }
# the geojson for the hexagons
input_geojson_file = "output/hexagons.geojson"
# load the area of interest into a JSON object
with open(input_geojson_file) as json_file:
geojson = json.load(json_file)
hexagons = geojson['features']
for i, hexagon in enumerate(hexagons):
hexagon_id = hexagon['properties']['id']
lat, lng = getHexagonCentroid(hexagon)
[hexagon_x, hexagon_y] = TRAN_4326_TO_3857.transform(lat, lng)
rows = c1.execute('''
SELECT facility_id, facility_x, facility_y, trade_area_distance_constraint
FROM facilities;
''').fetchall()
for row in rows:
facility_id, facility_x, facility_y, trade_area_distance_constraint = row
distance = getDistance(hexagon_x, hexagon_y, facility_x, facility_y)
if(distance > int(trade_area_distance_constraint)):
distance = 100000
sql = '''
INSERT INTO od_matrix(facility_id, facility_x, facility_y, hexagon_id, hexagon_x, hexagon_y, distance)
VALUES ({},{},{},{},{},{},{});
'''.format(facility_id, facility_x, facility_y, hexagon_id, hexagon_x, hexagon_y, distance)
c2.execute(sql)
(hexagon_lat, hexagon_lng) = TRAN_3857_TO_4326.transform(hexagon_x, hexagon_y)
(facility_lat, facility_lng) = TRAN_3857_TO_4326.transform(facility_x, facility_y)
coords = [[facility_lng, facility_lat],[hexagon_lng, hexagon_lat]]
if(distance <= trade_area_distance_constraint):
f = {
"type": "Feature",
"properties": {},
"geometry": {
"type": "LineString",
"coordinates": coords
}
}
# add the hexagon to the feature collection
feature_collection['features'].append(f)
if((i+1) % 1000 == 0):
print('Processed {} hexagons'.format(i+1))
conn.commit()
conn.close()
# output the feasible facility-hexagon pairs to geojson
with open("output/routes.geojson", "w") as output_file:
output_file.write(json.dumps(feature_collection))
for i in range(1,2):
os.system('afplay /System/Library/Sounds/Glass.aiff')3.solve_the_model.py
from pyomo.environ import *
from pyomo.opt import SolverFactory
import datetime
import json
import os
import pyproj
import sqlite3
# for converting the coordinates to and from geographic and projected coordinates
TRAN_4326_TO_3857 = pyproj.Transformer.from_crs("EPSG:4326", "EPSG:3857")
TRAN_3857_TO_4326 = pyproj.Transformer.from_crs("EPSG:3857", "EPSG:4326")
# the input database created from the previous script
db = 'output/results.sqlite'
# create a database connection
conn = sqlite3.connect(db)
# create a cursor for the database connection
c = conn.cursor()
# the demand, supply, and cost matrices
Demand = {}
Supply = {}
Cost = {}
'''
Supply['S0'] is for infeasible results, i.e. hexagons that do not
have any facilities when the nearest facility is too far away,
or when the population constraint for the facilities means the
hexagon cannot be assigned to that facility
'''
# the population capacity constraint of the "unassigned" facility
Supply['S0'] = {}
Supply['S0']['min_pop'] = 0
Supply['S0']['max_pop'] = 1E10
sql = '''
SELECT DISTINCT hexagon_id
FROM od_matrix
ORDER BY 1;
'''
# the assignment constraint, i.e. each hexagon can only be assigned to one facility
for row in c.execute(sql):
hexagon_id = row[0]
d = 'D{}'.format(hexagon_id)
Demand[d] = 1
# the infeasible case for each hexagon
Cost[(d,'S0')] = 1E4
sql = '''
SELECT DISTINCT facility_id, min_population_constraint, max_population_constraint
FROM facilities
ORDER BY 1;
'''
# the facility capacity constraint - cannot supply more hexagons than the facility has capacity for
for row in c.execute(sql):
[facility_id, min_population_constraint, max_population_constraint] = row
s = 'S{}'.format(facility_id)
Supply[s] = {}
Supply[s]['min_pop'] = min_population_constraint
Supply[s]['max_pop'] = max_population_constraint
sql = '''
SELECT facility_id, hexagon_id, distance
FROM od_matrix;
'''
# creating the Cost matrix
for row in c.execute(sql):
(facility_id, hexagon_id, distance) = row
d = 'D{}'.format(hexagon_id)
s = 'S{}'.format(facility_id)
Cost[(d,s)] = distance
print('Building the model')
# creating the model
model = ConcreteModel()
model.dual = Suffix(direction=Suffix.IMPORT)
# Step 1: Define index sets
dem = list(Demand.keys())
sup = list(Supply.keys())
# Step 2: Define the decision variables
model.x = Var(dem, sup, domain=NonNegativeReals)
# Step 3: Define Objective
model.Cost = Objective(
expr = sum([Cost[d,s]*model.x[d,s] for d in dem for s in sup]),
sense = minimize
)
# Step 4: Constraints
model.sup = ConstraintList()
# each facility cannot supply more than its population capacity
for s in sup:
model.sup.add(sum([model.x[d,s] for d in dem]) >= Supply[s]['min_pop'])
model.sup.add(sum([model.x[d,s] for d in dem]) <= Supply[s]['max_pop'])
model.dmd = ConstraintList()
# each hexagon can only be assigned to one facility
for d in dem:
model.dmd.add(sum([model.x[d,s] for s in sup]) == Demand[d])
'''
There is no need to add a constraint for the service/trade area distances
for the facilities. We are already handling this when we generate
the origin destination matrix. If any hexagon falls outside of all
facility trade areas, then it gets assigned to the "unassigned" facility.
'''
print('Solving the model')
# use the CBC solver and solve the model
results = SolverFactory('cbc').solve(model)
# for c in dem:
# for s in sup:
# print(c, s, model.x[c,s]())
# if the model solved correctly
if 'ok' == str(results.Solver.status):
print("Objective Function = ", model.Cost())
print('Outputting the results to GeoJSON') # note it would be faster to write the results directly to a database, e.g. Postgres / SQL Server
# print("Results:")
for s in sup:
for d in dem:
if model.x[d,s]() > 0:
# print("From ", s," to ", d, ":", model.x[d,s]())
facility_id = s.replace('S','')
hexagon_id = d.replace('D','')
c.execute('''
UPDATE od_matrix
SET optimal = 1
WHERE facility_id = {}
AND hexagon_id = {};
'''.format(facility_id, hexagon_id))
# create an empty feature collection for the results
feature_collection = { "type": "FeatureCollection", "features": [] }
rows = c.execute('''
SELECT facility_id, hexagon_id, facility_x, facility_y, hexagon_x, hexagon_y, distance
FROM od_matrix
WHERE optimal = 1;
''')
for row in rows:
(facility_id, hexagon_id, facility_x, facility_y, hexagon_x, hexagon_y, distance) = row
(hexagon_lat, hexagon_lng) = TRAN_3857_TO_4326.transform(hexagon_x, hexagon_y)
(facility_lat, facility_lng) = TRAN_3857_TO_4326.transform(facility_x, facility_y)
coords = [[facility_lng, facility_lat],[hexagon_lng, hexagon_lat]]
f = {
"type": "Feature",
"properties": {},
"geometry": {
"type": "LineString",
"coordinates": coords
}
}
# add the route to the feature collection
feature_collection['features'].append(f)
# output the optimally assigned pairs to geojson
with open("output/optimal_results.geojson", "w") as output_file:
output_file.write(json.dumps(feature_collection))
# update the hexagons
with open('output/hexagons.geojson') as json_file:
geojson = json.load(json_file)
hexagons = geojson['features']
for hexagon in hexagons:
facility_id = -1
hexagon_id = hexagon['properties']['id']
sql = '''
SELECT facility_id
FROM od_matrix
WHERE hexagon_id = {}
AND optimal = 1;
'''.format(hexagon_id)
row = c.execute(sql).fetchone()
if row:
facility_id = row[0]
hexagon['properties']['facility_id'] = facility_id
# load the area of interest into a JSON object
with open("output/hexagon_results.geojson", "w") as output_file:
output_file.write(json.dumps(geojson))
else:
print("No Feasible Solution Found")
finish_time = datetime.datetime.now()
# print('demand nodes = {} and supply nodes = {} | time = {}'.format(no_of_demand_nodes, no_of_supply_nodes, finish_time-start_time))
conn.commit()
conn.close()
for i in range(1,2):
os.system('afplay /System/Library/Sounds/Glass.aiff')