def _make_link_name_number(self): '''道路名称和番号.''' temp_file_obj = cache_file.open('link_name') # 创建临时文件(名称) temp_shield_file = cache_file.open('link_shield') # 创建临时文件(番号) for name_info in self._get_name_info(): link_id = name_info[0] org_name = name_info[1] l_country = name_info[2] r_country = name_info[3] if l_country != r_country: self.log.error('l_country != r_country. link') continue lang_code = LANGUAGE_CODE.get(l_country) if not lang_code: self.log.error('No Language code. country=%s' % l_country) continue if org_name in NAME_REPLACE_DICT: org_name = NAME_REPLACE_DICT.get(org_name) # 如果被替换成空,代表错误的,并要被删除的名称 if not org_name: continue org_name_list = org_name.split(NAME_SPIT_CHAR) # 把名称, 拆成名称list和番号list name_list = self._get_name_and_number(org_name_list, l_country) road_number_list, road_name_list = name_list # ## 取得多语言多个名称的名称和番号 multi_name = self._get_multi_name(road_name_list, road_number_list, lang_code) multi_number = self._get_multi_number(road_number_list, lang_code) if multi_name: json_name = multi_name.json_format_dump() # 存到本地的临时文件 self._store_name_to_temp_file(temp_file_obj, link_id, json_name) if multi_number: json_name = multi_number.json_format_dump() # 存到本地的临时文件 self._store_name_to_temp_file(temp_shield_file, link_id, json_name) # ## 把名称导入数据库 temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'temp_link_name') self.pg.commit2() #temp_file_obj.close() cache_file.close(temp_file_obj, True) # ## 把shield导入数据库 temp_shield_file.seek(0) self.pg.copy_from2(temp_shield_file, 'temp_link_shield') self.pg.commit2() #temp_shield_file.close() cache_file.close(temp_shield_file, True) self.log.info('End Make Link Name and Shield Info.')
def _make_enter_exit_name(self): '''出入口名称''' self.log.info('start make exit sapa name...') temp_file_obj = cache_file.open('temp_exit_sapa_name') for nameinfo in self._get_enter_exit_name(): node_id = nameinfo[0] poi_id = nameinfo[1] poi_kind = nameinfo[2] poi_names = nameinfo[3] lang_codes = nameinfo[4] if not node_id: self.log.error('no node id') continue kind = POI_TYPE_DICT.get(poi_kind) json_name = self._get_json_name(lang_codes, poi_names) if not json_name: self.log.error('No json name. node id is %s' % node_id) self._store_name_to_temp_file(temp_file_obj, node_id, poi_id, kind, json_name) temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'mid_temp_hwy_exit_enter_poi_name_ni') self.pg.commit2() cache_file.close(temp_file_obj, True) self.CreateIndex2('mid_temp_hwy_exit_enter_poi_name_ni_node_id_idx') self.log.info('end make exit sapa name...') return 0
def __merge_dupli_link_name_shield(self): '''合并重叠link的名称和番号''' self.CreateTable2('temp_dupli_name_shield_comp') sqlcmd = """ SELECT array_agg(link_id_a) as link_del, link_id_b from temp_dupli_name_shield group by link_id_b; """ import subproj proj_object = subproj.proj_factory().instance('') comp_factory = proj_object.comp_factory proc_dict = comp_factory.CreateOneComponent('Dictionary') proc_dict.set_language_code() temp_file_obj = cache_file.open('diff_name') # 创建临时文件 features = self.get_batch_data(sqlcmd) for feature in features: link_id_del = feature[0] link_id_keep = feature[1] link_id_list = link_id_del link_id_list.append(link_id_keep) rtn_name_shield = proc_dict.merge_links_name(link_id_list) rtn_name, rtn_shield = rtn_name_shield if not rtn_name: rtn_name = '' if not rtn_shield: rtn_shield = '' temp_file_obj.write('%d\t%s\t%s\n' % (link_id_keep, rtn_name, rtn_shield)) temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'temp_dupli_name_shield_comp') self.pg.commit2() #temp_file_obj.close() cache_file.close(temp_file_obj,True)
def _make_main_link_mapping(self): '''本线link的Mapping''' self.log.info('Start Making Main Link Mapping.') file_obj = cache_file.open('temp_highway_mapping_main') # 创建临时文件 self.pg.connect1() for road_code, updown, path in self.data_mng.get_road_code_path(): bwd_ic_nos = [] # 后方(车流方向) fwd_ic_nos = [] # 前方(车流方向) bwd_ic_no = None fwd_ic_no = None road_maps = [] # 整条线路的Link section_maps = [] # 两个相邻的设施之间所有link for u, v in zip(path[:-1], path[1:]): link_id = self.G.get_linkid(u, v) displayclass = self.G.get_disp_class(u, v) road_no = self.data_mng.get_road_no(road_code) tile_id_14 = self.G.get_tile_id(u, v) tile_id = convert_tile_id(tile_id_14) # 14层tile转成高层tile road_kind = updown if(self.G.is_hwy_node(u) or self.data_mng.is_boundary_node(u)): # 取得后方设施 if fwd_ic_nos: bwd_ic_nos = fwd_ic_nos else: bwd_ic_nos = self.data_mng.get_ic_nos_by_node(u, road_code ) fwd_ic_nos = [] fwd_ic_no = None # 取得前方设施 if(self.G.is_hwy_node(v) or self.data_mng.is_boundary_node(v)): fwd_ic_nos = self.data_mng.get_ic_nos_by_node(v, road_code) if bwd_ic_nos: bwd_ic_no = max(bwd_ic_nos) else: self.log.error('Backward ic_no is null. road_code=%s,' 'link_id=%s.' % (road_code, link_id)) bwd_ic_no = 0 if fwd_ic_nos: fwd_ic_no = min(fwd_ic_nos) link_map = HwyLinkMapping(road_kind, 1, road_no, displayclass, link_id, None, bwd_ic_no, # fwd_ic先设成空 HWY_PATH_TYPE_MAIN, tile_id) section_maps.append(link_map) if fwd_ic_no: for link_map in section_maps: link_map.forward_ic_no = fwd_ic_no road_maps.append(link_map) section_maps = [] if not fwd_ic_no: # 最后一段线路有没有前方设施 self.log.error('Main_road: No Forward IC. road_code=%s' % road_code) self._insert_mapping(road_maps, file_obj) # 保存整条线路的Link的Mapping情报 self._store_hwy_mapping(file_obj) self.CreateIndex2('highway_mapping_link_id_idx') self.log.info('End Making Main Link Mapping.')
def _make_signpost_passlink(self): self.CreateFunction2('mid_findpasslinkbybothlinks') self.CreateFunction2('mid_get_connected_node') self.CreateTable2('mid_temp_signpost_passlink') sqlcmd = """ SELECT sign_id, destination_number, originating_link_id, dest_link_id, pass_link, mid_get_connected_node( (regexp_split_to_array(pass_link, E'\\\|+'))[1]::bigint, (regexp_split_to_array(pass_link, E'\\\|+'))[2]::bigint ) as node_id FROM ( SELECT origin.sign_id, destination_number, originating_link_id, dest_link_id, mid_findpasslinkbybothlinks( originating_link_id, dest_link_id, a.s_node, a.e_node, 1, 20 ) as pass_link -- include: inlink,outlink FROM rdf_sign_origin AS origin LEFT JOIN rdf_sign_destination as dest ON origin.sign_id = dest.sign_id left join link_tbl as a on originating_link_id = a.link_id ) AS A; """ temp_file_obj = cache_file.open('signpost_passlink') # 创建临时文件 signs = self.get_batch_data(sqlcmd, 1024) for sign_info in signs: sign_id = sign_info[0] dest_number = sign_info[1] in_link_id = sign_info[2] out_link_id = sign_info[3] pass_link = sign_info[4] # 包含InLink和OutLink node_id = sign_info[5] if not pass_link or not node_id: # 没有找到路 self.log.error("Can't find the path. inlink=%d, outlink=%d" % (in_link_id, out_link_id)) continue pass_link = pass_link.split('|')[1:-1] # 去掉InLink和OutLink pass_link_cnt = len(pass_link) if pass_link: pass_link = '|'.join(pass_link) else: pass_link = '' str_info = '%d\t%d\t%d\t%d\t%d\t%s\t%d' % ( sign_id, dest_number, node_id, in_link_id, out_link_id, pass_link, pass_link_cnt) self._store_name_to_temp_file(temp_file_obj, str_info) # ## 把名称导入数据库 temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'mid_temp_signpost_passlink') self.pg.commit2() # close file #temp_file_obj.close() cache_file.close(temp_file_obj, True) return 0
def __DoGet_Admin_name(self, code_dict, code_country_dict): self.log.info('begin admin_name ..') sqlcmd = ''' select id, name,country_name from ( select code as id, "name" as name,"name" as country_name from temp_country union select a.code as id,a."name" as name,b."name" as country_name from temp_state as a left join temp_country as b on a.country_code = b.code union select a.code as id, a."name" as name, c."name" as country_name from temp_city as a left join temp_state as b on a.state_code = b.code left join temp_country as c on b.country_code = c.code )as temp_all where name is not null order by id, name, country_name; ''' #test set_language self.CreateTable2('temp_admin_name') MultiLangName.set_language_code(code_dict) temp_file_obj = cache_file.open('temp_admin_name_new') rows = self.get_batch_data(sqlcmd) for row in rows: id = row[0] name = row[1] country_name = row[2] if name: ml_name = MultiLangName(code_country_dict[country_name], name, NAME_TYPE_OFFICIAL) json_name = ml_name.json_format_dump() if not json_name: self.log.error('Json Name is null. id=%d' % id) else: self.__store_name_to_temp_file(temp_file_obj, id, json_name, code_country_dict[country_name]) # ## 把名称导入数据库 temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'temp_admin_name') self.pg.commit2() # close file #temp_file_obj.close() cache_file.close(temp_file_obj, True) self.log.info('end admin_name ..')
def __get_temp_phoneme_table(self, table, column): # gid if not self.pg.IsExistColumn(table, 'gid'): sqlcmd = """ alter table %s add column gid serial primary key; """ % table self.pg.execute2(sqlcmd) self.pg.commit2() # temp_file_obj = cache_file.open(table) sqlcmd = """ drop table if exists temp_phoneme_%s; create table temp_phoneme_%s ( gid bigint, pym varchar ); """ % (table, table) self.pg.execute2(sqlcmd) self.pg.commit2() # sqlcmd = """ select array_agg(gid) as gid_array, 'BPMF', [column] from ( select gid, [column] from [table] where [column] is not null )as t group by [column]; """ sqlcmd = sqlcmd.replace('[table]', table) sqlcmd = sqlcmd.replace('[column]', column) phlist = self.get_batch_data(sqlcmd) for ph in phlist: gid_array = ph[0] language = ph[1] phoneme = ph[2] phoneme_pym = common.ntsamp_to_lhplus.ntsamapa2lhplus( language, phoneme) phoneme_pym = phoneme_pym.replace('\\', '\\\\') for gid in gid_array: temp_file_obj.write('%s\t%s\n' % (str(gid), phoneme_pym)) # temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'temp_phoneme_%s' % table) self.pg.commit2() sqlcmd = """ create index temp_phoneme_%s_gid_idx on temp_phoneme_%s using btree (gid); """ % (table, table) self.pg.execute2(sqlcmd) self.pg.commit2() cache_file.close(temp_file_obj, True)
def _make_path_link(self): "找到SignPost对应的link序 (如:in_link_id, out_link_id, passlid, passlid_cnt)" # 同一个id中, # seqnr = 1的, 作为in_link_id # seqnr最大的,作为out_link_id # 处于以上两者之间的,作为pass link self.CreateTable2('mid_temp_signpost_passlink') self.CreateFunction2('mid_get_connect_junction') # self.CreateFunction2('mid_make_signpost_path_links') temp_file_obj = cache_file.open('signpost_passlink') # 创建临时文件 sqlcmd = """ SELECT sign_id, link_ids, folder, mid_get_connect_junction(link_ids[1], link_ids[2]) FROM ( SELECT sign_id, array_agg(link_id) as link_ids, folder from ( SELECT sign_id, seqnr, edge_id::BIGINT as link_id, folder FROM org_signpost_table_1 order by folder, sign_id, seqnr ) as p group by folder, sign_id order by folder, sign_id ) AS A """ pathes = self.get_batch_data(sqlcmd) for path_info in pathes: sign_id = path_info[0] link_ids = path_info[1] folder = path_info[2] node_id = path_info[3] if not sign_id: self.log.error('Error Sign ID.') continue if not node_id: self.log.error('Error Node ID.') continue if link_ids: in_link_id = link_ids[0] out_link_id = link_ids[-1] pass_link = '|'.join([str(p) for p in link_ids[1:-1]]) pass_link_cnt = len(link_ids[1:-1]) str_info = ('%s\t%d\t%d\t%d\t%d\t%s\t%d' % (folder, sign_id, in_link_id, node_id, out_link_id, pass_link, pass_link_cnt)) self._store_name_to_temp_file(temp_file_obj, str_info) # ## 把名称导入数据库 temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'mid_temp_signpost_passlink') self.pg.commit2() # close file #temp_file_obj.close() cache_file.close(temp_file_obj,True) return 0
def _make_signpost_element(self): self.log.info('Start Make SignPost element.') sqlcmd = """ SELECT folder, link_id as out_link_id, rd_signs, l_country, gid as sign_id FROM org_processed_line AS line WHERE rd_signs IS NOT NULL AND rd_signs <> '' order by folder, out_link_id, sign_id; """ self.CreateTable2('mid_temp_signpost') temp_file_obj = cache_file.open('signpost_element') # 创建临时文件 signs = self.get_batch_data(sqlcmd) for sign_info in signs: folder = sign_info[0] # folder名/区域名 out_link_id = int(sign_info[1]) rd_signs = sign_info[2] country = sign_info[3] sign_id = sign_info[4] lang_code = LANGUAGE_CODE.get(country) link_end_pos = rd_signs.index(LIND_END_CHAR) in_link_id = int(rd_signs[:link_end_pos]) exitno_end_pos = rd_signs.index(EXIT_NO_END_CHAR) exitno = rd_signs[link_end_pos + 1:exitno_end_pos].strip() if rd_signs[exitno_end_pos + 1] == 'T': name = rd_signs[exitno_end_pos + 2:] else: self.log.warning('Unkown Name Type. rd_signs=%s' % rd_signs) if name: signpost_name = MultiLangName(lang_code, name, NAME_TYPE_OFFICIAL) else: # print folder, out_link_id signpost_name = None multi_exitno = self._get_exit_no(exitno, lang_code) sign_post = SignPostElementMmi(sign_id, folder) sign_post.set_exit_no(multi_exitno) sign_post.set_signpost_name(signpost_name) if sign_post.is_empty(): # 名称、route_no和exit_no都为空 self.log.warning('is empty folder=%s, out_link_id=%s.' % (folder, out_link_id)) continue str_info = sign_post.to_string() self._store_name_to_temp_file(temp_file_obj, in_link_id, out_link_id, str_info) # ## 把名称导入数据库 temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'mid_temp_signpost') self.pg.commit2() # close file #temp_file_obj.close() cache_file.close(temp_file_obj, True) self.log.info('End Make SignPost element.') return 0
def _make_jv_name(self): '''从JunctionView抽取名称''' sqlcmd = """ SELECT array_agg(gid), inlinkid, outlinkid, array_agg(th_roadname) as th_roadnames, array_agg(en_roadname) as en_roadnames, array_agg(tts_namt) as tts_namts, node_id, in_s_node, in_e_node, out_s_node, out_e_node FROM ( SELECT org_junctionview.gid, arc1::bigint AS inlinkid, arc2::bigint as outlinkid, th_roadname, en_roadname, ''::text as tts_namt, mid_get_connect_node(arc1::bigint, arc2::bigint) as node_id, in_l.s_node as in_s_node, in_l.e_node as in_e_node, out_l.s_node as out_s_node, out_l.e_node as out_e_node FROM org_junctionview left join ( SELECT pic_name, first_arrow_name as arrow_name, trim(th_roadname1, '') as th_roadname, trim(en_roadname1) as en_roadname FROM temp_junctionview_name_sort union SELECT pic_name, second_arrow_name as arrow_name, trim(th_roadname2, '') as th_roadname, trim(en_roadname2) as en_roadname FROM temp_junctionview_name_sort ) as jv_name on day_pic = pic_name and arrowimg = arrow_name left join link_tbl as in_l ON arc1 = in_l.link_id left join link_tbl as out_l ON arc2 = out_l.link_id order by arc1, arc2, gid ) AS A GROUP BY inlinkid, outlinkid, node_id, in_s_node, in_e_node, out_s_node, out_e_node ORDER BY inlinkid, outlinkid, node_id; """ temp_file_obj = cache_file.open('temp_jv_name') # 创建临时文件 self.CreateTable2('temp_jv_name') self._make_temp_name(sqlcmd, temp_file_obj, 'temp_jv_name') # close file #temp_file_obj.close() cache_file.close(temp_file_obj, True) return 0
def _make_hwy_exit_name(self): self.log.info('Make exit Name.') self.CreateIndex2('org_ig_elemid_idx') self.CreateIndex2('org_ig_id_idx') self.CreateIndex2('org_is_id_idx') sqlcmd = """ SELECT distinct node_tbl.node_id, org_is.fwinttyp, org_is.name, org_is.namelc FROM org_is LEFT JOIN org_ig ON org_is.id = org_ig.id INNER JOIN node_tbl on org_ig.elemid = node_tbl.node_id WHERE org_is.inttyp = 1 -- FREE WAY AND org_is.fwinttyp IN (1, 2) -- MOTORWAY AND org_ig.elemtyp = 4120 -- Junction AND org_is.name is not null; """ temp_file_obj = cache_file.open('hwy_exit_name') names = self.get_batch_data(sqlcmd) for name_info in names: node_id = name_info[0] fwin_type = name_info[1] name = name_info[2] lang_code = name_info[3] name_type = NAME_TYPE_OFFICIAL is_exit_name, is_junction_name = self._get_name_type(fwin_type) if not name: self.log.warning('No name and number. id=%d' % node_id) continue else: multi_name = MultiLangNameTa(node_id, lang_code, name, name_type) json_name = multi_name.json_format_dump() if not json_name: self.log.error('Json Name is none. node_id=%d' % node_id) self._store_name_to_temp_file(temp_file_obj, node_id, is_exit_name, is_junction_name, json_name) temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'mid_temp_hwy_exit_name_ta') self.pg.commit2() cache_file.close(temp_file_obj, True) self.log.info('End Make exit Name.') return 0
def _make_link_shield(self): self.log.info('Start Make Link Shield Info') sqlcmd = """ SELECT routeid, nav_rdnum, c_access, intrdlnnum FROM org_l_tran where nav_rdnum is not null or intrdlnnum is not null; """ temp_number_file_obj = cache_file.open('link_shield') # 创建临时文件 numbers = self.get_batch_data(sqlcmd) for number_info in numbers: link_id = number_info[0] link_num = number_info[1] c_access = number_info[2] intrdlnnum = number_info[3] num_list = [] nums = self._merge_route_num(link_num, intrdlnnum) for routenum in nums: shield_id = self._convert_shield_id(c_access, routenum) if not shield_id: self.log.error('No Shield Id. linkId=%s' % link_id) continue str_number = shield_id + '\t' + routenum number = self.mult_obj.convert_names_to_list( str_number, str_number, '', 'shield') if number: num_list += number if num_list: # shield的泰文和英文一样 json_shield = self.mult_obj.json_format_dump2(num_list) # 存到本地的临时文件 self._store_name_to_temp_file(temp_number_file_obj, link_id, json_shield) else: self.log.error('No Shield Info. linkID=%s' % link_id) # ## 导入数据库 temp_number_file_obj.seek(0) self.pg.copy_from2(temp_number_file_obj, 'temp_link_shield') self.pg.commit2() # close file #temp_number_file_obj.close() cache_file.close(temp_number_file_obj, True) self.log.info('End Make Link Shield Info') return 0
def _make_hwy_path_name(self): ''' make hwy path name''' self.log.info('start make hwy path name') temp_file_obj = cache_file.open('path_name') multi_name_obj = None for row in self._get_hwy_path_name(): path_id = row[0] lang_list = row[1] path_name_list = row[2] one_name_list = zip(lang_list, path_name_list) for index in xrange(0, len(one_name_list)): (org_lang, name) = one_name_list[index] lang = LANG_CODE_DICT.get(org_lang) if not lang: self.log.error('Unknown language_code=%s' % org_lang) continue if index == 0: if lang not in ('CHI', 'CHT'): self.log.error('no CHI or CHT name') multi_name_obj = MultiLangNameRDF(lang, name) else: multi_name_obj = self.set_trans_name( multi_name_obj, None, name, lang, None, # phonetic None # lang_code ) if multi_name_obj: json_name = multi_name_obj.json_format_dump() if not json_name: self.log.error('no json name') self._store_name_to_temp_file(temp_file_obj, path_id, json_name) # ## 把名称导入数据库 temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'mid_temp_hwy_path_name_ni') self.pg.commit2() cache_file.close(temp_file_obj, True) self.log.info('end make hwy path name') return 0
def _make_path_link(self): "找到SignPost对应的link序 (如:in_link_id, out_link_id, passlid, passlid_cnt)" # 同一个id中, # seqnr = 1的, 作为in_link_id # seqnr最大的,作为out_link_id # 处于以上两者之间的,作为pass link self.CreateTable2('mid_temp_signpost_passlink') # self.CreateFunction2('mid_make_signpost_path_links') temp_file_obj = cache_file.open('signpost_passlink') # 创建临时文件 sqlcmd = """ SELECT id, array_agg(link_id) as link_ids from ( SELECT id, seqnr, trpelid::bigint as link_id FROM org_sp order by id, seqnr ) as p group by id; """ pathes = self.get_batch_data(sqlcmd) for path_info in pathes: sign_id = path_info[0] link_ids = path_info[1] if not sign_id: self.log.error('Error Sign ID.') continue if link_ids: in_link_id = link_ids[0] out_link_id = link_ids[-1] pass_link = '|'.join([str(p) for p in link_ids[1:-1]]) pass_link_cnt = len(link_ids[1:-1]) str_info = '%d\t%d\t%d\t%s\t%d' % ( sign_id, in_link_id, out_link_id, pass_link, pass_link_cnt) self._store_name_to_temp_file(temp_file_obj, str_info) # ## 把名称导入数据库 temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'mid_temp_signpost_passlink') self.pg.commit2() # close file #temp_file_obj.close() cache_file.close(temp_file_obj, True) return 0
def _make_exit_sapa_name(self): ''' ''' self.log.info('start make exit sapa name...') temp_file_obj = cache_file.open('temp_exit_sapa_name') for nameinfo in self._get_exit_sapa_name(): node_id = nameinfo[0] poi_id = nameinfo[1] poi_name = nameinfo[2] lang = nameinfo[3] name_list = poi_name.split('|') lang_list = lang.split('|') # 以下!处理lang_code转换 list_lan = list() for lan in lang_list: list_lan.append(LANG_CODE_DICT[lan]) official_name = name_list[0] lang_code = list_lan[0] trans_langs = list_lan[1:] trans_names = name_list[1:] if not node_id: self.log.error('no node id') continue multi_name_obj = MultiLangNameRDF(lang_code, official_name) multi_name_obj = self.set_trans_name(multi_name_obj, None, trans_names, trans_langs, None, None) json_name = multi_name_obj.json_format_dump() if not json_name: self.log.error('no json name. node id is %s' % node_id) self._store_name_to_temp_file(temp_file_obj, node_id, poi_id, json_name) temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'mid_temp_hwy_exit_poi_name') self.pg.commit2() cache_file.close(temp_file_obj, True) self.log.info('end make exit sapa name...') return 0
def _make_link_shield(self): # 盾牌及番号 self.log.info('Make Link Shield.') temp_shield_file = cache_file.open('link_shield') # 创建临时文件 self.CreateTable2('temp_link_shield') sqlcmd = """ SELECT id::BIGINT, namelc, routenum FROM org_city_nw_gc_polyline WHERE routenum is not null; """ numbers = self.get_batch_data(sqlcmd) shield_obj = ShieldMmi() for number_info in numbers: link_id = number_info[0] lang_code = number_info[1] routenum = number_info[2] # 道路番号 shieldid = shield_obj.convert_shield_id(routenum) if not shieldid: self.log.error('ShieldID is none. linkid=%d' % link_id) continue ml_shield = MultiLangShield(shieldid, routenum, lang_code) if ml_shield: # ## shield number json_name = ml_shield.json_format_dump() if not json_name: self.log.error('Json Shield is none. linkid=%d' % link_id) # 存到本地的临时文件 self._store_name_to_temp_file(temp_shield_file, link_id, json_name ) # ## 把shield导入数据库 temp_shield_file.seek(0) self.pg.copy_from2(temp_shield_file, 'temp_link_shield') self.pg.commit2() # close file #temp_shield_file.close() cache_file.close(temp_shield_file,True) self.log.info('End Make Link Shield Info.') return 0
def __fix_phoneme(self): self.log.info('revise the vce_phonetic_text table ...') if not self.pg.IsExistTable('vce_phonetic_text_org'): sql = ''' alter table vce_phonetic_text RENAME TO vce_phonetic_text_org; CREATE TABLE vce_phonetic_text ( phonetic_id bigint NOT NULL, phonetic_string character varying(250) NOT NULL, phonetic_language_code character(3) NOT NULL, transcription_method character(1) NOT NULL, CONSTRAINT pk_vce_phonetic PRIMARY KEY (phonetic_id) ); ''' self.pg.execute2(sql) self.pg.commit2() self.pg.execute2('delete from vce_phonetic_text') self.pg.commit2() sql = ''' select phonetic_id, phonetic_string, phonetic_language_code, transcription_method from vce_phonetic_text_org; ''' temp_file_obj = cache_file.open('vce_phonetic_text_new') phlist = self.get_batch_data(sql) for ph in phlist: fields = list(ph) fields[1] = common.ntsamp_to_lhplus.nt_sampa_2_lh_plus( ph[2], ph[1]) self.__store_name_to_temp_file(temp_file_obj, fields[0], fields[1], fields[2], fields[3]) # ## temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'vce_phonetic_text') self.pg.commit2() # close file cache_file.close(temp_file_obj, True)
def _make_hwy_sapa_name(self): '''SAPA名称''' self.log.info('start make exit sapa name...') temp_file_obj = cache_file.open('sapa_name') for nameinfo in self._get_sapa_name(): poi_id = nameinfo[0] poi_names = nameinfo[1] lang_codes = nameinfo[2] if not poi_id: self.log.error('No poi_id') continue # kind = SAPA_TYPE_DICT.get(poi_kind) json_name = self._get_json_name(lang_codes, poi_names) if not json_name: self.log.error('No json name. poi_id is %s' % poi_id) self._store_name_to_temp_file(temp_file_obj, poi_id, json_name) temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'mid_temp_hwy_sapa_name') self.pg.commit2() cache_file.close(temp_file_obj, True) self.log.info('end make exit sapa name...') return 0
def _make_hwy_sapa_name(self): self.log.info('Make mid_temp_hwy_sapa_name') sqlcmd = ''' SELECT id, name, lancd FROM org_mnpoi_pi -- 7358:Truck Stop, 7395: Rest Area, 7369: Open Parking Area WHERE feattyp in (7358, 7395, 7369) AND name is not null ''' temp_file_obj = cache_file.open('hwy_sapa_name') contry_name = getProjCountry() names = self.get_batch_data(sqlcmd) default_lang_code = DEFAULT_LANG_CODES.get(contry_name.lower()) for name_info in names: poi_id = name_info[0] name = name_info[1] lang_code = name_info[2] name_type = NAME_TYPE_OFFICIAL if not name: self.log.warning('No name and number. id=%d' % poi_id) continue if not lang_code: lang_code = default_lang_code if not lang_code: self.log.error('language code is none.poi_id=%d' % poi_id) multi_name = MultiLangNameTa(poi_id, lang_code, name, name_type) json_name = multi_name.json_format_dump() if not json_name: self.log.error('Json Name is none. poi_id=%d' % poi_id) self._store_name_to_temp_file(temp_file_obj, poi_id, json_name) temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'mid_temp_hwy_sapa_name') self.pg.commit2() cache_file.close(temp_file_obj, True) self.log.info('End make mid_temp_hwy_sapa_name') return 0
def _make_toll_plaza_name(self): '''从Toll Plaza抽取名称''' self.CreateFunction2('mid_get_connect_node') sqlcmd = """ SELECT array_agg(gid) as gids, from_arc as inlinkid, to_arc as outlinkid, ARRAY_AGG(exit_namt) AS exit_namts, ARRAY_AGG(exit_name) AS exit_names, ARRAY_AGG(tts_namt) AS tts_namts, nodeid, in_s_node, in_e_node, out_s_node, out_e_node FROM ( SELECT org_tollplaza.gid, from_arc::bigint, to_arc::bigint, exit_namt, exit_name, tts_namt, mid_get_connect_node(from_arc::bigint, to_arc::bigint) as nodeid, in_l.s_node as in_s_node, in_l.e_node as in_e_node, out_l.s_node as out_s_node, out_l.e_node as out_e_node FROM org_tollplaza left join link_tbl as in_l ON from_arc::bigint = in_l.link_id left join link_tbl as out_l ON to_arc::bigint = out_l.link_id order by from_arc, to_arc, gid ) as A group by from_arc, to_arc, nodeid, in_s_node, in_e_node, out_s_node, out_e_node order by from_arc, to_arc, nodeid; """ temp_file_obj = cache_file.open('temp_toll_plaza_name') # 创建临时文件 self.CreateTable2('temp_toll_plaza_name') self._make_temp_name(sqlcmd, temp_file_obj, 'temp_toll_plaza_name') # close file #temp_file_obj.close() cache_file.close(temp_file_obj, True) return 0
def _make_signpost_element(self): self.log.info('Start Make SignPost element.') sqlcmd = """ SELECT folder, sign_id, array_agg("row") as "rows", array_agg("column") as "columns", array_agg(itemtype) as itemtypes, array_agg(iteminfo) as iteminfos FROM ( SELECT gid, sign_id::BIGINT, "row"::INTEGER, "column"::INTEGER, itemtype::INTEGER, iteminfo, folder FROM org_signpost_table_2 order by folder, sign_id, "row", "column", gid ) AS A GROUP BY folder, sign_id ORDER BY folder, sign_id; """ self.CreateTable2('mid_temp_signpost_element') temp_file_obj = cache_file.open('signpost_element') # 创建临时文件 signs = self.get_batch_data(sqlcmd) for sign_info in signs: folder = sign_info[0] # folder名or区域名 sign_id = sign_info[1] # 看板id # rows = sign_info[2] # 方向番号 # columns = sign_info[3] # 同个方向内顺序号 itemtypes = sign_info[4] # 种别 iteminfos = sign_info[5] # 种别 sign_post = SignPostElementMmi(sign_id, folder) exit_no = None route_no = None signpost_name = None lang_code = MMI_OFFICIAL_LANG_CODE for itemtype, iteminfo in zip(itemtypes, iteminfos): # 去掉距离 name = self._delete_distance_str(iteminfo) if not name: # 去掉只有距离,没有名称的记录 # self.log.info(iteminfo) continue sign_type = self._get_sign_type(itemtype) if not sign_type: # 空不收录 continue name_type = self._cvt_name_type(sign_type) # ## 处理各种种别的数据 if SIGN_POST_TYPE_SHIELD_ID == sign_type: # shield id route_no = self._get_route_no(name, lang_code) if not route_no: # 'National Highway 5', 'National Highway 5 10 Km' c = re.compile(r'^National\s*Highway', re.I) if not c.findall(name): self.log.error('Error RouteNo. ' 'SignId=%s,Folder=%s,info=%s' % (sign_id, folder, iteminfo)) # 'National Highway' replace 'NH' routenum = c.sub('NH', name) info_list = routenum.split(' ') length = len(info_list) p = re.compile(r'\D+') # len=2, 番号是数字,如:'National Highway 5'改为'NH 5',并做成番号 if length == 2 and not p.findall(info_list[1]): route_no = self._get_route_no(routenum, lang_code) else: # 把这种'National Highway 5 10 Km', 做成方面名称 # 由于距离被去掉,这里一般不会再执行 self.log.error('Error RouteNo. ' 'SignId=%s,Folder=%s,info=%s' % (sign_id, folder, iteminfo)) continue if route_no: sign_post.add_route_no(route_no) else: self.log.error('Get RouteNo Failed. ' 'SignId=%s,Folder=%s,type=%s,info=%s' % (sign_id, folder, itemtype, iteminfo)) elif SIGN_POST_TYPE_EXIT_NO == sign_type: # 出口番号 # 存在多个出口番号,其他出口番号做出口番号的别名 e_no = MultiLangName(lang_code, name, name_type) if exit_no: exit_no.add_alter(e_no) else: # 第一个出口番号 exit_no = e_no elif SIGN_POST_TYPE_NAME == sign_type: # 方面名称 sign_name = MultiLangName(lang_code, name, name_type) if signpost_name: # 其他番号 signpost_name.add_alter(sign_name) else: # 第一个名称 signpost_name = sign_name else: continue if route_no or exit_no or signpost_name: sign_post.set_exit_no(exit_no) sign_post.set_signpost_name(signpost_name) str_info = sign_post.to_string() if str_info: self._store_name_to_temp_file(temp_file_obj, str_info) else: self.log.error('No SignPost Info. ' 'Sign_id=%d, Folder=%s' % (sign_id, folder)) else: self.log.warning('No route_no/exit_no/signpost_name.' 'Sign_id=%d, Folder=%s' % (sign_id, folder)) # ## 把名称导入数据库 temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'mid_temp_signpost_element') self.pg.commit2() # close file #temp_file_obj.close() cache_file.close(temp_file_obj,True) self.log.info('End Make SignPost element.') return 0
def _make_signpost_element(self): self.log.info('Start Make SignPost element.') # 注:有TTS音素的名称:4G, 6T, 4I, 9D, 4E, RN # 前五个种别做成名称,4E做成出口番号,RN,不收录 sqlcmd = """ SELECT id::bigint, array_agg(seqnr) as seqnrs, array_agg(destseq) as destseqs, array_agg(infotyp) as infotyps, array_agg(rnpart) as rnparts, array_agg(txtcont) as names, array_agg(txtcontlc) as lang_codes, array_agg(phoneme_info) as phoneme_infos FROM ( SELECT id, seqnr, destseq, infotyp, rnpart, txtcont, txtcontlc, mid_get_phoneme(id::bigint, 2128, 0, -- 2128: SignPost txtcont, txtcontlc, infotyp) as phoneme_info FROM org_si order by id, seqnr, destseq, rnpart ) AS A GROUP BY id; """ self.CreateTable2('mid_temp_signpost_element') temp_file_obj = cache_file.open('signpost_element') # 创建临时文件 shield_obj = ShieldTA() signs = self.get_batch_data(sqlcmd) for sign_info in signs: sign_id = sign_info[0] # 看板id seqnr = sign_info[1] # 方向番号 destseqs = sign_info[2] # 同个方向内顺序号 infotyps = sign_info[3] # 种别 # rnparts = sign_info[3] # 道路番号内部无素的序号 names = sign_info[5] # 名称、番号 lang_codes = sign_info[6] # 语种 phoneme_infos = sign_info[7] # 音素 sign_post = SignPostElementTa(sign_id, seqnr) shield_id = None exit_no = None route_no = None signpost_name = None for destseq, infotyp, name, lang_code, phoneme_info in \ zip(destseqs, infotyps, names, lang_codes, phoneme_infos): sign_type = self._get_sign_type(infotyp) if not sign_type: # 空不收录 continue name_type = self._cvt_name_type(sign_type) if not lang_code or lang_code == 'UND': lang_code = MultiLangNameTa.get_lang_code_by_id(sign_id) # ## 处理各种种别的数据 if SIGN_POST_TYPE_SHIELD_ID == sign_type: # shield id # 取得shield id shield_id = shield_obj.convert_shield_id(int(name)) elif SIGN_POST_TYPE_SHIELD_NUM == sign_type: # shield number if shield_id: # 盾牌号存在 shield_number = name route_no = MultiLangShield(shield_id, shield_number, lang_code) sign_post.add_route_no(route_no) shield_id = None else: # 盾牌和番号是成对出现的,而且盾牌在前 self.log.error( 'No Shield id. sign_id=%d, seqnr=%d, destseq=%d' % (sign_id, seqnr, destseq)) elif SIGN_POST_TYPE_EXIT_NO == sign_type: # 出口番号 if exit_no: # 存在多个出口番号,其他出口番号做出口番号的别名 alter_exit_no = MultiLangNameTa( sign_id, lang_code, name, name_type) # ## TTS音素 phoneme_list, language_list = \ alter_exit_no.split_phoneme_info(phoneme_info) alter_exit_no.add_all_tts(phoneme_list, language_list, lang_code) exit_no.add_alter(alter_exit_no) else: # 第一个出口番号 exit_no = MultiLangNameTa(sign_id, lang_code, name, name_type) # ## TTS音素 phoneme_list, language_list = \ exit_no.split_phoneme_info(phoneme_info) exit_no.add_all_tts(phoneme_list, language_list, lang_code) elif SIGN_POST_TYPE_NAME == sign_type: # 方面名称 if signpost_name: # 其他名称番号 alter_sign_name = MultiLangNameTa( sign_id, lang_code, name, name_type) # ## TTS音素 phoneme_list, language_list = \ alter_sign_name.split_phoneme_info(phoneme_info) alter_sign_name.add_all_tts(phoneme_list, language_list, lang_code) signpost_name.add_alter(alter_sign_name) else: # 第一个名称 signpost_name = MultiLangNameTa( sign_id, lang_code, name, name_type) # ## TTS音素 phoneme_list, language_list = \ signpost_name.split_phoneme_info(phoneme_info) signpost_name.add_all_tts(phoneme_list, language_list, lang_code) else: continue if route_no or exit_no or signpost_name: sign_post.set_exit_no(exit_no) sign_post.set_signpost_name(signpost_name) str_info = sign_post.to_string() if str_info: self._store_name_to_temp_file(temp_file_obj, str_info) else: self.log.error('No SignPost Info. sign_id=%d' % sign_id) else: self.log.warning( 'No route_no/exit_no/signpost_name.sign_id=%d' % (sign_id)) # ## 把名称导入数据库 temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'mid_temp_signpost_element') self.pg.commit2() # close file #temp_file_obj.close() cache_file.close(temp_file_obj, True) self.log.info('End Make SignPost element.') return 0
def _make_link_name(self): '''道路名称(包括显示用番号)''' self.log.info('Make Link Name.') sqlcmd = """ SELECT link.id::BIGINT, "name", pop_name, alt_name, namelc, routenum, phoneme.types as phoneme_types, phoneme.name_englishs as phoneme_name_engs, phoneme.name_nuances as phoneme_nuances, regional.types as regional_name_types, regional.name_englishs as regional_name_engs, name_regionals as regional_names, regional_lang_types FROM ( SELECT id, "name", base_nme, pop_name, alt_name, namelc, routenum FROM org_city_nw_gc_polyline WHERE "name" is not null or pop_name is not null or alt_name is not null or routenum is not null ) AS link FULL JOIN mid_temp_link_name_phoneme AS phoneme ON link.id = phoneme.id FULL JOIN mid_temp_link_regional_name as regional on link.id = regional.id ; """ temp_file_obj = cache_file.open('link_name') # 创建临时文件 self.CreateTable2('temp_link_name') names = self.get_batch_data(sqlcmd) for name_info in names: link_id = name_info[0] official_name = name_info[1] pop_name = name_info[2] alt_names = name_info[3] lang_code = name_info[4] routenum = name_info[5] # ## 音素 phoneme_name_types = name_info[6] # MN:official, AN: Alter phoneme_name_engs = name_info[7] phoneme_nuances = name_info[8] # ## regional regional_name_types = name_info[9] regional_name_engs = name_info[10] regional_names = name_info[11] regional_lang_types = name_info[12] if not link_id: self.log.error('Link id is None.') continue # 有其他种别的Phoneme if phoneme_name_types: s1 = set(phoneme_name_types) if len(s1) > 1: self.log.warning('Exist other name type. type=%s' % s1) # 有其他种别的小语种名称 if regional_name_types: s2 = set(regional_name_types) if len(s2) > 1: self.log.warning('Exist other regional name type. type=%s' % s2) phoneme_dict = self._get_phoneme_dict(phoneme_name_types, phoneme_name_engs, phoneme_nuances ) reg_name_dict = self._get_regional_dict(regional_name_types, regional_name_engs, regional_names, regional_lang_types ) # ## 官方名称 ml_name = self._get_multi_name(lang_code, official_name, NAME_TYPE_OFFICIAL, ['MN'], phoneme_dict, reg_name_dict, ) if official_name: # 没有做成到TTS if phoneme_nuances and not ml_name.has_tts(): self.log.warning("Can't find phoneme text. LinkId=%s" % link_id) if regional_names and not ml_name.has_trans(): self.log.warning("Can't find Regional Name. LinkId=%s" % link_id) key = ('MN', official_name.lower()) self._set_regional_name_fuzzy(ml_name, reg_name_dict, key ) # ## POP Name ml_pop = self._get_multi_name(lang_code, pop_name, NAME_TYPE_ALTER, ['MN', 'AN'], phoneme_dict, reg_name_dict, ) if ml_name: ml_name.add_alter(ml_pop) else: ml_name = ml_pop # ## Alter Name if alt_names: # Multiple names with ";" separation alt_names=sorted(alt_names.split(';')) for alt_name in alt_names: ml_alt = self._get_multi_name(lang_code, alt_name, NAME_TYPE_ALTER, ['AN'], phoneme_dict, reg_name_dict, ) if ml_name: ml_name.add_alter(ml_alt) else: ml_name = ml_alt if routenum: shield = ShieldMmi() if shield.is_name_include_number([official_name], routenum): pass else: ml_num = MultiLangName(lang_code, routenum, NAME_TYPE_ROUTE_NUM ) if ml_name: ml_name.add_alter(ml_num) else: ml_name = ml_num json_name = ml_name.json_format_dump() if not json_name: self.log.error('Json Name is none. linkid=%d' % link_id) # 存到本地的临时文件 self._store_name_to_temp_file(temp_file_obj, link_id, json_name ) # ## 把名称导入数据库 temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'temp_link_name') self.pg.commit2() # close file #temp_file_obj.close() cache_file.close(temp_file_obj,True) self.log.info('End Make Link Name.') return 0
def __DoGet_Admin_name(self,code_dict,code_country_dict,code_country_code): self.log.info('begin admin_name ..') sqlcmd = ''' select id,name,name_alt,country_name from ( select id,"name" as name,"names" as name_alt,"name" as country_name from ( select id,"name","names" from org_area where kind = '10' )as temp union select a.id,a."name" as name,a."names" as name_alt,b."name" as country_name from ( select id,"name","names",parent_id from org_area where kind = '9' group by id,"name","names",parent_id order by id,"name","names",parent_id )as a left join ( select id,"name" from org_area where kind = '10' group by id,"name" order by id,"name" )as b on a.parent_id = b.id union select dst_id as id,dst_nme as name,dst_alt as name_alt,d."name" as country_name from org_district_region as c left join ( select state.id,country."name" from ( select id,parent_id from org_area where kind = '9' group by id,parent_id order by id,parent_id )as state left join ( select id,"name" from org_area where kind = '10' group by id,"name" order by id,"name" )as country on state.parent_id = country.id )as d on c.parent_id = d.id group by dst_id,dst_nme,dst_alt,d."name" )as temp_all where (name is not null) or (name_alt is not null) order by id,name,name_alt,country_name; ''' #test set_language MultiLangName.set_language_code(code_dict) temp_file_obj = cache_file.open('temp_admin_name_new') rows = self.get_batch_data(sqlcmd) for row in rows: id = row[0] name = row[1] namealtes = row[2] country_name = row[3] if name: ml_name = MultiLangName(code_country_dict[country_name], name, NAME_TYPE_OFFICIAL) if namealtes: # ";" separation for namealte in namealtes.split(';'): alt_name = MultiLangName(code_country_dict[country_name], namealte, NAME_TYPE_ALTER) if ml_name: ml_name.add_alter(alt_name) else: ml_name = alt_name json_name = ml_name.json_format_dump() if not json_name: self.log.error('Json Name is null. id=%d' % id) else: self.__store_name_to_temp_file(temp_file_obj, id, json_name,code_country_code[country_name]) # ## 把名称导入数据库 temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'temp_admin_name') self.pg.commit2() # close file #temp_file_obj.close() cache_file.close(temp_file_obj,True) self.log.info('end admin_name ..')
def _make_admin_name(self): self.log.info('begin make admin_name ..') sqlcmd = ''' select temp_all.id, name, name_alt, country_name, phoneme.types as phoneme_types, phoneme.name_englishs as phoneme_name_engs, phoneme.name_nuances as phoneme_nuances, regional.types as regional_name_types, regional.name_englishs as regional_name_engs, name_regionals as regional_names, regional_lang_types from ( select id,"name" as name,"names" as name_alt,"name" as country_name from ( select id,"name","names" from org_area where kind = '10' )as temp union select a.id,a.stt_nme as name,a.stt_alt as name_alt,b."name" as country_name from ( select stt_id as id,stt_nme,stt_alt,parent_id from org_state_region where kind = '9' group by stt_id,stt_nme,stt_alt,parent_id order by stt_id,stt_nme,stt_alt,parent_id )as a left join ( select id,"name" from org_area where kind = '10' group by id,"name" order by id,"name" )as b on a.parent_id = b.id union select dst_id as id,dst_nme as name,dst_alt as name_alt,d."name" as country_name from org_district_region as c left join ( select state.id,country."name" from ( select stt_id as id,parent_id from org_state_region where kind = '9' group by stt_id,parent_id order by stt_id,parent_id )as state left join ( select id,"name" from org_area where kind = '10' group by id,"name" order by id,"name" )as country on state.parent_id = country.id )as d on c.parent_id = d.id group by dst_id,dst_nme,dst_alt,d."name" )as temp_all left join mid_temp_admin_name_phoneme as phoneme on temp_all.id = phoneme.id left join mid_temp_admin_regional_name as regional on temp_all.id = regional.id where (name is not null) or (name_alt is not null) order by id,name,name_alt,country_name; ''' self.CreateTable2('temp_admin_name') temp_file_obj = cache_file.open('admin_name') rows = self.get_batch_data(sqlcmd) for row in rows: id = int(row[0]) official_name = row[1] alt_names = row[2] country_name = row[3] # ## 音素 phoneme_name_types = row[4] # MN:official, AN: Alter phoneme_name_engs = row[5] phoneme_nuances = row[6] # ## regional regional_name_types = row[7] regional_name_engs = row[8] regional_names = row[9] regional_lang_types = row[10] if not id: self.log.error('admin id is None.') continue phoneme_dict = self._get_phoneme_dict(phoneme_name_types, phoneme_name_engs, phoneme_nuances ) reg_name_dict = self._get_regional_dict(regional_name_types, regional_name_engs, regional_names, regional_lang_types ) # ## 官方名称 ml_name = self._get_multi_name(MMI_OFFICIAL_LANG_CODE, official_name, NAME_TYPE_OFFICIAL, ['MN'], phoneme_dict, reg_name_dict, ) if official_name: # 没有做成到TTS if phoneme_nuances and not ml_name.has_tts(): self.log.warning("Can't find phoneme text. LinkId=%s" % id) if regional_names and not ml_name.has_trans(): self.log.warning("Can't find Regional Name. LinkId=%s" % id) key = ('MN', official_name.lower()) self._set_regional_name_fuzzy(ml_name, reg_name_dict, key ) # ## Alter Name if alt_names: # Multiple names with ";" separation for alt_name in alt_names.split(';'): ml_alt = self._get_multi_name(MMI_OFFICIAL_LANG_CODE, alt_name, NAME_TYPE_ALTER, ['AN'], phoneme_dict, reg_name_dict, ) if ml_name: ml_name.add_alter(ml_alt) else: ml_name = ml_alt json_name = ml_name.json_format_dump() if not json_name: self.log.error('Json Name is none. admin id=%d' % id) # 存到本地的临时文件 self._store_name_to_temp_file(temp_file_obj, id, json_name ) # ## 把名称导入数据库 temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'temp_admin_name') self.pg.commit2() cache_file.close(temp_file_obj,True) self.log.info('end admin_name ..')
def _make_ic_link_mapping(self): '''IC(SAPA, JCT, IC) Link的mapping''' self.log.info('Start Make IC Link Mapping.') file_obj = cache_file.open('temp_highway_mapping_ic') # 创建临时文件 # 做成每条IC Link的每个前后设施。 self._make_ic_link_temp_mapping() self.pg.connect1() data = self._get_forward_backward_ic() # 取得link的前后方所有设施 for fb_info in data: link_id = fb_info[0] bwd_ic_nos = fb_info[1] fwd_ic_nos = fb_info[2] bwd_node_ids = fb_info[3] fwd_node_ids = fb_info[4] displayclass = fb_info[5] tile_id_14 = fb_info[6] tile_id = convert_tile_id(tile_id_14) self.link_type = fb_info[7] self.link_id = link_id one_way = fb_info[8] if not bwd_ic_nos and not fwd_ic_nos: # self.log.error('1. No Backward/Forward IC for link=%s' # % link_id) continue bwd_ic_list, bwd_facil_list = self._get_ic_facils(bwd_ic_nos, bwd_node_ids) fwd_ic_list, fwd_facil_list = self._get_ic_facils(fwd_ic_nos, fwd_node_ids) road_code, updown = self._get_ic_link_road_code(bwd_facil_list, fwd_facil_list) if road_code: road_no = self.data_mng.get_road_no(road_code) road_kind = updown # self.data_mng.get_road_kind(road_code, updown) else: road_no = HWY_INVALID_ROAD_NO road_kind = HWY_ROAD_ATTR_NONE bwd_ics, fwd_ics = self._get_ic_link_fb_ic(bwd_ic_list, fwd_ic_list) if not bwd_ics and not fwd_ics: self.log.error('2. No Backward/Forward IC for link=%s' % link_id) continue link_maps = [] if one_way == ONE_WAY_BOTH: # 双向通行 # if road_no > HWY_INVALID_ROAD_NO: # print 'Both:', link_id # 双向通行,不能确认前后方向,所有前后方设施都填0 fwd_ic, bwd_ic = HWY_INVALID_IC_NO, HWY_INVALID_IC_NO link_map = HwyLinkMapping(road_kind, 1, road_no, displayclass, link_id, fwd_ic, bwd_ic, 'IC', tile_id) link_maps.append(link_map) else: for bwd_ic, fwd_ic in zip(bwd_ics, fwd_ics): # 前后设施都为空——通常是无料区间 if not bwd_ic and not fwd_ic: self.log.error('3. No Backward/Forward IC for link=%s' % link_id) continue link_map = HwyLinkMapping(road_kind, len(bwd_ics), road_no, displayclass, link_id, fwd_ic, bwd_ic, 'IC', tile_id) link_maps.append(link_map) self._insert_mapping(link_maps, file_obj) self._store_hwy_mapping(file_obj) self.pg.commit1() self.log.info('End Make IC Link Mapping.')
def _make_lanearrowinfo_and_passlink_shortest_path(self): self._create_tempindex_function() temp_file_obj = cache_file.open('signpost_passlink_short_distance') sqlcmd = ''' select a.arcin::bigint, a.arcout::bigint, a.direction, a.lane, mid_get_laneinfo(lane,no_list), b.s_node, b.e_node, c.s_node, c.e_node, ST_ASEWKT(mid_get_expand_box(a.arcin::bigint, a.arcout::bigint, %s) ), b.one_way_code, c.one_way_code FROM ( select arcin, arcout,array_agg(no) as no_list, direction, lane from temp_mid_lane_table group by arcin,arcout,direction, lane ) as a left join link_tbl as b on a.arcin = b.link_id left join link_tbl as c ON a.arcout = c.link_id order by a.arcin,a.arcout; ''' from component import link_graph as lg datas = self.get_batch_data(sqlcmd, (lg.UNITS_TO_EXPAND,)) for data in datas: in_link_id = data[0] out_link_id = data[1] direction = data[2] lane_num = data[3] laneinfo = data[4] in_s_node = data[5] in_e_node = data[6] out_s_node = data[7] out_e_node = data[8] expand_box = data[9] in_oneway = data[10] out_oneway = data[11] graph_obj = lg.LinkGraph() paths = lg.all_shortest_paths_in_expand_box(graph_obj, expand_box, (in_s_node, in_e_node), (out_s_node, out_e_node), in_oneway_code = in_oneway, out_oneway_code = out_oneway ) if not paths: self.log.warning("Can't find the path. inlink=%d, outlink=%d" % (in_link_id, out_link_id)) continue if len(paths) > 1: # 存在多条最短路径 self.log.warning('Shortest Paths Number>1.Inlink=%d,outlink=%d' % (in_link_id, out_link_id)) node_id = paths[0][0] if not node_id: self.log.error('No NodeId.') pass_link = graph_obj.get_linkid_of_path(paths[0]) if pass_link: if None in pass_link: self.log.error('Error PassLink.') pass_link_cnt = len(pass_link) pass_link = '|'.join(pass_link) else: pass_link = '' pass_link_cnt = 0 second_link = (pass_link.split('|'))[0] if not second_link: second_link = out_link_id str_info = '%d\t%d\t%d\t%d\t%d\t%s\t%s\t%d' % ( in_link_id, out_link_id, node_id, lane_num, ARROW_DIC.get(direction), laneinfo, pass_link, pass_link_cnt ) self._store_name_to_temp_file(temp_file_obj, str_info) # ## 把名称导入数据库 temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'mid_temp_guidelane_passlink_shortest_distance') self.pg.commit2() # close file #temp_file_obj.close() cache_file.close(temp_file_obj,True) return
def _make_short_distance_passlink(self): '距离最短' self.log.info('Make PassLink.') self.CreateFunction2('mid_get_expand_box') # 包含两条link的扩大区域 self.CreateTable2('mid_temp_signpost_passlink_shortest_distance') temp_file_obj = cache_file.open('signpost_passlink_short_distance') sqlcmd = """ SELECT origin.sign_id, destination_number, originating_link_id, dest_link_id, in_l.s_node, in_l.e_node, out_l.s_node, out_l.e_node, ST_ASEWKT(mid_get_expand_box(originating_link_id, dest_link_id, %s) ) FROM rdf_sign_origin AS origin LEFT JOIN rdf_sign_destination as dest ON origin.sign_id = dest.sign_id left join link_tbl as in_l ON originating_link_id = in_l.link_id left join link_tbl as out_l ON dest_link_id = out_l.link_id ; """ from component import link_graph as lg datas = self.get_batch_data(sqlcmd, (lg.UNITS_TO_EXPAND,)) for data in datas: sign_id = data[0] dest_number = data[1] in_link_id = data[2] out_link_id = data[3] in_s_node = data[4] in_e_node = data[5] out_s_node = data[6] out_e_node = data[7] expand_box = data[8] graph_obj = lg.LinkGraph() paths = lg.all_shortest_paths_in_expand_box(graph_obj, expand_box, (in_s_node, in_e_node), (out_s_node, out_e_node) ) if not paths: self.log.warning("Can't find the path. inlink=%d, outlink=%d" % (in_link_id, out_link_id)) continue if len(paths) > 1: # 存在多条最短路径 self.log.warning('Shortest Paths Number>1.Inlink=%d,outlink=%d' % (in_link_id, out_link_id)) node_id = paths[0][0] if not node_id: self.log.error('No NodeId.') pass_link = graph_obj.get_linkid_of_path(paths[0]) if pass_link: if None in pass_link: self.log.error('Error PassLink.') pass_link_cnt = len(pass_link) pass_link = '|'.join(pass_link) else: pass_link = '' pass_link_cnt = 0 str_info = '%d\t%d\t%d\t%d\t%d\t%s\t%d' % (sign_id, dest_number, node_id, in_link_id, out_link_id, pass_link, pass_link_cnt ) self._store_name_to_temp_file(temp_file_obj, str_info) # ## 把名称导入数据库 temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'mid_temp_signpost_passlink_shortest_distance') self.pg.commit2() # close file #temp_file_obj.close() cache_file.close(temp_file_obj,True) return 0
def _make_signpost_exit_no(self): self.CreateTable2('mid_temp_signpost_exit_no') temp_file_obj = cache_file.open('signpost_exit_no') # 创建临时文件 sqlcmd = """ SELECT dest.sign_id, dest.destination_number, exit_number, language_code, alt_exit_number, trans_exit_langs, trans_exit_nums, iso_country_code, phonetic_strings, phonetic_language_codes FROM rdf_sign_destination as dest left join mid_temp_sign_destination_trans_group as trans_group ON dest.sign_id = trans_group.sign_id and dest.destination_number = trans_group.destination_number LEFT JOIN temp_rdf_nav_link on dest_link_id = link_id LEFT JOIN mid_temp_sign_destination_phonetic AS phonetic ON dest.sign_id = phonetic.sign_id AND dest.destination_number = phonetic.destination_number WHERE exit_number is not null ORDER BY dest.sign_id, dest.destination_number; """ dict_rdf = comp_dictionary_rdf() exits = self.get_batch_data(sqlcmd) for exit_info in exits: sign_id = exit_info[0] dest_number = exit_info[1] exit_number = exit_info[2] lang_code = exit_info[3] alt_exit_number = exit_info[4] trans_langs = exit_info[5] # 翻译语言 trans_exit_nums = exit_info[6] # 翻译文本 iso_country_code = exit_info[7] phonetic_strings = exit_info[8] phonetic_lang_codes = exit_info[9] sign_post_exit = SignPostExit(sign_id, dest_number) name_type = 'office_name' if exit_number: exit_no = MultiLangNameRDF(lang_code, exit_number, name_type) if phonetic_strings: # 有音素 tts_obj = dict_rdf.get_tts_name(iso_country_code, lang_code, exit_no.get_name_type(), phonetic_strings, phonetic_lang_codes ) if tts_obj: exit_no.set_tts(tts_obj) else: self.log.warning('No TTS. sign_id=%d, dest_number=%d' % (sign_id, dest_number)) exit_no = dict_rdf.set_trans_name(exit_no, iso_country_code, trans_exit_nums, trans_langs, phonetic_strings, phonetic_lang_codes, ) # exit_no.set_trans(trans_exit_nums, trans_langs) sign_post_exit.set_exit_no(exit_no) else: continue if alt_exit_number: name_type = 'alter_name' alt_exit_no = MultiLangNameRDF(lang_code, alt_exit_number, name_type) sign_post_exit.set_alter_exit_no(alt_exit_no) str_info = sign_post_exit.to_string() self._store_name_to_temp_file(temp_file_obj, str_info) # ## 把名称导入数据库 temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'mid_temp_signpost_exit_no') self.pg.commit2() # close file #temp_file_obj.close() cache_file.close(temp_file_obj,True) return 0
def _make_signpost_element(self): self.CreateTable2('mid_temp_signpost_element') temp_file_obj = cache_file.open('signpost_element') # 创建临时文件 # 排序:sign_id, destination_number(方向ID), entry_number # 同个方向上的名称做group (sign_id, destination_number) sqlcmd = """ SELECT element.sign_id, element.destination_number, entry_number, text_type, element."text" as official_name, element.language_code, direction_code, entry_type, text_number, trans_types, trans_group.trans_names as trans_names, iso_country_code, phonetic_strings, phonetic_language_codes FROM rdf_sign_element as element LEFT JOIN mid_temp_sign_element_trans_group as trans_group ON element.sign_element_id = trans_group.sign_element_id LEFT JOIN rdf_sign_destination AS dest ON element.sign_id = dest.sign_id and element.destination_number = dest.destination_number LEFT JOIN temp_rdf_nav_link on dest_link_id = link_id LEFT JOIN mid_temp_sign_element_phonetic as phonetic ON element.sign_element_id = phonetic.sign_element_id order by sign_id, destination_number, entry_number ; """ signs = self.get_batch_data(sqlcmd) from component.rdf.dictionary_rdf import INVALID_SHIELD_ID prev_sign_id = None curr_sign_id = None prev_dest_num = None curr_dest_num = None signpost = None signpost_name = None dict_rdf = comp_dictionary_rdf() for sign_info in signs: curr_sign_id = sign_info[0] curr_dest_num = sign_info[1] # 方向ID # entry_number = sign_info[2] # 同个方向上的名称序号 text_type = sign_info[3] # T: 名称, R: 番号 official_name = sign_info[4] # 官方语言下的文本 lang_code = sign_info[5] # 官方语言种别——ISO Country Code # dir_code = sign_info[6] # 方向, E, S, W, N, '-' trans_types = sign_info[9] # 翻译语言种别 trans_names = sign_info[10] # 翻译文本 iso_country_code = sign_info[11] phonetic_strings = sign_info[12] phonetic_lang_codes = sign_info[13] if not curr_sign_id: self.log.error('None sign_id.') continue if not curr_dest_num: self.log.error('None destination_number. sign_id=%d' % curr_sign_id) continue if (prev_sign_id != curr_sign_id or # 不同的看板 prev_dest_num != curr_dest_num): # 不同的方向 # 保存上一条 if prev_dest_num: signpost.set_signpost_name(signpost_name) str_info = signpost.to_string() self._store_name_to_temp_file(temp_file_obj, str_info) # 新的一条 prev_sign_id = curr_sign_id prev_dest_num = curr_dest_num signpost = SignPostElement(curr_sign_id, curr_dest_num) signpost_name = None # 名称不存在,或为空 if not official_name: self.log.error('None Name. sign_id=%d.' % prev_sign_id) continue # ## 番号 if self._is_route_number(text_type): if iso_country_code == 'SGP': shield_id = 4111 else: shield_id = INVALID_SHIELD_ID shield_number = official_name shield_obj = MultiLangShieldRDF(shield_id, shield_number, lang_code ) shield_obj.set_trans(trans_names, trans_types) # ## shield不做TTS # shield_obj = dict_rdf.set_trans_name(shield_obj, # iso_country_code, # trans_names, # trans_types, # phonetic_strings, # phonetic_lang_codes, # ) signpost.add_route_no(shield_obj) else: # 名称 name_type = 'office_name' if signpost_name: # 同个方向上的其他名称 trans_name_obj = MultiLangNameRDF(lang_code, official_name, name_type) if phonetic_strings: # 有音素 tts_obj = dict_rdf.get_tts_name(iso_country_code, lang_code, name_type, phonetic_strings, phonetic_lang_codes ) if tts_obj: trans_name_obj.set_tts(tts_obj) else: self.log.warning('No TTS.sign_id=%d,dest_number=%d' % (curr_sign_id, curr_dest_num)) trans_name_obj = dict_rdf.set_trans_name(trans_name_obj, iso_country_code, trans_names, trans_types, phonetic_strings, phonetic_lang_codes, ) signpost_name.add_alter(trans_name_obj) else: # 当前方向上的第一名称 signpost_name = MultiLangNameRDF(lang_code, official_name, name_type) if phonetic_strings: # 有音素 name_type = signpost_name.get_name_type() tts_obj = dict_rdf.get_tts_name(iso_country_code, lang_code, name_type, phonetic_strings, phonetic_lang_codes ) if tts_obj: signpost_name.set_tts(tts_obj) else: self.log.warning('No TTS.sign_id=%d,dest_number=%d' % (curr_sign_id, curr_dest_num)) signpost_name = dict_rdf.set_trans_name(signpost_name, iso_country_code, trans_names, trans_types, phonetic_strings, phonetic_lang_codes ) # 最后一条 signpost.set_signpost_name(signpost_name) str_info = signpost.to_string() self._store_name_to_temp_file(temp_file_obj, str_info) # ## 把名称导入数据库 temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'mid_temp_signpost_element') self.pg.commit2() # close file #temp_file_obj.close() cache_file.close(temp_file_obj,True) return 0