0

Pgr_drivingdistance function works as :

SELECT seq, id1 AS node, cost
    FROM pgr_drivingDistance(
            'SELECT id, source, target, cost FROM edge_table',
            id, distance, false, false
    );

How could I get this to work substituting x, y for id?

SELECT seq, id1 AS node, cost
    FROM pgr_drivingDistance(
            'SELECT id, source, target, cost FROM edge_table',
            x,y, distance, false, false
    );

Checking the pgrouting functions in Postgis I have found SQL code to change but not for pgr_drivingdistance.

  • I 'm sorry it took to answer. Thanks for your help. I'll work under your recommendation. – user19566 Feb 5 '15 at 8:09
  • I don't think you can pass x/y into the driving distance function. You would have to join the results back onto the original table to get the x and y. – John Powell Feb 5 '15 at 10:31
  • I tried the following code: SELECT id,seq, id1 AS node,ST_X(the_geom) AS x, ST_Y(the_geom) AS y,id2 AS edge, cost, the_geom FROM pgr_drivingdistance('SELECT id, source, target, time_4 as cost FROM jaen',1493, 5, false, false)as di JOIN jaen_vertices_pgr pt ON di.id1 = pt.id;and the result show x y fields for each node.Now i am trying to use EXECUTE 'SELECT id::integer FROM jaen_vertices_pgr ORDER BY the_geom <-> ST_GeometryFromText(''POINT('||x||'' ||y||')'',4326) LIMIT 1' INTO rec; source := rec.id; from pgr_fromatob function and change the pgr_dijkstra function to pgr_drivingdistance. – user19566 Feb 5 '15 at 11:10
  • So, you are getting the x, y fields, you want? There was an error in original post, you need to join id1 against source, I believe. – John Powell Feb 5 '15 at 11:26
  • Thanks, it works. The final objetive is to write sql code into geoserver id and pass the id as x y parameters so when an user click on map ( leaflet ) a polygon is drawn using pgr_pointsaspolygon. These points are generated from pgr_drivingdistance.I thing pgr_drivingdistance function is fairly accurate to calculating areas of influence. – user19566 Feb 5 '15 at 11:48
1

If you look at the source declaration of pgr_drivingDistance you will see that it is linking to a C function, driving_distance. So unless you want to change the underlying C function, and recompile, you are stuck, as this is not one of those plpgsql functions that you can change directly, unlike say [pgr_pointsAsPolygon]((https://github.com/pgRouting/pgrouting/blob/master/src/driving_distance/sql/routing_dd.sql) from the same source, which you could change directly.

However, you can join the returned id on the original table, to return the x, y values easily enough.

SELECT seq, id1 AS node, edge.x, edge.y, cost
FROM pgr_drivingDistance(
        'SELECT id, source, target, cost FROM edge_table',
        id, distance, false, false
) 
as dist
INNER JOIN 
edge_table edge on dist.id1 = edge.source;
|improve this answer|||||
  • Does this answer make sense? You're joining edge ID's with node ID's. Node ID's in your network table are either source or target ID's. – dkastl Feb 5 '15 at 0:05
  • Well, you cannot assume that the dist.id1 is source. It could be also target or it could be even both. I think your answer is still not correct. It would be better to use the vertices table, which you can create, if it doesn't exist yet: docs.pgrouting.org/2.0/en/src/common/doc/functions/… – dkastl Feb 5 '15 at 14:52
  • Hello.Finally i have used the next function that provide the id parameter from mark coordinates: CREATE OR REPLACE FUNCTION pgr_nearest_node(IN tbl character varying, IN x1 double precision, IN y1 double precision, OUT source integer,OUT geom Geometry) RETURNS SETOF record AS $BODY$ DECLARE source integer; BEGIN RETURN QUERY -- Find nearest node SELECT id,the_geom FROM jaen_vertices_pgr ORDER BY the_geom <-> ST_GeometryFromText('POINT( ' || x1 || ' ' || y1 ||' )',4326) LIMIT 1 ; END; $BODY$ LANGUAGE plpgsql VOLATILE STRICT; Thank you very much for your help. – user19566 Feb 9 '15 at 18:51

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.