É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.

Analyse-taxe-Montreal.sql

/*

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