mise à jour opendata Arles pour intégration

gistfile1.sql

-- update des codes FANTOIR sans clé rivoli sur données opendata
with f as (select fantoir, cle_rivoli from cumul_adresses join fantoir_voie on (code_insee=insee_com and concat(code_insee,id_voie)=fantoir) where source = 'OD-ACCM-Arles' group by fantoir, cle_rivoli) update cumul_adresses c set fantoir = concat(f.fantoir,f.cle_rivoli) from f where c.fantoir=f.fantoir and source like 'OD%';


-- update des voie_osm reprises depuis source cadastre
with v as (select od.fantoir, cu.voie_osm from cumul_adresses od join cumul_adresses cu on (cu.fantoir=od.fantoir and cu.voie_osm is not null and cu.voie_osm != '') where od.source = 'OD-ACCM-Arles' and od.voie_osm is null group by od.fantoir, cu.voie_osm) update cumul_adresses c set voie_osm=v.voie_osm from v where c.voie_osm is null and c.fantoir=v.fantoir and c.source like 'OD%';

-- update nom de voies depuis voie_cadastre
with v as (select od.fantoir, cu.voie_cadastre from cumul_adresses od join cumul_adresses cu on (cu.fantoir=od.fantoir and cu.source not like 'OD%') where od.source like 'OD-ACCM%' and od.voie_osm is null group by od.fantoir, cu.voie_cadastre order by od.fantoir) update cumul_adresses cu set voie_osm = v.voie_cadastre from v where cu.fantoir=v.fantoir and cu.source like 'OD%';

-- on supprime ce qu'on n'a pas pu rapprocher
delete from cumul_adresses where source like 'OD-ACCM%' and voie_osm is null;



-- mise à jour des codes FANTOIR opendata Montpellier
with f as (select fantoir, cle_rivoli from cumul_adresses join fantoir_voie on (code_insee=insee_com and concat(code_insee,id_voie)=fantoir) where source = 'OD-MONTPELLIER' group by fantoir, cle_rivoli) update cumul_adresses c set fantoir = concat(f.fantoir,f.cle_rivoli) from f where c.fantoir=f.fantoir and source like 'OD%';



-- mise à jour des codes FANTOIR opendata Nancy
with f as (select fantoir, cle_rivoli from cumul_adresses join fantoir_voie on (code_insee=insee_com and concat(code_insee,id_voie)=fantoir) where source = 'OD-NANCY' group by fantoir, cle_rivoli) update cumul_adresses c set fantoir = concat(f.fantoir,f.cle_rivoli) from f where c.fantoir=f.fantoir and source like 'OD%';

-- mise à jour NANCY
with v as (select od.fantoir, cu.voie_osm from cumul_adresses od join cumul_adresses cu on (cu.fantoir=od.fantoir and cu.voie_osm is not null and cu.voie_osm != '') where od.source = 'OD-NANCY' and od.voie_osm is null group by od.fantoir, cu.voie_osm) update cumul_adresses c set voie_osm=v.voie_osm from v where c.voie_osm is null and c.fantoir=v.fantoir and c.source like 'OD%';

with v as (select od.fantoir, cu.voie_cadastre from cumul_adresses od join cumul_adresses cu on (cu.fantoir=od.fantoir and cu.source not like 'OD%') where od.source='OD-NANCY' and od.voie_osm is null group by od.fantoir, cu.voie_cadastre order by od.fantoir) update cumul_adresses cu set voie_osm = v.voie_cadastre from v where cu.fantoir=v.fantoir and cu.source like 'OD%';

-- on supprime ce qu'on n'a pas pu rapprocher
delete from cumul_adresses where source like 'OD-NANCY' and voie_osm is null;



-- mise à jour des codes FANTOIR opendata RENNES
with f as (select fantoir, cle_rivoli from cumul_adresses join fantoir_voie on (code_insee=insee_com and concat(code_insee,id_voie)=fantoir) where source = 'OD-RENNES' group by fantoir, cle_rivoli) update cumul_adresses c set fantoir = concat(f.fantoir,f.cle_rivoli) from f where c.fantoir=f.fantoir and source like 'OD%';

with v as (select od.fantoir, cu.voie_osm from cumul_adresses od join cumul_adresses cu on (cu.fantoir=od.fantoir and cu.voie_osm is not null and cu.voie_osm != '') where od.source = 'OD-RENNES' and od.voie_osm is null group by od.fantoir, cu.voie_osm) update cumul_adresses c set voie_osm=v.voie_osm from v where c.voie_osm is null and c.fantoir=v.fantoir and c.source like 'OD%';

with v as (select od.fantoir, cu.voie_cadastre from cumul_adresses od join cumul_adresses cu on (cu.fantoir=od.fantoir and cu.source not like 'OD%') where od.source='OD-RENNES' and od.voie_osm is null group by od.fantoir, cu.voie_cadastre order by od.fantoir) update cumul_adresses cu set voie_osm = v.voie_cadastre from v where cu.fantoir=v.fantoir and cu.source like 'OD%';


-- TOULOUSE
with v as (select od.fantoir, cu.voie_osm from cumul_adresses od join cumul_adresses cu on (cu.fantoir=od.fantoir and cu.voie_osm is not null and cu.voie_osm != '') where od.source = 'OD-TOULOUSE' and od.voie_osm is null group by od.fantoir, cu.voie_osm) update cumul_adresses c set voie_osm=v.voie_osm from v where c.voie_osm is null and c.fantoir=v.fantoir and c.source like 'OD%';

with v as (select od.fantoir, cu.voie_cadastre from cumul_adresses od join cumul_adresses cu on (cu.fantoir=od.fantoir and cu.source not like 'OD%') where od.source='OD-TOULOUSE' and od.voie_osm is null group by od.fantoir, cu.voie_cadastre order by od.fantoir) update cumul_adresses cu set voie_osm = v.voie_cadastre from v where cu.fantoir=v.fantoir and cu.source like 'OD%';

-- NANTES
with f as (select fantoir, cle_rivoli from cumul_adresses join fantoir_voie on (code_insee=insee_com and concat(code_insee,id_voie)=fantoir) where source = 'OD-NANTES' group by fantoir, cle_rivoli) update cumul_adresses c set fantoir = concat(f.fantoir,f.cle_rivoli) from f where c.fantoir=f.fantoir and source like 'OD%';

with v as (select od.fantoir, cu.voie_osm from cumul_adresses od join cumul_adresses cu on (cu.fantoir=od.fantoir and cu.voie_osm is not null and cu.voie_osm != '') where od.source = 'OD-NANTES' and od.voie_osm is null group by od.fantoir, cu.voie_osm) update cumul_adresses c set voie_osm=v.voie_osm from v where c.voie_osm is null and c.fantoir=v.fantoir and c.source like 'OD%';