[GIS] How to loop in PostGIS to add the next lat long to a route

looppgroutingpostgispostgresqlsql

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.

Best Answer

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