140 lines
6.6 KiB
Bash
140 lines
6.6 KiB
Bash
#!/usr/bin/env bash
|
|
|
|
read -p "DB User : " USER
|
|
read -p "DB Password : " PGPASSWORD
|
|
read -p "DB Host : " HOST
|
|
read -p "DB Port : " PORT
|
|
read -p "DB dbname : " DB
|
|
|
|
curl https://static.data.gouv.fr/resources/bases-de-donnees-annuelles-des-accidents-corporels-de-la-circulation-routiere-annees-de-2005-a-2021/20231005-093927/carcteristiques-2022.csv --output ${HOME}/www/accidents_2022/caracteristiques-2022.csv
|
|
curl https://static.data.gouv.fr/resources/bases-de-donnees-annuelles-des-accidents-corporels-de-la-circulation-routiere-annees-de-2005-a-2021/20231005-094229/usagers-2022.csv --output ${HOME}/www/accidents_2022/usagers-2022.csv
|
|
curl https://static.data.gouv.fr/resources/bases-de-donnees-annuelles-des-accidents-corporels-de-la-circulation-routiere-annees-de-2005-a-2021/20231005-094112/lieux-2022.csv --output ${HOME}/www/accidents_2022/lieux-2022.csv
|
|
curl https://static.data.gouv.fr/resources/bases-de-donnees-annuelles-des-accidents-corporels-de-la-circulation-routiere-annees-de-2005-a-2021/20231005-094147/vehicules-2022.csv --output ${HOME}/www/accidents_2022/vehicules-2022.csv
|
|
curl "https://data.nantesmetropole.fr/api/explore/v2.1/catalog/datasets/244400404_communes-nantes-metropole/exports/csv?lang=fr&timezone=Europe%2FBerlin&use_labels=true&delimiter=%3B" | csvcut --delimiter ";" -C 8 > ${HOME}/www/Communes_de_Nantes_metropole.csv
|
|
|
|
sed -i "s/,/./g" ${HOME}/www/accidents_2022/caracteristiques-2022.csv
|
|
sed -i "s/Accident_Id/Num_Acc/g" ${HOME}/www/accidents_2022/caracteristiques-2022.csv
|
|
sed -r -i 's/[[:digit:]]{2}:[[:digit:]]{2}/&:00/g' ${HOME}/www/accidents_2022/caracteristiques-2022.csv
|
|
|
|
csvsql --delimiter ";" --no-constraints --tables onisr_2022_usagers --insert --db "postgresql://${USER}:${PGPASSWORD}@${HOST}:${PORT}/${DB}" --create-if-not-exists --overwrite --snifflimit "-1" ${HOME}/www/accidents_2022/usagers-2022.csv
|
|
csvsql --delimiter ";" --no-constraints --tables onisr_2022_lieux --insert --db "postgresql://${USER}:${PGPASSWORD}@${HOST}:${PORT}/${DB}" --create-if-not-exists --overwrite --snifflimit "-1" ${HOME}/www/accidents_2022/lieux-2022.csv
|
|
csvsql --delimiter ";" --no-constraints --tables onisr_2022_vehicules --insert --db "postgresql://${USER}:${PGPASSWORD}@${HOST}:${PORT}/${DB}" --create-if-not-exists --overwrite --snifflimit "-1" ${HOME}/www/accidents_2022/vehicules-2022.csv
|
|
csvsql --delimiter ";" --no-constraints --tables onisr_2022_caracteristiques --insert --db "postgresql://${USER}:${PGPASSWORD}@${HOST}:${PORT}/${DB}" --create-if-not-exists --overwrite --snifflimit "-1" ${HOME}/www/accidents_2022/caracteristiques-2022.csv
|
|
csvsql --delimiter "," --no-constraints --tables commune_nm --insert --db "postgresql://${USER}:${PGPASSWORD}@${HOST}:${PORT}/${DB}" --create-if-not-exists --overwrite --snifflimit "-1" ${HOME}/www/Communes_de_Nantes_metropole.csv
|
|
|
|
|
|
psql "postgresql://${USER}:${PGPASSWORD}@${HOST}:${PORT}/${DB}" << SQL
|
|
WITH keep_this_accident as (
|
|
SELECT "onisr_2022_caracteristiques"."Num_Acc" AS caracteristiques_2022_id
|
|
FROM onisr_2022_caracteristiques
|
|
JOIN onisr_2022_vehicules on "onisr_2022_vehicules"."Num_Acc" = "onisr_2022_caracteristiques"."Num_Acc"
|
|
JOIN onisr_2022_usagers on "onisr_2022_usagers"."Num_Acc" = "onisr_2022_caracteristiques"."Num_Acc"
|
|
JOIN onisr_2022_lieux on "onisr_2022_lieux"."Num_Acc" = "onisr_2022_caracteristiques"."Num_Acc"
|
|
WHERE "com" IN (
|
|
SELECT "Identifiant INSEE"::VARCHAR FROM commune_nm
|
|
)
|
|
)
|
|
DELETE FROM onisr_2022_caracteristiques WHERE "Num_Acc" NOT IN (select * from keep_this_accident);
|
|
SQL
|
|
|
|
|
|
psql "postgresql://${USER}:${PGPASSWORD}@${HOST}:${PORT}/${DB}" << SQL
|
|
WITH keep_this_accident as (
|
|
SELECT "onisr_2022_caracteristiques"."Num_Acc" AS caracteristiques_2022_id
|
|
FROM onisr_2022_caracteristiques
|
|
JOIN onisr_2022_vehicules on "onisr_2022_vehicules"."Num_Acc" = "onisr_2022_caracteristiques"."Num_Acc"
|
|
JOIN onisr_2022_usagers on "onisr_2022_usagers"."Num_Acc" = "onisr_2022_caracteristiques"."Num_Acc"
|
|
JOIN onisr_2022_lieux on "onisr_2022_lieux"."Num_Acc" = "onisr_2022_caracteristiques"."Num_Acc"
|
|
WHERE "com" IN (
|
|
SELECT "Identifiant INSEE"::VARCHAR FROM commune_nm
|
|
)
|
|
)
|
|
DELETE FROM onisr_2022_vehicules WHERE "Num_Acc" NOT IN (select * from keep_this_accident);
|
|
SQL
|
|
|
|
psql "postgresql://${USER}:${PGPASSWORD}@${HOST}:${PORT}/${DB}" << SQL
|
|
WITH keep_this_accident as (
|
|
SELECT "onisr_2022_caracteristiques"."Num_Acc" AS caracteristiques_2022_id
|
|
FROM onisr_2022_caracteristiques
|
|
JOIN onisr_2022_vehicules on "onisr_2022_vehicules"."Num_Acc" = "onisr_2022_caracteristiques"."Num_Acc"
|
|
JOIN onisr_2022_usagers on "onisr_2022_usagers"."Num_Acc" = "onisr_2022_caracteristiques"."Num_Acc"
|
|
JOIN onisr_2022_lieux on "onisr_2022_lieux"."Num_Acc" = "onisr_2022_caracteristiques"."Num_Acc"
|
|
WHERE "com" IN (
|
|
SELECT "Identifiant INSEE"::VARCHAR FROM commune_nm
|
|
)
|
|
)
|
|
DELETE FROM onisr_2022_usagers WHERE "Num_Acc" NOT IN (select * from keep_this_accident);
|
|
SQL
|
|
|
|
psql "postgresql://${USER}:${PGPASSWORD}@${HOST}:${PORT}/${DB}" << SQL
|
|
WITH keep_this_accident as (
|
|
SELECT "onisr_2022_caracteristiques"."Num_Acc" AS caracteristiques_2022_id
|
|
FROM onisr_2022_caracteristiques
|
|
JOIN onisr_2022_vehicules on "onisr_2022_vehicules"."Num_Acc" = "onisr_2022_caracteristiques"."Num_Acc"
|
|
JOIN onisr_2022_usagers on "onisr_2022_usagers"."Num_Acc" = "onisr_2022_caracteristiques"."Num_Acc"
|
|
JOIN onisr_2022_lieux on "onisr_2022_lieux"."Num_Acc" = "onisr_2022_caracteristiques"."Num_Acc"
|
|
WHERE "com" IN (
|
|
SELECT "Identifiant INSEE"::VARCHAR FROM commune_nm
|
|
)
|
|
)
|
|
DELETE FROM onisr_2022_lieux WHERE "Num_Acc" NOT IN (select * from keep_this_accident);
|
|
SQL
|
|
|
|
exit
|
|
|
|
psql "postgresql://${USER}:${PGPASSWORD}@${HOST}:${PORT}/${DB}" << SQL
|
|
CREATE TABLE IF NOT EXISTS onisr_intersection (
|
|
id INT PRIMARY KEY,
|
|
name VARCHAR(64) NOT NULL
|
|
);
|
|
|
|
INSERT INTO onisr_intersection(id, name)
|
|
VALUES
|
|
(1, 'Hors intersection'),
|
|
(2, 'Intersection en X'),
|
|
(3, 'Intersection en T'),
|
|
(4, 'Intersection en Y'),
|
|
(5, 'Intersection à plus de 4 branches'),
|
|
(6, 'Giratoire'),
|
|
(7, 'Place'),
|
|
(8, 'Passage à niveau'),
|
|
(9, 'Autre intersection');
|
|
|
|
CREATE TABLE IF NOT EXISTS onisr_gravite (
|
|
id INT PRIMARY KEY,
|
|
name VARCHAR(64) NOT NULL
|
|
);
|
|
|
|
INSERT INTO onisr_gravite(id, name)
|
|
VALUES
|
|
(1, 'Indemne'),
|
|
(2, 'Tué'),
|
|
(3, 'Blessé hospitalisé'),
|
|
(4, 'Blessé léger');
|
|
|
|
CREATE TABLE IF NOT EXISTS onisr_categorie_vehicule (
|
|
id INT PRIMARY KEY,
|
|
name VARCHAR(64) NOT NULL
|
|
);
|
|
INSERT INTO onisr_categorie_vehicule(id, name)
|
|
VALUES
|
|
(1, 'Vélo'),
|
|
(60, 'VAE');
|
|
|
|
CREATE TABLE IF NOT EXISTS onisr_obstacle_mobile (
|
|
id NUMERIC,
|
|
name VARCHAR(64) NOT NULL
|
|
);
|
|
|
|
INSERT INTO onisr_obstacle_mobile(id, name)
|
|
VALUES
|
|
(-1, 'Non renseigné'),
|
|
(0, 'Aucun'),
|
|
(1, 'Piéton'),
|
|
(2, 'Véhicule'),
|
|
(4, 'Véhicule sur rail'),
|
|
(5, 'Animal domestique'),
|
|
(6, 'Animal sauvage'),
|
|
(9, 'Autre');
|
|
SQL
|