[pgrouting-users] Problem using osm2pgrouting: value too large

pgRouting pgrouting-users
1

Greetings-

I have postgis 2.0 installed under postgresql 9.1 on a Debian server. I am seeking to use pgRouting for a research task: we will ultimately need to be able to locate the three nearest locations (by routing, not geography) in our database to a list of addresses we will provide.

I am working on importing OSM data on North Carolina, which will include all the routing information we need. I downloaded the file from http://download.geofabrik.de/north-america/us/north-carolina-latest.osm.bz2 and uncompressed it.

I then ran:

/usr/src/osm2pgrouting-master/osm2pgrouting -file ./north-carolina-latest.osm -conf /usr/src/osm2pgrouting-master/mapconfig.xml -dbname lenoir -user postgres -passwd *******

The output was:
host=127.0.0.1 user=postgres dbname=lenoir port=5432 password=*******
connection success
Trying to load config file /usr/src/osm2pgrouting-master/mapconfig.xml
Trying to parse config
Trying to load data
Trying to parse data
Split ways
Adding tag types and classes to database...
Adding relations to database...
Adding nodes to database...
SSL error: sslv3 alert unexpected message
Adding ways to database...
Creating topology...
#########################
size of streets: 484625
size of splitted ways : 1080943
finished

HOWEVER, no records are in the "ways" or "nodes" tables afterward:
lenoir=# select count(*) from nodes;
  count
-------
      0
(1 row)

lenoir=# select count(*) from ways;
  count
-------
      0
(1 row)

...and the postgresql log shows:
2013-03-18 13:41:12 EDT ERROR: value "2147643707" is out of range for type integer
2013-03-18 13:41:12 EDT CONTEXT: COPY nodes, line 31000313, column id: "2147643707"
2013-03-18 13:41:12 EDT STATEMENT: COPY nodes(id, lon, lat) from STDIN
2013-03-18 13:41:13 EDT LOG: SSL renegotiation failure
2013-03-18 13:41:13 EDT STATEMENT: COPY nodes(id, lon, lat) from STDIN
2013-03-18 13:41:13 EDT LOG: SSL error: unexpected record
2013-03-18 13:41:13 EDT STATEMENT: COPY nodes(id, lon, lat) from STDIN
2013-03-18 13:41:13 EDT LOG: could not send data to client: Connection reset by peer
2013-03-18 13:41:13 EDT STATEMENT: COPY nodes(id, lon, lat) from STDIN
2013-03-18 13:41:13 EDT LOG: SSL error: unexpected record
2013-03-18 13:41:13 EDT LOG: could not receive data from client: Connection reset by peer
2013-03-18 13:41:13 EDT LOG: unexpected EOF on client connection

Thanks for any advice.

--
--

----------------------------------------------------------------------
Andrew J Perrin - Associate Professor of Sociology
University of North Carolina - CB#3210, Chapel Hill, NC 27599-3210 USA
andrew_perrin@unc.edu - http://perrin.socsci.unc.edu - (919) 962-6876

2

Hi Andrew,

osm2pgrouting right now doesn’t work with PostGIS 2.0.
Even if you load legacy.sql it will fail because of MULTILINESTRING geometry type created by the tool.
The source shouldn’t be too difficult to fix and make it work with PostGIS 2.0. A pull request would be very welcome, because I currently have no time to fix and test it.

One alternative would be to use osm2po (osm2po.de), which is fast and already has fixed the MULTILINESTRING issue.

Daniel

On Tue, Mar 19, 2013 at 2:53 AM, Andrew J Perrin <andrew_perrin@unc.edu> wrote:

Greetings-

I have postgis 2.0 installed under postgresql 9.1 on a Debian server. I am seeking to use pgRouting for a research task: we will ultimately need to be able to locate the three nearest locations (by routing, not geography) in our database to a list of addresses we will provide.

I am working on importing OSM data on North Carolina, which will include all the routing information we need. I downloaded the file from http://download.geofabrik.de/north-america/us/north-carolina-latest.osm.bz2 and uncompressed it.

I then ran:

/usr/src/osm2pgrouting-master/osm2pgrouting -file ./north-carolina-latest.osm -conf /usr/src/osm2pgrouting-master/mapconfig.xml -dbname lenoir -user postgres -passwd *******

The output was:
host=127.0.0.1 user=postgres dbname=lenoir port=5432 password=*******
connection success
Trying to load config file /usr/src/osm2pgrouting-master/mapconfig.xml
Trying to parse config
Trying to load data
Trying to parse data
Split ways
Adding tag types and classes to database…
Adding relations to database…
Adding nodes to database…
SSL error: sslv3 alert unexpected message
Adding ways to database…
Creating topology…
#########################
size of streets: 484625
size of splitted ways : 1080943
finished

HOWEVER, no records are in the “ways” or “nodes” tables afterward:
lenoir=# select count(*) from nodes;
count

0
(1 row)

lenoir=# select count(*) from ways;
count

0
(1 row)

…and the postgresql log shows:
2013-03-18 13:41:12 EDT ERROR: value “2147643707” is out of range for type integer
2013-03-18 13:41:12 EDT CONTEXT: COPY nodes, line 31000313, column id: “2147643707”
2013-03-18 13:41:12 EDT STATEMENT: COPY nodes(id, lon, lat) from STDIN
2013-03-18 13:41:13 EDT LOG: SSL renegotiation failure
2013-03-18 13:41:13 EDT STATEMENT: COPY nodes(id, lon, lat) from STDIN
2013-03-18 13:41:13 EDT LOG: SSL error: unexpected record
2013-03-18 13:41:13 EDT STATEMENT: COPY nodes(id, lon, lat) from STDIN
2013-03-18 13:41:13 EDT LOG: could not send data to client: Connection reset by peer
2013-03-18 13:41:13 EDT STATEMENT: COPY nodes(id, lon, lat) from STDIN
2013-03-18 13:41:13 EDT LOG: SSL error: unexpected record
2013-03-18 13:41:13 EDT LOG: could not receive data from client: Connection reset by peer
2013-03-18 13:41:13 EDT LOG: unexpected EOF on client connection

Thanks for any advice.

–

Andrew J Perrin - Associate Professor of Sociology
University of North Carolina - CB#3210, Chapel Hill, NC 27599-3210 USA
andrew_perrin@unc.edu - http://perrin.socsci.unc.edu - (919) 962-6876

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

3

Actually I was able to make this work by:

alter table relation_ways alter column relation_id type bigint;
alter table relation_ways alter column way_id type bigint;
alter table nodes alter column id type bigint;
\i '/usr/src/postgis-2.0.1/spatial_ref_sys.sql';
SELECT assign_vertex_id('ways', 0.00001, 'the_geom', 'gid');

...so a shortest_path() query is now working correctly in my dataset. Now I have to go about figuring out how to apply it to the research question!

Andy

On 03/18/2013 02:33 PM, Daniel Kastl wrote:

Hi Andrew,

osm2pgrouting right now doesn't work with PostGIS 2.0.
Even if you load legacy.sql it will fail because of MULTILINESTRING
geometry type created by the tool.
The source shouldn't be too difficult to fix and make it work with
PostGIS 2.0. A pull request would be very welcome, because I currently
have no time to fix and test it.

One alternative would be to use osm2po (osm2po.de <http://osm2po.de>),
which is fast and already has fixed the MULTILINESTRING issue.

Daniel

On Tue, Mar 19, 2013 at 2:53 AM, Andrew J Perrin <andrew_perrin@unc.edu
<mailto:andrew_perrin@unc.edu>> wrote:

    Greetings-

    I have postgis 2.0 installed under postgresql 9.1 on a Debian
    server. I am seeking to use pgRouting for a research task: we will
    ultimately need to be able to locate the three nearest locations (by
    routing, not geography) in our database to a list of addresses we
    will provide.

    I am working on importing OSM data on North Carolina, which will
    include all the routing information we need. I downloaded the file
    from
    http://download.geofabrik.de/__north-america/us/north-__carolina-latest.osm.bz2
    <http://download.geofabrik.de/north-america/us/north-carolina-latest.osm.bz2&gt;
    and uncompressed it.

    I then ran:

    /usr/src/osm2pgrouting-master/__osm2pgrouting -file
    ./north-carolina-latest.osm -conf
    /usr/src/osm2pgrouting-master/__mapconfig.xml -dbname lenoir -user
    postgres -passwd *******

    The output was:
    host=127.0.0.1 user=postgres dbname=lenoir port=5432 password=*******
    connection success
    Trying to load config file /usr/src/osm2pgrouting-master/__mapconfig.xml
    Trying to parse config
    Trying to load data
    Trying to parse data
    Split ways
    Adding tag types and classes to database...
    Adding relations to database...
    Adding nodes to database...
    SSL error: sslv3 alert unexpected message
    Adding ways to database...
    Creating topology...
    #########################
    size of streets: 484625
    size of splitted ways : 1080943
    finished

    HOWEVER, no records are in the "ways" or "nodes" tables afterward:
    lenoir=# select count(*) from nodes;
    count
    -------
    0
    (1 row)

    lenoir=# select count(*) from ways;
    count
    -------
    0
    (1 row)

    ...and the postgresql log shows:
    2013-03-18 13:41:12 EDT ERROR: value "2147643707" is out of range
    for type integer
    2013-03-18 13:41:12 EDT CONTEXT: COPY nodes, line 31000313, column
    id: "2147643707"
    2013-03-18 13:41:12 EDT STATEMENT: COPY nodes(id, lon, lat) from STDIN
    2013-03-18 13:41:13 EDT LOG: SSL renegotiation failure
    2013-03-18 13:41:13 EDT STATEMENT: COPY nodes(id, lon, lat) from STDIN
    2013-03-18 13:41:13 EDT LOG: SSL error: unexpected record
    2013-03-18 13:41:13 EDT STATEMENT: COPY nodes(id, lon, lat) from STDIN
    2013-03-18 13:41:13 EDT LOG: could not send data to client:
    Connection reset by peer
    2013-03-18 13:41:13 EDT STATEMENT: COPY nodes(id, lon, lat) from STDIN
    2013-03-18 13:41:13 EDT LOG: SSL error: unexpected record
    2013-03-18 13:41:13 EDT LOG: could not receive data from client:
    Connection reset by peer
    2013-03-18 13:41:13 EDT LOG: unexpected EOF on client connection

    Thanks for any advice.

    --

    ------------------------------__------------------------------__----------
    Andrew J Perrin - Associate Professor of Sociology
    University of North Carolina - CB#3210, Chapel Hill, NC 27599-3210 USA
    andrew_perrin@unc.edu <mailto:andrew_perrin@unc.edu> -
    http://perrin.socsci.unc.edu - (919) 962-6876

    _________________________________________________
    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&gt;

--
Georepublic UG & Georepublic Japan
eMail: daniel.kastl@georepublic.de <mailto:daniel.kastl@georepublic.de>
Web: http://georepublic.de/&gt;

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

--
--

----------------------------------------------------------------------
Andrew J Perrin - Associate Professor of Sociology
University of North Carolina - CB#3210, Chapel Hill, NC 27599-3210 USA
andrew_perrin@unc.edu - http://perrin.socsci.unc.edu - (919) 962-6876