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