[pgrouting-users] Pgrouting-users Digest, Vol 57, Issue 12

pgRouting pgrouting-users
1

Steve: You're amazing! I installed "postgresql-server-dev-all" after a search for "postgresql-dev" in Synaptic and it then installed perfectly.

THANKS!
Nate

On 06/29/2013 03:00 PM, pgrouting-users-request@lists.osgeo.org wrote:

Send Pgrouting-users mailing list submissions to
  pgrouting-users@lists.osgeo.org

To subscribe or unsubscribe via the World Wide Web, visit
  http://lists.osgeo.org/mailman/listinfo/pgrouting-users
or, via email, send a message with subject or body 'help' to
  pgrouting-users-request@lists.osgeo.org

You can reach the person managing the list at
  pgrouting-users-owner@lists.osgeo.org

When replying, please edit your Subject line so it is more specific
than "Re: Contents of Pgrouting-users digest..."

Today's Topics:

    1. Re: TSP Euclidean distance function (Stephen Woodbridge)
    2. PgRouting fails to compile on Ubuntu 13.04 (Nate Wessel)
    3. Re: PgRouting fails to compile on Ubuntu 13.04
       (Stephen Woodbridge)
    4. Re: PgRouting fails to compile on Ubuntu 13.04
       (Stephen Woodbridge)

----------------------------------------------------------------------

Message: 1
Date: Fri, 28 Jun 2013 21:03:00 -0400
From: Stephen Woodbridge <woodbri@swoodbridge.com>
To: pgrouting-users@lists.osgeo.org
Subject: Re: [pgrouting-users] TSP Euclidean distance function
Message-ID: <51CE3244.9090505@swoodbridge.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

Here is an interesting way deal with this problem:

create or replace function pgr_makeDistanceMatrix(sqlin text, OUT
dmatrix double precision, OUT ids integer)
    as
$body$
declare
      sql text;
      r record;

begin
      dmatrix := array::double precision;
      ids := array::integer;

      sql := 'with nodes as (' || sqlin || ')
          select i, array_agg(dist) as arow from (
              select a.id as i, b.id as j, st_distance(st_makepoint(a.x,
a.y), st_makepoint(b.x, b.y)) as dist
                from nodes a, nodes b
               order by a.id, b.id
             ) as foo group by i order by i';

      for r in execute sql loop
          dmatrix := array_cat(dmatrix, array[r.arow]);
          ids := ids || array[r.i];
      end loop;

end;
$body$
language plpgsql stable cost 10;

with dm as (
      select * from pgr_makeDistanceMatrix(
          'select source_id as id, x, y
             from tsp_00
            where source_id in (1,7,16,3,5)')
),
    ids as (
      select (row_number() over (order by id asc))-1 as rnum, id
        from (
              select unnest(ids) as id
                from dm
              ) foo
)
select a.seq, b.rnum, b.id, d.dmatrix[a.seq+1][b.rnum+1] as dist
    from pgr_tsp(
                 (select dmatrix from dm),
                 (select rnum from ids where id=7)::integer
         ) a,
         ids b,
         dm d
   where a.id=b.rnum;

i; j; id; cost
0; 3; 7; 6
1; 0; 1; 2.23606797749979
2; 1; 3; 3
3; 2; 5; 4.47213595499958
4; 4; 16; 0 <<-- this line seems to be bad see below

Since it took me all afternoon and evening to figure this out I will
leave that as an exercise for the reader, but feel free to ask questions.

I think the whole second query can be wrapped in a stored procedure
where it takes sql string the gets dropped into the dm CTE and returns
the results.

I think there is a bug in my logic somewhere because I always seem to
get back one row there the seq=rnum and the cost is zero so this not is
not linked to the path.

But my brain is too fried to make sense of this anymore tonight.

Thoughts,
    -Steve

On 6/28/2013 10:17 AM, Stephen Woodbridge wrote:

Hi All,

I'm trying to understand how the old TSP code was supposed to work, as
it is not making any sense to me. So the signature is:

select * from pgr_tsp(sql text, ids text, start_id integer, end_id
integer);

sql - sql that selects some number of point that have id, x, and y, ie:
'select id, x, y from table'
ids - is a comma separated list of ids, :ie: '3,27,44,6'
start_id - must be one of the ids in the ids list
end_id - [optional] will be the end of the path if passed in, otherwise
assumes a loop. must be in ids

Currently 'sql' can select an arbitrary list of nodes greater than the
list of ids. This does not make any sense to me and the code behaves
badly in this case. For example if you select 100 points in the sql and
only have 5 in the ids list what does this mean? Why is the ids list
separate from the sql?

Currently the code only works cleanly if the the sql returns exactly the
list of ids. ie:

select * from pgr_tsp(
    'select id, x, y from mytable where id in (3,27,44,6)',
   '3,27,44,6',
   3, -1);

This seems redundant and messy at best.
Can anyone clear up what was supposed to happen in the old version?

I'm temped to just through out the old code and rewrite this as a
wrapper that computes a distance matrix and calls the matrix function,
or something like that.

Thoughts, use cases?

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

------------------------------

Message: 2
Date: Sat, 29 Jun 2013 10:07:21 -0400
From: Nate Wessel <bike756@gmail.com>
To: pgrouting-users@lists.osgeo.org
Subject: [pgrouting-users] PgRouting fails to compile on Ubuntu 13.04
Message-ID: <51CEEA19.40407@gmail.com>
Content-Type: text/plain; charset="iso-8859-1"; Format="flowed"

Howdy all,
I've spent the last three days trying to install PgRouting on my Xubuntu
13.04 machine. I've got a deadline coming up for a big cartography
project and this is driving me up a wall! I was able to install the
program quite easily through the PPA before I upgraded from Ubuntu
12.04. I tried adding the PPA again, but I get a 404, I guess because
there's nothing there for Raring yet.

I've tried following every set of installation instructions on the web
to no avail.

I tried compiling both the stable and unstable releases from source but
things keep failing. At first, cmake failed to find my postgresql
installation. After poking around a bit and adding an extra directories
to cmake/FindPostgreSQL.cmake, the errors went away and it looked like
cmake had run successfully:

nate@desktop:/usr/local/src/pgrouting$ cmake .
-- PGROUTING_GIT_TAG: v2.0.0-alpha
-- PGROUTING_GIT_BUILD: 97
-- PGROUTING_GIT_HASH: 9193863
-- PGROUTING_GIT_BRANCH: develop
-- PGROUTING_VERSION_REVISION_NAME: 9193863 develop
-- PGROUTING_VERSION_REVISION_HASH: 9193863
-- UNIX=1
-- WIN32=
-- POSTGRESQL_EXECUTABLE is /usr/lib/postgresql/9.1/bin/postgres
-- POSTGRESQL_PG_CONFIG is /usr/bin/pg_config
-- POSTGRESQL_INCLUDE_DIR: /usr/include/postgresql
-- POSTGRESQL_LIBRARIES: /usr/lib
-- Boost version: 1.49.0
Boost headers were found here: /usr/include
-- POSTGRESQL_VERSION_STRING is PostgreSQL 9.1.9
-- POSTGRESQL_VERSION is 9.1.9
-- Install directory for libraries is set to /usr/lib/postgresql/9.1/lib
-- Contrib directory for SQL files is set to
/usr/share/postgresql/9.1/contrib/pgrouting-2.0
-- Extension directory for SQL files is set to
/usr/share/postgresql/9.1/extension
-- Configuring done
-- Generating done
-- Build files have been written to: /usr/local/src/pgrouting

When I run make however, it quickly fails to find some includes:

nate@desktop:/usr/local/src/pgrouting$ make
Scanning dependencies of target trsp
[ 3%] Building C object src/trsp/src/CMakeFiles/trsp.dir/trsp.c.o
/usr/local/src/pgrouting/src/trsp/src/trsp.c:2:26: fatal error:
executor/spi.h: No such file or directory
compilation terminated.
make[2]: *** [src/trsp/src/CMakeFiles/trsp.dir/trsp.c.o] Error 1
make[1]: *** [src/trsp/src/CMakeFiles/trsp.dir/all] Error 2
make: *** [all] Error 2

I'm getting a little desparate! Can anyone help me with this? I'd give
anything to just have the GUI package manager take care of all this for me.

I have no idea what I'm doing trying to compile from source. I can
follow the instructions from the README.md file, but not after they fail
on the first step :wink:

Thanks,
Nate
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20130629/60e14879/attachment-0001.html&gt;

------------------------------

Message: 3
Date: Sat, 29 Jun 2013 10:37:32 -0400
From: Stephen Woodbridge <woodbri@swoodbridge.com>
To: pgrouting-users@lists.osgeo.org
Subject: Re: [pgrouting-users] PgRouting fails to compile on Ubuntu
  13.04
Message-ID: <51CEF12C.6090105@swoodbridge.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

Nate,

It looks like you need to install the various *-dev packages for thins
like postgresql, boost, cgal, etc.

-Steve

On 6/29/2013 10:07 AM, Nate Wessel wrote:

Howdy all,
I've spent the last three days trying to install PgRouting on my Xubuntu
13.04 machine. I've got a deadline coming up for a big cartography
project and this is driving me up a wall! I was able to install the
program quite easily through the PPA before I upgraded from Ubuntu
12.04. I tried adding the PPA again, but I get a 404, I guess because
there's nothing there for Raring yet.

I've tried following every set of installation instructions on the web
to no avail.

I tried compiling both the stable and unstable releases from source but
things keep failing. At first, cmake failed to find my postgresql
installation. After poking around a bit and adding an extra directories
to cmake/FindPostgreSQL.cmake, the errors went away and it looked like
cmake had run successfully:

nate@desktop:/usr/local/src/pgrouting$ cmake .
-- PGROUTING_GIT_TAG: v2.0.0-alpha
-- PGROUTING_GIT_BUILD: 97
-- PGROUTING_GIT_HASH: 9193863
-- PGROUTING_GIT_BRANCH: develop
-- PGROUTING_VERSION_REVISION_NAME: 9193863 develop
-- PGROUTING_VERSION_REVISION_HASH: 9193863
-- UNIX=1
-- WIN32=
-- POSTGRESQL_EXECUTABLE is /usr/lib/postgresql/9.1/bin/postgres
-- POSTGRESQL_PG_CONFIG is /usr/bin/pg_config
-- POSTGRESQL_INCLUDE_DIR: /usr/include/postgresql
-- POSTGRESQL_LIBRARIES: /usr/lib
-- Boost version: 1.49.0
Boost headers were found here: /usr/include
-- POSTGRESQL_VERSION_STRING is PostgreSQL 9.1.9
-- POSTGRESQL_VERSION is 9.1.9
-- Install directory for libraries is set to /usr/lib/postgresql/9.1/lib
-- Contrib directory for SQL files is set to
/usr/share/postgresql/9.1/contrib/pgrouting-2.0
-- Extension directory for SQL files is set to
/usr/share/postgresql/9.1/extension
-- Configuring done
-- Generating done
-- Build files have been written to: /usr/local/src/pgrouting

When I run make however, it quickly fails to find some includes:

nate@desktop:/usr/local/src/pgrouting$ make
Scanning dependencies of target trsp
[ 3%] Building C object src/trsp/src/CMakeFiles/trsp.dir/trsp.c.o
/usr/local/src/pgrouting/src/trsp/src/trsp.c:2:26: fatal error:
executor/spi.h: No such file or directory
compilation terminated.
make[2]: *** [src/trsp/src/CMakeFiles/trsp.dir/trsp.c.o] Error 1
make[1]: *** [src/trsp/src/CMakeFiles/trsp.dir/all] Error 2
make: *** [all] Error 2

I'm getting a little desparate! Can anyone help me with this? I'd give
anything to just have the GUI package manager take care of all this for me.

I have no idea what I'm doing trying to compile from source. I can
follow the instructions from the README.md file, but not after they fail
on the first step :wink:

Thanks,
Nate

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

------------------------------

Message: 4
Date: Sat, 29 Jun 2013 10:39:43 -0400
From: Stephen Woodbridge <woodbri@swoodbridge.com>
To: pgrouting-users@lists.osgeo.org
Subject: Re: [pgrouting-users] PgRouting fails to compile on Ubuntu
  13.04
Message-ID: <51CEF1AF.1020500@swoodbridge.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

Oh and the version of Boost might be a problem. I recommend using 1.42
which is what I use here into we sort out issues using the new versions.

-Steve

On 6/29/2013 10:07 AM, Nate Wessel wrote:

Howdy all,
I've spent the last three days trying to install PgRouting on my Xubuntu
13.04 machine. I've got a deadline coming up for a big cartography
project and this is driving me up a wall! I was able to install the
program quite easily through the PPA before I upgraded from Ubuntu
12.04. I tried adding the PPA again, but I get a 404, I guess because
there's nothing there for Raring yet.

I've tried following every set of installation instructions on the web
to no avail.

I tried compiling both the stable and unstable releases from source but
things keep failing. At first, cmake failed to find my postgresql
installation. After poking around a bit and adding an extra directories
to cmake/FindPostgreSQL.cmake, the errors went away and it looked like
cmake had run successfully:

nate@desktop:/usr/local/src/pgrouting$ cmake .
-- PGROUTING_GIT_TAG: v2.0.0-alpha
-- PGROUTING_GIT_BUILD: 97
-- PGROUTING_GIT_HASH: 9193863
-- PGROUTING_GIT_BRANCH: develop
-- PGROUTING_VERSION_REVISION_NAME: 9193863 develop
-- PGROUTING_VERSION_REVISION_HASH: 9193863
-- UNIX=1
-- WIN32=
-- POSTGRESQL_EXECUTABLE is /usr/lib/postgresql/9.1/bin/postgres
-- POSTGRESQL_PG_CONFIG is /usr/bin/pg_config
-- POSTGRESQL_INCLUDE_DIR: /usr/include/postgresql
-- POSTGRESQL_LIBRARIES: /usr/lib
-- Boost version: 1.49.0
Boost headers were found here: /usr/include
-- POSTGRESQL_VERSION_STRING is PostgreSQL 9.1.9
-- POSTGRESQL_VERSION is 9.1.9
-- Install directory for libraries is set to /usr/lib/postgresql/9.1/lib
-- Contrib directory for SQL files is set to
/usr/share/postgresql/9.1/contrib/pgrouting-2.0
-- Extension directory for SQL files is set to
/usr/share/postgresql/9.1/extension
-- Configuring done
-- Generating done
-- Build files have been written to: /usr/local/src/pgrouting

When I run make however, it quickly fails to find some includes:

nate@desktop:/usr/local/src/pgrouting$ make
Scanning dependencies of target trsp
[ 3%] Building C object src/trsp/src/CMakeFiles/trsp.dir/trsp.c.o
/usr/local/src/pgrouting/src/trsp/src/trsp.c:2:26: fatal error:
executor/spi.h: No such file or directory
compilation terminated.
make[2]: *** [src/trsp/src/CMakeFiles/trsp.dir/trsp.c.o] Error 1
make[1]: *** [src/trsp/src/CMakeFiles/trsp.dir/all] Error 2
make: *** [all] Error 2

I'm getting a little desparate! Can anyone help me with this? I'd give
anything to just have the GUI package manager take care of all this for me.

I have no idea what I'm doing trying to compile from source. I can
follow the instructions from the README.md file, but not after they fail
on the first step :wink:

Thanks,
Nate

_______________________________________________
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

End of Pgrouting-users Digest, Vol 57, Issue 12
***********************************************