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