pgRouting database load trouble: "value is out of range for type smallint"

[hamish]

r105556:

maybe related to version upgrade, maybe related to new larger CBD osm file extract (should I shrink the area now that there is more detail?)

Setting up osm2pgrouting (2.0.0-beta-ppa4) ...
create pgrouting database with PostGIS and pgRouting
CREATE EXTENSION
CREATE EXTENSION
unpack sample data
Run osm2pgrouting converter (this may take a while)
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "nodes_pkey" for table "nodes"
2create ways failed: 
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "types_pkey" for table "types"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "classes_pkey" for table "classes"
ERROR:  value "45028" is out of range for type smallint
CONTEXT:  COPY nodes, line 115830, column numofuse: "45028"
NOTICE:  table "vertices_tmp" does not exist, skipping
CONTEXT:  SQL statement "DROP TABLE IF EXISTS vertices_tmp"
PL/pgSQL function "pgr_createtopology" line 44 at SQL statement
NOTICE:  CREATE TABLE will create implicit sequence "vertices_tmp_id_seq" for serial column "vertices_tmp.id"
CONTEXT:  SQL statement "CREATE TABLE vertices_tmp (id bigserial)"
PL/pgSQL function "pgr_createtopology" line 45 at SQL statement
NOTICE:  0 out of 21142 edges processed
NOTICE:  10000 out of 21142 edges processed
NOTICE:  20000 out of 21142 edges processed
VACUUM

Hamish

[dkastl]

Need to check where this "smallint" data type is required. I would understand a limitation for integer (and not bigint), but I currently don't see where would be a need to limit to small integer type.

Nevertheless, this won't really harm the sample data for pgRouting. Maybe a node/link is wrong or missing, but OSM data might have other errors anyway. The Quickstart should still work as I have just rewritten and tested in this week.

Tracked it as bug in "osm2pgrouting" project and it might get fixed with the next minor Ubuntu package update: ​https://github.com/pgRouting/osm2pgrouting/issues/21

[hamish]

any comments on the size of the updated feature_city_CBD.osm extract? is it too big for a tutorial run from a netbook? or is size is alright?

thanks, Hamish

[dkastl]

I have just checked where the "smallint" comes from and it was "introduced" with a pull request that added a bunch of useful stuff ... but it seems also a little bit of not so useful things ;-)

It's probably something easy to fix, but it only affects the "nodes" table and in this table some counter attribute, which I first need to look at in details to understand what it is for. But anyway, it doesn't affect the routing part at all, so I would just ignore this error for now.

About the size of the OSM extract: if the size doesn't harm the total size of the LiveDVD, you only need to worry about your build server and not the netbooks. osm2pgrouting may eat up your RAM memory (and SWAP memory) if the area is too big. But 20.000 records is pretty small. From half a million records we probably should think about a smaller extract.

[dkastl]

Changed the attribute type to integer. Should be included in the latest osm2pgrouting Ubuntu package.