def get_data_from_pg(data_type,insee_com,cadastre_com,local=False,suffixe_data=None): # print(data_type,insee_com,cadastre_com,suffixe_data) cache_file = get_cache_filename(data_type,insee_com,cadastre_com) # print(os.path.abspath(__file__)) if not use_cache or not os.path.exists(cache_file) or (time.time() - os.path.getmtime(cache_file)) > 86400 : fq = open(os.path.join(os.path.dirname(os.path.abspath(__file__)),'sql/{:s}.sql'.format(data_type)),'rb') str_query = fq.read().replace('__com__',insee_com) fq.close() if local: pgc = get_pgc() else: pgc = get_pgc_layers() if suffixe_data: str_query = str_query.replace('__suffixe_data__',suffixe_data) cur = pgc.cursor() cur.execute(str_query) f = open(cache_file,'w+') for lt in cur: l = list(lt) f.write(str(l)+'\n') cur.close() f.seek(0) else : f = open(cache_file,'r') res = [] for l in f: res.append(eval(l)) f.close() return res
def get_data_from_pg(data_type, insee_com, cadastre_com, local=False, suffixe_data=None): # print(data_type,insee_com,cadastre_com,suffixe_data) cache_file = get_cache_filename(data_type, insee_com, cadastre_com) # print(cache_file) if not os.path.exists(cache_file) or (time.time() - os.path.getmtime(cache_file)) > 86400: fq = open("sql/{:s}.sql".format(data_type), "rb") str_query = fq.read().replace("__com__", insee_com) fq.close() if local: pgc = get_pgc() else: pgc = get_pgc_layers() if suffixe_data: str_query = str_query.replace("__suffixe_data__", suffixe_data) cur = pgc.cursor() cur.execute(str_query) f = open(cache_file, "w+") for lt in cur: l = list(lt) f.write(str(l) + "\n") cur.close() f.seek(0) else: f = open(cache_file, "r") res = [] for l in f: res.append(eval(l)) f.close() return res
def main(args): global source, batch_id global pgc, pgcl global code_insee, code_cadastre, code_dept global dicts global nodes, ways, adresses global commune_avec_suffixe global geom_suffixe debut_total = time.time() usage = "USAGE : python addr_cad_2_db.py <code INSEE> <OSM|CADASTRE>" if len(args) != 3: print(usage) os._exit(0) source = args[2].upper() if source not in ["OSM", "CADASTRE"]: print(usage) os._exit(0) adresses = Adresses() pgc = get_pgc() pgcl = get_pgc_layers() code_insee = args[1] code_cadastre = get_code_cadastre_from_insee(code_insee) code_dept = get_cadastre_code_dept_from_insee(code_insee) batch_id = batch_start_log(source, "loadCumul", code_cadastre) dicts = Dicts() dicts.load_all(code_insee) commune_avec_suffixe = has_addreses_with_suffix(code_insee) geom_suffixe = None if commune_avec_suffixe: geom_suffixe = get_geom_suffixes(code_insee, code_cadastre) if source == "CADASTRE": fnadresses = os.path.join( "/data/work/cadastre.openstreetmap.fr/bano_cache", code_dept, code_cadastre, code_cadastre + "-adresses.osm" ) load_hsnr_from_cad_file(fnadresses, source) if source == "OSM": load_hsnr_from_pg_osm(code_insee, code_cadastre) load_highways_from_pg_osm(code_insee, code_cadastre) load_highways_relations_from_pg_osm(code_insee, code_cadastre) add_fantoir_to_hsnr() load_point_par_rue_from_pg_osm(code_insee, code_cadastre) nb_rec = load_to_db(adresses, code_insee, source, code_cadastre, code_dept) batch_end_log(nb_rec, batch_id) fin_total = time.time() print("Execution en " + str(int(fin_total - debut_total)) + " s.")
def main(args): global source, batch_id global pgc, pgcl global code_insee, code_cadastre, code_dept global dicts global nodes, ways, adresses global commune_avec_suffixe global geom_suffixe debut_total = time.time() usage = 'USAGE : python addr_cad_2_db.py <code INSEE> <OSM|CADASTRE>' if len(args) != 3: print(usage) os._exit(0) source = args[2].upper() if source not in ['OSM', 'CADASTRE']: print(usage) os._exit(0) adresses = Adresses() pgc = get_pgc() pgcl = get_pgc_layers() code_insee = args[1] code_cadastre = get_code_cadastre_from_insee(code_insee) code_dept = get_cadastre_code_dept_from_insee(code_insee) batch_id = batch_start_log(source, 'loadCumul', code_cadastre) dicts = Dicts() dicts.load_all(code_insee) commune_avec_suffixe = has_addreses_with_suffix(code_insee) geom_suffixe = None if commune_avec_suffixe: geom_suffixe = get_geom_suffixes(code_insee, code_cadastre) if source == 'CADASTRE': fnadresses = os.path.join( '/data/work/cadastre.openstreetmap.fr/bano_cache', code_dept, code_cadastre, code_cadastre + '-adresses.osm') load_hsnr_from_cad_file(fnadresses, source) if source == 'OSM': load_hsnr_from_pg_osm(code_insee, code_cadastre) load_highways_from_pg_osm(code_insee, code_cadastre) load_highways_relations_from_pg_osm(code_insee, code_cadastre) add_fantoir_to_hsnr() load_point_par_rue_from_pg_osm(code_insee, code_cadastre) nb_rec = load_to_db(adresses, code_insee, source, code_cadastre, code_dept) batch_end_log(nb_rec, batch_id) fin_total = time.time() print('Execution en ' + str(int(fin_total - debut_total)) + ' s.')
def main(args): global source,batch_id global pgc,pgcl global code_insee,code_cadastre,code_dept global dicts global nodes,ways,adresses debut_total = time.time() usage = 'USAGE : python addr_cad_2_db.py <code INSEE> <OSM|CADASTRE>' if len(args) != 3: print(usage) os._exit(0) source = args[2].upper() if source not in ['OSM','CADASTRE']: print(usage) os._exit(0) adresses = Adresses() pgc = get_pgc() pgcl = get_pgc_layers() code_insee = args[1] code_cadastre = get_code_cadastre_from_insee(code_insee) code_dept = get_cadastre_code_dept_from_insee(code_insee) batch_id = batch_start_log(source,'loadCumul',code_cadastre) dicts = Dicts() dicts.load_all(code_insee) if source == 'CADASTRE': fnadresses = os.path.join('/data/work/cadastre.openstreetmap.fr/bano_cache',code_dept,code_cadastre,code_cadastre+'-adresses.osm') load_hsnr_from_cad_file(fnadresses,source) if source == 'OSM': load_hsnr_from_pg_osm(code_insee,code_cadastre) load_highways_from_pg_osm(code_insee,code_cadastre) load_highways_relations_from_pg_osm(code_insee,code_cadastre) add_fantoir_to_hsnr() nb_rec = load_to_db(adresses,code_insee,source,code_cadastre,code_dept) batch_end_log(nb_rec,batch_id) fin_total = time.time() print('Execution en '+str(int(fin_total - debut_total))+' s.')
def main(args): debut_total = time.time() if len(args) < 2: print('USAGE : python addr_cad_2_db.py <code INSEE>') os._exit(0) global source, batch_id global pgc, pgcl global code_insee, code_cadastre, code_dept global dicts global nodes, ways, adresses adresses = Adresses() source = 'CADASTRE' pgc = get_pgc() pgcl = get_pgc_layers() code_insee = args[1] code_cadastre = get_code_cadastre_from_insee(code_insee) code_dept = '0' + code_insee[0:2] if code_insee[0:2] == '97': code_dept = code_insee[0:3] batch_id = batch_start_log(source, 'loadCumul', code_cadastre) dicts = Dicts() dicts.load_all(code_insee) fnadresses = os.path.join( '/data/work/cadastre.openstreetmap.fr/bano_cache', code_dept, code_cadastre, code_cadastre + '-adresses.osm') load_hsnr_from_cad_file(fnadresses, source) load_highways_from_pg_osm(code_insee) load_highways_relations_from_pg_osm(code_insee) add_fantoir_to_hsnr() nb_rec = load_to_db(adresses, code_insee, source, code_cadastre, code_dept) batch_end_log(nb_rec, batch_id) fin_total = time.time() print('Execution en ' + str(int(fin_total - debut_total)) + ' s.')
def main(args): debut_total = time.time() if len(args) < 2: print('USAGE : python addr_cad_2_db.py <code INSEE>') os._exit(0) global source,batch_id global pgc,pgcl global code_insee,code_cadastre,code_dept global dicts global nodes,ways,adresses adresses = Adresses() source = 'CADASTRE' pgc = get_pgc() pgcl = get_pgc_layers() code_insee = args[1] code_cadastre = get_code_cadastre_from_insee(code_insee) code_dept = '0'+code_insee[0:2] if code_insee[0:2] == '97': code_dept = code_insee[0:3] batch_id = batch_start_log(source,'loadCumul',code_cadastre) dicts = Dicts() dicts.load_all(code_insee) fnadresses = os.path.join('/data/work/cadastre.openstreetmap.fr/bano_cache',code_dept,code_cadastre,code_cadastre+'-adresses.osm') load_hsnr_from_cad_file(fnadresses,source) load_highways_from_pg_osm(code_insee) load_highways_relations_from_pg_osm(code_insee) add_fantoir_to_hsnr() nb_rec = load_to_db(adresses,code_insee,source,code_cadastre,code_dept) batch_end_log(nb_rec,batch_id) fin_total = time.time() print('Execution en '+str(int(fin_total - debut_total))+' s.')
def main(args): debut_total = time.time() usage = 'USAGE : python place_2_db.py <code INSEE> <CADASTRE|OSM>' if len(args) < 3: print(usage) os._exit(0) global pgc,pgcl,fantoir,source,code_insee,code_cadastre,code_dept,dicts source = args[2].upper() if source not in ['OSM','CADASTRE']: print(usage) os._exit(0) pgc = get_pgc() pgcl = get_pgc_layers() code_insee = args[1] code_cadastre = get_code_cadastre_from_insee(code_insee) code_dept = get_cadastre_code_dept_from_insee(code_insee) fantoir = {} dicts = Dicts() dicts.load_all() load_fantoir(code_insee) # print(dicts) batch_id = batch_start_log(source,'cumulPlaces',code_cadastre) global places places = [] if source == 'CADASTRE': data = get_data_from_pg('cadastre_2_places',code_insee) # for d in data: # places.append(Place(d[0],d[1],d[2],d[3],d[5],source,d[4])) else: data = get_data_from_pgl('place_insee',code_insee,code_cadastre) for d in data: places.append(Place(d[0],d[1],d[2],d[3].decode('utf8'),d[5],source,d[4])) nb_rec = load_to_db(places,code_insee,source,code_cadastre,code_dept) batch_end_log(nb_rec,batch_id)
def main(args): debut_total = time.time() if len(args) < 2: print('USAGE : python addr_osm_2_db.py <code INSEE>') os._exit(0) global source,batch_id global pgc,pgcl global code_insee,code_cadastre,code_dept global dicts global nodes,ways,adresses source = 'OSM' pgc = get_pgc() pgcl = get_pgc_layers() code_insee = args[1] code_cadastre = get_code_cadastre_from_insee(code_insee) code_dept = '0'+code_insee[0:2] if code_insee[0:2] == '97': code_dept = code_insee[0:3] batch_id = batch_start_log(source,'loadCumul',code_cadastre) adresses = Adresses() dicts = Dicts() dicts.load_all(code_insee) load_highways_from_pg_osm(code_insee) load_hsnr_from_pg_osm(code_insee) add_fantoir_to_hsnr() load_to_db(adresses) fin_total = time.time() print('Execution en '+str(int(fin_total - debut_total))+' s.')
def main(args): debut_total = time.time() if len(args) < 2: print('USAGE : python addr_osm_2_db.py <code INSEE>') os._exit(0) global source, batch_id global pgc, pgcl global code_insee, code_cadastre, code_dept global dicts global nodes, ways, adresses source = 'OSM' pgc = get_pgc() pgcl = get_pgc_layers() code_insee = args[1] code_cadastre = get_code_cadastre_from_insee(code_insee) code_dept = '0' + code_insee[0:2] if code_insee[0:2] == '97': code_dept = code_insee[0:3] batch_id = batch_start_log(source, 'loadCumul', code_cadastre) adresses = Adresses() dicts = Dicts() dicts.load_all(code_insee) load_highways_from_pg_osm(code_insee) load_hsnr_from_pg_osm(code_insee) add_fantoir_to_hsnr() load_to_db(adresses) fin_total = time.time() print('Execution en ' + str(int(fin_total - debut_total)) + ' s.')
'(ST_PointFromText(\'POINT({:s} {:s})\', 4326),\'{:s}\',\'{:s}\',\'{:s}\',\'{:s}\',\'{:s}\',\'{:s}\',\'{:s}\',\'{:s}\',\'{:s}\')' .format(numadresse.node.attribs['lon'], numadresse.node.attribs['lat'], numadresse.numero.encode('utf8'), street_name_fantoir.replace("'", "''"), street_name_osm.replace("'", "''"), cle_fantoir, code_insee, code_cadastre, code_dept, '', source)) nb_rec += 1 sload = sload + ','.join(a_values) + ';COMMIT;' cur_insert.execute(sload) return (nb_rec) def get_best_fantoir(cle): res = '' if 'FANTOIR' in adresses.a[cle]['fantoirs']: res = adresses.a[cle]['fantoirs']['FANTOIR'] if 'OSM' in adresses.a[cle]['fantoirs']: res = adresses.a[cle]['fantoirs']['OSM'] return res global pgc, pgcl pgc = get_pgc() pgcl = get_pgc_layers() # global dicts # dicts = Dicts() # dicts.load_all(code_insee) global adresses adresses = Adresses()
def main(args): global source,batch_id global pgc,pgcl global code_insee,code_cadastre,code_dept global dicts global nodes,ways,adresses global commune_avec_suffixe global geom_suffixe global use_cache use_cache = True debut_total = time.time() usage = 'USAGE : python addr_cad_2_db.py <code INSEE> <OSM|CADASTRE> {use_cache=True}' if len(args) < 3: print(usage) os._exit(0) if len(args) > 3: # use_cache = eval(args[3]) use_cache = args[3] source = args[2].upper() if source not in ['OSM','CADASTRE']: print(usage) os._exit(0) adresses = Adresses() pgc = get_pgc() pgcl = get_pgc_layers() code_insee = args[1] code_cadastre = get_code_cadastre_from_insee(code_insee) code_dept = get_cadastre_code_dept_from_insee(code_insee) batch_id = batch_start_log(source,'loadCumul',code_cadastre) dicts = Dicts() dicts.load_all(code_insee) commune_avec_suffixe = has_addreses_with_suffix(code_insee) geom_suffixe = None if commune_avec_suffixe: geom_suffixe = get_geom_suffixes(code_insee,code_cadastre) if source == 'CADASTRE': fnadresses = os.path.join('/data/work/cadastre.openstreetmap.fr/bano_cache',code_dept,code_cadastre,code_cadastre+'-adresses.osm') load_hsnr_from_cad_file(fnadresses,source) if source == 'OSM': load_hsnr_from_pg_osm(code_insee,code_cadastre) load_hsnr_bbox_from_pg_osm(code_insee,code_cadastre) load_type_highway_from_pg_osm(code_insee,code_cadastre) load_highways_from_pg_osm(code_insee,code_cadastre) load_highways_relations_from_pg_osm(code_insee,code_cadastre) load_highways_bbox_from_pg_osm(code_insee,code_cadastre) load_highways_relations_bbox_from_pg_osm(code_insee,code_cadastre) add_fantoir_to_hsnr() load_point_par_rue_from_pg_osm(code_insee,code_cadastre) load_point_par_rue_complement_from_pg_osm(code_insee,code_cadastre) nb_rec = load_to_db(adresses,code_insee,source,code_cadastre,code_dept) batch_end_log(nb_rec,batch_id) fin_total = time.time()
street_name_osm = adresses.a[v]['voies']['OSM'].encode('utf8') if 'FANTOIR' in adresses.a[v]['voies']: street_name_fantoir = adresses.a[v]['voies']['FANTOIR'].encode('utf8') if 'CADASTRE' in adresses.a[v]['voies']: street_name_fantoir = adresses.a[v]['voies']['CADASTRE'].encode('utf8') if street_name_osm == '' and street_name_fantoir == '': print('****** voies muettes '+v) # nodes for num in adresses.a[v]['numeros']: numadresse = adresses.a[v]['numeros'][num] a_values.append('(ST_PointFromText(\'POINT({:s} {:s})\', 4326),\'{:s}\',\'{:s}\',\'{:s}\',\'{:s}\',\'{:s}\',\'{:s}\',\'{:s}\',\'{:s}\',\'{:s}\')'.format(numadresse.node.attribs['lon'],numadresse.node.attribs['lat'],numadresse.numero.encode('utf8'),street_name_fantoir.replace("'","''"),street_name_osm.replace("'","''"),cle_fantoir,code_insee,code_cadastre,code_dept,'',source)) nb_rec +=1 sload = sload+','.join(a_values)+';COMMIT;' cur_insert.execute(sload) return(nb_rec) def get_best_fantoir(cle): res = '' if 'FANTOIR' in adresses.a[cle]['fantoirs']: res = adresses.a[cle]['fantoirs']['FANTOIR'] if 'OSM' in adresses.a[cle]['fantoirs']: res = adresses.a[cle]['fantoirs']['OSM'] return res global pgc,pgcl pgc = get_pgc() pgcl = get_pgc_layers() # global dicts # dicts = Dicts() # dicts.load_all(code_insee) global adresses adresses = Adresses()