Annonce
Pour sa 21ème année, l’association GeoRezo a toujours besoin de vous !
10€ = 1 mois de frais bancaires ; 15€ = 12 mois de nom de domaine ; 30€ = 1 semaine de location des serveurs …
Retrouver nos membres bienfaiteurs
Pages: 1
- Sujet précédent - Postgis : soustraire 2 colonnes nouvelles dans une 3 colonne LEFT JOIN - Sujet suivant
#1 Thu 22 June 2023 14:06
- lili55
- Juste Inscrit !
- Date d'inscription: 30 Jul 2019
- Messages: 2
Postgis : soustraire 2 colonnes nouvelles dans une 3 colonne LEFT JOIN
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)
Hors ligne
#2 Thu 22 June 2023 16:49
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1166
Re: Postgis : soustraire 2 colonnes nouvelles dans une 3 colonne LEFT JOIN
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 maxDernière modification par tumasgiu (Fri 23 June 2023 09:50)
Hors ligne
#3 Fri 23 June 2023 10:03
- lili55
- Juste Inscrit !
- Date d'inscription: 30 Jul 2019
- Messages: 2
Re: Postgis : soustraire 2 colonnes nouvelles dans une 3 colonne LEFT JOIN
merci pour votre proposition, mais cela ne fonctionne pas :-| (colonnes ont la valeur NULL)
Hors ligne
#4 Thu 29 June 2023 11:59
- tweaxy
- Participant actif
- Lieu: Abbeville
- Date d'inscription: 27 Dec 2018
- Messages: 76
Re: Postgis : soustraire 2 colonnes nouvelles dans une 3 colonne LEFT JOIN
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
Hors ligne
Pages: 1
- Sujet précédent - Postgis : soustraire 2 colonnes nouvelles dans une 3 colonne LEFT JOIN - Sujet suivant
