Développer une application avec pgrouting sous Windows (5):écrire un Wrapper

Un wrapper est une fonction de PostgreSQL comme une autre, qui contient des fonctionnalités de pgRouting et PostGIS sous une forme plus facile à utiliser que lors de l’appel des fonctions pgRouting et PostGIS directement.
Les fonctions natives de pgRouting ont été conçues pour être génériques afin de pouvoir être utilisées dans le plus large éventail d’applications. Bien que cela offre de la flexibilité, l’inconvénient est que vous pouvez être obligés de remodeler temporairement votre propre structure de données avant de pouvoir appliquer des fonctions de pgRouting.
Rédiger une fonction wrapper adaptée à votre structure de données diminue la nécessité de passer par des requêtes SQL encombrantes.
Une autre raison d’utiliser les fonctions de wrapper est de contourner les attaques par injection SQL. Ériger une barrière fonctionnelle entre les entrées de l’utilisateur et les fonctions de pgRouting natives vous permet de désinfecter les entrées utilisateur.

PostgreSQL supporte de nombreux langages de programmation pour l’écriture des fonctions utilisateur, mais SQL et PL/pgSQL sont les plus courantes. Nous allons voir ici un exemple avec PL/pgSQL.

Jusqu’à présent, dans notre série d’articles, nous nous sommes cantonnés au niveau des données du réseau, mais le but est d’arriver à mettre en place une application web de recherche d’itinéraire. Dans ce cas, l’idée est de présenter une carte à l’utilisateur, et lui permettre de définir un point de départ et un point d’arrivée. Parmi les multiples possibilités, retenons la plus simple: un clic sur la carte pour définir le point de départ et un autre clic pour définir le point d’arrivée.

Notre page web envoie alors deux couples de coordonnées x-y et s’attend à recevoir en retour l’itinéraire conseillé pour relier ces deux points géographiques.

Si vous avez suivi la série d’articles, vous pouvez mesurer la distance qu’il y a entre ce que la page web fait et les recherches d’itinéraire que nous avons vu, où on doit rentrer comme paramètres les identifiant des nœuds de départ et d’arrivée, ainsi que d’autres paramètres selon l’algorithme utilisé.

N’oublions pas que, entre notre page web et notre base Postgresql/Postgis nous aurons aussi Geoserver. Une possibilité est d’écrire une requête complexe dans Geoserver, mais nous avons une solution beaucoup plus simple et efficace en utilisant un wrapper dans PostgreSQL.

Ce wrapper, qui n’est autre chose qu’une nouvelle fonction que nous allons ajouter dans notre base Postgres, s’occupera de traduire les x-y transmis par la page web en paramètres utilisables par notre algorithme de recherche d’itinéraire, d’optimiser son exécution , et de mettre en forme le résultat pour l’affichage sur la page web.

Le code présenté ici est une variation d’un exemple que l’on trouve dans différents sites traitant de pgrouting et que l’on retrouve sous le nom de pgr_fromAtoB.

Texte du wrapper

–DROP FUNCTION pgr_route_entreAetB(varchar, double precision, double precision,
— double precision, double precision);

CREATE OR REPLACE FUNCTION pgr_route_entreAetB(
IN tbl varchar,
IN x1 double precision,
IN y1 double precision,
IN x2 double precision,
IN y2 double precision,
OUT seq integer,
OUT gid integer,
OUT direction double precision,
OUT cost double precision,
OUT geom geometry)

RETURNS SETOF record AS

$BODY$
DECLARE
sql text;
rec record;
source integer;
target integer;
point integer;
buff double precision;
dist double precision;
legid integer;

BEGIN

— Trouver le nœud le plus proche des lat/lon en entrée

EXECUTE ‘SELECT id::integer, the_geom::geometry FROM ‘ || tbl || ‘_vertices_pgr
ORDER BY the_geom <->ST_GeometryFromText( »POINT(‘
|| x1 || ‘ ‘ || y1 || ‘) »,4326) LIMIT 1’ INTO rec;
source := rec.id;

EXECUTE ‘SELECT id::integer, the_geom::geometry FROM ‘ || tbl || ‘_vertices_pgr
ORDER BY the_geom <->ST_GeometryFromText( »POINT(‘
|| x2 || ‘ ‘ || y2 || ‘) »,4326) LIMIT 1’ INTO rec;
target := rec.id;

–Définir une sous-zone de travail
EXECUTE ‘SELECT ST_Distance(ST_GeomFromText( »POINT(‘ || x1 ||’ ‘ || y1 ||’) »,4326),’
‘ST_GeomFromText( »POINT(‘ || x2 ||’ ‘ || y2 ||’) »,4326)) as dist’ INTO rec;
buff := GREATEST(rec.dist,0.5);

–Trouver l’identifiant du tronçon de départ
EXECUTE ‘SELECT gid as legid FROM ‘ || tbl || ‘ WHERE source = ‘ || source || ‘ LIMIT 1’ INTO rec;
legid := rec.legid;

— Calculer l’itinéraire avec l’algorithme A*

seq := 0;
sql := ‘SELECT gid, the_geom,pgr_astar.cost, source, target, ST_Reverse(the_geom) AS flip_geom
FROM ‘
‘pgr_astar( »SELECT gid as id, source::int, target::int, ‘
‘length_m * ‘ || tbl || ‘.cost as cost, x1, y1, x2, y2 FROM ‘
|| quote_ident(tbl) ||
‘ WHERE the_geom @ (SELECT ST_buffer(the_geom,’
|| buff || ‘) FROM ‘
|| quote_ident(tbl) || ‘ WHERE gid=’ || legid || ‘)’
 »’, ‘
|| source || ‘, ‘ || target
|| ‘ , false, false), ‘
|| quote_ident(tbl) || ‘ WHERE id2 = gid ORDER BY seq’;

— mémoriser la point de départ

point := source;

FOR rec IN EXECUTE sql
LOOP

— Inverser les tronçons si nécessaire

IF ( point != rec.source ) THEN
rec.the_geom := rec.flip_geom;
point := rec.source;
ELSE
point := rec.target;
END IF;

— Calculer le cap

EXECUTE ‘SELECT degrees( ST_Azimuth(
ST_StartPoint( »’ || rec.the_geom::text ||’  »),
ST_EndPoint( »’ || rec.the_geom::text || ‘  ») ) )’
INTO direction;

— Return

seq := seq + 1;
gid := rec.gid;
cost := rec.cost;
geom := rec.the_geom;
RETURN NEXT;

END LOOP;
RETURN;
END;
$BODY$
LANGUAGE ‘plpgsql’ VOLATILE STRICT;

Voyons maintenant qu’est-ce que cette fonction fait.

Paramétrage de la fonction

Tout d’abord nous avons le paramétrage de la fonction:
CREATE OR REPLACE FUNCTION pgr_route_entreAetB(
IN tbl varchar,
IN x1 double precision,
IN y1 double precision,
IN x2 double precision,
IN y2 double precision,
OUT seq integer,
OUT gid integer,
OUT direction double precision,
OUT cost double precision,
OUT geom geometry)

Nous définissons 5 paramètres en entrée: la table contenant le réseau et les coordonnées du point de départ et d’arrivée.
Nous définissons aussi les paramètres en sortie : une série de tronçons comportant un numéro d’ordre (seq), l’identifiant du tronçon (gid), la direction géographique ou cap de ce tronçon (direction), le coût associé au tronçon et la géométrie de celui-ci pour pouvoir le dessiner sur la carte résultat.

Pré-traitement des paramètres

Ensuite, nous devons préparer les paramètres de notre algorithme. Ici nous allons utiliser l’algorithme a-star de pgrouting.

EXECUTE ‘SELECT id::integer, the_geom::geometry FROM ‘ || tbl ||’_vertices_pgr
ORDER BY the_geom ST_GeometryFromText( »POINT(‘
|| x1 || ‘ ‘ || y1 || ‘) »,4326) LIMIT 1’ INTO rec;
source := rec.id;

EXECUTE ‘SELECT id::integer, the_geom::geometry FROM ‘ || tbl || ‘_vertices_pgr
ORDER BY the_geom ST_GeometryFromText( »POINT(‘
|| x2 || ‘ ‘ || y2 || ‘) »,4326) LIMIT 1’ INTO rec;
target := rec.id;

Ces deux paragraphes recherchent les identifiants des deux points de la table de nœuds de notre réseau les plus proches des coordonnées en entrée.
Les valeurs entre || sont remplacées lors de l’exécution par les paramètres en entrée de la fonction.

–Définir une sous-zone de travail
EXECUTE ‘SELECT ST_Distance(ST_GeomFromText( »POINT(‘ || x1 ||’ ‘ || y1 ||’) »,4326),’
‘ST_GeomFromText( »POINT(‘ || x2 ||’ ‘ || y2 ||’) »,4326)) as dist’ INTO rec;
buff := GREATEST(rec.dist,0.5);

Pour optimiser la recherche d’itinéraires, il est fortement recommandé de limiter a recherche à une sous-zone (bbox) de notre réseau. Ce paragraphe calcule la distance entre le point de départ et d’arrivée et définit une zone de recherche une fois et demie plus grande que cette distance. Les tronçons situés en dehors de cette zone ne seront pas pris en compte pour la recherche d’itinéraires possibles.  Ici nous avons utilisé la fonction GREATEST qui permet de définir une sous-zone minimale : si la distance à vol d’oiseau est inférieure à 0,5° on prend par défaut 0,5° comme sous-zone. On aurait pu définir un facteur comme:

buff :=rec.dist * 1.5

Il est clair que ce facteur (1.5) doit être décidé en connaissance de cause, si non il peut amener la fonction à ne pas trouver de solution. Par exemple, si vous avez deux vallées séparées par une montagne sans routes qui la traversent, la distance calculée ici étant à vol d’oiseau, elle peut ne pas inclure le col le plus proche.

–Trouver l’identifiant du tronçon de départ
EXECUTE ‘SELECT gid as legid FROM ‘ || tbl || ‘ WHERE source = ‘ || source || ‘ LIMIT 1’ INTO rec;
legid := rec.legid;

Pour exclure les tronçons trop éloignés nous allons utiliser la fonction ST_buffer appliquée à partir du tronçon de départ. Ils nous faut alors trouver l’identifiant de ce tronçon, car pour l’instant nous n’avons que les identifiants de la table des nœuds.

Nous pouvons maintenant exécuter l’algorithme :

sql := ‘SELECT gid, the_geom,pgr_astar.cost, source, target, ST_Reverse(the_geom) AS flip_geom
FROM ‘
‘pgr_astar( »SELECT gid as id, source::int, target::int, ‘
‘length_m * ‘ || tbl || ‘.cost as cost, x1, y1, x2, y2 FROM ‘
|| quote_ident(tbl) ||
‘ WHERE the_geom @ (SELECT ST_buffer(the_geom,’
|| buff || ‘) FROM ‘
|| quote_ident(tbl) || ‘ WHERE gid=’ || legid || ‘)’
 »’, ‘
|| source || ‘, ‘ || target
|| ‘ , false, false), ‘
|| quote_ident(tbl) || ‘ WHERE id2 = gid ORDER BY seq’;

Le coût utilisé ici est la longueur en mètres du tronçon multiplié par la colonne « cost » de la table. La fonction ST_buffer limite le nombre de tronçons de la recherche à ceux situés dans la zone définie lors de la génération de la variable buff.

La suite, inverser le tronçon et calculer le cap vont ensemble. Ils sont ici pour montrer un exemple de calcul non prévu dans l’algorithme et comment l’intégrer dans le wrapper. Si vous n’en voyez pas l’utilité, les effacer du wrapper n’aura aucun effet sur l’itinéraire résultant. L’inversion du tronçon est nécessaire pour calculer la véritable direction dans le cas où le tronçon est emprunté en sens inverse (du point target vers le point source). Ceci explique la partie « ST_Reverse(the_geom) AS flip_geom » dans la clause SELECT.

Ajout du wrapper à la base postgres

Pour pouvoir intégrer cette nouvelle fonction dans votre base PostgreSQL, simplement copiez le texte du wrapper dans une fenêtre SQL et exécutez la requête:

ajout du wrapper parmi les fonctions de postgresLa nouvelle fonction est maintenant dans la liste des fonctions de votre base.

Nous pouvons maintenant la tester avec QGis en utilisant la fenêtre de gestion de base de données.

w2Nous rentrons le nom de la table contenant notre réseau, et les coordonnées des points de départ et d’arrivée.

Une fois exécutée, la requête renvoie la liste des tronçons de l’itinéraire. En cliquant sur le bouton « Charger » nous pouvons voir l’affichage de l’itinéraire (sur fond d’OpensStreetMap):

itinéraire dans qgisNous pouvons aussi tester une requête un peu plus complexe, pour n’obtenir qu’une seule entité en résultat:

w4

SELECT ST_MakeLine(route.geom) FROM ( SELECT geom FROM pgr_route_entreAetB(‘ways’,-4.4888433,48.3967909,-4.5267335, 48.403296) ORDER BY seq) AS route

Cette requête prend la liste des tronçons résultant de la fonction pgr_route_entreAetB et fabrique une seule entité MultiLigne. Remarquez le nom de la colonne géométrie générée.

Si nous chargeons cette entité dans QGis nous remarquerons que visuellement le résultat est identique. Par contre la quantité d’information retourné est optimisée.

résultat avec st_makelineNous garderons cette dernière requête pour l’utiliser lors de la mise en place de Geoserver, ce que nous ferons dans le prochain article.

 

14 pensées sur “Développer une application avec pgrouting sous Windows (5):écrire un Wrapper”

  1. Bonjour j’essaie d’exécuter votre requête sur le wrapper mais j’ai une erreur de syntaxe que je n’arive pas à régler .

    ERREUR: chaîne entre guillemets non terminée sur ou près de « ‘

    J’ai pourtant copier exactement votre en prenant soin remplacer toutes les apostrophes par celle de la touche 4 de mon clavier. l’erreur est à la ligne 85 dans ce bloc

    — Calculer le cap

    EXECUTE ‘SELECT degrees( ST_Azimuth(
    ST_StartPoint( »’ || rec.the_geom::text || »’),
    ST_EndPoint( »’ || rec.the_geom::text || »’) ) )’
    INTO direction;

    Avez vous une idée svp? Merci

    1. après les || vous devez inverser les guillemets et les apostrophes
      EXECUTE ‘SELECT degrees( ST_Azimuth(
      ST_StartPoint( »’ || rec.the_geom::text ||’ »),
      ST_EndPoint( »’ || rec.the_geom::text ||’ ») ) )’
      INTO direction;

      Mea culpa! je corrige le texte de l’article

  2. bonjour , j’ai suivi les mêmes étapes que vous en utilisant l’algorithme de dijkstra et non le A*. j’ai également enlevé les étapes
    –Définir une sous-zone de travail
    et
    –Trouver l’identifiant du tronçon de départ
    le code s’exécute bien mais pas de résulat (vide).
    Est ce que c’est deux étapes sont nécessaires pour l’affichage?

    Merci de votre réponse

    1. Vos commentaires réveillent en moi des mauvais souvenirs… Si vous ne travaillez pas avec « mes » tables, il faut vous assurer de deux choses: la première c’est que vous avez tous les champs nécessaires à pgrouting dans votre table tronçons. La deuxième, que vous créez bien une table vertices avec pgr_createtopology. Si vous ne faites pas la table avec la topologie (ou si vous utilisez une à vous) le résultat de tous les algorithmes de pgrouting seront vides.
      En ce qui concerne la partie du code pour chercher le tronçon de départ, elle ne sert que si vous cliquez sur la carte, récupérez une position géographique et donc, vous avez besoin de trouver les nœuds les plus proches des clics pour déterminer le noeud de dépar et le noeud d’arrivée. Si vous connaissez leur identifiant (ceux qui se trouvent dans les champs source et target),; vous pouvez vous en passer.

      1. Dans ma table des tronçons je dispose bien des champs id, geom, source et target qui sont bien nécessaires à pgrouting.
        Ensuite j’ai bien créé une table de vertices avec pgr_createtopology. Je ne comprends donc toujours pas pourquoi j’ai des résultats vides. Par ailleurs à aucun moment dans le code du wrapper vous n’exécutez la fonction pgr_createtopology. La table des vertices qui en résulte est simplement appelé comme ceci

        EXECUTE ‘SELECT id::integer,the_geom::geometry FROM ‘||tbl||’_vertices_pgr
        ORDER BY the_geom ST_GeometryFromText( »POINT(‘
        || x1 || ‘ ‘ || y1 || ‘) »,4326) LIMIT 1’ INTO rec;
        source := rec.id;

        EXECUTE ‘SELECT id::integer,the_geom::geometry FROM ‘||tbl||’_vertices_pgr
        ORDER BY the_geom ST_GeometryFromText( »POINT(‘
        || x2 || ‘ ‘ || y2 || ‘) »,4326) LIMIT 1’ INTO rec;
        target := rec.id;

        On est bien d’accord que là il s’agit d’une concatenation avec le nom qui sera rentré en paramètre.
        En clair dans tous les cas il faut que l’on crée soit meme une table de vertices avant d’éxécuter l’algorithme. quand j’ai essayé d’exécuter l’algo sans avoir créé cette table au préalabe, il m’a dit qu’il ne la trouvait pas.

        Je ne vois donc pas pourquoi créer un wrapper s’il faut encore refaire toutes ces étapes ou est ce une incompréhension de ma part.
        Merci de m’éclairer là dessus

        1. Les tables sont créées une seule fois, à moins que vous ne fassiez des mises à jour. Si vous ajoutez des tronçons, il faut ré-exécuter pgr_topology qui complètera la table des vertices. On a bien deux tables dans la base de données. Le wrapper va interroger ces deux tables. Si le résultat est vide c’est que l’algorithme ne trouve pas d’itinéraire entre les deux points (départ et arrivée). Et ça, ça peut venir de plein de choses.
          De la géométrie de la table des tronçons: Est-ce que vous êtes sûr que les tronçons ont été créés pour une recherche d’itinéraire? Vous êtes sûr que chaque noeud final d’un tronçon correspond à un noeud initial du suivant (avec les mêmes identifiants)? Est-ce qu’il y a un noeud présent à chaque croisement de deux segments?
          Du champ ou de la fonction de coût, de l’utilisation ou non du reverse.cost,… Les choix ne manquent pas! Commencez par vous assurer que la géométrie est bonne pour une recherche d’itinéraire. Prennez deux points pas trop éloignés et suivez l’itinéraire à la min, en recherchant chaque source et target.

          1. Imaginons que je change ma table de tronçons en important réimportant une autre dans ma base de données . suis je obligé de recréer une nouvelle table de vertices avant de relancer mon wrapper?

          2. Vous avez une table de tronçons, c’est à dire des polylignes. Les algorithmes de routage travaillent avec une table de points : chaque ligne de la table a un point source et un point cible (target). L’algorithme prend le point target du premier enregistrement (celui qui a le point source paramétré dans l’appel de l’algorithme) et cherche les enregistrements qui ont cet identifiant comme point source. Il fait ceci jusqu’à trouver le point target paramétré dans l’appel.
            Il a besoin d’une table appropriée à son fonctionnement. La commande pgr_createtopology fabrique cette table à partir d’une table de tronçons. Si vous changez la table de tronçons VOUS DEVEZ créer ou re-créer la table de vertices correspondante, si non il ne pourra pas fonctionner correctement (les tronçons ne correspondront pas aux vertices)

  3. Je tiens à préciser que je ne travaille pas sur la même table que vous et toutes mes tables sont dans le SRID (4326) ainsi que le SRC de mon projet Qgis

    1. Bonjour, je reviens vers vous presque désespéré car toutes mes tentatives pour exécuter le wrapper sont restées infuctueuses.
      Je travaille sur des tronçons de la commune de brignais.
      J’ai essayé d’éxécuter ma requête avec des coordonnées (lat, long) des points qui se trouvent dans ma commune mais toujours rien.
      Quand j’exécute cette requête
      <>
      je n’ai rien qui s’affiche.
      Mêmes avec ces coordonnées qui représentent des points dans zone de travail < >j’ai toujours rien.

      Est ce que c’est mon code qui a un souci. je vous le mets ci-bas pour voir si possible. Merci

      — Function: public.salomon(character varying, numeric,numeric,numeric,numeric,)

      — DROP FUNCTION public.salomon(character varying, numeric,numeric,numeric,numeric,;

      CREATE OR REPLACE FUNCTION public.salomon(
      IN tbl character varying,
      IN x1 numeric,
      IN y1 numeric,
      IN x2 numeric,
      IN y2 numeric,
      OUT seq integer,
      OUT gid integer,
      OUT name text,
      OUT heading double precision,
      OUT cost double precision,
      OUT geom geometry)
      RETURNS SETOF record AS
      $BODY$
      DECLARE
      sql text;
      rec record;
      source integer;
      target integer;
      point integer;

      BEGIN
      — Find nearest node
      EXECUTE ‘SELECT id::integer FROM vertices_tmp
      ORDER BY the_geom ST_GeometryFromText( »POINT(‘
      || x1 || ‘ ‘ || y1 || ‘) »,4326) LIMIT 1’ INTO rec;
      source := rec.id;

      EXECUTE ‘SELECT id::integer FROM vertices_tmp
      ORDER BY the_geom ST_GeometryFromText( »POINT(‘
      || x2 || ‘ ‘ || y2 || ‘) »,4326) LIMIT 1’ INTO rec;
      target := rec.id;

      — Shortest path query (TODO: limit extent by BBOX)
      seq := 0;
      sql := ‘SELECT*
      FROM ‘ ||
      ‘pgr_dijkstra( »SELECT gid as id, source, target, ‘
      || ‘longueur AS cost FROM ‘
      || quote_ident(tbl) ||  »’, ‘
      || source || ‘, ‘ || target
      || ‘ , false) as di
      JOIN ‘|| quote_ident(tbl) ||’ vertices_tmp ON di.edge = vertices_tmp.gid’;

      — Remember start point
      point := source;

      FOR rec IN EXECUTE sql
      LOOP
      — Flip geometry (if required)
      IF ( point != rec.source ) THEN
      rec.the_geom := rec.flip_geom;
      point := rec.source;
      ELSE
      point := rec.target;
      END IF;

      — Calculate heading (simplified)
      EXECUTE ‘SELECT degrees( ST_Azimuth(
      ST_StartPoint( »’ || rec.the_geom::text ||  »’),
      ST_EndPoint( »’ || rec.the_geom::text ||  »’) ) )’
      INTO heading;

      — Return record
      seq := seq + 1;
      gid := rec.gid;
      name := rec.name;
      cost := rec.cost;
      geom := rec.the_geom;
      RETURN NEXT;
      END LOOP;
      RETURN;
      END;
      $BODY$
      LANGUAGE plpgsql VOLATILE STRICT
      COST 100
      ROWS 1000;
      ALTER FUNCTION public.salomon(character varying, double precision, double precision, double precision, double precision)
      OWNER TO postgres;

      1. les  » ‘ || doivent se refermer avec || ‘  » et pas ||  » ‘
        Il y en a plusieurs dans votre code.
        Une fois corrigés, si vous n’avez toujours pas de résultat, il faut tester un par un les selects mais manuellement:
        Par exemple

        EXECUTE ‘SELECT id::integer FROM vertices_tmp
        ORDER BY the_geom ST_GeometryFromText( »POINT(‘
        || x1 || ‘ ‘ || y1 || ‘) »,4326) LIMIT 1’ INTO rec;

        vous devez remplacer les variables entre || par des valeurs et exécuter la requête sql dans une fenêtre sql, exemple
        SELECT id::integer FROM vertices_tmp
        ORDER BY the_geom ST_GeometryFromText( « POINT(1.256 42,678) »,4326) LIMIT 1;

        Vous verrez alors quelle requête ne rend pas de résultat et donc, là où le problème peut être résolu.

        1. En essayant de remplacer les variables par des valeurs j’obtiens le même point qui est renvoyé dans le premier select aussi bien que dans le deuxième Ce qui signifie que mes deux select me renvoient un source égal au target. je comprends donc pourquoi mes résultats sont vides.
          Qu’est ce qui pourrait expliquer cela puisque ma syntaxe est correcte et je n’ai aucune erreur de code. J’ai par ailleurs bien respecter la construction du ST_GeometryFrom text.

          SELECT id::integer FROM rezo_vertices_pgr
          ORDER BY the_geom ST_GeometryFromText(‘POINT(-1.11762 52.93691)’,4326) LIMIT 1;

          Voici le code que j’exécute .
          Quelque soient les coordonnées que je rentre j’ai toujours le meme point qui est renvoyé(le même id)

          1. Cela vient probablement de la table de tronçons et des vertices. Mais impossible de deviner sans les avoir. Est-ce que vous avez moyen de faire un backup de vos deux tables et de me les faire parvenir (dropbox ou autre)?

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *