Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Étapes des traitements de données réalisés pour le billet de blogue d'analyse des données de taxe de la ville de Montréal: https://dataholic.ca/2022/05/30/analyse-fonciere-Montreal. Il s'agit là des principales étapes, certaines étapes comme les créations d'index ou certaines assignations simples peuvent manquer.
/*
Etape préparatoire: charger les données
Pour les fichiers shapefile, on utilise shp2pgsql
shp2pgsql -s 2950 geobase_mtl.shp geobase | psql -U user -d db_name
shp2pgsql -s 3857 demo2016_ad_mtl_island.shp stats_socio | psql -U user -d db_name
Pour la valeur d'évaluation foncière venant de CSV, il faut créer la table et les importer fichier par fichier:
*/
CREATE TABLE imposition
(
arrondissement integer,
nom_arrondissement text,
annee_exercice integer,
id_cum bigint,
no_compte text,
ad_emplac_civ1 text,
ad_emplac_civ2 text ,
ad_emplac_gener text,
ad_emplac_rue text,
ad_emplac_orient text,
ad_emplac_suite1 text,
ad_emplac_suite2 text,
code_descr_longue character varying(3),
descr_longue text,
val_imposable real,
taux_imposi real,
montant_detail real
);
COPY imposition FROM '/mon/chemin/taxes-municipales-villeray-saint-michel-parc-extension.csv' DELIMITER ',' CSV HEADER;
-- Étape 1. Regroupement / sommes unité d'évaluation foncière
create table total_taxe_par_uev
as
(
select id_cum,
sum(montant_detail) as impot_tot,
(array_agg(val_imposable))[1] as val_imposable,
array_agg(no_compte) as comptes,
array_agg(descr_longue) as liste_taxes
from imposition
where annee_exercice = 2021
group by id_cum
)
-- Étape 2. Jointure données spatiales des unités d'évaluation avec le total de taxe par unité d'évaluation (étape précédente)
On crée des index au besoin
CREATE INDEX total_taxe_par_uev_id_cum_idx
ON total_taxe_par_uev (id_cum);
CREATE INDEX unites_eval_id_uev_idx
ON unites_eval (id_uev);
create table unite_eval_avec_taxes as
(
select
unites_eval.gid,
unites_eval.id_uev::bigint,
total_taxe_par_uev.impot_tot, total_taxe_par_uev.val_imposable,
(total_taxe_par_uev.comptes)[1] as no_compte,
unites_eval.civique_de, unites_eval.civique_fi,
unites_eval.nom_rue, unites_eval.suite_debu,
unites_eval.nombre_log, unites_eval.annee_cons,
unites_eval.code_utili, unites_eval.libelle_ut, unites_eval.categorie_,
unites_eval.superficie, unites_eval.superfic_1,
unites_eval.no_arrond_,
total_taxe_par_uev.liste_taxes,
unites_eval.geom
from unites_eval
FULL JOIN total_taxe_par_uev
ON unites_eval.id_uev::bigint = total_taxe_par_uev.id_cum
)
-- Étape 3. Regroupement des données d'évaluation foncière par géométries
-- (Les condos par exemple forment plusieurs unités avec la même géométrie)
create table taxe_total_par_geom
as
(
select
row_number() OVER () as gid,
(array_agg(civique_de))[1] as numero_rue,
(array_agg(civique_fi))[1] as numero_rue2,
(array_agg(nom_rue))[1] as nom_rue,
(array_agg(no_arrond_))[1] as arrondissement,
sum(nombre_log) as nb_logement,
sum(impot_tot) as impot_tot,
sum(val_imposable) as val_imposable,
(array_agg(id_uev)) as uevs,
geom
from unite_eval_avec_taxes
group by geom
)
-- Étape 3.bis Ajout de l'impot purement résidentiel au résultat précédent
with tmp as (
select
sum(impot_tot) as impot_residentiel,
geom
from unite_eval_avec_taxes
where substring(code_utili,1,1) = '1'
group by geom
)
update taxe_total_par_geom
set impot_residentiel = tmp.impot_residentiel
from tmp
where taxe_total_par_geom.geom = tmp.geom
-- Étape 4. Assignation des sommes des taxes par aire de dissémination
WITH taxe_dist_area AS
(
SELECT
dist_area.adidu as adidu,
sum(impot_residentiel) as total_residentiel
FROM dist_area, taxe_total_par_geom
where st_contains(dist_area.geom_2950, st_centroid(taxe_total_par_geom.geom))
group by dist_area.adidu
)
UPDATE dist_area
set total_taxe_residentiel = taxe_dist_area.total_residentiel
from taxe_dist_area
where dist_area.adidu = taxe_dist_area.adidu
-- Étape 4.bis Calcul des ratios taxe/km
update dist_area
set ratio_residentiel_km = total_taxe_residentiel / km_rue
update dist_area
set ratio_taxe_km = total_taxe / km_rue
-- Étape 5. Ajout du nombre de kilomètres de rue par aire de dissémination
-- (Il faut resegmenter la geobase selon les polygons pour assigner la longueur de rue aux polygones le plus justement possible)
WITH geobase_segmentee AS
(
SELECT
dist_area.adidu as adidu,
sum(st_length(st_intersection(ST_transform(dist_area.geom, 2950), geobase.geom))) as longueur
FROM dist_area, geobase
where st_intersects(ST_transform(dist_area.geom, 2950),geobase.geom)
group by dist_area.adidu
)
UPDATE dist_area
set km_rue = geobase_segmentee.longueur
from geobase_segmentee
where dist_area.adidu = geobase_segmentee.adidu
-- Étape 6. Récupérer le nombre de logement par DA
WITH taxe_dist_area AS
(
SELECT
dist_area.adidu as adidu,
sum(unite_eval_avec_taxes.nombre_log) as nb_logement
FROM dist_area, unite_eval_avec_taxes
where st_contains(dist_area.geom_2950, st_centroid(unite_eval_avec_taxes.geom))
and substring(unite_eval_avec_taxes.code_utili,1,1) = '1'
group by dist_area.adidu
)
UPDATE dist_area
set nb_logement = taxe_dist_area.nb_logement
from taxe_dist_area
where dist_area.adidu = taxe_dist_area.adidu
-- Étape 7 Calculs additionnels:
-- Ajouter la surface habitable (en m2) pour chaque DA
WITH taxe_dist_area AS
(
with tmp as (
select
geom
from unite_eval_avec_taxes
where substring(code_utili,1,1) = '1'
group by geom
)
SELECT
dist_area.adidu as adidu,
sum(st_area(tmp.geom)) as surface
FROM dist_area, tmp
where st_contains(dist_area.geom_2950, st_centroid(tmp.geom))
group by dist_area.adidu
)
UPDATE dist_area
set surface_habitale = taxe_dist_area.surface
from taxe_dist_area
where dist_area.adidu = taxe_dist_area.adidu
-- définir la surface au sol moyenne par DA
UPDATE dist_area
set superficie_au_sol_logement = surface_habitale / nb_logement
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment