Blog GIS & Territories

A wrapper is a PostgreSQL function that contains pgRouting and PostGIS features easier to use than pgRouting and PostGIS functions straightaway.
The pgRouting native functions have been designed to be generic so they can be used in a wide range of applications . Although this offers flexibility, the disadvantage is that, most likely, you will have to reshape, temporarily, your own data structure before you applying pgRouting functions
Writing a wrapper function adapted to your data structure decreases the need to go through cumbersome SQL queries .
Another reason to use the wrapper function is to bypass the SQL injection attacks. Building a functional barrier between the  user’s input and the function of native pgRouting native functions will allow you clear the user’s input .

PostgreSQL supports many programming languages for writing functions, but SQL and PL / pgSQL are the most common . We will discuss an example with PL / pgSQL .

Until now, in our series of articles , we have restricted ourselves to the network data level, but the final goal is to set up a route search web application. In this case , the idea is to introduce to the user a map , and let him define a starting and an ending point. Among the many possibilities , let’s remember the simplest: a click on the map to define the starting point and another click to set the finish point .

Our web page submits two pairs of xy coordinates and it is expected to retrieve the advised itinerary connecting these two geographical points .

If you have followed the series of articles , you can measure the distance between what the web page results and the itinerary searches we have discussed, where we have to re-enter as parameters the starting and end nodes identifiers, as well as other settings according to the algorithm used .

Let’s not forget that between our web page and our Postgresql / Postgis database we will, also, have Geoserver . A possibility is to write a complex query in Geoserver , but we have a much simpler and more effective solution by using a wrapper in PostgreSQL.

This wrapper, which constitutes a new function that we will add in our Postgres database, will translate the xy transmitted by the web page in usable parameters by our route search algorithm, optimize its execution , and format the result for displaying in the web page.

The code being used is a variation of an example found in different sites dealing with pgrouting under the name pgr_fromAtoB .

Wrapper text

–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;

Let’s see how this function works .

Setting up the function

First of all we have to set up of the function :
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)

We define 5 input parameters: the table containing the network and the coordinates of the starting and arrival point.
We, also,  define the output parameters: a series of sections having a sequence number (seq ), the section identifier ( gid ), the geographical direction or section cap (direction), the cost associated with the section and its geometry in order to be able to draw it in the resulting map.

Pre-processing of parameters

Now we have to prepare the parameters of our algorithm . Here we will use the a-star pgrouting algorithm .

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;

These two paragraphs search the identifiers of the two nearest  points  to the input coordinates of the node table of our network.
Values between || are replaced when running with the input parameters of the function .

– Define a working sub- area
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);

To optimize the routes search, it is strongly recommended to limit the search to a sub-area ( bbox ) of our network . This paragraph calculates the distance between the departure and arrival points and defines a search area one and a half times greater than that distance. The sections located outside this area will not be taken in account by the possible route search.   In this case, we have used the GREATEST function which allows to define a minimum subfield : if, at a glance, the distance is less than 0.5 °, by default a 0.5 ° subarea is considered. We could have defined a factor such as :

buff: = rec.dist * 1.5

It is obvious that this factor (1.5) must be decided knowingly , if not it can preclude the function to find any solution. For example , if you have two valleys separated by a mountain without crossing roads, the calculated distance at a glance , cannot include the nearest path .

– Find the identifier of the starting section
EXECUTE ‘SELECT gid as legid FROM’ || tbl || ‘WHERE source =’ || source || ‘LIMIT 1’ INTO rec;
legid : = rec.legid ;

To exclude the very distant sections, we will use the ST_Buffer function applied from the starting section.  Then, we must find the identifier of this section , since for the time being,  we have just the identifiers of the nodes table.

Now, we can execute the algorithm :

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 ‘;

The cost in use right here is the length in meters of the section multiplied by the column ”   cost   ” of the table. The function ST_Buffer limits the number of sections of the search to those located in the defined area upon generation of the buff variable.

Next, reverse the section and calculate the direction all together. They are here to show an example of unplanned calculation in the algorithm and how to integrate it in the wrapper. If you do not find it useful , erase them from the wrapper, it will not have an effect on the resulting  route. The inversion of the section is necessary to calculate the true direction in the case where the section is taken in opposite direction (from the target point to the source point). This explains the section ”   S T_Reverse (the_geom) AS flip_geom   ” in the SELECT clause.

How to add the wrapper to the postgres database To be able to include this new function in your PostgreSQL database, simply copy the wrapper text into a SQL window and run the query :   

The new function is, now, in the list of functions of your database. Now we can test the function with QGis by using the database management window.

We enter the name of the table containing our network , and the coordinates of the departure and arrival points. Once executed , the query returns the list of sections of the route . By clicking the button “Load”  we can see the route displayed (against the backdrop of OpensStreetMap):

We can also test a request a little more complex , to obtain just one entity as result :

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

This request uses the sections list resulting from the function pgr_route_entreAetB and builds an unique entity MultiLine . Notice the name of the generated geometry column. If we load this entity in QGis we will notice that, visually, the result is identical . Nevertheless,  the quantity of information returned is optimized .  

We will keep this last request to use it when formatting  Geoserver , what we will do in the following article.

Si cet article vous a intéressé et que vous pensez qu'il pourrait bénéficier à d'autres personnes, n'hésitez pas à le partager sur vos réseaux sociaux en utilisant les boutons ci-dessous. Votre partage est apprécié !

Atilio Francois