4

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.

  • 1
    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
  • 2
    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
3

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();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.