Le portail francophone de la géomatique

bruhnild · Mon 29 April 2019 15:23

Bonjour,

J'aimerais savoir si vous connaissez un moyen de réaliser une classification d'après la méthode statistique Jenks ou dite des Seuils naturels dans postgresql.

Merci d'avance pour vos retours.

ppluvinet · Mon 29 April 2019 16:55

Bonjour,
Il vous faudra je pense regarder à écrire des fonctions de Kmeans avec R ou python. A moins qu'il existe désormais une extension spécifique?
J'avais, il y a au moins 10 ans, implémenté des fonctions de Médiane, et quantiles avec PL/R. Toutefois, je ne pense plus être la bonne ressource !!!
Bon courage,

tumasgiu · Mon 29 April 2019 17:15

son calcul étant itératif, çà n'est pas faisable en SQL pur,
sauf peut être en utilisant une cte recursive, et encore,
pas sur que cela change quelque chose ou alors, si c'est
possible, je doute que çà soit très efficace.

Donc comme Pascal, plutôt voir du coté pl/R, créer une fonction
qui prends un argument le nom d'une table et renvoie les données
classées. Il y a sans doute déjà des implémentations en R de la classif Jenks.

tumasgiu · Mon 29 April 2019 19:12

En fait, on peut peut être ruser :

si j'ai bien compris la classif de jenks est une classif k-mean
qui ne prends en compte qu'une dimension.

Donc, si vous avez postgis >= 2.3 d'installé, vous pouvez tricher :

Code:

SELECT 
    st_clusterkmeans(st_makepoint(_valeur_a_classer_, 0), 
                    _nombre_de_classe_) 
           OVER () 
           AS classe,
    _valeur_a_classer_
FROM 
     ma_table

Dernière modification par tumasgiu (Tue 30 April 2019 22:09)

bruhnild · Thu 09 May 2019 13:17

Merci pour vos réponses!
Je vais essayer la méthode des k-means.

Cordialement.

bruhnild · Thu 09 May 2019 13:48

Merci pour vos réponses!
Je vais essayer la méthode des k-means.

Cordialement.

bruhnild · Thu 09 May 2019 13:54

Bonjour,

J'ai fait un test sur mes données mais la fonction ne me ressort que 7 classes, alors qu'il devrait m'en ressortir 10. Est ce parce que je n'ai pas assez d'individus homogènes dans ma distribution pour former des palliers marquants entre les classes 6 et 9?:

Code:

WITH nb AS
(SELECT 
    st_clusterkmeans(st_makepoint(surf_total, 0), 
                    10) 
           OVER (ORDER BY surf_total DESC)  
           AS classe,
    surf_total
FROM 
     mcd.tm2_espace_activite
     ORDER BY classe)
     select count(classe)as nb_element, classe
     from nb
     group by classe
     order by classe

Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

bruhnild · Thu 09 May 2019 13:54

Bonjour,

J'ai fait un test sur mes données mais la fonction ne me ressort que 7 classes, alors qu'il devrait m'en ressortir 10. Est ce parce que je n'ai pas assez d'individus homogènes dans ma distribution pour former des palliers marquants entre les classes 6 et 9?:

Code:

WITH nb AS
(SELECT 
    st_clusterkmeans(st_makepoint(surf_total, 0), 
                    10) 
           OVER (ORDER BY surf_total DESC)  
           AS classe,
    surf_total
FROM 
     mcd.tm2_espace_activite
     ORDER BY classe)
     select count(classe)as nb_element, classe
     from nb
     group by classe
     order by classe

Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

bruhnild · Thu 09 May 2019 13:54

Bonjour,

J'ai fait un test sur mes données mais la fonction ne me ressort que 7 classes, alors qu'il devrait m'en ressortir 10. Est ce parce que je n'ai pas assez d'individus homogènes dans ma distribution pour former des palliers marquants entre les classes 6 et 9?:

Code:

WITH nb AS
(SELECT 
    st_clusterkmeans(st_makepoint(surf_total, 0), 
                    10) 
           OVER (ORDER BY surf_total DESC)  
           AS classe,
    surf_total
FROM 
     mcd.tm2_espace_activite
     ORDER BY classe)
     select count(classe)as nb_element, classe
     from nb
     group by classe
     order by classe

Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

tumasgiu · Thu 16 May 2019 11:56

Salut,

apparemment c'est un bug qui a été corrigé dans les versions 2.3.6 et 2.4.3

Dernière modification par tumasgiu (Thu 16 May 2019 11:58)

bruhnild · Tue 24 March 2020 14:42

Bonjour,

J'aimerais automatiser la répartition de mes données en classes d'après la méthode de jenks pour pouvoir ensuite les styler.
Pour ça je suis repartie de la fonction clusterkmeans :

Code:

SELECT 
    st_clusterkmeans(st_makepoint(confirmed, 0), 
                    6) OVER (ORDER BY confirmed DESC) AS classe, 
                    confirmed
FROM 
     sante.test
ORDER BY classe, confirmed

Mes données sont bien réparties dans les différentes classes, cependant celles ci ne sont pas ordonnées par grandeur.

Exemple :

- la classe 0 comprend les valeurs allant de 0 à 2621 (jusqu'ici pas de problème)
- la classe 1 comprend une seule valeur qui est 81116
- la classe 2 comprend les valeurs 35136, 43667
- la classe 3 comprend les valeurs 19874, 23049, 29056
- la classe 4 comprend une seule valeur qui est 63927
- la classe 5 comprend les valeurs 3743, 4474, 4764, 6711, 8795, 8961

J'aimerais obtenir cet ordre là :
- classe 0 = classe 0
- classe 1 = classe 5
- classe 2 = classe 3
- classe 3 = classe 2
- classe 4 = classe 4
- classe 5 = classe 1

Après vérification, cette fonction créé les groupes mais ne gère pas leur ordre, elle leur attribue un numéro de manière aléatoire.
J'aimerais les renuméroter dans un ordre numérique en les triant selon la colonne confirmed, est ce que vous voyez un moyen de faire ça en sql?

Merci d'avance pour votre retour!

Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

tumasgiu · Tue 24 March 2020 15:28

Salut,

avec des CTE et row_number, une autre window function,
pour renumeroter des lignes :

Code:

WITH jenks AS (
    SELECT 
        st_clusterkmeans(st_makepoint(confirmed, 0), 
                        6) OVER (ORDER BY confirmed DESC) AS classe, 
                        confirmed
    FROM 
         sante.test
    )
    
, classes AS (    
    SELECT 
        classe, 
        row_number() OVER (ORDER BY min) AS n_classe
    FROM --on ordonne les classes par leur valeur min
        ( SELECT 
            classe, min(confirmed) 
          FROM 
            jenks
          GROUP BY 
            classe) AS subreq
      )

SELECT
    confirmed, n_classe
FROM
    jenks
--natural joint fait une jointure sur toutes 
-- les colonnes de A & B ayant les mêmes noms.
-- ici : classe
NATURAL JOIN 
    classes
;

Peut être qu'il y a un moyen plus simple,cela dit.

sante.test, confirmed, c'est en lien avec le virus ?

Dernière modification par tumasgiu (Tue 24 March 2020 15:29)

bruhnild · Wed 25 March 2020 08:05

Je vous remercie pour cette solution toute prête et qui fonctionne, je vais m'empresser de mettre mes tables à jour.

Oui tout à fait, je travaille sur la réalisation de cartes à partir des données en open data du coronavirus. Ici il s'agit des données du politologue (données mondiales) : https://www.data.gouv.fr/fr/datasets/co … tidienne/.

La mise à jour des données étant quotidienne, le calcul des classes dans la table me permettra d'avoir toujours la bonne répartition des individus par classe, pour un style dynamique!

Bonne journée à vous.

bruhnild · Tue 07 April 2020 12:31

Bonjour!
C'est encore moi, cette fois-ci j'aimerais personnaliser la fonction de kmeans en appelant mon champs de valeurs à répartir ainsi que le nombre de classe que je souhaite obtenir.
J'ai commencé à écrire la fonction mais ça coince quand je l'appelle et je n'arrive pas à trouver plus d'infos sur l'erreur que me renvoie postgres (voir en pj).
Ce que je comprend c'est que ma window ne comprend qu'une seule ligne et que je lui envoie un paramètre (6) qui doit être inférieur à la taille de la window.

C'est étonnant car lorsque j'utilise la vraie fonction st_clusterkmeans avec ma table test et en mettant 6 en paramètre j'ai bien le résultat attendu :

Code:

SELECT 
    st_clusterkmeans(st_makepoint(hosp, 0), 
                    6) 
OVER (ORDER BY hosp DESC) AS classe,hosp
from test

Voir en pj le résultat.

Voici le code de ma fonction :

Code:

create or replace function classes_jenks 
(n integer, kclasse integer)
    returns integer
as $$
declare
    classe integer;
begin
 if n is not null then
        classe := st_clusterkmeans(st_makepoint(n, 0), 
                        kclasse) OVER (ORDER BY n DESC);
    end if;   
    --return classe;
END;
$$ LANGUAGE plpgsql;

Et voici comment elle est appelée :

Code:

select classes_jenks (hosp, 6), hosp
from test

Voilà ne sais pas trop quoi faire. Merci d'avance pour vos idées, si vous en avez!

Dernière modification par bruhnild (Tue 07 April 2020 12:31)

Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

tumasgiu · Tue 07 April 2020 12:54

Je ne crois pas que ce vous tentiez de faire, à savoir écrire une window function en pl/pgsql
soit possible avec PostgreSQL.

Pour faire ce que vous voulez vous devriez passer par une écriture dynamique
de la requête dans votre fonction, que vous appelleriez comme ceci :

Code:

SELECT
    *
FROM
    jenks('mon_schema', 'ma_table', nb_classe)
;

ThomasG · Tue 07 April 2020 13:05

Bonjour,

Personnellement, j'utilise les méthodes fournies dans le projet https://github.com/CartoDB/cartodb-postgresql
Il serait aussi possible de les implémenter en pl python en le combinant avec la bibliothèque Python PySAL.

Cordialement

Thomas

bruhnild · Tue 07 April 2020 14:14

Merci pour vos retours.
J'ai intégré la fonction CDB_JenksBins (https://github.com/CartoDB/cartodb-post … ksBins.sql) qui semble bien marcher, mais qui ne répond pas totalement à mon besoin car je voudrais que la valeurdu champ "classes" soit affecté à chaque valeur "hosp" dans ma table test. Il faut que je réalise une jointure pour récupérer la liste de toutes les valeurs hosp qui sont comprises dans l'intervalle de chaque classe de jenks.

Je vais continuer à investiguer.

Code:

SELECT ROW_NUMBER() OVER(
                         ORDER BY breaks ASC) classes,
                    breaks
FROM
    (SELECT UNNEST(CDB_JenksBins(ARRAY_AGG(hosp), 6)) breaks
     FROM test)b

Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

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 Mon 29 April 2019 15:23

Méthode de classification Jenks dans postgres

#2 Mon 29 April 2019 16:55

Re: Méthode de classification Jenks dans postgres

#3 Mon 29 April 2019 17:15

Re: Méthode de classification Jenks dans postgres

#4 Mon 29 April 2019 19:12

Re: Méthode de classification Jenks dans postgres

Code:

#5 Thu 09 May 2019 13:17

Re: Méthode de classification Jenks dans postgres

#6 Thu 09 May 2019 13:48

Re: Méthode de classification Jenks dans postgres

#7 Thu 09 May 2019 13:54

Re: Méthode de classification Jenks dans postgres

Code:

#8 Thu 09 May 2019 13:54

Re: Méthode de classification Jenks dans postgres

Code:

#9 Thu 09 May 2019 13:54

Re: Méthode de classification Jenks dans postgres

Code:

#10 Thu 16 May 2019 11:56

Re: Méthode de classification Jenks dans postgres

#11 Tue 24 March 2020 14:42

Re: Méthode de classification Jenks dans postgres

Code:

#12 Tue 24 March 2020 15:28

Re: Méthode de classification Jenks dans postgres

Code:

#13 Wed 25 March 2020 08:05

Re: Méthode de classification Jenks dans postgres

#14 Tue 07 April 2020 12:31

Re: Méthode de classification Jenks dans postgres

Code:

Code:

Code:

#15 Tue 07 April 2020 12:54

Re: Méthode de classification Jenks dans postgres

Code:

#16 Tue 07 April 2020 13:05

Re: Méthode de classification Jenks dans postgres

#17 Tue 07 April 2020 14:14

Re: Méthode de classification Jenks dans postgres

Code:

Pied de page des forums