scripts/update_car_distances.py

   1 #!/usr/bin/python
   2 import argparse
   3 import configparser
   4 import csv
   5 import re
   6 import sqlite3
   7 from datetime import timedelta
   8 from itertools import islice
   9 from typing import List, NamedTuple, Optional
  10
  11 import isodate
  12 import jsonschema
  13 from osgeo import ogr
  14 from osgeo.ogr import Layer, DataSource, Geometry, wkbPoint, Feature
  15 from osgeo.osr import SpatialReference, CoordinateTransformation, OAMS_TRADITIONAL_GIS_ORDER
  16
  17 from wrpylib.json_tools import order_json_keys, format_json
  18 from wrpylib.mwapi import WikiSite, page_json
  19 from wrpylib.vao import Vao
  20
  21
  22 class DistInfo(NamedTuple):
  23     city_name: str
  24     geoname_id: int
  25     duration_minutes: int
  26     dist_m: int
  27     co2_kg: float
  28
  29
  30 def vao_car_distance(vao: Vao, parking_lon: float, parking_lat: float, city: Feature) -> Optional[DistInfo]:
  31     geometry = city.GetGeometryRef()
  32     point = geometry.GetPoint(0)
  33     city_lon, city_lat, _ = point
  34     parameter = {
  35         'originCoordLat': city_lat,
  36         'originCoordLong': city_lon,
  37         'destCoordLat': parking_lat,
  38         'destCoordLong': parking_lon,
  39         'groupFilter': 'API_CAR',
  40         'totalCar': '1|evnt=0,aevnt=0,tsta=0,htsta=0,getInitEndTimes=0',
  41     }
  42     response = vao.trip(parameter).json()
  43     trip = response.get('Trip', [])
  44     if trip:
  45         leg_list = trip[0].get('LegList', {}).get('Leg', [])
  46         if leg_list:
  47             leg = leg_list[0]
  48             duration = isodate.parse_duration(leg['duration'])
  49             duration_minutes = int(round(duration / timedelta(minutes=1)))
  50             dist_m = leg['dist']
  51             co2_kg = trip[0]['Eco']['co2']
  52             return DistInfo(city['name'], city['geonameid'], duration_minutes, dist_m, co2_kg)
  53
  54
  55 def update_sledrun(vao: Vao, db_cities: Layer, site: WikiSite, title: str):
  56     sledrun_json_page = site.query_page(f'{title}/Rodelbahn.json')
  57     sledrun_json = page_json(sledrun_json_page)
  58
  59     # for now...
  60     if 'car_distances' in sledrun_json:
  61         return
  62
  63     sledrun_json_orig = sledrun_json.copy()
  64
  65     car_parking = sledrun_json.get('car_parking')
  66     if not car_parking:
  67         print('  (no parking)')
  68         print('')
  69         return
  70
  71     parking = car_parking[0].get('position', {}).get('position')
  72     if not parking:
  73         return
  74     parking_lon = parking['longitude']
  75     parking_lat = parking['latitude']
  76
  77     spatial_reference_ll = SpatialReference()
  78     spatial_reference_ll.ImportFromEPSG(4326)
  79     spatial_reference_ll.SetAxisMappingStrategy(OAMS_TRADITIONAL_GIS_ORDER)
  80
  81     spatial_reference_m = SpatialReference()
  82     spatial_reference_m.ImportFromProj4(f'+proj=merc +lat_ts={parking_lat}')
  83     # spatial_reference_m.ImportFromProj4(f'+proj=merc')
  84
  85     ll_to_m = CoordinateTransformation(spatial_reference_ll, spatial_reference_m)
  86     m_to_ll = CoordinateTransformation(spatial_reference_m, spatial_reference_ll)
  87
  88     loc_ll = Geometry(wkbPoint)
  89     loc_ll.AddPoint(parking_lon, parking_lat)
  90     # print(loc_ll.ExportToWkt())
  91
  92     loc_m = loc_ll.Clone()
  93     loc_m.Transform(ll_to_m)
  94     # print(loc_m.ExportToWkt())
  95     max_dist_m = 60000
  96     bound_m = loc_m.Buffer(max_dist_m)
  97     # print(bound_m.ExportToWkt())
  98     bound_ll = bound_m.Clone()
  99     # print(bound_ll.ExportToWkt())
 100     bound_ll.Transform(m_to_ll)
 101     # print(bound_ll.ExportToWkt())
 102
 103     db_cities.SetSpatialFilter(bound_ll)
 104     db_cities.SetAttributeFilter('level<=2')
 105     dist_info_list = []
 106     for city in db_cities:
 107         dist_info = vao_car_distance(vao, parking_lon, parking_lat, city)
 108         if dist_info is not None:
 109             dist_info_list.append(dist_info)
 110     dist_info_list = sorted(dist_info_list, key=lambda di: di.dist_m)[:3]
 111     car_distances = [{
 112         'km': round(di.dist_m / 1000, 1),
 113         'route': di.city_name,
 114         'minutes': di.duration_minutes,
 115         'geonames_id': di.geoname_id,
 116         'onward_co2_kg': round(di.co2_kg, 1),
 117     } for di in dist_info_list]
 118     sledrun_json['car_distances'] = car_distances
 119     if sledrun_json == sledrun_json_orig:
 120         return
 121
 122     jsonschema.validate(instance=sledrun_json, schema=site.sledrun_schema())
 123     sledrun_json_ordered = order_json_keys(sledrun_json, site.sledrun_schema())
 124     assert sledrun_json_ordered == sledrun_json
 125     sledrun_json_str = format_json(sledrun_json_ordered)
 126
 127     site(
 128         'edit',
 129         pageid=sledrun_json_page['pageid'],
 130         text=sledrun_json_str,
 131         summary=f'Entfernungen zu {title} eingefügt (dank VAO).',
 132         # minor=1,
 133         bot=1,
 134         baserevid=sledrun_json_page['revisions'][0]['revid'],
 135         nocreate=1,
 136         token=site.token(),
 137     )
 138
 139
 140 def update_car_distances(ini_files: List[str]):
 141     ogr.UseExceptions()
 142
 143     config = configparser.ConfigParser()
 144     config.read(ini_files)
 145     host = config.get('mysql', 'host')
 146     dbname = config.get('mysql', 'dbname')
 147     user = config.get('mysql', 'user_name')
 148     passwd = config.get('mysql', 'user_pass')
 149
 150     cities_wr_source = ogr.Open(f'MySQL:{dbname},"host={host}","user={user}","password={passwd}","tables=wrcity"')
 151     db_cities = cities_wr_source.GetLayerByIndex(0)
 152
 153     site = WikiSite(ini_files)
 154     vao = Vao(config.get('vao', 'access_id'))
 155     for result in site.query(list='categorymembers', cmtitle='Kategorie:Rodelbahn', cmlimit='max'):
 156         for page in result['categorymembers']:
 157             print(page['title'])
 158             update_sledrun(vao, db_cities, site, page['title'])
 159
 160
 161 def main():
 162     parser = argparse.ArgumentParser(description='Update car distance information in sledrun JSON files.')
 163     parser.add_argument('inifile', nargs='+', help='inifile.ini, see: https://www.winterrodeln.org/trac/wiki/ConfigIni')
 164     args = parser.parse_args()
 165     update_car_distances(args.inifile)
 166
 167
 168 if __name__ == '__main__':
 169     main()