· 8 years ago · Dec 11, 2017, 12:16 AM
1import datetime
2import gzip
3import sqlite3
4import xml.etree.ElementTree as ET
5
6import requests
7from retrying import retry
8import sys
9
10rtti_url = sys.argv[1]
11username = sys.argv[2]
12password = sys.argv[3]
13
14@retry
15def mlrealtime_downloader(rtti_url):
16 now = datetime.datetime.now().isoformat()
17 print('{} downloading: {}'.format(now, rtti_url))
18 r = gzip.decompress(requests.get(rtti_url, auth=(username, password)).content).decode('utf-8')
19 return r
20
21
22def mlrealtime_parser():
23 mlrealtime = mlrealtime_downloader(rtti_url)
24 mlrealtime_xml = ET.fromstring(mlrealtime)
25 tmc_map_version = mlrealtime_xml.get('MAP_VERSION')
26 tmc_units = mlrealtime_xml.get('UNITS')
27 tmc_feed_version = mlrealtime_xml.get('VERSION')
28 tmc_created_timestamp = mlrealtime_xml.get('CREATED_TIMESTAMP')
29 tmc_table_version = mlrealtime_xml.get('TMC_TABLE_VERSION')
30 xmlns = mlrealtime_xml.tag[0:mlrealtime_xml.tag.find('}') + 1]
31
32 timestamp_tuple = datetime.datetime.strptime(tmc_created_timestamp, "%Y-%m-%dT%H:%M:%SZ")
33 year = timestamp_tuple.year
34 month = timestamp_tuple.month
35 day = timestamp_tuple.day
36 hour = timestamp_tuple.hour
37 minute = timestamp_tuple.minute
38 second = timestamp_tuple.second
39
40 now = datetime.datetime.now().isoformat()
41 print('{} opening: {}_{}_{}_{}.sqlite'.format(now, year, month, day, hour))
42
43 conn = sqlite3.connect('{}_{}_{}_{}.sqlite'.format(year, month, day, hour))
44 cursor = conn.cursor()
45 create_tmc = "CREATE TABLE IF NOT EXISTS tmc (year int, month int, day int, hour int, minute int, second int, tmc_created_timestamp varchar(32),tmc_map_version int,tmc_units varchar(16),tmc_feed_version varchar(8),tmc_table_version float,tmc_ebu_country_code varchar(4),tmc_extended_country_code varchar(4),tmc_table_id varchar(4),roadway_id varchar(16),roadway_description varchar(255),place_code int,place_description varchar(255),queue_direction char(1),length float,type char(2),speed float,speed_uncapped float,free_flow float,jam_factor float,confidence float,traversability_status char(1),ss_length float,ss_speed float,ss_speed_uncapped float,ss_free_flow float,ss_jam_factor float,ss_traversability_status char(1))"
46 create_shp = "CREATE TABLE IF NOT EXISTS shp (year int, month int, day int, hour int, minute int, second int, tmc_created_timestamp varchar(32),tmc_map_version int,tmc_units varchar(16),tmc_feed_version varchar(8),tmc_table_version float,tmc_ebu_country_code varchar(4),tmc_extended_country_code varchar(4),tmc_table_id varchar(4),functional_class int,link_id varchar(16),length float,form_of_way varchar(8),shape varchar(512),type char(2),speed float,speed_uncapped float,free_flow float,jam_factor float,confidence float)"
47 cursor.execute(create_tmc)
48 cursor.execute(create_shp)
49
50
51 for child in mlrealtime_xml:
52 if child.get('TY') == 'TMC':
53 rws_tmc = child
54 tmc_ebu_country_code = rws_tmc.get('EBU_COUNTRY_CODE')
55 tmc_extended_country_code = rws_tmc.get('EXTENDED_COUNTRY_CODE')
56 tmc_table_id = rws_tmc.get('TABLE_ID')
57 for rw in rws_tmc:
58 tmc_rw = rw.attrib
59 roadway_id = tmc_rw.get('LI')
60 roadway_description = tmc_rw.get('DE')
61 ss_list = []
62 for fis in rw:
63 for fi in fis:
64 for child in fi:
65 if child.tag == xmlns + 'TMC':
66 tmc_rw_fi_tmc = child
67 place_code = tmc_rw_fi_tmc.get('PC')
68 place_description = tmc_rw_fi_tmc.get('DE')
69 queue_direction = tmc_rw_fi_tmc.get('QD')
70 length = tmc_rw_fi_tmc.get('LE')
71 if child.tag == xmlns + 'CF':
72 tmc_rw_fi_cf = child
73 type = tmc_rw_fi_cf.get('TY')
74 speed = tmc_rw_fi_cf.get('SP')
75 speed_uncapped = tmc_rw_fi_cf.get('SU')
76 free_flow = tmc_rw_fi_cf.get('FF')
77 jam_factor = tmc_rw_fi_cf.get('JF')
78 confidence = tmc_rw_fi_cf.get('CN')
79 traversability_status = tmc_rw_fi_cf.get('TS')
80 if len(tmc_rw_fi_cf) > 0:
81 for sss in tmc_rw_fi_cf:
82 for ss in sss:
83 ss_length = ss.get('LE')
84 ss_speed = ss.get('SP')
85 ss_speed_uncapped = ss.get('SU')
86 ss_free_flow = ss.get('FF')
87 ss_jam_factor = ss.get('JF')
88 ss_traversability_status = ss.get('TS')
89 ss_list.append(
90 [ss_length, ss_speed, ss_speed_uncapped, ss_free_flow, ss_jam_factor,
91 ss_traversability_status])
92 else:
93 ss_list = [[]]
94 for ss in ss_list:
95 if len(ss) > 0:
96 ss_length = ss[0]
97 ss_speed = ss[1]
98 ss_speed_uncapped = ss[2]
99 ss_free_flow = ss[3]
100 ss_jam_factor = ss[4]
101 ss_traversability_status = ss[5]
102 tmc_sql = "insert into tmc values ({},{},{},{},{},{},'{}',{},'{}','{}',{},'{}','{}','{}','{}','{}',{},'{}','{}',{},'{}',{},{},{},{},{},'{}',{},{},{},{},{},'{}')".format(
103 year, month, day, hour, minute, second, tmc_created_timestamp,
104 tmc_map_version,
105 tmc_units, tmc_feed_version, tmc_table_version, tmc_ebu_country_code,
106 tmc_extended_country_code, tmc_table_id, roadway_id, roadway_description,
107 place_code, place_description, queue_direction, length, type, speed,
108 speed_uncapped, free_flow, jam_factor, confidence, traversability_status,
109 ss_length, ss_speed, ss_speed_uncapped, ss_free_flow, ss_jam_factor,
110 ss_traversability_status)
111 cursor.execute(tmc_sql)
112 else:
113 tmc_sql = "insert into tmc values ({},{},{},{},{},{},'{}',{},'{}','{}',{},'{}','{}','{}','{}','{}',{},'{}','{}',{},'{}',{},{},{},{},{},'{}','','','','','','')".format(
114 year, month, day, hour, minute, second, tmc_created_timestamp,
115 tmc_map_version,
116 tmc_units, tmc_feed_version, tmc_table_version, tmc_ebu_country_code,
117 tmc_extended_country_code, tmc_table_id, roadway_id, roadway_description,
118 place_code, place_description, queue_direction, length, type, speed,
119 speed_uncapped, free_flow, jam_factor, confidence, traversability_status)
120 cursor.execute(tmc_sql)
121 if child.get('TY') == 'SHP':
122 rws_shp = child
123 shp_ebu_country_code = rws_shp.get('EBU_COUNTRY_CODE')
124 shp_extended_country_code = rws_shp.get('EXTENDED_COUNTRY_CODE')
125 shp_table_id = rws_shp.get('TABLE_ID')
126 for rw in rws_shp:
127 for fis in rw:
128 for fi in fis:
129 for fi_elem in fi:
130 if fi_elem.tag == xmlns + 'SHP':
131 functional_class = fi_elem.get('FC')
132 link_id = fi_elem.get('LID')
133 length = fi_elem.get('LE')
134 form_of_way = fi_elem.get('FW')
135 shape = fi_elem.text
136 if fi_elem.tag == xmlns + 'CF':
137 type = fi_elem.get('TY')
138 speed = fi_elem.get('SP')
139 speed_uncapped = fi_elem.get('SU')
140 free_flow = fi_elem.get('FF')
141 jam_factor = fi_elem.get('JF')
142 confidence = fi_elem.get('CN')
143 shp_sql = "insert into shp values ({},{},{},{},{},{},'{}',{},'{}','{}',{},'{}','{}','{}',{},'{}',{},'{}','{}','{}',{},{},{},{},{})".format(
144 year, month, day, hour, minute, second, tmc_created_timestamp, tmc_map_version,
145 tmc_units, tmc_feed_version, tmc_table_version, shp_ebu_country_code,
146 shp_extended_country_code, shp_table_id, functional_class, link_id, length,
147 form_of_way,
148 shape, type, speed, speed_uncapped, free_flow, jam_factor, confidence)
149 cursor.execute(shp_sql)
150 conn.commit()
151
152mlrealtime_parser()