Le portail francophone de la géomatique

csv2 · Mon 23 February 2015 11:53

Bonjour
Dans un sujet consacré à la création d'index, Lsam avait donné une technique particulièrement efficace via sqlite/spatialite :

-après création d'une grille composée de "numero_carré", via le code

Code:

select "rues"."nom", group_concat("grille"."numero_carré")
from "rues" join "grille"
on intersects("rues"."geometry", "grille"."geometry")
group by 1

Je cherche à obtenir le même résultat mais dans postgresql (via pgadmin par exemple), du fait que mes bases sont dans postgresql/postgis.
Or la fonction group_concat semble ne pas exister en pgsql, et les autres fonctionner de manière différente.
Etant novice en la matière, j'ai beaucoup de difficultés à retrouver les fonctions qui me permettent de parvenir au même résultat.

Savez-vous si cela est possible ? Et si oui, avez-vous des pistes à me proposer ?

Merci beaucoup !
Cyril

MathieuB · Mon 23 February 2015 16:26

Bonjour et bienvenue sur Georezo.

La liste des fonctions d’agrégation de PostgreSQL est décrite ici :

http://www.postgresql.org/docs/9.3/stat … egate.html

string_agg(expression, delimiter) devrait répondre à votre besoin.

csv2 · Mon 23 February 2015 16:41

Bonjour
et merci de votre réponse.
J'ai cherché du côté de string_agg, qui paraissait aller dans le bon sens.
Mais la logique étant différente et - me semble-t-il - plus complexe que celle de spatialite, je reste comme une poule devant un couteau. Il faut dire que je suis graphiste et analyste SIG, et que les BDD sont une terre nouvelle pour ma petite tête...
Je vais essayer de creuser... Si jamais une bonne âme a le temps et l'énergie de me donner une piste à suivre, ce serait très apprécié !
Merci encore, pour ce forum et l'aide apporté à la communauté !
Cyril

MathieuB · Tue 24 February 2015 09:24

Bonjour,

les deux fonctions sont les mêmes, sauf que group_concat ne nécessite pas de deuxième paramètres (le séparateur), qui est optionnel.

Ceci devrait fonctionner (notez que l'opératuer d'intersection est st_intersects ) :

Code:

select "rues"."nom", string_agg("grille"."numero_carré",',')
from "rues" join "grille"
on st_intersects("rues"."geometry", "grille"."geometry")
group by 1

string_agg("grille"."numero_carré",',') regroupera les valeurs de numero_carré de chaque nom de rue et les séparera par une virgule.

csv2 · Tue 24 February 2015 17:51

Ca alors ! Je tentais des opérations nettement plus complexes !
Il est vrai que le séparateur me posait problème ; mais je ne pensais pas possible de séparer table et colonne par un point dans postgis ! Du coup je cherchais à utiliser le FROM, etc.
Je vais tester votre solution !
Merci beaucoup !
Cyril

csv2 · Tue 24 February 2015 17:59

Je viens de tester : problème, je ne vois pas ce qui remplace la colonne geometry dans postgis !
Pour l'un j'ai bien une colonne wkb_geometry, mais rien pour l'autre !
Je vais creuser !

Dof · Wed 25 February 2015 09:23

Bonjour,
Je ne suis pas sur de comprendre le dernier message.
Pour ajouter la géométrie, il faut le mettre dans le select :

Code:

select "rues"."nom", string_agg("grille"."numero_carré",',') , wkb_geometry
from "rues" join "grille"
on st_intersects("rues"."geometry", "grille"."geometry")
group by 1

Edit : Vous pouvez aussi concaténer grâce au double pipe :

Code:

SELCT"rues"."nom", grille."numero_carré" || ',' as le_nouveau_nom, wkb_geometry
FROM"rues" 
JOIN "grille" ON st_intersects("rues"."geometry", "grille"."geometry")

Dernière modification par Dof (Wed 25 February 2015 09:29)

csv2 · Wed 25 February 2015 11:07

Bonjour
Et merci !
Ce forum est vraiment une perle !
En fait, les geometry qui me manquaient étaient celles qui suivaient la fonction st_intersects ; mais je les ai trouvé, pour chaque table.
Par contre, je vais tenter votre seconde solution, parce que, problème (encore !) : l'éditeur pgsql pgadmin3 ne reconnait pas la fonction string_agg ! Je ne comprends pas ! J'ai pourtant la version 9.3 de postgresql ! Mais il me donne le message "la fonction string_agg(integer, unknown) n'existe pas".
Merci de votre aide à tous deux !
Cyril

csv2 · Wed 25 February 2015 11:26

Suite : incompréhensible ! J'ai du éviter le string_agg, et passer par

Code:

select lines.name,
    array_to_string(ARRAY_AGG(test_carroyage.id), ';') As index
from "lines" 
    join "test_carroyage"
on st_intersects ("lines"."wkb_geometry", "test_carroyage"."geom")
group by 1

Pourtant ça c'est du pgsql 8, non ?
Bizarre bizarre...

Dof · Wed 25 February 2015 11:56

Les fonctions postgresql 8 fonctionnent sur une version 9 (pour la grande majorité tout du moins).
Je pense que votre problème vient du typage des données. La fonction string_agg, comme l'indique son nom, prend comme paramètres des chaines de caractères.
Vous devez donc convertir le 1er paramètre en string (vu le message, ça doit être un entier)
Pour cela : on peut faire

test_carroyage.id::TEXT

Mais je viens de vérifier, il n'y a pas besoin de caster quand on utilise les || pour concaténer.

La conversion en array puis, en string est un peu tordu.

Si j'étais à votre place, je ferais au plus simple et au plus compréhensible :

Code:

SELECT lines.name,
    (test_carroyage.id || ';') As index
FROM"lines" 
JOIN "test_carroyage" ON st_intersects ("lines"."wkb_geometry", "test_carroyage"."geom")
GROUP BY 1

Par contre je ne comprends pas le GROUP BY 1

Dernière modification par Dof (Wed 25 February 2015 11:57)

Nicolas Ribot · Wed 25 February 2015 12:31

Bonjour,

GROUP BY (comme ORDER BY) peut prendre des expressions ou des numéros de positions d'expressions:

"GROUP BY nom, id " peut ainsi etre écrit "GROUP BY 1, 2 " si nom et id sont les deux premieres colonnes du select.

Ca permet des raccourcis quand les expressions sont longues, et c'est source de belles erreurs dans les requetes complexes qd on modifie une expression du select.

Dans le cas de Cyril, le string_agg (ou autre fonction d'agrégat) est obligatoire car il veut grouper des identifiants par nom: la concaténation ne marche pas ici.
La version la plus simple est bien:

Code:

SELECT lines.name, string_agg(test_carroyage.id::text, ';') As index
FROM "lines" 
JOIN "test_carroyage" ON st_intersects ("lines"."wkb_geometry", "test_carroyage"."geom")
GROUP BY 1

Cyril, notez que Postgresql est le contraire d'incompréhensible

1. La fonction string_agg est décrite comme prenant un texte ou bytea en parametre:

Code:

db=# \df string_agg*
                                           Liste des fonctions
   Schéma   |        Nom         | Type de données du résultat | Type de données des paramètres |  Type
------------+--------------------+-----------------------------+--------------------------------+--------
 pg_catalog | string_agg         | bytea                       | bytea, bytea                   | agg
 pg_catalog | string_agg         | text                        | text, text                     | agg
 pg_catalog | string_agg_finalfn | text                        | internal                       | normal
 pg_catalog | string_agg_transfn | internal                    | internal, text, text           | normal

2. Le message de PG lors de l'utilisation dit exactement ca: la fonction string_agg(integer, unknown) n'existe pas:

L'avantage de ce message est qu'il indique la façon dont PG a utilisé la fonction string_agg: il l'a utilisée avec un integer et ne la connait pas avec ce type de parametre.

Nicolas

csv2 · Wed 25 February 2015 13:33

Aie, je suis repéré !
C'est en effet incompréhensible POUR MOI ! Mais il faut le dire, suite à vos explications très claires (dont je vous remercie vivement), ça devient limpide, à ma grande honte ! (Bon, enfin, limpide... me semble nécessaire de faire des progrès...)
En effet la concaténation ne marchait pas, j'ai essayé. Me reste donc à tester cette transformation en texte !
Je dois avouer que c'est passionnant, à partir du moment où de bonnes âmes se penchent sur le berceau du rigolo qui cherche à babiller !
Merci, merci !
Cyril

Nicolas Ribot · Wed 25 February 2015 14:09

csv2 a écrit:

Aie, je suis repéré !
C'est en effet incompréhensible POUR MOI ! Mais il faut le dire, suite à vos explications très claires (dont je vous remercie vivement), ça devient limpide, à ma grande honte ! (Bon, enfin, limpide... me semble nécessaire de faire des progrès...)
En effet la concaténation ne marchait pas, j'ai essayé. Me reste donc à tester cette transformation en texte !
Je dois avouer que c'est passionnant, à partir du moment où de bonnes âmes se penchent sur le berceau du rigolo qui cherche à babiller !
Merci, merci !
Cyril

(ouais, moi je fais le malin parce que ca fait 16 ans que je tripatouille des bd spatiales et ca commence juste a rentrer... )

csv2 · Wed 25 February 2015 14:20

Dur... Je risque de sucrer les fraises avant d'y comprendre un minimum...

csv2 · Wed 25 February 2015 16:00

J'ai testé :

Code:

SELECT lines.name, string_agg(test_carroyage.id::text, ';') As index
FROM "lines" 
JOIN "test_carroyage" ON st_intersects ("lines"."wkb_geometry", "test_carroyage"."geom")
GROUP BY 1

Ca marche comme un seul homme.
C'est presque magique (non, je sais, juste logique !)

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 23 February 2015 11:53

Equivalent de group_concat() dans PostGis

Code:

#2 Mon 23 February 2015 16:26

Re: Equivalent de group_concat() dans PostGis

#3 Mon 23 February 2015 16:41

Re: Equivalent de group_concat() dans PostGis

#4 Tue 24 February 2015 09:24

Re: Equivalent de group_concat() dans PostGis

Code:

#5 Tue 24 February 2015 17:51

Re: Equivalent de group_concat() dans PostGis

#6 Tue 24 February 2015 17:59

Re: Equivalent de group_concat() dans PostGis

#7 Wed 25 February 2015 09:23

Re: Equivalent de group_concat() dans PostGis

Code:

Code:

#8 Wed 25 February 2015 11:07

Re: Equivalent de group_concat() dans PostGis

#9 Wed 25 February 2015 11:26

Re: Equivalent de group_concat() dans PostGis

Code:

#10 Wed 25 February 2015 11:56

Re: Equivalent de group_concat() dans PostGis

Code:

#11 Wed 25 February 2015 12:31

Re: Equivalent de group_concat() dans PostGis

Code:

Code:

#12 Wed 25 February 2015 13:33

Re: Equivalent de group_concat() dans PostGis

#13 Wed 25 February 2015 14:09

Re: Equivalent de group_concat() dans PostGis

#14 Wed 25 February 2015 14:20

Re: Equivalent de group_concat() dans PostGis

#15 Wed 25 February 2015 16:00

Re: Equivalent de group_concat() dans PostGis

Code:

Pied de page des forums