def _Do(self):
sqlcmd = """
UPDATE spatial_ref_sys
SET proj4text='+proj=longlat +ellps=bessel towgs84=-146.43,507.89,681.46'
WHERE srid=4301;
"""
self.pg.execute2(sqlcmd)
self.pg.commit2()
kensaku_path = common.GetPath('sf_highway')
highway_path = common.GetPath('highway')
connectway_path = common.GetPath('connectway')
self._Do_highway(highway_path)
self._Do_hwysame(highway_path)
self._Do_tollguide(highway_path)
self._Do_ferry_code(kensaku_path)
self._Do_gs(kensaku_path)
self._Do_multiple_store(kensaku_path)
self._Do_road_code(kensaku_path)
self._Do_road_code_highway(kensaku_path)
self._Do_roadpoint(kensaku_path)
self._Do_roadpoint_normal(kensaku_path)
self._Do_roadpoint_bunnki(kensaku_path)
self._Do_store_code(kensaku_path)
self._Do_connectway(connectway_path)
def _Do(self):
pg = self.pg
pgcur = self.pg.pgcur2
highway_path = common.GetPath('highway')
toll_path = common.GetPath('toll')
f_tollnode = io.open(os.path.join(highway_path, 'tollnode.txt'), 'r',
8192, 'euc-jp')
pgcur.copy_from(f_tollnode, 'road_tollnode', '\t', "", 8192, None)
pg.commit2()
f_tollnode.close()
f_hwynode = io.open(os.path.join(highway_path, 'hwynode.txt'), 'r',
8192, 'euc-jp')
pgcur.copy_from(f_hwynode, 'road_hwynode', '\t', "", 8192, None)
pg.commit2()
f_hwynode.close()
f_dicin = io.open(os.path.join(toll_path, 'hwymode', 'dic.in'), 'r',
8192, 'euc-jp')
pgcur.copy_from(f_dicin, 'road_dicin', ' ', "", 8192, None)
pg.commit2()
f_dicin.close()
return 0
def _Do_store_code_mapping(self):
"import DDN store code mapping table."
self.log.info('Import Store Code.')
self.CreateTable2('store_code_mapping')
path = common.GetPath('store_code_mapping')
if path == None or path == '':
self.log.warning('Does not exist store code mapping file.')
return 0
code_list = common.GetSheetRegion(path, 'B', 'G', 4, 62)
sqlcmd = """
INSERT INTO store_code_mapping(
org_store_code
, org_store_name
, ddn_store_code
, ddn_store_code_hex
, ddn_store_name)
VALUES (%s, %s, %s, %s, %s);
"""
for record in code_list:
#print record
org_store_code = record[0]
org_store_name = record[1]
ddn_store_code_hex = record[4]
ddn_store_name = record[5]
if ddn_store_code_hex != '':
ddn_store_code = int(ddn_store_code_hex, 16)
else:
ddn_store_code = None
self.pg.execute2(sqlcmd, (org_store_code, org_store_name,
ddn_store_code, ddn_store_code_hex,
ddn_store_name))
self.pg.commit2()
return 0
def __deal_with_forceguide_update(self):
# import data
forceguide_patch_update_full_path = common.GetPath('forceguide_update')
self._copy_forceguide_X_patch_data(
forceguide_patch_update_full_path,
'road_force_guide_update_patch_tbl')
# make temporarily table and then to update current force guide status
sqlcmd = """
insert into temp_update_patch_force_guide_tbl
(
select gid, link_array[1] as fromlinkid,
link_array[link_cnt] as tolinkid,
(link_cnt -2) as midcount,
array_to_string(link_array[2:link_cnt-1],',') as midlink,
(guide_code_int + 1) as guidetype
from
(
select gid, link_array,
array_upper(link_array,1) as link_cnt, guide_code_int
from
(
select gid, mid_make_link_list_by_nodes(array[node1,node2,node3,node4,node5,node6,node7]) as link_array,guide_code_int
from road_force_guide_update_patch_tbl
) as a
) as b
)
"""
self.pg.execute2(sqlcmd)
self.pg.commit2()
self._split_link_handler('temp_update_patch_force_guide_tbl',
'road_force_guide_update_patch_tbl')
self._update_old_patch('temp_update_path_force_guide_tbl_matching_tbl',
'temp_update_patch_force_guide_tbl')
def __make_admin_wav(self):
self.log.info('start making admin_wav.txt')
admin_wav_path = common.GetPath('admin_wav')
admin_wav_file = io.open(admin_wav_path, 'w', 8192, 'utf8')
admin_code_base = 1
admin_wav_base = 111
sqlcmd = '''
select ad_code
from mid_admin_zone
where ad_order = 1
order by ad_code;
'''
rows = self.get_batch_data(sqlcmd)
strlines = ''
for row in rows:
admin_code = int(row[0] / self._admin_order1_scale)
admin_wav = admin_wav_base + admin_code - admin_code_base
strlines = strlines + str(row[0]) + ',' + str(admin_wav) + '\n'
admin_wav_file.writelines(unicode(strlines, 'utf8'))
admin_wav_file.close()
self.log.info('end making admin_wav.txt')
def __CheckImagePath(self):
'''检查图片路径是否被指定。有，返回True；无，返回False。'''
path = common.GetPath('illust')
if not path:
return False
else:
return True
def _InsertLanguages(self):
'往language_tbl表，插入语言记录。'
path = common.GetPath('language_table')
self.CreateTable2('language_tbl')
language_list = common.GetAllLanguages(path)
sqlcmd = """
INSERT INTO language_tbl(
language_id
, l_full_name
, l_talbe
, pronunciation
, p_table
, language_code
, language_code_client
, language_id_client)
VALUES (%s, %s, %s, %s,
%s, %s, %s, %s);
"""
for recod in language_list:
self.pg.execute2(sqlcmd, recod)
self.pg.commit2()
self.CreateIndex2('language_tbl_language_code_idx')
self.CreateTable2('language_tbl_l_full_name_idx')
self.CreateTable2('language_tbl_l_talbe_idx')
def _Do(self):
# sqlcmd = """
# UPDATE spatial_ref_sys
# SET proj4text='+proj=longlat +ellps=bessel towgs84=-146.43,507.89,681.46'
# WHERE srid=4301;
# """
# self.pg.execute2(sqlcmd)
# self.pg.commit2()
# kensaku_path = common.GetPath('sf_highway')
# highway_path = common.GetPath('highway')
sapa_illust_path = common.GetPath('illust')
# self._Do_highway(highway_path)
# self._Do_hwysame(highway_path)
# self._Do_tollguide(highway_path)
#
# self._Do_ferry_code(kensaku_path)
# self._Do_gs(kensaku_path)
# self._Do_multiple_store(kensaku_path)
# self._Do_road_code(kensaku_path)
# self._Do_road_code_highway(kensaku_path)
# self._Do_roadpoint(kensaku_path)
# self._Do_roadpoint_normal(kensaku_path)
# self._Do_roadpoint_bunnki(kensaku_path)
# self._Do_store_code(kensaku_path)
self._Do_sapa_illust(sapa_illust_path)
self._Do_store_code_mapping()
def _Do(self):
# judgment whether needs to do forceguide patch operation
forceguide_patch_run_status = common.GetPath('forceguide_patch_open')
if cmp(forceguide_patch_run_status.strip().upper(),'TRUE') <> 0 :
return 0
self.__deal_with_forceguide_update()
self.__deal_with_forceguide_append()
self.__deal_with_forceguide_friendly_append()
def _Do(self):
# 非欧美
if self._IsOverSeaData() != True:
return 0
# 欧美
sf_path = common.GetPath('ta_jv_sidefile')
if sf_path:
self._Do_jv(sf_path)
self._Do_jv_ld(sf_path)
self._Do_jv_nw(sf_path)
self._Do_jv_si(sf_path)
def __deal_with_forceguide_friendly_append(self):
# import forceguide_patch data into database
forceguide_friendly_patch_append_full_path = common.GetPath('forceguide_friendly_append')
self._copy_forceguide_X_patch_data(forceguide_friendly_patch_append_full_path, 'road_force_guide_friendly_append_patch_tbl')
sqlcmd = """
insert into temp_friendly_append_patch_force_guide_tbl
(
select gid, link_array[1] as fromlinkid, link_array[link_cnt] as tolinkid, (link_cnt -2) as midcount, array_to_string(link_array[2:link_cnt-1],',') as midlink,
guide_code_int as guidetype
from
(
select gid, link_array, array_upper(link_array,1) as link_cnt, guide_code_int
from
(
select gid,mid_make_link_list_by_nodes(array[node1,node2,node3,node4,node5,node6,node7]) as link_array,guide_code_int
from road_force_guide_friendly_append_patch_tbl
) as a
) as b
)
"""
self.pg.execute2(sqlcmd)
self.pg.commit2()
# append records into road_gid from append patch
sqlcmd = """
select max(objectid) as max_id
from road_gid
"""
self.pg.execute2(sqlcmd)
row = self.pg.fetchone2()
if row:
max_id = row[0]
sqlcmd = """
insert into road_gid(gid, objectid, fromlinkid, tolinkid,
midcount,midlinkid, guidetype)
(
select %s+ 7000 + objectid, %s+ 7000+ objectid, fromlinkid, tolinkid,
midcount, midlinkid, guidetype
from temp_friendly_append_patch_force_guide_tbl
)
""" %(max_id, max_id)
self.pg.execute2(sqlcmd)
self.pg.commit2()
def InsertJunctionViewImage(self):
path = common.GetPath('illust')
if self.MakePictoBinary(path) == -1:
exit(1)
pass
def init(self):
SqlScriprt.init(self)
path = common.GetPath('sql_script')
path = os.path.join(path, 'jdb')
self.LoadSqlScriprts(path)
def _DoBoundary(self):
pg = self.pg
pgcur = self.pg.pgcur2
admin_wav_path = common.GetPath('admin_wav')
f = io.open(admin_wav_path, 'r', 8192, 'utf8')
pgcur.copy_from(f, 'temp_admin_wavid', ',', "", 8192, None)
pg.commit2()
f.close()
sqlcmd = """
INSERT into temp_boundary(ad_cd, the_geom)
SELECT ad_cd, ST_Boundary(the_geom) as the_geom
FROM
(
select ad_code as ad_cd,the_geom
from mid_admin_zone
where ad_order = 1
) as a;
"""
self.pg.execute2(sqlcmd)
self.pg.commit2()
sqlcmd = """
INSERT INTO
temp_hlink(link_id, s_node, e_node, one_way_code, the_geom)
SELECT link_id, s_node, e_node, one_way_code, the_geom
FROM link_tbl
where road_type in (0,1) and link_type = 2;
"""
self.pg.execute2(sqlcmd)
self.pg.commit2()
self.CreateIndex2('temp_boundary_the_geom_idx')
self.CreateIndex2('temp_hlink_the_geom_idx')
sqlcmd = """
INSERT INTO
temp_inode(link_id, s_node, e_node, one_way_code, the_geom)
SELECT distinct a.link_id, a.s_node, a.e_node, a.one_way_code,
ST_Intersection(a.the_geom, b.the_geom) as the_geom
FROM temp_hlink as a
inner join temp_boundary as b
on ST_Intersects(a.the_geom, b.the_geom) = TRUE
"""
self.pg.execute2(sqlcmd)
self.pg.commit2()
sqlcmd = """
INSERT INTO temp_bnode(
link_id, s_node, e_node, one_way_code, bnode,
link_id2, s_node2, e_node2, one_way_code2, the_geom
)
SELECT t1.link_id, t1.s_node, t1.e_node,
t1.one_way_code, t1.bnode,
t2.link_id, t2.s_node, t2.e_node,
t2.one_way_code, t1.the_geom
FROM
(
SELECT link_id, s_node, e_node, one_way_code,
case when slocate < 0.5 and slocate < 1 - elocate then s_node else e_node end as bnode,
case when slocate < 0.5 and slocate < 1 - elocate then sp else ep end as the_geom,
slocate,
elocate
FROM
(
SELECT a.link_id, a.s_node, a.e_node, a.one_way_code,
ST_Line_Locate_Point(the_geom, sbp) as slocate,
ST_Line_Locate_Point(the_geom, ebp) as elocate,
sp,
ep
FROM
(
select m.link_id, m.s_node, m.e_node, m.one_way_code, n.the_geom,
ST_ClosestPoint(m.the_geom, ST_StartPoint(n.the_geom)) as sbp,
ST_ClosestPoint(m.the_geom, ST_EndPoint(n.the_geom)) as ebp,
ST_StartPoint(n.the_geom) as sp,
ST_EndPoint(n.the_geom) as ep
from temp_inode as m
inner join link_tbl as n
on m.link_id = n.link_id
) as a
) as t
) as t1
inner join link_tbl as t2
on t1.bnode = t2.s_node or t1.bnode = t2.e_node
where t1.link_id != t2.link_id;
"""
self.pg.execute2(sqlcmd)
self.pg.commit2()
sqlcmd = """
INSERT INTO temp_guideinfo_boundary(
inlinkid, nodeid, outlinkid, innode, outnode, out_adcd
)
SELECT distinct inlinkid, bnode, outlinkid, innode,
outnode, b.ad_cd
from
(
SELECT links[1] as inlinkid, bnode, links[2] as outlinkid,
links[3] as innode, links[4] as outnode
from
(
SELECT
bnode,
mid_get_inout_link(
link_id, s_node, e_node, one_way_code,
link_id2, s_node2, e_node2, one_way_code2
) as links
from temp_bnode
) as t
where links is not null
) as a
inner join node_tbl as c
on a.outnode = c.node_id
inner join (
select ad_code as ad_cd,the_geom
from mid_admin_zone
where ad_order = 1
)as b
on ST_Intersects(c.the_geom, b.the_geom) = TRUE;
"""
self.pg.execute2(sqlcmd)
self.pg.commit2()
sqlcmd = """
INSERT INTO caution_tbl(
inlinkid, nodeid, outlinkid, passlid,
passlink_cnt, data_kind, voice_id
)
select inlinkid, nodeid, outlinkid, null, 0, 4, b.wav_id
from temp_guideinfo_boundary as a
left join temp_admin_wavid as b
on a.out_adcd = b.ad_cd
order by inlinkid, nodeid, outlinkid;
"""
self.pg.execute2(sqlcmd)
self.pg.commit2()
return 0
def __deal_with_forceguide_update(self):
# import data
forceguide_patch_update_full_path = common.GetPath('forceguide_update')
self._copy_forceguide_X_patch_data(forceguide_patch_update_full_path, 'road_force_guide_update_patch_tbl')
# make temporarily table and then to update current force guide status
sqlcmd = """
insert into temp_update_patch_force_guide_tbl
(
select gid, link_array[1] as fromlinkid, link_array[link_cnt] as tolinkid, (link_cnt -2) as midcount, array_to_string(link_array[2:link_cnt-1],',') as midlink,
(guide_code_int + 1) as guidetype
from
(
select gid, link_array, array_upper(link_array,1) as link_cnt, guide_code_int
from
(
select gid,mid_make_link_list_by_nodes(array[node1,node2,node3,node4,node5,node6,node7]) as link_array,guide_code_int
from road_force_guide_update_patch_tbl
) as a
) as b
)
"""
self.pg.execute2(sqlcmd)
self.pg.commit2()
sqlcmd = """
insert into temp_update_path_force_guide_tbl_matching_tbl
(
select a.objectid as new_objectid, a.fromlinkid as new_fromlinkid, a.tolinkid as new_tolinkid,a.midcount as new_midcount,a.midlinkid as new_midlinkid,a.guidetype as new_guidetype,
b.objectid as old_objectid, b.fromlinkid as old_fromlinkid, b.tolinkid as old_tolinkid, b.midcount as old_midcount, b.midlinkid as old_midlinkid, b.guidetype as old_guidetype
from temp_update_patch_force_guide_tbl as a
left join road_gid as b
on a.fromlinkid = b.fromlinkid and a.tolinkid = b.tolinkid and (a.midlinkid = b.midlinkid or (a.midcount = 0 and b.midcount =0) )
);
"""
self.pg.execute2(sqlcmd)
self.pg.commit2()
sqlcmd = """
update road_gid
set guidetype = a.new_guidetype
from temp_update_path_force_guide_tbl_matching_tbl as a
where a.old_objectid is not null and road_gid.objectid = a.old_objectid
"""
self.pg.execute2(sqlcmd)
self.pg.commit2()
# append records into road_gid from update patch
sqlcmd = """
select max(objectid) as max_id
from road_gid
"""
self.pg.execute2(sqlcmd)
row = self.pg.fetchone2()
if row:
max_id = row[0]
sqlcmd = """
insert into road_gid(gid, objectid, fromlinkid, tolinkid,
midcount,midlinkid, guidetype)
(
select %s+ 5000+ new_objectid, %s+ 5000+ new_objectid, new_fromlinkid, new_tolinkid,
new_midcount, new_midlinkid, new_guidetype
from temp_update_path_force_guide_tbl_matching_tbl
where old_objectid is null
)
""" %(max_id, max_id)
self.pg.execute2(sqlcmd)
self.pg.commit2()
def _DoBoundary(self):
pg = self.pg
pgcur = self.pg.pgcur2
admin_wav_path = common.GetPath('admin_wav')
f = io.open(admin_wav_path, 'r', 8192, 'utf8')
pgcur.copy_from(f, 'temp_admin_wavid', ',', "", 8192, None)
pg.commit2()
f.close()
sqlcmd = """
drop table if exists temp_admin_province_boundary;
select ad_cd, ST_Boundary(the_geom) as the_geom
into temp_admin_province_boundary
from rdb_admin_province;
"""
self.pg.execute2(sqlcmd)
self.pg.commit2()
sqlcmd = """
drop table if exists temp_highway_link;
select link_id, the_geom
into temp_highway_link
from link_tbl
where road_type in (0,1) and link_type = 2;
"""
self.pg.execute2(sqlcmd)
self.pg.commit2()
self.CreateIndex2('temp_admin_province_boundary_the_geom_idx')
self.CreateIndex2('temp_highway_link_the_geom_idx')
sqlcmd = """
drop table if exists temp_link_with_interest_node;
select distinct a.link_id, xxxxx() as interest_node, b.ad_cd
into temp_link_with_interest_node
from temp_highway_link as a
inner join temp_admin_province_boundary as b
on ST_Intersects(a.the_geom, b.the_geom) = TRUE
"""
self.pg.execute2(sqlcmd)
self.pg.commit2()
sqlcmd = """
insert into temp_bnode(link_id, bnode, link_id2, the_geom)
select t1.link_id, t1.bnode, t2.link_id, t1.the_geom
from
(
select m.link_id as link_id1,
from
temp_link_with_interest_node as m
inner join link_tbl as n
on m.link_id = n.link_id
) as t1
inner join link_tbl as t2
on t1.bnode = t2.s_node or t1.bnode = t2.e_node
where t1.link_id != t2.link_id;
"""
self.pg.execute2(sqlcmd)
self.pg.commit2()
sqlcmd = """
insert into temp_guideinfo_boundary(inlinkid, nodeid, outlinkid, innode, outnode, out_adcd)
select distinct inlinkid, bnode, outlinkid, innode, outnode, b.ad_cd
from
(
select links[1] as inlinkid, bnode, links[2] as outlinkid,
links[3] as innode, links[4] as outnode
from
(
select bnode, mid_get_inout_link(link_id, s_node, e_node, one_way_code,
link_id2, s_node2, e_node2, one_way_code2) as links
from temp_bnode
) as t
where links is not null
) as a
inner join node_tbl as c
on a.outnode = c.node_id
inner join rdb_admin_province as b
on ST_Intersects(c.the_geom, b.the_geom) = TRUE;
"""
self.pg.execute2(sqlcmd)
self.pg.commit2()
sqlcmd = """
INSERT INTO caution_tbl(inlinkid, nodeid, outlinkid, passlid, passlink_cnt, data_kind, voice_id)
select inlinkid, nodeid, outlinkid, null, 0, 4, b.wav_id
from temp_guideinfo_boundary as a
inner join temp_admin_wavid as b
on a.out_adcd = b.ad_cd
order by inlinkid, nodeid, outlinkid;
"""
self.pg.execute2(sqlcmd)
self.pg.commit2()
return 0
def init(self):
path = common.GetPath('sql_script')
path = os.path.join(path, 'Middle')
self.LoadSqlScriprts(path)
