IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Requêtes PostgreSQL Discussion :

Débuts en requête spatiale


Sujet :

Requêtes PostgreSQL

  1. #1
    Candidat au Club
    Profil pro
    Statisticienne
    Inscrit en
    mars 2008
    Messages
    4
    Détails du profil
    Informations personnelles :
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Statisticienne

    Informations forums :
    Inscription : mars 2008
    Messages : 4
    Points : 3
    Points
    3
    Par défaut Débuts en requête spatiale
    Bonjour,

    Je me lance dans les bases de données spatiales et donc je dois faire des requetes SQL spatiales.
    Je manipule le SQL régulièrement pour faire du data-management mais je ne pensais pas que la dimension "spatiale" me perdrait aussi vite.

    Alors... je vous explique mon pb: J'ai une couche de coordonnées géographiques (environ 9000 points) -qu'on appelera "couche1"- et une couche de polygones (environ 2500 polygones), qu'on appelera "couche 2".
    Je veux récupérer la distance entre chaque point de la couche1 et le polygone(couche 2) le plus proche.
    Idéalement je voudrais afficher une colonne dans une nouvelle table avec l'identifiant de ma couche 1, l'identifiant de ma couche 2 le plus proche des coordonnées de ma couche 1 et la distance entre les 2.

    Voici ma requete:

    NB: J'ai fait au préalable un st_transform sur les geom de mes 2 couches pour avoir la mm projection (WGS 84: 4326)

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    create table public.MERGE as
    SELECT a.id_couche1,min(ST_Distance(a.geom2_couche1, b.geom2_couche2,TRUE)) as distance
        FROM public."COUCHE1" as a,public."COUCHE2" as b 
    	group BY a.id_couche1;
    Le souci c'est que (i) ca tourne pendant des heures et ca c'est pas possible parce qu'il faut que je le fasse au moins 50 fois et (ii) je ne peux pas afficher mon identifiant couche2 (Enfin quand je le fais il refuse de tourner si je le mets pas dans "group by" et du coup j'ai les distances pour les 2500 polygones pour chaque point de ma couche1).

    Avez-vous des astuces/solutions/corrections qui pourraient m'aider?

    Merci beaucoup.

  2. #2
    ced
    ced est déconnecté
    Rédacteur/Modérateur

    Avatar de ced
    Homme Profil pro
    Gestion de bases de données techniques
    Inscrit en
    avril 2002
    Messages
    5 903
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Loiret (Centre)

    Informations professionnelles :
    Activité : Gestion de bases de données techniques
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : avril 2002
    Messages : 5 903
    Points : 22 877
    Points
    22 877
    Par défaut
    Bonjour,

    Pour une première requête spatiale sous PostGIS, vous n'avez pas choisi la plus facile à réaliser.
    Tout d'abord, si vous voulez que les performances de la requête soient acceptables, il faut impérativement indexer spatialement les géométries qui sont dans la requête (geom2_couche1 et geom2_couche2). Sans ça, ça va être une catastrophe en temps d'exécution.

    Une fois que c'est indexé, alors vous pouvez faire une recherche du plus proche voisin (KNN) entre les deux couches. Il y a plusieurs façon d'écrire ce genre de requêtes, en voici une :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    SELECT a.id_couche1, pr.distance
    FROM public."COUCHE1" AS a
    JOIN LATERAL (
        SELECT ST_Distance(a.geom2_couche1, b.geom2_couche2) AS distance
        FROM public."COUCHE2" AS b
        ORDER BY a.geom2_couche1 <-> b.geom2_couche2
        LIMIT 1
    ) AS pr ON TRUE;
    Attention ! Un point qui tombe à l'intérieur d'un polygone renvoie une distance égale à zéro (ce qui est parfaitement compréhensible, vu qu'il est à l'intérieur du polygone).
    À tester et à adapter à votre cas.

    Bon courage,
    ced
    Rédacteur / Modérateur SGBD et R
    Mes tutoriels et la FAQ MySQL

    ----------------------------------------------------
    Pensez aux balises code et au tag
    Une réponse vous a plu ? N'hésitez pas à y mettre un
    Je ne réponds pas aux questions techniques par message privé, les forums sont là pour ça

  3. #3
    Candidat au Club
    Profil pro
    Statisticienne
    Inscrit en
    mars 2008
    Messages
    4
    Détails du profil
    Informations personnelles :
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Statisticienne

    Informations forums :
    Inscription : mars 2008
    Messages : 4
    Points : 3
    Points
    3
    Par défaut
    Alors c'est génial ca marche !!!! Merci beaucoup pour votre retour :-) :-) :-) par contre maintenant je veux comprendre ;-)

    Alors, en effet j'ai vu qu'il me faudrait crééer des index pour améliorer les performances mais ne sachant pas je n'avais pas crééé l'index sur la bonne variable.
    Merci donc de cette précision :-)

    Quand je créé l'index sur geom pour ma couche 2 j'ai un message d'erreur:

    NB: la couche 2 sort de BDTOPO

    ERROR: ERREUR: la taille de la ligne d'index, 4144, dépasse le maximum pour un btree de version 4, soit 2704, pour l'index « idx_geom_sport »
    DETAIL: La ligne d'index référence le tuple (143,2) dans la relation « couche2 ».
    HINT: Les valeurs plus larges qu'un tiers d'une page de tampon ne peuvent pas être
    indexées.
    Utilisez un index sur le hachage MD5 de la valeur ou passez à l'indexation
    de la recherche plein texte.
    SQL state: 54000
    Mais bon ca marche quand mm sans ca :-)

    Par contre pourriez-vous m'expliciter les lignes de commande de votre programme que je n'ai pas saisies? ou corriier si j'ai compris de travers?

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    /*Ca me semble faire l'équivalent d'un left join? afin de garder que les observations de la couche1? */
    SELECT a.id_couche1, pr.distance
    FROM public."couche1" AS a
     
    /*Là on va crééer une sous couche à partir de couche2 pr calculer la distance*/
    JOIN LATERAL (
        SELECT b.id_couche2, ST_Distance(a.geom2_couche1, b.geom2_couche2) AS distance
        FROM public."Couche2" AS b
     
    /*A partir de là je comprends bcp moins bien*/
        ORDER BY a.geom2_couche1 <-> b.geom2_couche2 /*C'est pour faire la correspondance entre les géométries?*/
        LIMIT 1 /*pkoi une limite à 1?*/
     
    ) AS pr ON TRUE; /*??*/
    Question subsidiaire: le fait que ma distance soit exprimée en degrés c'est dû à la projection choisie?

    Merci infiniment pour votre aide.

    Une très belle fin de journée à vous.

  4. #4
    ced
    ced est déconnecté
    Rédacteur/Modérateur

    Avatar de ced
    Homme Profil pro
    Gestion de bases de données techniques
    Inscrit en
    avril 2002
    Messages
    5 903
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Loiret (Centre)

    Informations professionnelles :
    Activité : Gestion de bases de données techniques
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : avril 2002
    Messages : 5 903
    Points : 22 877
    Points
    22 877
    Par défaut
    Attention ! Un index spatial n'est pas un index de type BTREE (indexation par défaut quand on crée un index sans préciser le type). Il faut créer un index de type GIST, comme ça :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    CREATE INDEX couche2_geom2_idx ON public."COUCHE2" USING gist(geom2);
    Normalement, il n'y aura plus de message d'erreur et les requêtes spatiales vont pouvoir utiliser ces index GIST (elles ne feront rien avec un index BTREE). Donc refaites tous vos index sur vos géométries avec le type GIST.

    Pour plus d'explication sur la requête, c'est une jointure latérale, qui permet d'utiliser des éléments de la requête principale. Voici ce qu'en dit la documentation de PostgreSQL :
    Le mot clé LATERAL peut précéder un élément sous-SELECT de la clause FROM. Ceci permet au sous-SELECT de faire référence aux colonnes des éléments du FROM qui apparaissent avant lui dans la liste FROM. (Sans LATERAL, chaque sous-SELECT est évalué indépendamment et donc ne peut pas faire référence à tout autre élément de la clause FROM.)
    Comme c'est une jointure, il lui faut une condition (ON...), mais comme elle n'a ici aucun intérêt, il suffit de mettre ON TRUE;.

    Le ORDER BY ... permet de faire la recherche KNN (K plus proches voisins) des deux géométries en utilisant leur indexation spatiale. Et le LIMIT 1, c'est pour ne garder que le voisin le plus proche (le premier voisin en distance).
    Mais vous pourriez ramener les K plus proches voisins (avec un LIMIT K) de chaque point (d'où l'acronyme KNN pour "K Nearest Neighboors").

    J'espère que c'est plus clair.

    ced
    Rédacteur / Modérateur SGBD et R
    Mes tutoriels et la FAQ MySQL

    ----------------------------------------------------
    Pensez aux balises code et au tag
    Une réponse vous a plu ? N'hésitez pas à y mettre un
    Je ne réponds pas aux questions techniques par message privé, les forums sont là pour ça

  5. #5
    Candidat au Club
    Profil pro
    Statisticienne
    Inscrit en
    mars 2008
    Messages
    4
    Détails du profil
    Informations personnelles :
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Statisticienne

    Informations forums :
    Inscription : mars 2008
    Messages : 4
    Points : 3
    Points
    3
    Par défaut
    C'est limpide
    Merci beaucoup pour votre réactivité, vos explications et le temps que vous m'avez accordé :-)

    Bonne fin de journée

  6. #6
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    7 076
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 7 076
    Points : 22 460
    Points
    22 460
    Billets dans le blog
    2
    Par défaut
    Attention : l'utilisation de la clause LIMIT ne permet pas de gérer les ex-aequo. Il est préférable de calculer le rang avec RANK ou DENSE_RANK

  7. #7
    ced
    ced est déconnecté
    Rédacteur/Modérateur

    Avatar de ced
    Homme Profil pro
    Gestion de bases de données techniques
    Inscrit en
    avril 2002
    Messages
    5 903
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Loiret (Centre)

    Informations professionnelles :
    Activité : Gestion de bases de données techniques
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : avril 2002
    Messages : 5 903
    Points : 22 877
    Points
    22 877
    Par défaut
    Exact.
    Dans le cas présent, comme on veut juste la distance minimale d'un point au polygone le plus proche, peut importe les ex-aequo (que le point soit à équidistance de deux polygones ne pose pas de problème)...

    À noter que depuis PostgreSQL 13, les ex-aequo peuvent être récupérés avec la clause FETCH FIRST... WITH TIES à la place de la clause LIMIT.
    Rédacteur / Modérateur SGBD et R
    Mes tutoriels et la FAQ MySQL

    ----------------------------------------------------
    Pensez aux balises code et au tag
    Une réponse vous a plu ? N'hésitez pas à y mettre un
    Je ne réponds pas aux questions techniques par message privé, les forums sont là pour ça

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. mise à jour par requête spatiale
    Par amelo dans le forum PL/SQL
    Réponses: 0
    Dernier message: 25/03/2011, 10h35
  2. Réponses: 2
    Dernier message: 19/01/2011, 14h46
  3. Réponses: 0
    Dernier message: 27/05/2009, 16h16
  4. Requête spatiale - géolocalisation sur géoïde
    Par darkned dans le forum Requêtes
    Réponses: 0
    Dernier message: 08/10/2008, 16h32
  5. requête spatiale postgis
    Par jonathan1 dans le forum PostgreSQL
    Réponses: 1
    Dernier message: 06/02/2007, 12h06

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo