Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'm trying to reproduce what Underdark did here.
I succesfully installed pgrouting and the two required function (nearest neighbour and driving_distance).
cdv_osm_v contains all the openstreetmap points of my region, from which I select only transportation infrastructure.
taa_2po_4pgr and taa_2po_v contain network and nodes extracted by osm2po for my region.
When I try to run this query in pgAdmin3 I got a syntax error at or near "FOR".
The first select works well, and also driving_distance outputs results fine if called separately.
I'm a noob at postgresql/postgis and I don't know how to pass a geometry to nn() to test it separately (and maybe x,y and factor parameters are not so good).
What am I doing wrong?

CREATE TEMPORARY TABLE nodes_costs (vertex_id integer,edge_id integer,cost double precision);

FOR i IN
  SELECT the_geom
  FROM cdv_osm_v
  WHERE highway like 'bus_stop' OR railway like 'station' or amenity like 'bus_station'
LOOP
  INSERT INTO nodes_cost
  driving_distance(
    'SELECT id, source, target, km AS cost
    FROM taa_2po_4pgr',
    mm(i.the_geom ,0.0001,2,2,'taa_2po_v','id','geom_vertex'), 0.4, false, false);
END LOOP;

SELECT * 
FROM taa_2po_v 
JOIN 
  SELECT vertex_id, min(cost) AS costo
  FROM nodes_costs
ON
taa_2po_v.id = nodes_costs.vertex_id;

I forgot: posgresql 9.1, postgis 2.0.1, pgrouting 1.0.5 from kyngchaos.

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

Ok, I got it!
First thing first, in PostgreSQL I cannot execute inline commands, so I had to write a function.
Then, driving_distance returns a SETOF (namely, a table), so I had to loop through this set and insert the records in my table. I don't know if it could be rewritten in a more efficient way.
Finally, a correction to the output query to make all three fields available.
Here's the result:

CREATE OR REPLACE FUNCTION coverage() RETURNS TABLE(geometria geometry, id_vertice integer, costo_tot double precision) AS $$
DECLARE
  i RECORD;
  dd_record RECORD;
BEGIN
CREATE TEMPORARY TABLE nodes_costs (vertex_id integer,edge_id integer,cost double precision) ON COMMIT DROP;

FOR i IN
  SELECT the_geom FROM cdv_osm_v  WHERE highway like 'bus_stop' OR railway like 'station' or amenity like 'bus_station'
 LOOP
  FOR dd_record IN SELECT * from driving_distance('SELECT id, source, target, km AS cost FROM taa_2po_4pgr', nn(i.the_geom ,0.0001,2,2,'taa_2po_v','id','geom_vertex'), 1, false, false)
  LOOP
     INSERT INTO nodes_costs VALUES ( dd_record.vertex_id,dd_record.edge_id,dd_record.cost);
  END LOOP;
 END LOOP;
 RETURN QUERY SELECT geom_vertex, vertex_id, costo
 FROM taa_2po_v 
 JOIN 
   (SELECT vertex_id, min(cost) AS costo
   FROM nodes_costs GROUP BY vertex_id) AS costs_nodes
 ON
 taa_2po_v.id = costs_nodes.vertex_id;
 END;
$$ LANGUAGE plpgsql;

SELECT * FROM coverage();
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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