Le portail francophone de la géomatique

lili55 · Thu 22 June 2023 14:06

Bonjour,

cela fait quelques jours que je cherche une solution à une requête sql

l'objectif est d'avoir une vue avec cette structuration :
vue_evolution_demographique
id | geom (centroïde dans le polygone) | nom_commune | code_insee | annee_dernier_recensement | population_dernier_recensement | annee_avant_dernier_recensement | population_avant_dernier_recensement | evolution_population

La requête sql est ok sauf pour la dernière colonne "evolution_population" elle reste NULL ... alors que j'aimerai qu'elle indique l'évolution démographique entre les 2 derniers recensements.

Voici la structuration des tables nécessaire pour réaliser la vue :
limite_commune :
id | geom | nom_commune |code_insee

recensement_population:
id | nom_commune | code_insee | population | annee

et voici la requête sql :

SELECT * from
(select row_number() OVER () AS id,
limite_commune .code_insee,
limite_commune .nom,
st_pointonsurface(limite_commune .the_geom)::geometry(Point,3948) AS the_geom
from public.limite_commune ) l1
LEFT JOIN (
select
recensement_population.code_insee,
recensement_population.annee as annee_dernier_recensement,
recensement_population.population as population_dernier_recensement
from donnees_metiers.recensement_population
where annee = (select max(annee) from donnees_metiers.recensement_population)) l2
USING (code_insee)
LEFT JOIN (
select
recensement_population.code_insee,
recensement_population.annee as annee_avant_dernier_recensement,
recensement_population.population as population_avant_dernier_recensement
from donnees_metiers.recensement_population
where annee = (select max(annee)-1 from donnees_metiers.recensement_population)) l3
USING (code_insee)
LEFT JOIN (
select
recensement_population.code_insee,
recensement_population.population as evolution_population
from donnees_metiers.recensement_population
where annee = ((select max(annee) from donnees_metiers.recensement_population)
-
(select (max(annee)-1) from donnees_metiers.recensement_population)
)) l4
using (code_insee)

je pèche sur la partie en gras
Si vous pouviez m'aiguiller pour la dernière étape :-)

merci d'avance

Dernière modification par lili55 (Thu 22 June 2023 14:43)

tumasgiu · Thu 22 June 2023 16:49

Bonjour,

si vous avez des valeurs nulles pour evolution_population, c'est qu'une de vos outer join ne renvoient pas de lignes.

Je pense que c'est votre condition qui coince :

* Déjà il faut s'assurer que vos recenscements sont "continus" (il n'y pas de trou dans les années de recensenement)

* Ensuite votre condition doit satisfaire l'equation annee = (max(annee) - max(annee) -1), mettons que annee = 2000, max(annee) = 1999 et
max(annee) -1 = 1998,
ca donne 2000 = 1999 -1998 -> 2000 = 1 . Je doute que cela soit ce que vous cherchez.

* Enfin vos sous-requêtes pour selectionner max(annee) et max(annee) - 1 ne prenne pas en compte le code_insee, ce qui peut vous donner de faux résultats

Ci-dessous une ebauche d'une autre façon de resoudre le problème à base de window functions :

Code:

SELECT 
    *
FROM
    (
    SELECT
        r.code_insee,
        first_value(annee)         over (w) annee,
        first_value(population) over (w) pop,
        lead(population, 1)     over (w) pop_moins_1,
        lead(annee, 1)             over (w) annee_moins_1,
        lead(population, 2)     over (w) pop_moins_2,
        lead(annee, 2)             over (w) annee_moins_2
    FROM 
        recensement_population r
    WINDOW w as 
        (PARTITION BY code_insee ORDER BY annee DESC)
    ) as win
NATURAL JOIN (
    SELECT 
        code_insee,
        max(annee) annee
    FROM 
        recensement_population 
    GROUP BY 
        code_insee
) as max

Dernière modification par tumasgiu (Fri 23 June 2023 09:50)

lili55 · Fri 23 June 2023 10:03

merci pour votre proposition, mais cela ne fonctionne pas :-| (colonnes ont la valeur NULL)

tweaxy · Thu 29 June 2023 11:59

Bonjour,

En passant par une requête WITH, on peut générer cela :

WITH commune AS
(
SELECT id, code_insee, nom_commune, st_centroid(geom) as geom
FROM public.limite_commune
),
pop_n AS
(
SELECT id, code_insee, nom_commune, annee, population
FROM donnees_metiers.recensement_population
WHERE annee = 2023
),
pop_n_1 AS
(
SELECT id, code_insee, nom_commune, annee, population
FROM donnees_metiers.recensement_population
WHERE annee = 2022
)

SELECT commune.id,
commune.code_insee,
commune.nom_commune,
pop_n.annee as annee_n,
pop_n.population as pop_n,
pop_n_1.annee as annee_n_1,
pop_n_1.population as pop_n_1,
pop_n.population - pop_n_1.population as evolution_demographique
FROM commune
LEFT JOIN pop_n ON commune.code_insee = pop_n.code_insee
LEFT JOIN pop_n_1 ON commune.code_insee = pop_n_1.code_insee
ORDER BY commune.nom_com;

Cela devrait fonctionner.
Il ne reste plus qu'à incorporer cela dans un CREATE OR REPLACE VIEW.

Néanmoins, si c'est une donnée qui sera souvent sollicitée dans des applications SIG par exemple, je vous invite à passer par une vue matérialisée, les données étant mises à jour annuellement. Vous gagnerez en performance pour les utilisateurs.

Cordialement,
Léandre BERON

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

Annonce

#1 Thu 22 June 2023 14:06

Postgis : soustraire 2 colonnes nouvelles dans une 3 colonne LEFT JOIN

#2 Thu 22 June 2023 16:49

Re: Postgis : soustraire 2 colonnes nouvelles dans une 3 colonne LEFT JOIN

Code:

#3 Fri 23 June 2023 10:03

Re: Postgis : soustraire 2 colonnes nouvelles dans une 3 colonne LEFT JOIN

#4 Thu 29 June 2023 11:59

Re: Postgis : soustraire 2 colonnes nouvelles dans une 3 colonne LEFT JOIN

Pied de page des forums