search

aicenter / road-graph-tool

1 stars 0 forks source link

Missing tables #85

Closed domupls closed 1 month ago

domupls commented 1 month ago

According to REAME.md, if importing was done with pipeline.lua style file, we should run install_sql.py afterwards. However, I'm getting this error:

Adding FOREIGN KEY constraints to table nodes...
psql:SQL/after_import.sql:42: ERROR:  relation "areas" does not exist
Adding FOREIGN KEY constraints to table edges...
psql:SQL/after_import.sql:47: ERROR:  relation "edges" does not exist
psql:SQL/after_import.sql:48: ERROR:  relation "edges" does not exist
Adding FOREIGN KEY constraints to table trip_location...
psql:SQL/after_import.sql:53: ERROR:  relation "trip_locations" does not exist
psql:SQL/after_import.sql:54: ERROR:  relation "trip_locations" does not exist
Adding FOREIGN KEY constraints to table nodes_ways...
psql:SQL/after_import.sql:59: ERROR:  relation "areas" does not exist
Adding FOREIGN KEY constraints to table nodes_ways_speeds...
psql:SQL/after_import.sql:64: ERROR:  relation "nodes_ways_speeds" does not exist
psql:SQL/after_import.sql:65: ERROR:  relation "nodes_ways_speeds" does not exist

There are indeed missing tables. Can someone answer, whether they should be present in DB or not?

zlochina commented 1 month ago

Hey, @domupls. I think you’ve misread the manual. ‘install_sql.py’ should be run before importing, as for post-processing, file ‘after_import.sql’ should be run instead

domupls commented 1 month ago

Apologies @zlochina, I meant SQL/after_import.sql. install_sql.py was run before, then data was imported with pipeline.lua.

zlochina commented 1 month ago

More likely, you’ve not initialised database with tables. Its done either by executing install_sql.py by hand, or executing this file with ‘py scripts/main.py -i’

zlochina commented 1 month ago

Sorry, i’ve read your message wrongly. You’ve initialised the database. The only thing, which comes to mind, that could be the problem is that you’ve got another schema with the same tables, thus an issue “relation … does not exist” is raised because of ambiguity of the query.

can you tell me if that’s the case, and if so show the output of query ‘show search_path;’

domupls commented 1 month ago

Yes, it turned out that it was in different schema. I got another problem though:

Adding FOREIGN KEY constraints to table ways...
psql:SQL/after_import.sql:35: ERROR:  insert or update on table "ways" violates foreign key constraint "fk_ways_from"
DETAIL:  Key (from)=(1097188755) is not present in table "nodes".
psql:SQL/after_import.sql:36: ERROR:  insert or update on table "ways" violates foreign key constraint "fk_ways_to"
DETAIL:  Key (to)=(1097188755) is not present in table "nodes".

I tried to replicate the problem on my local database with smaller dataset and ran into problems with install_sql.py - one of the problems was that I has search path set to , public (so all extensions were installed into beforehand) which resulted in errors like: type "public.hstore" does not exist type "public.geometry" does not exist So mentioning that it expects all extensions to be installed in public schema would be beneficial in README.md. I'd also suggest mentioning that the database should be empty - or at least specifying in which format should the tables be. In main.sql table is created if it doesn't exist yet but that suggests it's not always the case.

zlochina commented 1 month ago

Also, I would like to talk about the first issue mentioned in your last message. I do not see how it is related to the problems you've mentioned below, do you still have issues with that? If yes, please notify me so we could resolve this together

domupls commented 1 month ago

I agree that making the project more flexible in terms of schema would be a great improvement. I've already implemented an option to specify the desired schema during data import.

Regarding the first error I mentioned earlier:

psql:SQL/after_import.sql:42: ERROR:  relation "areas" does not exist
Adding FOREIGN KEY constraints to table edges...
psql:SQL/after_import.sql:47: ERROR:  relation "edges" does not exist
psql:SQL/after_import.sql:48: ERROR:  relation "edges" does not exist

This issue was resolved based on your suggestion about the different schema. However, after fixing that, I encountered a new error (I still need help with that):

Adding FOREIGN KEY constraints to table ways...
psql:SQL/after_import.sql:35: ERROR:  insert or update on table "ways" violates foreign key constraint "fk_ways_from"
DETAIL:  Key (from)=(1097188755) is not present in table "nodes".
psql:SQL/after_import.sql:36: ERROR:  insert or update on table "ways" violates foreign key constraint "fk_ways_to"
DETAIL:  Key (to)=(1097188755) is not present in table "nodes".

This occurred on a remote database. When I tried replicating the issue locally, I encountered the errors I mentioned earlier. While these additional errors don't seem directly connected to the main issue (as far as I can tell), I thought it was worth noting since they appeared during my debugging process.

zlochina commented 1 month ago

@domupls, I cannot come up with the straightforward solution, I would need to look at the flow you use, which raised this issue, dataset also could be the factor. Do you wanna discuss it personally?

domupls commented 1 month ago

@zlochina and I resolved the issue by identifying that some nodes were no longer being imported due to changes in osm2pgsql version 2.0.0. Simply using the -x flag is no longer sufficient to import untagged objects. Now, the process_untagged_*() functions must be defined in Lua style files to import all objects. The necessary updates to the files were made in the importer branch