Forums PostgreSQL.fr

Bonjour,

Etant toujours bloqué, je vais reformuler ma question. J'aimerai pouvoir executer une commande du type :

WITH
t1 AS (
SELECT * FROM t01 WHERE x = 'O'
),
t2 AS (
SELECT * FROM t02 WHERE x = 'N'
)
SELECT t1.id, t2.id FROM t1, t2, t3, t4
WHERE ST_INTERSECTS (t1.geom, t2.geom)
AND ST_INTERSECTS (t1.geom, t3. geom)
OR ST_INTERSECTS (t1.geom, t4.geom)

J'ai essayé avec des jointures mais cela ne marche pas non plus... Une bonne âme aurait elle une suggestion?
Merci d'avance.

Bonjour,

Après plusieurs jours de travail, j'ai suis enfin arrivé à mes fins (bien que tout cela soit un peu lourd... et malheureusement pas dynamique!), avec un petit emprunt aux expressions régulières (clin d'oeil à Marc Cousin qui m'a mis sur la bonne piste avec le PERL).

Tout d'abord, il faut retravailler la table de départ afin de remplacer les valeurs par des codes correspondant à des chaine de caractères de taille égale. Ensuite, il faut créer un attribut concaténant tous les champs visés par l'analyse :

CREATE TABLE position_type AS	
SELECT type05 ||' '|| type06 ||' '|| type07||' '|| type08 ||' '|| type09 ||' '|| type10 ||' '|| type11 ||' '|| type12 ||' '|| type13 ||' '|| type14 ||' '|| type15 ||' '|| type16 AS type_vec

On peut dès lors compter le nombre d’occurrences d'une chaine de caractères dans la chaine de caractères ainsi créée. Pour ce faire, on calcule la longueur de string totale avec CHAR_LENGTH, à laquelle on retranche la longueur de la chaine sans le string recherché (ici 'AF', remplacé par des 'blancs' : ''), ce qui nous donne la dimension de la (répétition de) chaine de caractère recherchée. Il reste alors à diviser la valeur obtenue par la longueur de la chaine de caractère recherchée pour avoir le nombre de répétitions :

ALTER TABLE position_type ADD COLUMN nbre_occurrence NUMERIC;
UPDATE position_type 
SET nbre_occurrence = (CHAR_LENGTH(type_vec) - CHAR_LENGTH(REPLACE(type_vec, 'AF', ''))) 
    / CHAR_LENGTH('AF');

Un fois le nombre d’occurrences obtenu, on peut alors définir le nombre d'attributs à créer (=nombre maximum d'occurences) ensuite pour le calcul des positions des strings recherchés dans la chaine de caractère totale.

SELECT nbre_occurrence FROM position_type GROUP BY nbre_occurrence

Pour ma part, j'ai 4 colonnes à créer :

ALTER TABLE position_type ADD COLUMN position_1 VARCHAR(40), ADD COLUMN position_2 VARCHAR(40), ADD COLUMN position_3 VARCHAR(40), ADD COLUMN position_4 VARCHAR(40);

Il faut ensuite calculer les positions des chaines de caractère grâce à la fonction POSITION. Celle-ci ne donne malheureusement que l'emplacement de la première occurrence, et non pas de toutes, ce qui nous oblige à répéter les instructions en boucle avec un CASE renvoyant sur la colonne précédemment remplie :

UPDATE position_type
SET 
position_1 = POSITION('AF' IN type_vec);
UPDATE position_type
SET 
position_2 = (CASE 
WHEN nbre_occurrence >= 2 THEN 2 + position_1::integer + POSITION('AF' IN substring (type_vec from 3 + position_1::integer))
ELSE 0
END);
UPDATE position_type
SET 
position_3 = (CASE 
WHEN nbre_occurrence >= 3 THEN 2 + position_2::integer + POSITION('AF' IN substring (type_vec from 3 + position_2::integer))
ELSE 0
END);
UPDATE position_type
SET 
position_4 = (CASE 
WHEN nbre_occurrence >= 4 THEN 2 + position_3::integer + POSITION('AF' IN substring (type_vec from 3 + position_3::integer))
ELSE 0
END);

Pour finir, on peut compter le nombre d'années (de champs séparant deux valeurs similaires pour le même enregistrement). J'ai ici écrit la fonction pour calculer directement une moyenne. Je ne détaille pas mais il faut bien prendre en compte les espaces dans le comptage des positions :

ALTER TABLE position_type ADD COLUMN jachere_moy NUMERIC;
UPDATE position_type
SET
jachere_moy = ROUND(
(CASE 
WHEN nbre_occurrence = 2 THEN
(((position_2::numeric - position_1::numeric)/3)-1)/(nbre_occurrence::numeric-1)
WHEN nbre_occurrence = 3 THEN
((((position_2::numeric - position_1::numeric)/3)-1) + (((position_3::numeric - position_2::numeric)/3)-1))/(nbre_occurrence::numeric-1)
WHEN nbre_occurrence = 4 THEN
((((position_2::numeric - position_1::numeric)/3)-1) + (((position_3::numeric - position_2::numeric)/3)-1) + (((position_4::numeric - position_3::numeric)/3)-1))/(nbre_occurrence::numeric-1)
ELSE 0
END)::numeric, 3)

On a donc la distance moyenne entre deux occurrences. Tout cela est très lourd, et surtout pas du tout dynamique (besoin de rajouter plusieurs lignes à chaque ajout d'un attribut dans la table de base).

@ Marc Cousin : Merci pour le script, mais je n'ai pas du tout compris et je ne suis pas en mesure d'apprendre un nouveau language de programmation... Bien qu'on m'aie conseillé PLPgSQL avec un travail sur les information_schema.COLUMNS pour rendre des colonnes dynamiques.
Cette expérience de script m'aura convaincu que PostGreSQL n'est pas fait pour faire de la statistique et je vais essayer de travailler en R ou Pl/R.

@ dverite : merci pour vos commentaires, personne ne m'avait encore faire cette remarque le modèle conceptuel de ma base. J'entends qu'un attribut ne doit pas porter en soi une information, mais qu'elle doit rester dans les valeurs. Si j'ai bien compris votre schéma, on a plusieurs fois le même id dans la table mesure, autant que d'années (avec leur type correspondant)? Quelle serait alors la différence entre le modèle que vous proposez et la table suivante, qui synthétise votre modèle en une seule table en dupliquant par la même occasion les géométries? Aurait-on une perte de performance? :

CREATE TABLE matrice_total_test AS
SELECT type05 AS type, '2005' AS annee, geom FROM matrice_test
UNION
SELECT type06 AS type, '2006' AS annee, geom FROM matrice_test
UNION
SELECT type07 AS type, '2007' AS annee, geom FROM matrice_test
UNION
SELECT type08 AS type, '2008' AS annee, geom FROM matrice_test
UNION
SELECT type09 AS type, '2009' AS annee, geom FROM matrice_test
UNION
SELECT type10 AS type, '2010' AS annee, geom FROM matrice_test
UNION
SELECT type11 AS type, '2011' AS annee, geom FROM matrice_test
UNION
SELECT type12 AS type, '2012' AS annee, geom FROM matrice_test
UNION
SELECT type13 AS type, '2013' AS annee, geom FROM matrice_test
UNION
SELECT type14 AS type, '2014' AS annee, geom FROM matrice_test
UNION
SELECT type15 AS type, '2015' AS annee, geom FROM matrice_test
UNION
SELECT type16 AS type, '2016' AS annee, geom FROM matrice_test
ORDER BY annee ASC geom DESC;
ALTER TABLE matrice_total_test ADD COLUMN id SERIAL PRIMARY KEY

Merci beaucoup pour vos contributions!

Je viens de découvrir l'utilisation des CTE (Common Table Expression) avec WITH (l'équivalent de votre syntaxe pour les requêtes imbriquées) qui rendent la construction de requêtes beaucoup plus aisées. Je vais persévérer encore un peu dans le postgreSQL...

https://www.postgresql.org/docs/9.1/sta … -with.html
http://www.portailsig.org/content/postg … recursives
http://www.craigkerstiens.com/2013/11/1 … not-using/

@ dverite : Par curiosité, je suis allé voir sur votre blog, et votre dernier post traite exactement de ce dont je veux parler! Merci beaucoup pour cette documentation sur les pivots dynamiques, c'est bien mieux que CROSSTAB!

Problème résolu!
Il fallait tout simplement supprimer les espaces (ce qui est bizarre car dans les requêtes sans CROSSTAB fonctionnent avec les espaces) comme suit (et aussi pour précision, parce que j'avais pas compris au début, il faut bien doubler les guillemets simple et non pas mettre des doubles guillemets) :

SELECT * FROM CROSSTAB(
'SELECT nomzone, annee, SUM(ST_AREA(geom) FROM parcellaire_total_test WHERE type=''abattis frais'' GROUP BY nomzone, annee ORDER BY 1,2',
'SELECT annee FROM parcellaire_total_test WHERE type=''abattis frais'' GROUP BY annee ORDER BY annee'
)
AS ct("nomzone" varchar, "2006" varchar, "2007" varchar, "2008" varchar, "2009" varchar, "2010" varchar, "2011" varchar, "2012" varchar, "2013" varchar, "2014" varchar, "2015" varchar, "2016" varchar)

Par contre je ne comprends toujours pas l'utilité de "doubler la requête" par :

'SELECT annee FROM parcellaire_total_test WHERE type=''abattis frais'' GROUP BY annee ORDER BY annee'

Si je l'enlève, j'obtiens une erreur de type :

ERROR:  return and sql tuple descriptions are incompatible
SQL state: 42601

Mais je ne vois pas à quoi cela correspond.

Merci pour votre prompte réponse. Je comprends mieux pourquoi la commande ne marche pas, mais dans ce cas comment insérer une colonne dans une table à partir d'une jointure sur cette propre table?
J'ai fait des essais et il est possible de faire un CREATE TABLE satisfaisant avec le SELECT tel qu'il est écrit, c'est à dire créer une copie de la table 'departements' avec les résultats de la jointure et des données de base, mais comment éditer une table existante? C'est un peu fastidieux de devoir recréer une table à chaque fois puis de supprimer l'ancienne... Ce problème se pose d'ailleurs pour associer des clés primaires et étrangères sans passer par de la saisie manuelle.
Cordialement

Bonjour,

Cette fonction est magique! Moi qui pensait qu'il fallait passer par un montage complexe avec une séquence et une sorte de boucle... On découvre toujours de nouvelles fonctions!
Merci beaucoup.

Bonjour,

Je suis allé voir du côté de d'EXPOSE et c'était une bonne piste. Il y a effectivement là des commandes qui permettent de lister/d'assigner des ports au lancement d'un docker
https://stackoverflow.com/questions/221 … -in-docker
https://forums.docker.com/t/how-to-expo … ner/3252/5

Du coup j'ai fait un :
docker stop container
Puis un :
docker run -d -p port
Pour relancer le container avec le bon port. Et ça a marché.
Merci beaucoup pour les conseils, je pense que seul j'y aurai passé quelques jours de plus...
@dverite : le port indiqué par le netstat taupen. Mais effectivement c'est bien le port du DNS, j'ai confondu avec ceux du ps -a, qui n'étaient pas affichés.
Mais le service postgres était bien lancé, le docker ps était formel (et j'ai vérifié dans les logs, aucun problème). C'était donc bien un problème de routage.

Encore merci!

Effectivement ça marche mieux en précisant /0 (j'imagine que c'est pour accepter tout type de masques CIDR). Mais bon comme je le pressentais dans mon post précédent, un nmap m'a bien renseigné que mon port était fermé, et j'essaie désormais de le paramétrer avec iptable. Donc j'imagine que ça n'a plus grand chose à voir avec postgresql...
Merci pour votre aide!