# How to loop in PostGIS to add the next lat long to a route

I want to loop over the results of a query and then pass `row(n)`'s `longitude, latitude` and `row(n+1)`'s `longitude and latitude` to another function.

I have a query which returns a set of coordinates from a way-point table with no geometry field.

``````SELECT longitude, latitude FROM way-points ORDER BY date
``````

I am also using the pgRouting extension and I have replicated the `pgr_fromAtoB` function to allow me to generate a route between two coordinates.

Essentially, I have a table of waypoints (GPS lat/lon) and a separate table of roads. I need to get each GPS lat/lon, then get the road (or roads) that make up the route to the next waypoint.

My idea is for each waypoint pair (row(n) origin, row(n+1) destination) in the waypoint query, call the pg_Routing function to find the nearest road vertices's (NNS), then route between the two.

All I have to do is pass the input: `x1,y1,x2,y2` and I get back the list of geometries.

``````SELECT geom FROM pgr_fromAtoB('road_table',-122.662,45.528,-122.684,45.514);
``````

Ideally I would like to have this automated so that instead of specifying the values of `x1,y1,x2,y2` I can just use a SELECT query that returns the coordinates.

So maybe something like:

``````BEGIN
-- Generate x1, y1, x2, y2

-- Set origin and remember start point
x1 := 0;
y1 := 0;

FOR rec IN EXECUTE 'SELECT * FROM waypoints WHERE user_id = 5 ORDER BY waypoint_timestamp'
LOOP
-- first iteration sets x1, y1
IF ( x1 = 0 AND y1 = 0 ) THEN
x1 := rec.longitude;
y1 := rec.latitude;
geom1 := rec.geom;
ELSE
-- If the origin and destination geom are the same don't route
IF geom1 = rec.geom THEN

ELSE
-- geoms are different so furuther iterations set x2, y2
x2 := rec.longitude;
y2 := rec.latitude;
-- call the pgr_fromAtoB function (road table hard coded)
EXECUTE 'SELECT ST_Union(f.geom) FROM pgr_fromatob(''roads''' || ',' || x1 || ',' || y1 || ',' || x2 || ',' || y2 || ') AS f' INTO geom;
-- now set our new start point equal to the previous destination
x1 := x2;
y1 := y2;
END IF;

RETURN NEXT;
END IF;
END LOOP;
RETURN;
END;
``````

Do you know how to write loops in SQL and can help me refactor this into something that actually performs well in PostGIS?

Perhaps you know a faster way to do this without loops. Performance is my biggest consideration.

• Are the lat/lon values also coming from road_table, ie, do you want a route from all points to all other points. There are other ways to set this up without using a plpgsql loop, which will probably be more efficient. If you want to do each row to each row +1, you can write something like `with offseter (id1, id2, geom1, geom2) as (select t.id, t1.id, t.geom, t1.geom from points t inner join points t1 on t.id = t1.id + 1)`, pass this to pgr_fromAtoB and use the result in an update query. I didn't post an answer, because I'm not totally clear on your set up, but I can if you clarify. – John Powell May 12 '16 at 9:40
• Just to be clear, the loop approach will work, but will likely be much slower. – John Powell May 12 '16 at 9:41
• What do you think about lead window function to get next row @John Barça ? – Jose Hermosilla Rodrigo May 12 '16 at 10:40
• @josehermosillarodrigo, yeah, lead is essentially the same as what I'm suggesting, offset by one, but more sophisticated. thanks for that :D – John Powell May 12 '16 at 10:55
• @JohnBarça my lat/lon vales are coming from a non-spatial table (i.e. no point geometry). – jase81 May 12 '16 at 21:01

You can do your own function in PostgreSQL like this (Example taken from the docs) :

``````CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');

CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS
\$BODY\$
DECLARE
r foo%rowtype;
BEGIN
FOR r IN
SELECT * FROM foo WHERE fooid > 0
LOOP
-- can do some processing here
RETURN NEXT r; -- return current row of SELECT
END LOOP;
RETURN;
END
\$BODY\$
LANGUAGE plpgsql;

SELECT * FROM get_all_foo();
``````