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 ..')
