# [pgrouting-users] Calculating the shortest way between pairs

Stephen Woodbridge woodbri at swoodbridge.com
Fri Mar 14 08:33:08 PDT 2014

```Hi Carsten,

I assume you can make a single pgRouting query from point A to B using
pgr_astar() or pgr_trsp() or something similar.

Work that out first. Read the Documentation and the workshop.

Then you just want to write a simple procedure to query your table and
for each row run the pgr_*() query and do something with the results.

Read these to get a basic understanding ot plpgsql sripting for stored
procedures.

If you table is big it will take a while. Get this working first and
then we can talk about how to speed it up if you need that.

-Steve

On 3/14/2014 11:00 AM, Carsten Hogertz wrote:
> lol. Now we're talking about the same issue!
> I could run n x n queries but this would not be very efficient as I'd
> like to have a more "automatic" solution.
>
> I already thought about function and looping but as being a complete
> newbie on looping and writing functions this seems quite hard to solve
> for myself.
>
> Maybe you've got a hint?
>
> thanks
> carsten
>
>
>
> 2014-03-14 15:53 GMT+01:00 Daniel Kastl <daniel at georepublic.de
> <mailto:daniel at georepublic.de>>:
>
>
>
>
>     On Fri, Mar 14, 2014 at 11:42 PM, Carsten Hogertz
>     <carsten.hogertz at gmail.com <mailto:carsten.hogertz at gmail.com>> wrote:
>
>         Hi Daniel,
>         thanks for your eMail.
>         Well, I think it's not a 1-n calculation. IMHO it's a 1-1
>         calculation,right?
>         So I don't know how to calculate the distance for each row of
>         the routing_pair table.
>
>
>     Well, you need to run this query for each row. So with n rows you
>     need n queries.
>     If you use standard shortest path function you need to run "n x n"
>     queries.
>
>     You may want to write a custom function, which makes first a select
>     of all records in your table and then loops through these records.
>     Or don't I still understand the problem?
>
>     Daniel
>
>
>
>
>         Thanks
>         Carsten
>
>
>         2014-03-14 15:33 GMT+01:00 Daniel Kastl <daniel at georepublic.de
>         <mailto:daniel at georepublic.de>>:
>
>
>
>
>             On Fri, Mar 14, 2014 at 11:26 PM, Carsten Hogertz
>             <carsten.hogertz at gmail.com
>             <mailto:carsten.hogertz at gmail.com>> wrote:
>
>                 Maybe to clearify.
>
>                 I've got the following two tables:
>
>                 Table at_2po_4pgr with the columns:
>
>                    id integer NOT NULL,
>                    osm_id bigint,
>                    osm_name character varying,
>                    osm_meta character varying,
>                    osm_source_id bigint,
>                    osm_target_id bigint,
>                    clazz integer,
>                    flags integer,
>                    source integer,
>                    target integer,
>                    km double precision,
>                    kmh integer,
>                    cost2 double precision,
>                    reverse_cost double precision,
>                    x1 double precision,
>                    y1 double precision,
>                    x2 double precision,
>                    y2 double precision,
>                    geom_way geometry(LineString,4326)
>
>                 and Table routing_pair with the columns:
>
>                    id integer,
>                    h_key integer,
>                    lat_htl double precision,
>                    lon_htl double precision,
>                    source integer,
>                    geom_htl geometry(Point,4326),
>                    lat_bhf double precision,
>                    lon_bhf double precision,
>                    target integer,
>                    geom_bhf geometry(Point,4326)
>
>
>                 The routing_pair table hold three important information:
>                 ID
>                 SOURCE
>                 TARGET
>
>                 All columns are filled (source + target are calculated
>                 from at_2po_4pgr.
>
>                 The task seems to be quite simple: I want for each row
>                 from the routing_pair the distance between soure +
>                 target (and if possible its geom).
>                 Could you please help me how to solve this?
>
>
>             Hi Carsten,
>
>             There is a function named kDijkstra and it allows you to
>             calculate all paths from one node to many:
>             http://docs.pgrouting.org/2.0/en/src/kdijkstra/doc/index.html#pgr-kdijkstra
>
>             If something is not clear with the page above, feel free to ask.
>
>             Daniel
>
>
>
>
>
>             --
>             Georepublic UG & Georepublic Japan
>             eMail: daniel.kastl at georepublic.de
>             <mailto:daniel.kastl at georepublic.de>
>             Web: http://georepublic.info
>
>             _______________________________________________
>             Pgrouting-users mailing list
>             Pgrouting-users at lists.osgeo.org
>             <mailto:Pgrouting-users at lists.osgeo.org>
>             http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>
>
>
>         _______________________________________________
>         Pgrouting-users mailing list
>         Pgrouting-users at lists.osgeo.org
>         <mailto:Pgrouting-users at lists.osgeo.org>
>         http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>
>
>
>
>     --
>     Georepublic UG & Georepublic Japan
>     eMail: daniel.kastl at georepublic.de <mailto:daniel.kastl at georepublic.de>
>     Web: http://georepublic.info
>
>     _______________________________________________
>     Pgrouting-users mailing list
>     Pgrouting-users at lists.osgeo.org <mailto:Pgrouting-users at lists.osgeo.org>
>     http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>
>
>
>
> _______________________________________________
> Pgrouting-users mailing list
> Pgrouting-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>

```