I want to loop over the results of a query and then pass
longitude, latitude and
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.