22 Jun
2023
22 Jun
'23
2:06 p.m.
Message posté par : lili55 (leblanc.ingrid(a)gmail.com)
----------------------------------------
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_reference_limite_communale.planche,
limite_reference_limite_communale.nom,
st_pointonsurface(limite_reference_limite_communale.the_geom)::geometry(Point,3948) AS
the_geom
from public.limite_reference_limite_communale) l1
LEFT JOIN (
select
recensement_population.planche,
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 (planche)
LEFT JOIN (
select
recensement_population.planche,
recensement_population.annee as annee_avant_dernier_recensement,
recensement_population.population as population_avant_dernier_recensement
--recensement_population.population as evolution_population
from donnees_metiers.recensement_population
where annee = (select max(annee) from donnees_metiers.recensement_population)) l3
USING (planche)
LEFT JOIN (
select
recensement_population.planche,
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 (planche)
Si vous pouviez m'aiguiller pour la dernière étape :-)
merci d'avance
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=361294#p361294
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net
22 Jun
22 Jun
4:49 p.m.
Message posté par : T. Rossini
----------------------------------------
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 window
functions :
-----------------
Code :
SELECT
*
(
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,
most(annee) annee,
FROM
recensement_population
GROUP BY
code_insee
) as max
-----------------
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=361298#p361298
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net
29 Jun
29 Jun
11:59 a.m.
Message posté par : Léandre Béron
----------------------------------------
Bonjour,
En passant par une requête WITH, on peut générer cela :
-----------------
Citation :
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
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=361442#p361442
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net
23 Jun
23 Jun
10:03 a.m.
Message posté par : lili55
----------------------------------------
merci pour votre proposition, mais cela ne fonctionne pas :-| (colonnes ont la valeur
NULL)
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=361305#p361305
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net
834
days inactive
841
days old
3 comments
1 participants
participants (1)
-
geobd＠georezo.net