公共交通データとの
向き合い方 Ver.3.1

# ヒアドキュメントでクエリを流し込む、便利。
duckdb << EOS 
LOAD spatial;
COPY (
  SELECT ST_Union_Agg(geom) AS area FROM( -- 生成されたバス停毎の勢圏ポリゴンを重なっているものでマージする
    SELECT *, ST_Buffer(ST_Point(stop_lon, stop_lat), 0.003) AS geom, FROM read_csv('./stops.txt') -- ST_Bufferでバス停勢圏ポリゴンを生成する
  )
) TO './stops_area.geojson' WITH (FORMAT gdal, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS 'WRITE_BBOX=YES', SRS 'EPSG:4326'); -- stop_area.geojsonとしてポリゴンを保存する。SRSは元データに合わせるべき
EOS

# contest2025.duckdbにtamamura_demand.txtをtamamura.demandとしてロードする
SCHEMA=tamamura && cat ${SCHEMA}_demand.txt | duckdb contest2025.duckdb -c "CREATE OR REPLACE TABLE ${SCHEMA}.demand AS SELECT * FROM read_csv('/dev/stdin')"

-- demandにstopsをJOINしてODを表現したSQL
SELECT current_schema() AS region, pickup_stops.stop_name AS pickup_stop_name,  pickup_stops.stop_lat AS pickup_stop_lat,  pickup_stops.stop_lon AS pickup_stop_lon, drop_off_stops.stop_name AS drop_off_stop_name,  drop_off_stops.stop_lat AS drop_off_stop_lat,  drop_off_stops.stop_lon AS drop_off_stop_lon, count FROM (SELECT pickup_stop_id, drop_off_stop_id, count(pickup_stop_id) AS count 
FROM demand GROUP BY pickup_stop_id, drop_off_stop_id) AS demand_count 
LEFT JOIN stops AS pickup_stops ON demand_count.pickup_stop_id = pickup_stops.stop_id 
LEFT JOIN stops AS drop_off_stops ON demand_count.drop_off_stop_id = drop_off_stops.stop_id;

# od.csv出力用シェルスクリプト（SCHEMAを書き換えれば他の地域でも生成可能）
SCHEMA=tamamura && duckdb contest2025.duckdb -csv -c "USE ${SCHEMA}; SELECT current_schema() AS region, pickup_stops.stop_name AS pickup_stop_name,  pickup_stops.stop_lat AS pickup_stop_lat,  pickup_stops.stop_lon AS pickup_stop_lon, drop_off_stops.stop_name AS drop_off_stop_name,  drop_off_stops.stop_lat AS drop_off_stop_lat,  drop_off_stops.stop_lon AS drop_off_stop_lon, count FROM (SELECT pickup_stop_id, drop_off_stop_id, count(pickup_stop_id) AS count FROM demand GROUP BY pickup_stop_id, drop_off_stop_id) AS demand_count LEFT JOIN stops AS pickup_stops ON demand_count.pickup_stop_id = pickup_stops.stop_id LEFT JOIN stops AS drop_off_stops ON demand_count.drop_off_stop_id = drop_off_stops.stop_id;" > od.csv

SELECT * FROM stops 
LEFT JOIN ( SELECT pickup.pickup_stop_id AS stop_id, pickup.pickup_count, drop_off.drop_off_count FROM (SELECT pickup_stop_id, COUNT(*) AS pickup_count FROM demand GROUP BY pickup_stop_id) AS pickup 
LEFT JOIN (SELECT drop_off_stop_id, COUNT(*) AS drop_off_count FROM demand GROUP BY drop_off_stop_id) AS drop_off 
ON pickup.pickup_stop_id = drop_off.drop_off_stop_id) AS counts  
ON stops.stop_id = counts.stop_id;

# stops_count.csv出力用シェルスクリプト
SCHEMA=tamamura && duckdb contest2025.duckdb -csv -c "USE ${SCHEMA}; SELECT current_schema() AS region, * FROM stops LEFT JOIN ( SELECT pickup.pickup_stop_id AS stop_id, pickup.pickup_count, drop_off.drop_off_count FROM (SELECT pickup_stop_id, COUNT(*) AS pickup_count FROM demand GROUP BY pickup_stop_id) AS pickup LEFT JOIN (SELECT drop_off_stop_id, COUNT(*) AS drop_off_count FROM demand GROUP BY drop_off_stop_id) AS drop_off ON pickup.pickup_stop_id = drop_off.drop_off_stop_id) AS counts  ON stops.stop_id = counts.stop_id;" > stops_count.csv

$ unzip -p tblT001142Q5439.zip | iconv -c -f cp932 -t utf-8 > tblT001142Q5439_utf8.txt

-- メッシュ内総人口はT001142001カラムに保存されているので、メッシュコードのポリゴンと人口をJOINしてGeoJSONとして保存する
duckdb << EOS 
load spatial;
COPY (
    SELECT mesh_5th.KEY_CODE, census.T001142001::INT AS population, geom 
    FROM (SELECT * FROM ST_Read('./QDDSWQ5439/MESH05439.shp')) AS mesh_5th 
    LEFT JOIN (SELECT * FROM read_csv('tblT001142Q5439_utf8.txt', header = true)) AS census 
    ON mesh_5th.KEY_CODE = census.KEY_CODE
) TO 'census.geojson' WITH (FORMAT GDAL, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS 'WRITE_BBOX=YES');
EOS

docker run --rm -it -p 8002:8002 -v $PWD/custom_files:/custom_files ghcr.io/valhalla/valhalla-scripted:latest

-- 中心座標と徒歩距離を与えたらIsochroneを示すPolygonを返すマクロ
CREATE OR REPLACE MACRO get_isochrone_geom(lng, lat, dist := 0.3) AS (
    SELECT ST_GeomFromGeoJSON((res->>'body')::JSON->'$.features[0].geometry')
    FROM (
        SELECT http_get(
            'http://localhost:8002/isochrone',
            headers => MAP { 'accept': 'application/json' },
            params => MAP { 'json': '{"polygons": true, "denoise": 0.0, "generalize": 1.0 , "costing":"pedestrian","contours":[{"distance":' || dist || '}], "locations":[{"lat":' || lat || ',"lon":' || lng || '}]}' }
        ) AS res
    )
);

-- Stopsに徒歩300mのPolygonをJOINして、GeoJSONとして出力
USE tamamura;
COPY (
    SELECT ST_Union_Agg(geom) AS area FROM (
        SELECT stop_lat AS lat, stop_lon AS lon, get_isochrone_geom(lon, lat) as geom FROM stops
    )
) TO 'stops_isochrone.geojson' WITH (FORMAT gdal, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS 'WRITE_BBOX=YES', SRS 'EPSG:4326');

# 関東のOSM、たまGOのGTFS-Flex、永井バスのGTFSを読み込むconfigを書き出す
./motis config ./kanto.osm.pbf ./tamamura_GTFS-flex.zip ./nagai.zip
# 設定ファイルを読み込んで、Motisが使用するデータを生成する
./motis import
# サーバーを実行する
./motis server

osm: kanto.osm.pbf
tiles:
  profile: tiles-profiles/full.lua
  db_size: 274877906944
  flush_threshold: 100000
timetable:
  first_day: TODAY
  num_days: 365
  railviz: true
  with_shapes: true
  adjust_footpaths: true
  merge_dupes_intra_src: false
  merge_dupes_inter_src: false
  link_stop_distance: 100
  update_interval: 60
  http_timeout: 30
  incremental_rt_update: false
  use_osm_stop_coordinates: false
  extend_missing_footpaths: false
  max_footpath_length: 15
  max_matching_distance: 25.000000
  preprocess_max_matching_distance: 0.000000
  datasets:
    nagai:
      path: nagai.zip
      default_bikes_allowed: false
      default_cars_allowed: false
      extend_calendar: false
    tamamuraGTFS-flex:
      path: tamamura_GTFS-flex.zip
      default_bikes_allowed: false
      default_cars_allowed: false
      extend_calendar: false
elevators: false
street_routing: true
osr_footpath: false
geocoding: true
reverse_geocoding: true