[pgrouting-users] create geometry table with the result

pgRouting pgrouting-users
1

Hello to all,

Can anybody help me to solve this problem:

I want to create a geometry table with the result of this query:

SELECT * FROM shortest_path
('SELECT gid AS id,
source::int4 AS source,
target::int4 AS target,
lenght::float8 AS cost
FROM passeios',
438,
439,
false,
false)

someone know how I do that?
thanks

2

Hi,

Unless I’m missing something, you just need to change the first line to

SELECT * INTO new_table FROM shortest_path( … etc.

James

On 16 Mar 2012 00:14, “Pedro Costa” <pedrocostaarma@sapo.pt> wrote:

Hello to all,

Can anybody help me to solve this problem:

I want to create a geometry table with the result of this query:

SELECT * FROM shortest_path
(‘SELECT gid AS id,
source::int4 AS source,
target::int4 AS target,
lenght::float8 AS cost
FROM passeios’,
438,
439,
false,
false)

someone know how I do that?
thanks

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

3

also, if you only need a temporary table, you can use:

CREATE TABLE [options, such as temporary] table_name> as SELECT * FROM . . . .

On Thu, Mar 15, 2012 at 5:22 PM, James David Smith
<james.david.smith@gmail.com> wrote:

Hi,

Unless I'm missing something, you just need to change the first line to

SELECT * INTO new_table FROM shortest_path( ...... etc.

James

On 16 Mar 2012 00:14, "Pedro Costa" <pedrocostaarma@sapo.pt> wrote:

Hello to all,

Can anybody help me to solve this problem:

I want to create a geometry table with the result of this query:

SELECT * FROM shortest_path
('SELECT gid AS id,
source::int4 AS source,
target::int4 AS target,
lenght::float8 AS cost
FROM passeios',
438,
439,
false,
false)

someone know how I do that?
thanks
_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

4

Thanks to Talin and James but the problem is that the new table has no geometry. My difficulty is to give the table geometry. How I do that?

thanks

5

Hi Pedro,

The “core” shortest path function only returns a list of edge id’s with costs.
To include geometry information you need to link again to the original table or just use one of the wrapper functions:
http://workshop.pgrouting.org/chapters/shortest_path.html#wrapper

Daniel

On Fri, Mar 16, 2012 at 7:10 PM, Pedro Costa <pedrocostaarma@sapo.pt> wrote:

Thanks to Talin and James but the problem is that the new table has no geometry. My difficulty is to give the table geometry. How I do that?

thanks

On 16-03-2012 00:22, James David Smith wrote:

Hi,

Unless I’m missing something, you just need to change the first line to

SELECT * INTO new_table FROM shortest_path( … etc.

James

On 16 Mar 2012 00:14, “Pedro Costa” <pedrocostaarma@sapo.pt> wrote:

Hello to all,

Can anybody help me to solve this problem:

I want to create a geometry table with the result of this query:

SELECT * FROM shortest_path
(‘SELECT gid AS id,
source::int4 AS source,
target::int4 AS target,
lenght::float8 AS cost
FROM passeios’,
438,
439,
false,
false)

someone know how I do that?
thanks

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

_______________________________________________
Pgrouting-users mailing list
[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@lists.osgeo.org)
[http://lists.osgeo.org/mailman/listinfo/pgrouting-users](http://lists.osgeo.org/mailman/listinfo/pgrouting-users)

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Georepublic UG & Georepublic Japan
eMail: daniel.kastl@georepublic.de
Web: http://georepublic.de

6

Pedro,

Daniel is right. This should do it?

SELECT * INTO new_table
   FROM passeios
   JOIN
   (SELECT * FROM shortest_path('
      SELECT gid AS id,
          start_id::int4 AS source,
          end_id::int4 AS target,
          lenght::float8 AS cost
      FROM passeios',
      438,
      439,
      false,
      false)) AS route
   ON
   network.gid = route.edge_id;

James

On 16 March 2012 10:16, Daniel Kastl <daniel@georepublic.de> wrote:

Hi Pedro,

The "core" shortest path function only returns a list of edge id's with
costs.
To include geometry information you need to link again to the original table
or just use one of the wrapper functions:
http://workshop.pgrouting.org/chapters/shortest_path.html#wrapper

Daniel

On Fri, Mar 16, 2012 at 7:10 PM, Pedro Costa <pedrocostaarma@sapo.pt> wrote:

Thanks to Talin and James but the problem is that the new table has no
geometry. My difficulty is to give the table geometry. How I do that?

thanks

On 16-03-2012 00:22, James David Smith wrote:

Hi,

Unless I'm missing something, you just need to change the first line to

SELECT * INTO new_table FROM shortest_path( ...... etc.

James

On 16 Mar 2012 00:14, "Pedro Costa" <pedrocostaarma@sapo.pt> wrote:

Hello to all,

Can anybody help me to solve this problem:

I want to create a geometry table with the result of this query:

SELECT * FROM shortest_path
('SELECT gid AS id,
source::int4 AS source,
target::int4 AS target,
lenght::float8 AS cost
FROM passeios',
438,
439,
false,
false)

someone know how I do that?
thanks
_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

--
Georepublic UG & Georepublic Japan
eMail: daniel.kastl@georepublic.de
Web: http://georepublic.de

_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

7

Great James. Works :slight_smile:

I'm taking the first steps with pg_routing.
I'll use it for my dissertation.

thanks

On 16-03-2012 10:24, James David Smith wrote:

Pedro,

Daniel is right. This should do it?

SELECT * INTO new_table
    FROM passeios
    JOIN
    (SELECT * FROM shortest_path('
       SELECT gid AS id,
           start_id::int4 AS source,
           end_id::int4 AS target,
           lenght::float8 AS cost
       FROM passeios',
       438,
       439,
       false,
       false)) AS route
    ON
    network.gid = route.edge_id;

James

On 16 March 2012 10:16, Daniel Kastl<daniel@georepublic.de> wrote:

Hi Pedro,

The "core" shortest path function only returns a list of edge id's with
costs.
To include geometry information you need to link again to the original table
or just use one of the wrapper functions:
http://workshop.pgrouting.org/chapters/shortest_path.html#wrapper

Daniel

On Fri, Mar 16, 2012 at 7:10 PM, Pedro Costa<pedrocostaarma@sapo.pt> wrote:

Thanks to Talin and James but the problem is that the new table has no
geometry. My difficulty is to give the table geometry. How I do that?

thanks

On 16-03-2012 00:22, James David Smith wrote:

Hi,

Unless I'm missing something, you just need to change the first line to

SELECT * INTO new_table FROM shortest_path( ...... etc.

James

On 16 Mar 2012 00:14, "Pedro Costa"<pedrocostaarma@sapo.pt> wrote:

Hello to all,

Can anybody help me to solve this problem:

I want to create a geometry table with the result of this query:

SELECT * FROM shortest_path
('SELECT gid AS id,
source::int4 AS source,
target::int4 AS target,
lenght::float8 AS cost
FROM passeios',
438,
439,
false,
false)

someone know how I do that?
thanks
_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

--
Georepublic UG& Georepublic Japan
eMail: daniel.kastl@georepublic.de
Web: http://georepublic.de

_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

8

Thanks Daniel, I understand now…:slight_smile: