search

pgRouting / osm2pgrouting

Import tool for OpenStreetMap data to pgRouting database
https://pgrouting.org
GNU General Public License v2.0
293 stars 110 forks source link

Error while exporting configuration #199

Closed julsbreakdown closed 6 years ago

julsbreakdown commented 6 years ago

After a fresh install I try to launch the command
./osm2pgrouting -c ./mapconfig.xml -f ../../osm2pg/ --dbname pgrouting -U www-data -W www-data But I get the following error.

Connection success Opening configuration file: ./mapconfig.xml Parsing configuration

Exporting configuration ...

ERROR: relation "configuration" does not exist LINE 1: ...LECT a.* FROM __configuration22556 a LEFT JOIN configurat.

Connection success Opening configuration file: ./mapconfig.xml Parsing configuration

Exporting configuration ...

ERROR: relation "configuration" does not exist LINE 1: ...LECT a.* FROM __configuration22556 a LEFT JOIN configurat.

Any thoughts?

cvvergara commented 6 years ago

Can you do 

osm2pgrouting --version
clintharris commented 6 years ago

I'm also seeing this issue... Running osm2pgrouting 2.3.1 (with Postgres 9.6, PostGIS 2.3, and pgRouting 2.3). Also, note that I verified that the postgis and pgrouting extensions were installed in my DB...

I also verified that the (temp?) table __configurationXXXX was successfully created. Looks like it's just the configuration table that's missing (as indicated by the error message).

clintharris commented 6 years ago

Btw, I built osm2pgrouting from the v2.3.2, but osm2pgrouting --version returns 2.3.1. Mentioning this in case it's an issue with the fairly recent release (2.3.2).

clintharris commented 6 years ago

Looks like, in my case, the issue was caused by osm2pgrouting 2.3+ being incompatible with my version of pgRouting (2.3). Rolling back to osm2pgrouting v2.2.0 fixed the problem.

cvvergara commented 6 years ago

I am working fixing some bugs here:

https://github.com/cvvergara/osm2pgrouting/tree/fix-issues

I made this test:

createdb oldpgr
psql oldpgrsql oldpgr 
psql (10.1, server 9.3.20)
Type "help" for help.

oldpgr=# create extension postgis;
CREATE EXTENSION
oldpgr=# create extension pgrouting VERSION "2.3.0";
CREATE EXTENSION
oldpgr=# select pgr_version();
                  pgr_version                  
-----------------------------------------------
 (2.3.0,pgrouting-2.3.0,8c86efd,master,1.54.0)
(1 row)

oldpgr=# create extension hstore;
CREATE EXTENSION
oldpgr=# create schema foo;
CREATE SCHEMA
oldpgr=# select version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.3.20 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit
(1 row)

Then I executed:

osm2pgrouting -f file.osm  --clean --addnodes --attributes --schema foo --tags -d oldpgr

the output is:

Execution starts at: Thu Nov 16 15:57:33 2017

***************************************************
           COMMAND LINE CONFIGURATION             *
***************************************************
Filename = file.osm
Configuration file = /usr/share/osm2pgrouting/mapconfig.xml
host = localhost
port = 5432
dbname = oldpgr
username = 
password = 
schema= foo
prefix = 
suffix = 
Drop tables
Don't create indexes
Add OSM nodes
***************************************************
Testing database connection: oldpgr
database connection successful: oldpgr
Connecting to the database
connection success

Dropping tables...
TABLE: foo.ways dropped ... OK.
TABLE: foo.ways_vertices_pgr dropped ... OK.
TABLE: foo.pointsofinterest dropped ... OK.
TABLE: foo.configuration dropped ... OK.
TABLE: foo.osm_nodes dropped ... OK.
TABLE: foo.osm_ways dropped ... OK.
TABLE: foo.osm_relations dropped ... OK.

Creating tables...
TABLE: foo.ways_vertices_pgr created ... OK.
TABLE: foo.ways created ... OK.
TABLE: foo.pointsofinterest created ... OK.
TABLE: foo.configuration created ... OK.
TABLE: foo.osm_nodes created ... OK.
TABLE: foo.osm_ways created ... OK.
TABLE: foo.osm_relations created ... OK.
Opening configuration file: /usr/share/osm2pgrouting/mapconfig.xml
    Parsing configuration

Exporting configuration ...
  - Done 
Counting lines ...
  - Done 
Opening data file: restrictions.osm     total lines: 1422746
    Parsing data

Current osm_nodes:      500000
Final osm_nodes:        514326
Current osm_ways:       40000
Final osm_ways: 44880
Current osm_relations:  0
Final osm_relations:    1523
End Of file

    Finish Parsing data

Adding auxiliary tables to database...

Export Ways ...
    Processing 44880 ways:
[**********************|                            ] (44%) Total processed: 20000       Vertices inserted: 13873       Split ways inserted 13935
[********************************************|      ] (89%) Total processed: 40000       Vertices inserted: 6460        Split ways inserted 8832
[**************************************************|] (100%) Total processed: 44880      Vertices inserted: 691 Split ways inserted 1020

Creating indexes ...

Processing Points of Interest ...

Adding functions for processing Points of Interest ...

To process pointsOfInterest table:
osm2pgr_pois_update(radius default 200, within default 50)

  - Using areas of (radius)mts on POIS
  - Using edges that are at least (within) mts of each POI
POIS that do not have a closest edge is considered as too far
#########################
size of streets: 44880
Execution started at: Thu Nov 16 15:57:33 2017
Execution ended at:   Thu Nov 16 15:58:27 2017
Elapsed time: 53.747 Seconds.
User CPU time: -> 22.3675 seconds
#########################

I didnt get any error with the pgRouting version you have. Can you give it a try?

ghost commented 6 years ago

Hi guys, I have the same problem...

The link to the tutorial that I followed:

http://workshop.pgrouting.org/2.4.11/en/chapters/prepare_data.html

my psql version: psql (10.1, server 9.3.20)

my pgRouting version : (2.5.2,v2.5.2,60585f1f7,master,1.58.0)

my osm2pgrouting version: This is osm2pgrouting Version 2.3.3

My result of that command: $ osm2pgrouting -f Boston_MA.osm -d database -U user

result:

***************************************************
           COMMAND LINE CONFIGURATION             *
***************************************************
Filename = Boston_MA.osm
Configuration file = /usr/share/osm2pgrouting/mapconfig.xml
host = localhost
port = 5432
dbname = database
username = user
password = 
schema= 
prefix = 
suffix = 
Don't drop tables
Don't create indexes
Don't add OSM nodes
***************************************************
Testing database connection: database
database connection successful: database
Connecting to the database
connection success
Opening configuration file: /usr/share/osm2pgrouting/mapconfig.xml
    Parsing configuration

Exporting configuration ...

ERROR:  relation "configuration" does not exist
LINE 1: ...LECT a.*  FROM  __configuration20268 a LEFT JOIN  configurat...
                                                             ^

While exporting to configuration TODO insert one by one skip the guilty one
  - Done 
Counting lines ...
  - Done 
Opening data file: Boston_MA.osm    total lines: 1092919
    Parsing data

End Of file

    Finish Parsing data

Adding auxiliary tables to database...

Export Ways ...
    Processing 52410 ways:
[*******************|                               ] (38%) Total processed: 20000
ERROR:  relation "ways" does not exist
LINE 1:  DELETE FROM __ways20268 a      USING ways b      WHERE a.th...
                                              ^

While processing FROM 0th    to: 20000th way
count20000 While processing FROM 0th     to: 20000th way
[**************************************|            ] (76%) Total processed: 40000
ERROR:  relation "ways" does not exist
LINE 1:  DELETE FROM __ways20268 a      USING ways b      WHERE a.th...
                                              ^

While processing FROM 20000th    to: 40000th way
count40000 While processing FROM 20000th     to: 40000th way
[**************************************************|] (100%) Total processed: 52410
ERROR:  relation "ways" does not exist
LINE 1:  DELETE FROM __ways20268 a      USING ways b      WHERE a.th...
                                              ^

While processing FROM 40000th    to: 52410th way
count52410 While processing FROM 40000th     to: 52410th way

Creating indexes ...

WARNING: ERROR:  relation "configuration" does not exist

ALTER TABLE configuration
  ADD PRIMARY KEY (id)

WARNING: ERROR:  relation "configuration" does not exist

ALTER TABLE configuration
  ADD UNIQUE (tag_id)

WARNING: ERROR:  relation "ways_vertices_pgr" does not exist

ALTER TABLE ways_vertices_pgr
  ADD PRIMARY KEY (id)

WARNING: ERROR:  relation "ways_vertices_pgr" does not exist

ALTER TABLE ways_vertices_pgr
  ADD UNIQUE (osm_id)

WARNING: ERROR:  relation "ways_vertices_pgr" does not exist

CREATE INDEX ON ways_vertices_pgr
  USING GIST (the_geom);

WARNING: ERROR:  relation "ways" does not exist

ALTER TABLE ways
  ADD PRIMARY KEY (gid)

WARNING: ERROR:  relation "ways" does not exist

ALTER TABLE ways
  ADD FOREIGN KEY (source)
  REFERENCES ways_vertices_pgr(id)
  ON UPDATE NO ACTION 
  ON DELETE NO ACTION;

WARNING: ERROR:  relation "ways" does not exist

ALTER TABLE ways
  ADD FOREIGN KEY (target)
  REFERENCES ways_vertices_pgr(id)
  ON UPDATE NO ACTION 
  ON DELETE NO ACTION;

WARNING: ERROR:  relation "ways" does not exist

ALTER TABLE ways
  ADD FOREIGN KEY (source_osm)
  REFERENCES ways_vertices_pgr(osm_id)
  ON UPDATE NO ACTION 
  ON DELETE NO ACTION;

WARNING: ERROR:  relation "ways" does not exist

ALTER TABLE ways
  ADD FOREIGN KEY (target_osm)
  REFERENCES ways_vertices_pgr(osm_id)
  ON UPDATE NO ACTION 
  ON DELETE NO ACTION;

WARNING: ERROR:  relation "ways" does not exist

ALTER TABLE ways
  ADD FOREIGN KEY (tag_id)
  REFERENCES configuration(tag_id)
  ON UPDATE NO ACTION 
  ON DELETE NO ACTION;

WARNING: ERROR:  relation "ways" does not exist

CREATE INDEX ON ways
  USING GIST (the_geom);

WARNING: ERROR:  relation "pointsofinterest" does not exist

ALTER TABLE pointsofinterest
  ADD PRIMARY KEY (pid)

WARNING: ERROR:  relation "pointsofinterest" does not exist

CREATE INDEX ON pointsofinterest
  USING GIST (the_geom);

WARNING: ERROR:  relation "pointsofinterest" does not exist

ALTER TABLE pointsofinterest
  ADD UNIQUE (osm_id)

Processing Points of Interest ...
#########################
size of streets: 52410
#########################

Should I delete this version of pgrouting and osm2pgrouting . Then try with an older version to see if it works ? Thanks.

cvvergara commented 6 years ago

@MisterFalafel310

Can you try this command: (add a --clean)

osm2pgrouting \
    -f Boston_MA.osm \
    -d city_routing \
    --clean
cvvergara commented 6 years ago

updated the previous comment to add a missing \

ghost commented 6 years ago

Thanks !! no errors ! It displays 13 rows, in the tutorial there are 15 rows, and some values are not the same.

my results:

                   List of relations
 Schema |           Name           |   Type   |  Owner   
--------+--------------------------+----------+----------
 public | configuration            | table    | user
 public | configuration_id_seq     | sequence | user
 public | geography_columns        | view     | postgres
 public | geometry_columns         | view     | postgres
 public | pointsofinterest         | table    | user
 public | pointsofinterest_pid_seq | sequence | user
 public | raster_columns           | view     | postgres
 public | raster_overviews         | view     | postgres
 public | spatial_ref_sys          | table    | postgres
 public | ways                     | table    | user
 public | ways_gid_seq             | sequence | user
 public | ways_vertices_pgr        | table    | user
 public | ways_vertices_pgr_id_seq | sequence | user

the tutorial's results: 

Upload Data to the database
  Schema |           Name           |   Type   | Owner
  --------+--------------------------+----------+-------
  public | geography_columns        | view     | user
  public | geometry_columns         | view     | user
  public | osm_nodes                | table    | user
  public | osm_nodes_node_id_seq    | sequence | user
  public | osm_relations            | table    | user
  public | osm_way_classes          | table    | user
  public | osm_way_types            | table    | user
  public | raster_columns           | view     | user
  public | raster_overviews         | view     | user
  public | relations_ways           | table    | user
  public | spatial_ref_sys          | table    | user
  public | ways                     | table    | user
  public | ways_gid_seq             | sequence | user
  public | ways_vertices_pgr        | table    | user
  public | ways_vertices_pgr_id_seq | sequence | user
  (15 rows)

It is normal ? It is possible to access to this database from pgAdmin ? (if yes, how ? ) Thanks a lot !

cvvergara commented 6 years ago

The new version makes more tables The tutorial is based on the one installed on OSGeoLive v11

cvvergara commented 6 years ago

about: It is possible to access to this database from pgAdmin ? (if yes, how ? ) Like any other database I suppose, but that you can ask on stackexchange.

I will close this issue as the error is because is because it needs the --clean flag when the database is clean or needs to be cleaned