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)