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 '../antennes_relais/20150602_DATA/SUP_SUPPORT_OK.txt' -- Chemin absolu (pas testé en relatif...)
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 '../antennes_relais/20150602_DATA/SUP_STATION_OK.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/ericpommereau/Documents/Docs/2015/STSI/rcsi/antennes_relais/20150602_Tables_de_reference/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/ericpommereau/Documents/Docs/2015/STSI/rcsi/antennes_relais/20150602_Tables_de_reference/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 '/Users/ericpommereau/Documents/Docs/2015/STSI/rcsi/antennes_relais/20150602_Tables_de_reference/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 '/Users/ericpommereau/Documents/Docs/2015/STSI/rcsi/antennes_relais/20150602_Tables_de_reference/SUP_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/ericpommereau/Documents/Docs/2015/STSI/rcsi/antennes_relais/20150602_DATA/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 '/Users/ericpommereau/Documents/Docs/2015/STSI/rcsi/antennes_relais/20150602_DATA/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 '../antennes_relais/20150602_DATA/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;

-- Création d'une table pour import QGIS (transformer les Degrés Minustes Secondes en géométrie)
-- On exporte l'id et la concaténation des 3 valeurs

CREATE TABLE sup_support_tmp AS 
SELECT 
	sup_id ,
	CONCAT(cor_nb_dg_lat, ' ', cor_nb_mn_lat, ' ',  cor_nb_sc_lat, ' ', cor_cd_ns_lat) AS DMS_LAT,
	CONCAT(cor_nb_dg_lon, ' ', cor_nb_mn_lon, ' ',  cor_nb_sc_lon, ' ', cor_cd_ew_lon) AS DMS_LON
FROM sup_support

-- Génération des coordonnées DMS avec QGIS (import CSV)

-- Copie locale en CSV

COPY sup_support_tmp (sup_id, DMS_LAT,DMS_LON) 
TO '../antennes_relais/sup_support_tmp.csv' 
WITH CSV HEADER DELIMITER ';' ENCODING 'ISO-8859-1'

-- Import après génération d'un shp dans QGIS
-- Créee une table sup_support_import_shp dans la BDD anfr d'après les datas de sup_import_shp 

-- shp2pgsql -d -I -s 4326 sup_support_tmp.shp sup_support_import_shp | psql -d anfr

-- Créer la table finale avec les géométries (plus besoin de DMS)  
DROP TABLE sup_support_final;

CREATE TABLE sup_support_final AS 

SELECT 
  sup.sup_id::integer,
  sup.sta_nm_anfr,
  sup.nat_id::integer,
  sup.sup_nm_haut,
  sup.tpo_id::integer,
  sup.adr_lb_lieu,
  sup.adr_lb_add1,
  sup.adr_lb_add2,
  sup.adr_lb_add3,
  sup.adr_nm_cp,
  sup.com_cd_insee::varchar(6),
  shp.geom::geometry(Point, 4326)
FROM sup_support_import_shp shp
INNER JOIN sup_support_ok sup ON (shp.sup_id = sup.sup_id::numeric(10,0));

-- Supprimer les tables Intérmédiaires
DROP TABLE IF EXISTS sup_support_tmp;
DROP TABLE IF EXISTS sup_support_import_shp;
DROP TABLE IF EXISTS sup_support;

-- Renommer le schéma
ALTER TABLE sup_support_final RENAME TO sup_support;

-- Selections 

-- Supports par propriétaires
SELECT tpo_lb AS nom_proprietaire, COUNT(*) AS ctr  FROM sup_support sup
INNER JOIN sup_proprietaire prop ON (prop.tpo_id = sup.tpo_id)
-- INNER JOIN sup_nature nat ON (nat.nat_id = sup.nat_id)
GROUP BY prop.tpo_lb
ORDER BY ctr DESC;

-- Natures

SELECT nat_lb_nom AS nature_supp, COUNT(*) AS ctr  FROM sup_support sup
INNER JOIN sup_nature nat ON (nat.nat_id = sup.nat_id)
GROUP BY nat.nat_lb_nom
ORDER BY ctr DESC;

-- Exploitants

SELECT adm_lb_nom AS nom_operateur, COUNT(*) AS ctr  
FROM sup_support sup
INNER JOIN sup_station sta ON (sup.sta_nm_anfr = sta.sta_nm_anfr)
INNER JOIN sup_exploitant expl ON (expl.adm_id = sta.adm_id)
GROUP BY expl.adm_lb_nom
ORDER BY ctr DESC;

-- SELECT DISTINCT unnest(regexp_matches(adm_id, '(\d{1,10}),'))::integer AS digit FROM sup_station;

-- 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;

-- Propriétaire

SELECT tpo_lb AS nom_prop, COUNT(*) AS ctr  
FROM sup_support sup
INNER JOIN sup_proprietaire prop ON (prop.tpo_id = sup.tpo_id)
GROUP BY prop.tpo_lb
ORDER BY ctr DESC;

-- Types d'antenne

SELECT tae_lb AS nom_type_antenne, COUNT(*) AS ctr  
FROM sup_support sup
INNER JOIN sup_antenne ant ON (sup.sta_nm_anfr = ant.sta_nm_anfr)
INNER JOIN sup_type_antenne type_ant ON (ant.tae_id = type_ant.tae_id)
GROUP BY type_ant.tae_lb
ORDER BY ctr DESC;

-- Antennes

SELECT aer_nb_azimut AS azimut, COUNT(*) AS ctr  
FROM sup_antenne ant
GROUP BY azimut
ORDER BY ctr DESC
LIMIT 100

-- Créer un table pour export shp
-- Supports par propriétaires
CREATE TABLE antennes_export_shp AS 

SELECT 
	nat.nat_lb_nom as nature, 
	prop.tpo_lb as proprietaire, 
	sta.Dte_Implantation As dt_implantation_station,
	sta.Dte_modif As dt_modif_station,
	sup.SUP_NM_HAUT as hauteur_support--,
	-- count(ant.sta_nm_anfr) as nb_antennes,
	-- max(ant.aer_nb_dimension) as max_dimmension
-- SELECT count(*) 
FROM sup_support sup
	INNER JOIN sup_proprietaire prop ON (prop.tpo_id = sup.tpo_id)
	INNER JOIN sup_nature nat ON (nat.nat_id = sup.nat_id)
	INNER JOIN sup_station sta ON (sup.sta_nm_anfr = sta.sta_nm_anfr)
	-- INNER JOIN sup_antenne ant ON (ant.sta_nm_anfr = sta.sta_nm_anfr)
-- GROUP BY nat.nat_lb_nom, prop.tpo_lb, sta.Dte_Implantation, sup.SUP_NM_HAUT, sta.Dte_modif 
LIMIT 100;

-- pgsql2shp -f "export_antennes.shp" anfr "SELECT * FROM antennes_export_shp;"