Le portail francophone de la géomatique

staubli · Wed 16 September 2020 12:42

Salut tout le monde

J'ai trois tables differentes que je voudrais combiner au travers d'un VIEW. Mais j'ai beau tout essayer je n'y arrive pas.
J'ai une table Avec mes objets (Points) qui contient leur ID, leur geom et une Denomination Sous forme de Code.
Une table Avec toutes les denominations de tous mes objets (tel Code correspond a telle Denomination pour tel objet, exemple SY12=poteau electrique).
Une 3e table qui indique des poins kilometriques sur une route.

Je veux donc pour chaque objet lui associer sa denomination et son kilometrage (son plus proche voisin).
Je suis arrivé d'un coté à associer un objet à son nom et d'un autre coté à associer un objet à son kilometrage mais je n'arrive donc pas à combiner les deux.

J'imagine que ca doit etre tout bete mais je n'y arrive pas. Je commence juste à migrer les données vers Postgre et je n'ai pas fait beaucoup de SQL, ce qui explique mes difficultés.

Requete associer une Denomination à un Code

Code:

 SELECT p."ID",
    p.geom,
    p.name,
    p.angle,
    p.linie,
    i.bezeichnung
   FROM infra_visu._prellbockdist p
     JOIN infra_visu._symbolindex_bahnplan i ON p.name::text = i.symbolnr::text;

Requete associer un Point kilometrique

Code:

SELECT DISTINCT ON (_prellbockdist."ID")
  _prellbockdist."ID",
  _prellbockdist.name, 
  _prellbockdist.angle, 
  _prellbockdist.linie,
  _kilometrierung.linie, 
  _kilometrierung.km
  FROM
infra_visu._prellbockdist,
infra_visu._kilometrierung
 WHERE ST_DWithin(_kilometrierung.geom , _prellbockdist.geom, 100)
Order by   _prellbockdist."ID", ST_Distance (_kilometrierung.geom , _prellbockdist.geom)

JP LLORENS · Wed 16 September 2020 13:36

Bonjour
J'utiliserai les requêtes with :

Code:

With req1 as (SELECT p."ID",
    p.geom,
    p.name,
    p.angle,
    p.linie,
    i.bezeichnung
   FROM infra_visu._prellbockdist p
     JOIN infra_visu._symbolindex_bahnplan i ON p.name::text = i.symbolnr::text),

req2 as (SELECT DISTINCT ON (_prellbockdist."ID")
  _prellbockdist."ID",
  _prellbockdist.name, 
  _prellbockdist.angle, 
  _prellbockdist.linie,
  _kilometrierung.linie, 
  _kilometrierung.km
  FROM
infra_visu._prellbockdist,
infra_visu._kilometrierung
 WHERE ST_DWithin(_kilometrierung.geom , _prellbockdist.geom, 100)
Order by   _prellbockdist."ID", ST_Distance (_kilometrierung.geom , _prellbockdist.geom))

select req1.*, req2.* from req1 join req2 on req1.id = req2.id

Si les 2 requêtes fonctionnent, alors ça devrait rouler ! Il faut peut-être affiner le join en fonction du nombre d'entités dans chaque sous requête.
JP

Dernière modification par JP LLORENS (Wed 16 September 2020 13:37)

staubli · Wed 16 September 2020 14:08

Top JP merci c exactement ce que je voulais.
J'ai donc repris le Code à ma Sauce comme ci Dessous.

Question subsidiaire: je recherche un plus proche voisin dans la requete 1. Sur cet exemple là ca va car je n'ai que 58 éléments _prellbockdist mais j'ai 4500 mats caténaires pour plus de 100.000 Points metriques, donc la requete a mis 6 minutes avant d'etre complétée. Peut on consider que c'est un temps nornal d'execution compte tenu du nombre de données ou alors c que mon Code n'est pas optimal ?

Code:

With req1 as (SELECT DISTINCT ON (p."ID")
  p."ID",
  p.name, 
  p.angle, 
  k.linie, 
  k.km
  FROM
infra_visu._prellbockdist p,
infra_visu._kilometrierung k
 WHERE ST_DWithin(k.geom , p.geom, 100)
Order by   p."ID", ST_Distance (k.geom , p.geom)),

req2 as (SELECT p."ID",
    i.bezeichnung
   FROM infra_visu._prellbockdist p
     JOIN infra_visu._symbolindex_bahnplan i ON p.name::text = i.symbolnr::text)

select
  req1."ID",
  req1.name, 
  req2.bezeichnung, 
  req1.angle,
  req1.linie,
  req1.km as kilometrierung
from req1 join req2 on req1."ID" = req2."ID"

Nicolas Ribot · Wed 16 September 2020 16:43

Bonjour,

Pour la recherche du plus proche voisin (KNN), postgis dispose d'un opérateur distance (<->).
C'est BCP plus rapide que de calculer la distance et de classer, style quelques secondes vs 6 min pour votre dataset, je dirais comme ca a vue de nez.

L'opérateur distance a aussi le gros avantage de retourner un résultat dans tous les cas, sans avoir a décider arbitrairement d'une distance mini (100m dans votre exemple)

Nicolas

staubli · Wed 16 September 2020 17:27

Bonjour Nicolas, merci. J'allais effectivement partir sur KNN au début mais comme la doc de PostGIS faisait ses exemples sur les rues les plus proches de de la station de metro broad street (donc sur une seule entité), je suis resté bloqué là. J'ai évidemment besoin d'itérer.
https://postgis.net/workshops/postgis-intro/knn.html

Aussi ça prend du temps (avec ma solution actuelle) et c'est pas optimal dans PostGIS car je veux rester sur un view, ayant en effet calculé ces valeurs de kilonmetrage et n'étant pas parti sur un fichier existant. Si un jour on les retrouve, la MaJ serait beaucoup plus simple. Tout ça est par contre un bonus et n'était pas demandé, mais si je peux arriver à un résultat instantané dans PostGIS/QGIS, ce serait top.

Je vais potasser pour voir si j'arrive à m'en sortir.

Nicolas Ribot · Wed 16 September 2020 17:58

Je suis pas fan, perso, de vues qui mettent en jeu du spatial qui prend du temps à exécuter.
je préfère une vraie table, ou alors une vue matérialisée, qui stocke la requête utilisée et peut etre rafraichie.

Pour faire une recherche de plus proches voisins sur tous les éléments d'une table vs tous les éléments d'une autre, vous pouvez utiliser un JOIN LATERAL, qui offre la fonctionnalité puissante de pouvoir référencer dans la sous-requete du FROM des colonnes du SELECT de la première requête.

Dans votre exemple (j'ai ptet pas tout compris non plus ), je ferais une recherche de plus proche voisin (dans la sous-requête LATERAL..., le "LIMIT k" permet de choisir les k plus proches voisins d'une geométrie); puis une jointure sur la table bezeichnung pour remonter les infos voulues:

Code:

-- recherche du plus proche voisin (LIMIT 1) de chaque _prellbockdist dans la table _kilometrierung
with knn as (
    SELECT p.id, p.name, p.angle,
           t.linie,
           t.km
    FROM _prellbockdist p
             CROSS JOIN LATERAL
        (SELECT a.linie, a.km
         FROM _kilometrierung a
         ORDER BY p.geom <-> a.geom
         LIMIT 1) AS t
) -- puis jointure finale pour remonter l'info bezeichnung
select k.id, k.name, k.angle, k.linie, k.km,
         i.bezeichnung
from knn k join _symbolindex_bahnplan i ON k.name::text = i.symbolnr::text;

Nicolas

Nicolas Ribot · Wed 16 September 2020 18:10

Concernant le temps d'execution, je vous pose les questions habituelles

Les index GIST sont-ils bien créés sur les tables spatiales ?
les colonnes name et symbolnr sont-elles indexées ?
Analye <table> a-t-elle été lancée apres la création des index ?
La BD est-elle un peu tunée (mémoire shared_buffer et work_mem, notamment) ?
Le disque sur lequel sont les données est-il rapide ?

(work_mem, mémoire utilisée pour les ORDER BY et DISTINCT, peut etre réglée en live juste avant la requête, puis remise à sa valeur par défaut ensuite: ca peut permettre de bien booster le order by et le distinct on (...) dans votre cas:

Code:

set work_mem to '50MB'; -- ou autre valeur suivant la volumétrie des données

WITH ... ;

reset work_mem;

6min, ca me parait bien long pour un tel dataset.

Nicolas

Dernière modification par Nicolas Ribot (Wed 16 September 2020 18:35)

tumasgiu · Wed 16 September 2020 20:50

Salut,

juste pour ajouter une choses aux questions de Nicolas,

le plan d'execution de la requête est utile pour éventuellement voir ce qui clocherait
dans votre requête.

Vous pouvez l'obtenir avec la commande EXPLAIN, la publier ici,
et poster le lien dans votre reponse sur georezo.

Dernière modification par tumasgiu (Wed 16 September 2020 20:50)

staubli · Thu 17 September 2020 13:37

Salut tout le monde, j'ai bossé sur la fonction du neirest neighbour et les résultats sont moyennement satisfaisants. C'est mieux que ce que j'avais mais c'est pas encore ça.
Je suis parti de ce post sur GIS Stackexchange:
https://gis.stackexchange.com/questions … stance-knn

Vous trouverez mon code en bas qui fonctionne. J'ai juste tronqué la mesure de distance.

Au final je ferai un view mais que sur la requete 2, c'est à dire celle qui va me donner la description des codes. Pour le neirest neighbour je ferai soit directement dans QGIS soit dans Postgre mais je ne reprendrai que les numéros de lignes et pas le kilometrage. Ainsi je ne ferai pas un VIEW mais j'alimenterai ma BD avec ces données. Le numéro de ligne fait partie de mon modèle de données donc il me le faut. Je pense utiliser mes points de kilometrage quand meme ici, sinon je ferai avec mes lignes mais si ça fait chercher le centroïde des lignes c mort.

Pour les questions sur le temps d'exécution j'aurais du mal à vous répondre. C'est en dehors de mon scope. Je suis stagiaire, ça fait 7 mois que je suis sur ce projet et je dois migrer mes données vers Postgre la semaine prochaine. Pour le reste ce sera soit à la géomaticienne soit aux consultants occasionels de s'en occuper.

C'est peut etre pas l'issue que je m'étais imaginée mais c'est une issue.

Merci à tous en tous cas.

Code:

SELECT 
  _prellbockdist."ID",
  _prellbockdist.name, 
  closestkm.linie,
  closestkm.km    
FROM 
  infra_visu._prellbockdist
CROSS JOIN LATERAL 
  (SELECT
      linie,
      km
      FROM infra_visu._kilometrierung
      ORDER BY _kilometrierung.geom<-> _prellbockdist.geom
     LIMIT 1
   )as closestkm

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

#1 Wed 16 September 2020 12:42

Combinaison de requetes

Code:

Code:

#2 Wed 16 September 2020 13:36

Re: Combinaison de requetes

Code:

#3 Wed 16 September 2020 14:08

Re: Combinaison de requetes

Code:

#4 Wed 16 September 2020 16:43

Re: Combinaison de requetes

#5 Wed 16 September 2020 17:27

Re: Combinaison de requetes

#6 Wed 16 September 2020 17:58

Re: Combinaison de requetes

Code:

#7 Wed 16 September 2020 18:10

Re: Combinaison de requetes

Code:

#8 Wed 16 September 2020 20:50

Re: Combinaison de requetes

#9 Thu 17 September 2020 13:37

Re: Combinaison de requetes

Code:

Pied de page des forums