Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site. Si vous continuez à utiliser ce dernier, nous considèrerons que vous acceptez l'utilisation des cookies. J'ai compris ! ou En savoir plus !.
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

Annonce

Le portail retrouve peu à peu ses fonctionnalités.

Les listes ne fonctionnent pas mais nous mettons tout en oeuvre pour les réactiver rapidement.

Si vous avez pris contact avec l'association (petite enveloppe en haut/droite), sachez que nous lisons vos messages mais que les mails ne partent pas (#bug).

Ne soyez pas surpris si vous ne recevez pas de réponse.

#1 Fri 19 April 2019 17:29

Ericge
Membre
Date d'inscription: 23 Oct 2018
Messages: 18

PostGis : fonction St_makeline

Bonjour à tous,

Je débute dans la construction de requêtes avec la fonction St_MAkeline. J'arrive à peine à créer des lignes simples à partir de deux points.

Les choses se compliquent : J'ai une table de points à partir de laquelle j'aimerai par requête sur le DB manager, créer des lignes en opérant des groupement et en observant un sens :

     Ma structure de table :

             id ; time ; Gps_Lat  ; Gps_Long


     Les points doivent être regroupés par ID et dans l'ordre de TIME

Si quelqu'un peut m'aider ? Merci

Hors ligne

 

#2 Wed 24 April 2019 15:21

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 867

Re: PostGis : fonction St_makeline

Salut,

st_makeline possede une definition acceptant
un ensemble de geometry (set).
C'est ce qu'on appelle une fonction d'agrégat.
Vous avez sans doute déja utilisé des fonctions de ce genre,
comme SUM, ou COUNT, qui s'utilise
dans une requête comportant une clause GROUP BY.

Avec Postgresql, vous pouvez choisir d'ordonner
les sous-ensembles produits par votre clause GROUP BY,
avant que ceux-ci soient utilisés dans votre fonction d'agrégat.
On utilise pour çà la clause ORDER BY à la suite de l'argument
donné à la fonction.

Dans votre cas cela donne :

Code:

SELECT 
    st_makeline(st_makepoint(gps_long, gps_lat) 
        ORDER BY time) AS line
FROM 
    ma_table
GROUP BY
    id
;

Dernière modification par tumasgiu (Wed 24 April 2019 19:43)

Hors ligne

 

#3 Fri 26 April 2019 09:46

Ericge
Membre
Date d'inscription: 23 Oct 2018
Messages: 18

Re: PostGis : fonction St_makeline

Super, ça fonctionne bien.

  Mais mon problème est devenu plus complexe :

    - J'ai une table avec une série de points GPS relevés, qui déterminent la trace de trajets.
    - J'ai une seconde table avec d'autres points qui déterminent des tronçons avec seulement un point pour le début du tronçon et un point pour la fin.
   
   Je cherche à cartographier ces tronçons de façon plus précise que la simple origine-destination.


    - J'ai crée une troisième table dans laquelle j'importe les points début+fin de tronçon et tous les points trace. Classés dans l'ordre chronologique.

    - Maintenant je bloque : comment représenter cartographiquement mes tronçons, en prenant en compte uniquement les points trace entre chaque début de tronçon et chaque fin ... tout en ignorant les points trace entre chaque tronçon ?


.. peut-être plus clair avec une pièce-jointe ??

  Merci

Dernière modification par Ericge (Fri 26 April 2019 10:11)


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

Hors ligne

 

#4 Fri 26 April 2019 16:20

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 867

Re: PostGis : fonction St_makeline

Salut,

si j'ai bien compris, qu'on peut ordonner vos tronçons avec avec trac_id,
et que event_id vaut toujours 0 pour les "points traces tronçons à prendre en compte",
on peut récupérer pour chaque event_id, le trac_id de début et de fin, pour filtrer
la table et ne retenir que les points qui nous intéressent.

Code:

WITH f as (
        --array_agg est une fonction d'agrégat qui
        --génére un tableau de l'ensemble donné en argument
        SELECT
                array_agg(trac_id ORDER BY trac_id) bounds
        FROM
                t
        --on ne veut recuperer que le premier et le dernier
        --trac_id de chaque event
        WHERE
                event_id <> 0
        GROUP BY
                 event_id
        --On ne veut que des tableaux ayant deux élements
        HAVING
                COUNT(*) = 2
        )

SELECT
        st_makeline(
                st_makepoint(gps_lat_start, gps_long_start)
                ORDER BY trac_id) line
FROM
        f
JOIN
        t
        ON t.trac_id BETWEEN bounds[1] AND bounds[2]
GROUP BY
        event_id

Hors ligne

 

#5 Sat 27 April 2019 16:42

Ericge
Membre
Date d'inscription: 23 Oct 2018
Messages: 18

Re: PostGis : fonction St_makeline

Bonjour tumasgiu,
   
    Votre compréhension est bonne et ça m'a l'air plutôt malin !!!
    Je teste votre proposition dès que je peux (fin de semaine prochaine) et je vous fais un retour.

    Merci

Hors ligne

 

#6 Tue 07 May 2019 16:33

Ericge
Membre
Date d'inscription: 23 Oct 2018
Messages: 18

Re: PostGis : fonction St_makeline

Bonjour Tumasgiu

De retour, j'ai pu tester votre proposition.
Ca semble fonctionner par contre la table que je crée est vide. Comment conserver au moins le "event-id" ?

Hors ligne

 

#7 Tue 07 May 2019 16:34

Ericge
Membre
Date d'inscription: 23 Oct 2018
Messages: 18

Re: PostGis : fonction St_makeline

Bonjour Tumasgiu

De retour, j'ai pu tester votre proposition.
Ca semble fonctionner par contre la table que je crée est vide. Comment conserver au moins le "event-id" ?

Hors ligne

 

#8 Fri 10 May 2019 16:58

Ericge
Membre
Date d'inscription: 23 Oct 2018
Messages: 18

Re: PostGis : fonction St_makeline

A priori ce programme ne fonctionne pas :
-  cela me crée autant de lignes qu'il y a de event_id
- les lignes sont toutes identiques et relient tous les points de la table

Hors ligne

 

#9 Mon 13 May 2019 11:54

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 867

Re: PostGis : fonction St_makeline

Salut,

-  cela me crée autant de lignes qu'il y a de event_id


oui, c'est logique, on groupe par event_id, je pensais que c'était la chose désirée.
Si vous voulez grouper par autre chose, par exemple trip_id peut-être,
il vous faut modifier la clause GROUP BY.

Hors ligne

 

#10 Fri 17 May 2019 16:27

Ericge
Membre
Date d'inscription: 23 Oct 2018
Messages: 18

Re: PostGis : fonction St_makeline

Bonjour,

... je me suis mal exprimé.

En fait, le programme crée autant de géométries (lignes) que d'event_id, mais tous identiques c'est à dire reliant tous, tous les points traces, alors que l'objectif est d'avoir une géom par event_id.

J'ai retravaillé le visuel d'extrait de la table avec les objectifs recherchés. Voir la pj

Merci


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

Hors ligne

 

#11 Fri 17 May 2019 16:51

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 867

Re: PostGis : fonction St_makeline

Oui j'avais bien compris,

je crois que j'ai oublié  une condition dans la jointure entre t et f smile

Code:

WITH f as (
        --array_agg est une fonction d'agrégat qui
        --génére un tableau de l'ensemble donné en argument
        SELECT
                event_id,
                array_agg(trac_id ORDER BY trac_id) bounds
        FROM
                t
        --on ne veut recuperer que le premier et le dernier
        --trac_id de chaque event
        WHERE
                event_id <> 0
        GROUP BY
                 event_id
        --On ne veut que des tableaux ayant deux élements
        HAVING
                COUNT(*) = 2
        )

SELECT
        st_makeline(
                st_makepoint(gps_lat_start, gps_long_start)
                ORDER BY trac_id) line
FROM
        f
JOIN
        t
        ON t.event_id = f.event_id 
        AND t.trac_id BETWEEN bounds[1] AND bounds[2]
GROUP BY
        event_id

Hors ligne

 

#12 Tue 28 May 2019 10:56

Ericge
Membre
Date d'inscription: 23 Oct 2018
Messages: 18

Re: PostGis : fonction St_makeline

Bonjour, tumasgiu

Merci pour cette proposition.

Après test :

   - Les géométries de chaque EVENT_ID sont bien uniques :une géom pour chaque EVENT_ID
 
   - Par contre les points traces intermédiaires ne sont pas pris en compte.
      En fichier-joint, le résultat sur Qgis avec en sélectionnés jaune, les points du EVENT_ID=45 et les points intermédiaires. En noir le segment résultat de ta proposition.


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

Hors ligne

 

#13 Tue 28 May 2019 11:27

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 867

Re: PostGis : fonction St_makeline

Après relecture :

Le résultat que vous obtenez est dû à
la condition de jointure supplémentaire de la seconde proposition,
car event_id vaut 0 pour les points intermédiaires, ce qui
les écarte de la jointure.

Donc troisieme proposition : on enleve cette condtion de jointure,
et on ne fait juste que grouper avec le event_id de f, qui ne
vaudra jamais 0.

Code:

WITH f as (
        --array_agg est une fonction d'agrégat qui
        --génére un tableau de l'ensemble donné en argument
        SELECT
                event_id,
                array_agg(trac_id ORDER BY trac_id) bounds
        FROM
                t
        --on ne veut recuperer que le premier et le dernier
        --trac_id de chaque event
        WHERE
                event_id <> 0
        GROUP BY
                 event_id
        --On ne veut que des tableaux ayant deux élements
        HAVING
                COUNT(*) = 2
        )

SELECT
        st_makeline(
                st_makepoint(gps_lat_start, gps_long_start)
                ORDER BY trac_id) line
FROM
        f
JOIN
        t
        ON t.trac_id BETWEEN bounds[1] AND bounds[2]
GROUP BY
        f.event_id

Hors ligne

 

#14 Tue 28 May 2019 11:54

Ericge
Membre
Date d'inscription: 23 Oct 2018
Messages: 18

Re: PostGis : fonction St_makeline

.... piste possible : le "GROUP BY event_id" ne groupe que les point BOUNDS 1 et  pour lesquels l'event_id est renseigné. Pour les autres points intermédiaires, l'event_id est à 0, donc pas pris en compte.

?? peut-être

Hors ligne

 

#15 Tue 28 May 2019 13:04

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 867

Re: PostGis : fonction St_makeline

Oui c'est exactement çà.

Hors ligne

 

#16 Tue 28 May 2019 15:22

Ericge
Membre
Date d'inscription: 23 Oct 2018
Messages: 18

Re: PostGis : fonction St_makeline

mais comment faire ?

Hors ligne

 

#17 Tue 28 May 2019 22:17

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 867

Re: PostGis : fonction St_makeline

Avez vous tester la troisième version ?
Faites bien attention a spécifier l'event_id de f
et non de votre table dans la clause GROUP BY.

Si ca ne marche toujours pas,
Essayez de lancer juste la requête la CTE f.
Inspectez les résultats pour vérifier si la colonne bounds
est bien initialisée.

Hors ligne

 

#18 Tue 04 June 2019 13:08

Ericge
Membre
Date d'inscription: 23 Oct 2018
Messages: 18

Re: PostGis : fonction St_makeline

Bonjour,

J'ai bien testé la 3ième version et bien spécifié l'event_id de f.

Si je décompose,  la requête f me crée bien une table avec pour chaque event_id, une colonne bounds contenant entre crochet, les valeurs min et max de trac_id.

Hors ligne

 

#19 Thu 06 June 2019 12:44

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 867

Re: PostGis : fonction St_makeline

Peut être pourriez vous poster la requête que vous éxecuter ?
Merci.

Hors ligne

 

Pied de page des forums

Powered by FluxBB

Partagez  |