script d'extraction des noms et adresses actuelles et passées des entreprises

sirene_noms_adresses.sql

-- script d'extraction des noms et adresses actuelles et passées des entreprises
-- et établissements des données historisées de la base SIRENE de l'INSEE

-- vue matérialisée des différents noms des unités légales
CREATE MATERIALIZED VIEW ul_nom AS
SELECT siren, array_agg(distinct(nom)) AS noms FROM
(
    SELECT siren, denominationunitelegale AS nom FROM ul_histo
UNION
    SELECT siren, denominationusuelle1unitelegale AS nom FROM ul_histo
UNION
    SELECT siren, denominationusuelle2unitelegale AS nom FROM ul_histo
UNION
    SELECT siren, denominationusuelle3unitelegale AS nom FROM ul_histo
) AS n WHERE nom IS NOT NULL GROUP BY 1;

-- vue matérialisée des différents noms des établissements
CREATE MATERIALIZED VIEW ets_nom AS
select siren, siret, array_agg(distinct(nom)) as noms from
(
    select siren, siret, denominationusuelleetablissement as nom from ets_histo
UNION
    select siren, siret, enseigne1etablissement as nom from ets_histo
UNION
    select siren, siret, enseigne2etablissement as nom from ets_histo
UNION
    select siren, siret, enseigne3etablissement as nom from ets_histo
UNION
    -- ainsi que les noms de l'entreprise
    select e.siren, siret, unnest(noms) FROM ets_histo e join ul_nom n on (n.siren=e.siren) 
) as n where nom is not null group by 1,2;

-- vue matérialisée des différentes adresses d'un établissement
CREATE MATERIALIZED VIEW ets_adresses AS
SELECT siren, siret, array_agg(distinct(adresse)) as adresses FROM
(
    select siren, siret, trim(regexp_replace(format('%s %s %s %s %s %s %s %s %s %s %s', numerovoieetablissement, indicerepetitionetablissement,
        typevoieetablissement, libellevoieetablissement, codepostaletablissement, libellecommuneetablissement,
        distributionspecialeetablissement, codecedexetablissement, libellecedexetablissement,
        libellecommuneetrangeretablissement, libellepaysetrangeretablissement),'  *',' ')) as adresse
        from ets_histo
    UNION
    select siren, siret, trim(regexp_replace(format('%s %s %s %s %s %s %s %s %s %s %s', numerovoie2etablissement, indicerepetition2etablissement,
        typevoie2etablissement, libellevoie2etablissement, codepostal2etablissement, libellecommune2etablissement,
        distributionspeciale2etablissement, codecedex2etablissement, libellecedex2etablissement,
        libellecommuneetranger2etablissement, libellepaysetranger2etablissement),'  *',' ')) as adresse
        from ets_histo
) as a where coalesce(adresse,'') != '' group by 1,2;

-- export global en json
\copy (SELECT row_to_json(r) FROM (
    SELECT n.siren, n.siret, n.noms, a.adresses
    FROM ets_nom n
    LEFT JOIN ets_adresses a ON (n.siret=a.siret)
) as r) to /tmp/sirene_noms_adresses.json