–DROP FUNCTION pgr_route_entreAetB(varchar, double precision, double precision,
\n— double precision, double precision);<\/p>\n

CREATE OR REPLACE FUNCTION pgr_route_entreAetB(
\nIN tbl varchar,
\nIN x1 double precision,
\nIN y1 double precision,
\nIN x2 double precision,
\nIN y2 double precision,
\nOUT seq integer,
\nOUT gid integer,
\nOUT direction double precision,
\nOUT cost double precision,
\nOUT geom geometry)<\/p>\n

RETURNS SETOF record AS<\/p>\n

$BODY$
\nDECLARE
\nsql text;
\nrec record;
\nsource integer;
\ntarget integer;
\npoint integer;
\nbuff double precision;
\ndist double precision;
\nlegid integer;<\/p>\n

BEGIN<\/p>\n

— Trouver le n\u0153ud le plus proche des lat\/lon en entr\u00e9e<\/p>\n

EXECUTE ‘SELECT id::integer, the_geom::geometry FROM ‘ || tbl || ‘_vertices_pgr
\nORDER BY the_geom <->ST_GeometryFromText(\u00a0\u00bbPOINT(‘
\n|| x1 || ‘ ‘ || y1 || ‘)\u00a0\u00bb,4326) LIMIT 1’ INTO rec;
\nsource := rec.id;<\/p>\n

EXECUTE ‘SELECT id::integer, the_geom::geometry FROM ‘ || tbl || ‘_vertices_pgr
\nORDER BY the_geom <->ST_GeometryFromText(\u00a0\u00bbPOINT(‘
\n|| x2 || ‘ ‘ || y2 || ‘)\u00a0\u00bb,4326) LIMIT 1’ INTO rec;
\ntarget := rec.id;<\/p>\n

–D\u00e9finir une sous-zone de travail
\nEXECUTE ‘SELECT ST_Distance(ST_GeomFromText(\u00a0\u00bbPOINT(‘ || x1 ||’ ‘ || y1 ||’)\u00a0\u00bb,4326),’
\n‘ST_GeomFromText(\u00a0\u00bbPOINT(‘ || x2 ||’ ‘ || y2 ||’)\u00a0\u00bb,4326)) as dist’ INTO rec;
\nbuff := GREATEST(rec.dist,0.5);<\/p>\n

–Trouver l’identifiant du tron\u00e7on de d\u00e9part
\nEXECUTE ‘SELECT gid as legid FROM ‘ || tbl || ‘ WHERE source = ‘ || source || ‘ LIMIT 1’ INTO rec;
\nlegid := rec.legid;<\/p>\n

— Calculer l’itin\u00e9raire avec l’algorithme A*<\/p>\n

seq := 0;
\nsql := ‘SELECT gid, the_geom,pgr_astar.cost, source, target, ST_Reverse(the_geom) AS flip_geom
\nFROM ‘
\n‘pgr_astar(\u00a0\u00bbSELECT gid as id, source::int, target::int, ‘
\n‘length_m * ‘ || tbl || ‘.cost as cost, x1, y1, x2, y2 FROM ‘
\n|| quote_ident(tbl) ||
\n‘ WHERE the_geom @ (SELECT ST_buffer(the_geom,’
\n|| buff || ‘) FROM ‘
\n|| quote_ident(tbl) || ‘ WHERE gid=’ || legid || ‘)’
\n\u00a0\u00bb’, ‘
\n|| source || ‘, ‘ || target
\n|| ‘ , false, false), ‘
\n|| quote_ident(tbl) || ‘ WHERE id2 = gid ORDER BY seq’;<\/p>\n

— m\u00e9moriser la point de d\u00e9part<\/p>\n

point := source;<\/p>\n

FOR rec IN EXECUTE sql
\nLOOP<\/p>\n

— Inverser les tron\u00e7ons si n\u00e9cessaire<\/p>\n

IF ( point != rec.source ) THEN
\nrec.the_geom := rec.flip_geom;
\npoint := rec.source;
\nELSE
\npoint := rec.target;
\nEND IF;<\/p>\n

— Calculer le cap<\/p>\n

EXECUTE ‘SELECT degrees( ST_Azimuth(
\nST_StartPoint(\u00a0\u00bb’ || rec.the_geom::text ||’ \u00a0\u00bb),
\nST_EndPoint(\u00a0\u00bb’ || rec.the_geom::text || ‘ \u00a0\u00bb) ) )’
\nINTO direction;<\/p>\n

— Return<\/p>\n

seq := seq + 1;
\ngid := rec.gid;
\ncost := rec.cost;
\ngeom := rec.the_geom;
\nRETURN NEXT;<\/p>\n

END LOOP;
\nRETURN;
\nEND;
\n$BODY$
\nLANGUAGE ‘plpgsql’ VOLATILE STRICT;<\/p>\n

<\/div><\/div>
\nVoyons maintenant qu’est-ce que cette fonction fait.<\/p>\n

Param\u00e9trage de la fonction<\/h2>\n
Tout d’abord nous avons le param\u00e9trage de la fonction:
\nCREATE OR REPLACE FUNCTION pgr_route_entreAetB(<\/em>
\n IN tbl varchar,<\/em>
\n IN x1 double precision,<\/em>
\n IN y1 double precision,<\/em>
\n IN x2 double precision,<\/em>
\n IN y2 double precision,<\/em>
\n OUT seq integer,<\/em>
\n OUT gid integer,<\/em>
\n OUT direction double precision,<\/em>
\n OUT cost double precision,<\/em>
\n OUT geom geometry)<\/em><\/p>\n
Nous d\u00e9finissons 5 param\u00e8tres en entr\u00e9e: la table contenant le r\u00e9seau et les coordonn\u00e9es du point de d\u00e9part et d’arriv\u00e9e.
\nNous d\u00e9finissons aussi les param\u00e8tres en sortie : une s\u00e9rie de tron\u00e7ons comportant un num\u00e9ro d’ordre (seq), l’identifiant du tron\u00e7on (gid), la direction g\u00e9ographique ou cap de ce tron\u00e7on (direction), le co\u00fbt associ\u00e9 au tron\u00e7on et la g\u00e9om\u00e9trie de celui-ci pour pouvoir le dessiner sur la carte r\u00e9sultat.<\/p>\n

Pr\u00e9-traitement des param\u00e8tres<\/h2>\nEnsuite, nous devons pr\u00e9parer les param\u00e8tres de notre algorithme. Ici nous allons utiliser l’algorithme a-star<\/a> de pgrouting.<\/p>\n
EXECUTE ‘SELECT id::integer, the_geom::geometry FROM ‘ || tbl ||’_vertices_pgr<\/em>
\n ORDER BY the_geom ST_GeometryFromText(\u00a0\u00bbPOINT(‘<\/em>
\n || x1 || ‘ ‘ || y1 || ‘)\u00a0\u00bb,4326) LIMIT 1’ INTO rec;<\/em>
\n source := rec.id;<\/em><\/p>\n
EXECUTE ‘SELECT id::integer, the_geom::geometry FROM ‘ || tbl || ‘_vertices_pgr<\/em>
\n ORDER BY the_geom ST_GeometryFromText(\u00a0\u00bbPOINT(‘<\/em>
\n || x2 || ‘ ‘ || y2 || ‘)\u00a0\u00bb,4326) LIMIT 1’ INTO rec;<\/em>
\n target := rec.id;<\/em><\/p>\n
Ces deux paragraphes recherchent les identifiants des deux points de la table de n\u0153uds de notre r\u00e9seau les plus proches des coordonn\u00e9es en entr\u00e9e.
\nLes valeurs entre || sont remplac\u00e9es lors de l’ex\u00e9cution par les param\u00e8tres en entr\u00e9e de la fonction.<\/p>\n
–D\u00e9finir une sous-zone de travail<\/em>
\n EXECUTE ‘SELECT ST_Distance(ST_GeomFromText(\u00a0\u00bbPOINT(‘ || x1 ||’ ‘ || y1 ||’)\u00a0\u00bb,4326),’<\/em>
\n ‘ST_GeomFromText(\u00a0\u00bbPOINT(‘ || x2 ||’ ‘ || y2 ||’)\u00a0\u00bb,4326)) as dist’ INTO rec;<\/em>
\n buff := GREATEST(rec.dist,0.5);<\/em><\/p>\n
Pour optimiser la recherche d’itin\u00e9raires, il est fortement recommand\u00e9 de limiter a recherche \u00e0 une sous-zone (bbox) de notre r\u00e9seau. Ce paragraphe calcule la distance entre le point de d\u00e9part et d’arriv\u00e9e et d\u00e9finit une zone de recherche une fois et demie plus grande que cette distance. Les tron\u00e7ons situ\u00e9s en dehors de cette zone ne seront pas pris en compte pour la recherche d’itin\u00e9raires possibles.\u00a0 Ici nous avons utilis\u00e9 la fonction GREATEST qui permet de d\u00e9finir une sous-zone minimale : si la distance \u00e0 vol d’oiseau est inf\u00e9rieure \u00e0 0,5\u00b0 on prend par d\u00e9faut 0,5\u00b0 comme sous-zone. On aurait pu d\u00e9finir un facteur comme:<\/p>\n
buff :=rec.dist * 1.5
\n<\/em><\/p>\n
Il est clair que ce facteur (1.5) doit \u00eatre d\u00e9cid\u00e9 en connaissance de cause, si non il peut amener la fonction \u00e0 ne pas trouver de solution. Par exemple, si vous avez deux vall\u00e9es s\u00e9par\u00e9es par une montagne sans routes qui la traversent, la distance calcul\u00e9e ici \u00e9tant \u00e0 vol d’oiseau, elle peut ne pas inclure le col le plus proche.<\/p>\n
–Trouver l’identifiant du tron\u00e7on de d\u00e9part<\/em>
\n EXECUTE ‘SELECT gid as legid FROM ‘ || tbl || ‘ WHERE source = ‘ || source || ‘ LIMIT 1’ INTO rec;<\/em>
\n legid := rec.legid;<\/em><\/p>\n
Pour exclure les tron\u00e7ons trop \u00e9loign\u00e9s nous allons utiliser la fonction ST_buffer<\/strong><\/em> appliqu\u00e9e \u00e0 partir du tron\u00e7on de d\u00e9part. Ils nous faut alors trouver l’identifiant de ce tron\u00e7on, car pour l’instant nous n’avons que les identifiants de la table des n\u0153uds.<\/p>\n
Nous pouvons maintenant ex\u00e9cuter l’algorithme :<\/p>\n
sql := ‘SELECT gid, the_geom,pgr_astar.cost, source, target, ST_Reverse(the_geom) AS flip_geom<\/em>
\n FROM ‘<\/em>
\n ‘pgr_astar(\u00a0\u00bbSELECT gid as id, source::int, target::int, ‘<\/em>
\n ‘length_m * ‘ || tbl || ‘.cost as cost, x1, y1, x2, y2 FROM ‘<\/em>
\n || quote_ident(tbl) ||<\/em>
\n ‘ WHERE the_geom @ (SELECT ST_buffer(the_geom,’<\/em>
\n || buff || ‘) FROM ‘<\/em>
\n || quote_ident(tbl) || ‘ WHERE gid=’ || legid || ‘)’<\/em>
\n \u00a0\u00bb’, ‘<\/em>
\n || source || ‘, ‘ || target<\/em>
\n || ‘ , false, false), ‘<\/em>
\n || quote_ident(tbl) || ‘ WHERE id2 = gid ORDER BY seq’;<\/em><\/p>\n
Le co\u00fbt utilis\u00e9 ici est la longueur en m\u00e8tres du tron\u00e7on multipli\u00e9 par la colonne \u00ab\u00a0cost\u00a0\u00bb de la table. La fonction ST_buffer limite le nombre de tron\u00e7ons de la recherche \u00e0 ceux situ\u00e9s dans la zone d\u00e9finie lors de la g\u00e9n\u00e9ration de la variable buff<\/strong><\/em>.<\/p>\n
La suite, inverser le tron\u00e7on et calculer le cap vont ensemble. Ils sont ici pour montrer un exemple de calcul non pr\u00e9vu dans l’algorithme et comment l’int\u00e9grer dans le wrapper. Si vous n’en voyez pas l’utilit\u00e9, les effacer du wrapper n’aura aucun effet sur l’itin\u00e9raire r\u00e9sultant. L’inversion du tron\u00e7on est n\u00e9cessaire pour calculer la v\u00e9ritable direction dans le cas o\u00f9 le tron\u00e7on est emprunt\u00e9 en sens inverse (du point target vers le point source). Ceci explique la partie \u00ab\u00a0ST_Reverse(the_geom) AS flip_geom<\/em>\u00a0\u00bb dans la clause SELECT.<\/p>\n
Ajout du wrapper \u00e0 la base postgres<\/h2>\n
Pour pouvoir int\u00e9grer cette nouvelle fonction dans votre base PostgreSQL, simplement copiez le texte du wrapper dans une fen\u00eatre SQL et ex\u00e9cutez la requ\u00eate:<\/p>\n
$\"ajout$ <\/a>La nouvelle fonction est maintenant dans la liste des fonctions de votre base.<\/p>\n
Nous pouvons maintenant la tester avec QGis en utilisant la fen\u00eatre de gestion de base de donn\u00e9es.<\/p>\n
$\"w2\"$ <\/a>Nous rentrons le nom de la table contenant notre r\u00e9seau, et les coordonn\u00e9es des points de d\u00e9part et d’arriv\u00e9e.<\/p>\n
Une fois ex\u00e9cut\u00e9e, la requ\u00eate renvoie la liste des tron\u00e7ons de l’itin\u00e9raire. En cliquant sur le bouton \u00ab\u00a0Charger<\/strong>\u00a0\u00bb nous pouvons voir l’affichage de l’itin\u00e9raire (sur fond d’OpensStreetMap):<\/p>\n
$\"itin\u00e9raire$ <\/a>Nous pouvons aussi tester une requ\u00eate un peu plus complexe, pour n’obtenir qu’une seule entit\u00e9 en r\u00e9sultat:<\/p>\n
$\"w4\"$ <\/a><\/p>\n
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<\/em><\/p>\n
Cette requ\u00eate prend la liste des tron\u00e7ons r\u00e9sultant de la fonction pgr_route_entreAetB et fabrique une seule entit\u00e9 MultiLigne. Remarquez le nom de la colonne g\u00e9om\u00e9trie g\u00e9n\u00e9r\u00e9e.<\/p>\n
Si nous chargeons cette entit\u00e9 dans QGis nous remarquerons que visuellement le r\u00e9sultat est identique. Par contre la quantit\u00e9 d’information retourn\u00e9 est optimis\u00e9e.<\/p>\n
$\"r\u00e9sultat$ <\/a>Nous garderons cette derni\u00e8re requ\u00eate pour l’utiliser lors de la mise en place de Geoserver, ce que nous ferons dans le prochain article.<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Un wrapper est une fonction de PostgreSQL comme une autre, qui contient des fonctionnalit\u00e9s de pgRouting et PostGIS sous une forme plus facile \u00e0 utiliser que lors de l’appel des fonctions pgRouting et PostGIS directement. Les…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"give_campaign_id":0,"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"sfsi_plus_gutenberg_text_before_share":"","sfsi_plus_gutenberg_show_text_before_share":"","sfsi_plus_gutenberg_icon_type":"","sfsi_plus_gutenberg_icon_alignemt":"","sfsi_plus_gutenburg_max_per_row":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[311,62],"tags":[336,326,227,312,58,335],"class_list":["post-3325","post","type-post","status-publish","format-standard","hentry","category-postgresql","category-qgis-2","tag-fonction","tag-pgrouting","tag-postgis","tag-postgresql","tag-qgis","tag-wrapper"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p6XU0A-RD","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/3325","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/comments?post=3325"}],"version-history":[{"count":0,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/3325\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/media?parent=3325"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/categories?post=3325"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/tags?post=3325"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}