scripts/update_wrcity_from_geonames.py

   1 #!/usr/bin/python
   2 import argparse
   3 import configparser
   4 from typing import List, Optional
   5
   6 from osgeo import ogr
   7 from osgeo.ogr import Layer, Feature
   8 from osgeo.osr import SpatialReference, CoordinateTransformation
   9
  10
  11 def matching_city(cities_geonames: Layer, city_wr: Feature) -> Optional[Feature]:
  12     city_name = city_wr['name']
  13     if city_name is None:
  14         return
  15
  16     spatial_reference_ll = SpatialReference()
  17     spatial_reference_ll.ImportFromEPSG(4326)
  18
  19     spatial_reference_m = SpatialReference()
  20     spatial_reference_m.ImportFromProj4('+proj=merc +lat_ts=47.3')
  21
  22     coord_transform = CoordinateTransformation(spatial_reference_ll, spatial_reference_m)
  23
  24     a = city_wr.GetGeometryRef().Clone()
  25     a.Transform(coord_transform)
  26
  27     cities_geonames.SetAttributeFilter("FEATCODE != 'PPLX' AND COUNTRY IN  ('AT', 'DE', 'IT', 'CH', 'LI')")
  28     matches = []
  29     for city_geonames in cities_geonames:
  30         alt_names = city_geonames['ALTNAMES'].split(',')
  31         if alt_names == ['']:
  32             alt_names = []
  33         if city_name == city_geonames['NAME'] or city_name in alt_names:
  34             b = city_geonames.GetGeometryRef().Clone()
  35             b.Transform(coord_transform)
  36             distance = a.Distance(b)
  37             if distance <= 10_000:
  38                 matches.append(city_geonames)
  39     if len(matches) == 1:
  40         return matches[0]
  41     if len(matches) > 1:
  42         print([m['NAME'] for m in matches])
  43
  44
  45 def update_wrcity2(ini_files: List[str]):
  46     ogr.UseExceptions()
  47
  48     cities_geonames_source = ogr.Open('/home/philipp/projects/winterrodeln/geodata/geonames.org/cities1000.csv')
  49     cities_geonames = cities_geonames_source.GetLayerByIndex(0)
  50
  51     config = configparser.ConfigParser()
  52     config.read(ini_files)
  53     host = config.get('mysql', 'host')
  54     dbname = config.get('mysql', 'dbname')
  55     user = config.get('mysql', 'user_name')
  56     passwd = config.get('mysql', 'user_pass')
  57
  58     cities_wr_source = ogr.Open(f'MySQL:{dbname},"host={host}","user={user}","password={passwd}","tables=wrcity"')
  59     cities_wr = cities_wr_source.GetLayerByIndex(0)
  60     for city_wr in cities_wr:
  61         if city_wr['geonameid'] is not None:
  62             continue
  63         # geometry = city_wr.GetGeometryRef()
  64         city_geonames = matching_city(cities_geonames, city_wr)
  65         if city_geonames is not None:
  66             print(f'Yeah, {city_geonames}')
  67             city_wr['geonameid'] = int(city_geonames['GEONAMEID'])
  68             cities_wr.SetFeature(city_wr)
  69         # print(geometry.ExportToWkt())
  70         # print(city_wr.items())
  71
  72
  73 def main():
  74     parser = argparse.ArgumentParser(description='Add the geocityid to the wrcity table.')
  75     parser.add_argument('inifile', nargs='+', help='inifile.ini, see: https://www.winterrodeln.org/trac/wiki/ConfigIni')
  76     args = parser.parse_args()
  77     update_wrcity2(args.inifile)
  78
  79
  80 if __name__ == '__main__':
  81     main()