Import opendata ANFR

gistfile1.sql

/*
Exemple de réutilisation opendata
    _   _  _ ___ ___ 
   /_\ | \| | __| _ \
  / _ \| .` | _||   /
 /_/ \_\_|\_|_| |_|_\
                     
Description : installations radioélectriques de plus de 5 watts, hormis celles de l'Aviation Civile et des ministères de la Défense et de l'Intérieur. 
Les données présentées proviennent d'une base de données de l'ANFR alimentée par tous les exploitants d'installations radioélectriques, 
publics ou privés, dans le cadre de la procédure administrative prévue par l'article L.43 du code des postes et communications électroniques.
Source : https://www.data.gouv.fr/fr/datasets/donnees-sur-les-installations-radioelectriques-de-plus-de-5-watts-1/
*/

-- Importer les supports d'antenne
/*
	Attention : 3 lignes trouvée avec erreur (plus ou moins de séparateurs que la liste des colonnes)
*/

DROP TABLE IF EXISTS sup_support_ok;

CREATE TABLE sup_support_ok (
        SUP_ID integer,
        STA_NM_ANFR varchar,
        NAT_ID integer,
        COR_NB_DG_LAT varchar,
        COR_NB_MN_LAT varchar,
        COR_NB_SC_LAT varchar,
        COR_CD_NS_LAT varchar,
        COR_NB_DG_LON varchar,
        COR_NB_MN_LON varchar,
        COR_NB_SC_LON varchar,
        COR_CD_EW_LON varchar,
        SUP_NM_HAUT varchar,
        TPO_ID integer,
        ADR_LB_LIEU varchar,
        ADR_LB_ADD1 varchar,
        ADR_LB_ADD2 varchar,
        ADR_LB_ADD3 varchar,
        ADR_NM_CP varchar,
        COM_CD_INSEE varchar
);

COPY sup_support_ok (SUP_ID, STA_NM_ANFR, NAT_ID, COR_NB_DG_LAT, COR_NB_MN_LAT, COR_NB_SC_LAT, COR_CD_NS_LAT, COR_NB_DG_LON, COR_NB_MN_LON, COR_NB_SC_LON, COR_CD_EW_LON, SUP_NM_HAUT, TPO_ID, ADR_LB_LIEU, ADR_LB_ADD1, ADR_LB_ADD2, ADR_LB_ADD3, ADR_NM_CP, COM_CD_INSEE) 
FROM '/Users/ontologiS/SUP_SUPPORT.txt' -- Chemin à modifier pour pointer les bon fichiers 
WITH CSV HEADER DELIMITER ';' ENCODING 'LATIN1';

--ALTER TABLE sup_support
--  ADD CONSTRAINT pk_support PRIMARY KEY (sup_id);


-- Stations 

DROP TABLE IF EXISTS sup_station_ok;

CREATE TABLE sup_station_ok (
        STA_NM_ANFR varchar,
        ADM_ID varchar,
        DEM_NM_COMSIS varchar,
        Dte_Implantation varchar,
        Dte_modif varchar,
        Dte_En_Service varchar
);

COPY sup_station_ok (STA_NM_ANFR, ADM_ID, DEM_NM_COMSIS, Dte_Implantation, Dte_modif, Dte_En_Service) 
FROM '/Users/ontologi/SUP_STATION.txt' 
WITH CSV HEADER DELIMITER ';' ENCODING 'LATIN1';


-- Les exploitants
-- Erreur dans le fichier d'import, un ";" en trop 

DROP TABLE IF EXISTS sup_exploitant;

CREATE TABLE sup_exploitant (
        ADM_ID integer,
        ADM_LB_NOM varchar
);
COPY sup_exploitant (ADM_ID, ADM_LB_NOM) 
FROM '/Users/onS/SUP_EXPLOITANT.txt' 
WITH CSV HEADER DELIMITER ';' ENCODING 'LATIN1';

-- Nature du support
DROP TABLE IF EXISTS sup_nature;

CREATE TABLE sup_nature (
        NAT_ID integer,
        NAT_LB_NOM varchar
);

COPY sup_nature (NAT_ID, NAT_LB_NOM) 
FROM '/Users/ontoS/SUP_NATURE.txt' 
WITH CSV HEADER DELIMITER ';' ENCODING 'LATIN1';

-- Type d'antenne
DROP TABLE IF EXISTS sup_type_antenne;


CREATE TABLE sup_type_antenne (
        TAE_ID integer,
        TAE_LB varchar
);

COPY sup_type_antenne (TAE_ID, TAE_LB) 
FROM '/Use/SUP_TYPE_ANTENNE.txt' 
WITH CSV HEADER DELIMITER ';' ENCODING 'LATIN1';

-- Propriétaire
DROP TABLE IF EXISTS sup_proprietaire;

CREATE TABLE sup_proprietaire (
        TPO_ID integer,
        TPO_LB varchar
);

COPY sup_proprietaire (TPO_ID, TPO_LB) 
FROM '/UsersSUP_PROPRIETAIRE.txt' 
WITH CSV HEADER DELIMITER ';' ENCODING 'LATIN1';

-- Antennes

DROP TABLE IF EXISTS sup_antenne;

CREATE TABLE sup_antenne (
        STA_NM_ANFR varchar,
        AER_ID integer,
        TAE_ID integer,
        AER_NB_DIMENSION varchar,
        AER_FG_RAYON varchar,
        AER_NB_AZIMUT varchar,
        AER_NB_ALT_BAS varchar
);

COPY sup_antenne (STA_NM_ANFR, AER_ID, TAE_ID, AER_NB_DIMENSION, AER_FG_RAYON, AER_NB_AZIMUT, AER_NB_ALT_BAS) 
FROM '/Users/ontMa/SUP_ANTENNE.txt' 
WITH CSV HEADER DELIMITER ';' ENCODING 'LATIN1';

-- L'émetteur
DROP TABLE IF EXISTS sup_emetteur;

CREATE TABLE sup_emetteur (
        EMR_ID integer,
        EMR_LB_SYSTEME varchar,
        STA_NM_ANFR varchar,
        AER_ID integer
);

COPY sup_emetteur (EMR_ID, EMR_LB_SYSTEME, STA_NM_ANFR, AER_ID) 
FROM '/UaisonEHS/SUP_EMETTEUR.txt' 
WITH CSV HEADER DELIMITER ';' ENCODING 'LATIN1';

-- La bande
DROP TABLE IF EXISTS sup_bande;

CREATE TABLE sup_bande (
        STA_NM_ANFR varchar,
        BAN_ID integer,
        EMR_ID integer,
        BAN_NB_F_DEB varchar,
        BAN_NB_F_FIN varchar,
        BAN_FG_UNITE varchar
);

COPY sup_bande (STA_NM_ANFR, BAN_ID, EMR_ID, BAN_NB_F_DEB, BAN_NB_F_FIN, BAN_FG_UNITE) 
FROM '//MaisonEHS/SUP_BANDE.txt' 
WITH CSV HEADER DELIMITER ';' ENCODING 'LATIN1';

-- Renomer les 2 tables ok (modifiées)
ALTER TABLE sup_station_ok RENAME TO sup_station;
ALTER TABLE sup_support_ok RENAME TO sup_support;





-- Pb de format (avec virgules...)
-- regexp_matches retourne un text[], d'où le unnest

UPDATE sup_station SET adm_id = unnest(regexp_matches(adm_id, '(\d{1,10}),'));

-- Obligatoire de caster avec USING
ALTER TABLE sup_station ALTER COLUMN adm_id TYPE integer USING adm_id::integer;

UPDATE sup_antenne SET aer_nb_alt_bas = unnest(regexp_matches(aer_nb_alt_bas, '(\d{1,10}),'));

ALTER TABLE sup_antenne ALTER COLUMN aer_nb_alt_bas TYPE integer USING aer_nb_alt_bas::integer;


update sup_bande set ban_nb_f_deb = replace(ban_nb_f_deb,',','.');
update sup_bande set ban_nb_f_fin = replace(ban_nb_f_fin,',','.');


drop table  IF EXISTS  BaseAntennesCompletes;

create table BaseAntennesCompletes as
select sa.aer_id, sa.tae_id, sa.AER_NB_DIMENSION, sa.AER_FG_RAYON, sa.AER_NB_AZIMUT, sa.AER_NB_ALT_BAS, sb.*
,case when sb.ban_fg_unite = 'G' then sb.ban_nb_f_deb::numeric*1000 when sb.ban_fg_unite = 'K' then sb.ban_nb_f_deb::numeric/1000 else sb.ban_nb_f_deb::numeric end as freqd
,case when sb.ban_fg_unite = 'G' then sb.ban_nb_f_fin::numeric*1000 when sb.ban_fg_unite = 'K' then sb.ban_nb_f_fin::numeric/1000  else sb.ban_nb_f_fin::numeric end as freqf
,sup_id, nat_id
, ST_SetSRID(st_makepoint(case when cor_cd_ns_lat = 'S' then
- (cor_nb_dg_lat::numeric + cor_nb_mn_lat::numeric/60 + cor_nb_sc_lat::numeric/3600)
else cor_nb_dg_lat::numeric + cor_nb_mn_lat::numeric/60 + cor_nb_sc_lat::numeric/3600 end
, case when cor_cd_ew_lon = 'W' then
- (cor_nb_dg_lon::numeric + cor_nb_mn_lon::numeric/60 + cor_nb_sc_lon::numeric/3600)
else cor_nb_dg_lon::numeric + cor_nb_mn_lon::numeric/60 + cor_nb_sc_lon::numeric/3600 end), 4326) as point
, sup_nm_haut ,tpo_id, adr_lb_lieu, adr_lb_add1, adr_nm_cp, com_cd_insee
from sup_support ss inner join sup_antenne sa on sa.sta_nm_anfr = ss.STA_NM_ANFR inner join sup_bande sb on sb.sta_nm_anfr = sa.STA_NM_ANFR 
inner join sup_emetteur se on se.sta_nm_anfr = sb.sta_nm_anfr and sb.EMR_ID = se.EMR_ID
where sa.AER_ID = se.AER_ID;

create index on BaseAntennesCompletes  using btree (point);