Last active
January 20, 2019 13:52
-
-
Save eric-pommereau/4a5966cffa3a71867210 to your computer and use it in GitHub Desktop.
Import opendata ANFR
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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;" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment