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
