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_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 _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 __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 __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 _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_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 _store_hwy_mapping(self, file_obj): # ## 把名称导入数据库 if file_obj: file_obj.seek(0) self.pg.copy_from2(file_obj, 'highway_mapping', columns=['road_kind', 'ic_count', 'road_no', 'display_class', 'link_id', 'forward_ic_no', 'backward_ic_no', 'path_type', 'tile_id']) self.pg.commit2() cache_file.close(file_obj, True) file_obj = None
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_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_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_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_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_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_admin_name(self): '''行政区名称''' self.log.info('Make Feature Name.') self.CreateFunction2('mid_compare_nametype') # 取得音素信息,返回格式'[音素文本1|音素文本2|...][语种1|语种2|...]' self.CreateFunction2('mid_get_phoneme') # 排序名规则 # 1. id, 2. nametyp降序, 3. gid # 注:行政名称里有很多重复的名称,不知为啥, 如:id = 17040000000001 # 一个越南语文本,一般会有一个越南音素和一个英文音素 sqlcmd = """ SELECT id, array_agg(nametyp) as nametypes, array_agg("name") as names, array_agg(namelc) as lang_codes, array_agg(phoneme_info) as phoneme_infos FROM ( SELECT min_gid, id, nametyp, "name", namelc, phoneme_info FROM ( SELECT min(gid) as min_gid, id, nametyp, "name", namelc, feattyp, mid_get_phoneme(id::bigint, feattyp, 0, -- 0: both side "name", namelc, nametyp ) as phoneme_info FROM org_an GROUP BY id, nametyp, namelc, "name", feattyp ) as b order by id, nametyp, min_gid ) AS an GROUP BY id; """ temp_file_obj = cache_file.open('admin_name') # 创建临时文件 self.CreateTable2('mid_temp_admin_name') features = self.get_batch_data(sqlcmd) from component.ta.multi_lang_name_ta import MultiLangAdminNameTa for feature in features: admin_id = feature[0] nametypes = feature[1] # ON官方名,AN别名 names = feature[2] lang_codes = feature[3] phoneme_infos = feature[4] # 音素 multi_name = MultiLangAdminNameTa(admin_id) multi_name.set_multi_name( names, lang_codes, nametypes, None, # side of line phoneme_infos) json_name = multi_name.json_format_dump() if not json_name: self.log.error('Json Name is none. id=%d' % admin_id) # 存到本地的临时文件 self._store_name_to_temp_file(temp_file_obj, admin_id, json_name) # ## 把名称导入数据库 temp_file_obj.seek(0) self.pg.copy_from2(temp_file_obj, 'mid_temp_admin_name') self.pg.commit2() # close file #temp_file_obj.close() cache_file.close(temp_file_obj, True) self.log.info('End Feature Name.')
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 _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 ..')