Bug report #10790
OpenStreetMap plugin only loads 32 bits numbers ID, then start with negative numbers
|Affected QGIS version:||2.18.9||Regression?:||No|
|Operating System:||Windows, Linux||Easy fix?:||No|
|Pull Request or Patch supplied:||No||Resolution:||wontfix|
|Crashes QGIS or corrupts data:||No||Copied to github as #:||19171|
The file where imported via Openstreetmap import topology, not sure if error at import or error at export to spatialite
#1 Updated by Andre Joost about 6 years ago
- Target version set to Future Release - High Priority
Confirmed here, with data recently added to the OSM database. The .osm file is correct, and when I look into the spatialite database with spatialite GUI, the id numbers are still correct there.
The id field is set to type INTEGER, not sure if spatialite offers a different data type for long integer (which is necessary by now).
#3 Updated by Andre Joost about 4 years ago
- Target version changed from Future Release - High Priority to Version 2.18
Updated discussion at http://gis.stackexchange.com/questions/211000/is-there-a-qgis-tool-that-handles-osm-64-bit-identifiers-correctly and http://osgeo-org.1560.x6.nabble.com/Deprecation-of-the-Vector-OpenStreetMap-Importer-td5286805.html
Since July 2016, the importer rejects all lines and polygons with newly created nodes, making the tool useless.
If noone is able to fix the data type, dropping the importer seems to be the easiest solution. Add Vector layer and the QuickOSM plugin do the job without errors.
#7 Updated by Sandro Santilli about 4 years ago
#8 Updated by Martin Dobias about 4 years ago
Just to avoid further confusion in the future:
- the OSM plugin mentioned in comment 7 is looong gone (probably removed before 2.0, I don't remember)
- there is OSM support in menu Vector > OpenStreetMap, which does support 64bit identifiers.
If you have found any issue with 64bit identifiers anyway, using any recent QGIS version, please reopen AND attach test data.
#9 Updated by Andre Joost over 3 years ago
- Target version changed from Version 2.18 to Future Release - High Priority
- File testosm.osm added
- Status changed from Closed to Reopened
Still having the same issue here with QGIS 2.18.3 64-bit on Windows 8.
sample bounding box: North 51.3979 West 6.37532 East 6.38387 South 51.395
All 3 steps of Vector -> Openstreetmap run without error message, but several buildings are missing (those added recently to the OSM database). Roads are missing completely (having new nodes too).
The downloaded testosm.osm data is attatched, and loads fine with Add Vector layer, but the spatialite database created from that is incomplete.
#14 Updated by Andre Joost over 3 years ago
Still the same issue with the test data using the 3-part Vector -> Openstreetmap menu entry on QGIS 2.18.7, on Linux Mint 18 (non-Ubuntugis).
Only some building polygons are generated, while the OSM tile background has more (those with 64-bit node numbers). Same for the roads. I still get the view from testosm.png.
With Add Vector layer you get the complete data, and with the QuickOSM plugin reading the test file as well.
Same result with QGIS-dev cd248c6 from OSGEO4W64.
#16 Updated by Sandro Santilli over 3 years ago
Andre I'm trying to reproduce the problem you are seeing but I'm missing full instructions.
What I'm doing:
1. Start qgis
2. From the menu: Vector -> OpenStreeMap -> Import Topology from XML...
3. Select the `testosm.osm` file you attached to this ticket in the "Input XML file (.osm)" field
4. Hit 'OK' (receiving "Import has been sucessful" message)
5. Close the OpenStreetMap dialogs
6. From the menu: Layer -> Add Layer -> Add SpatiaLite Layer...
7. Select the spatialite layer created by the previous import procedure
8. Click on Connect
At the final step (connecting to the newly created spatialite database) I get no table listed.
If I check the "Also list tables with no geometry" checkbox on the left then I do get some tables.
I add the "nodes" and "ways" tables to the canvas, then I select each and open attribute table, counting the number of entries.
For both tables (nodes and ways) there's an exact match between the records in the attribute table and the number of records in the .osm file (checked with `grep 'way id' testosm.osm | wc -l` and `grep 'node id' testosm.osm | wc -l` and obtaining respectively 32 and 127).
This is with QGIS from the 2.18 branch at 44d1454dd0d553466e3c62324f3ac73713844dbf
Are you seeing anything different ? If so please give exact step-by-step instructions as I did above.
I'm probably missing something because you reference a screenshot showing a map, while I see no map at all !
#17 Updated by Andre Joost over 3 years ago
Sandro, you missed the third part of the import: Vector -> OpenStreetMap -> Export to Spatialite...
That`s why you only have the geometryless tables of nodes, ways and relations (the OSM raw data). You need Point, Line and Multipolygon tables.
For a reference, you can use Layer -> Add Vector Layer on the testosm.osm file. It delivers 24 multipolygons, while Vector -> OpenStreetMap -> Export to Spatialite only delivers 8 multipolygons. Same for the Lines.
The background map is from the QuickMapServices plugin, OSM Standard map.
#20 Updated by Sandro Santilli over 3 years ago
Alright what "Export topology to Spatialite..." does is not really an "export" as it just creates more tables in an existing Spatialite database, which is used both as input and output. After this step using "Add Spatialite layer" also gives less polygons than found by "Add Vector Layer".
I also noticed that using "Save as" on the layer loaded via "Add Vector Layer" from the .osm file (the layer with all the polygons) to an "sqlite" export, doesn't loose any geometry, so that re-opening the newly created sqlite database (via "Add Vector Layer") gives all geometries. This latter "sqlite" database is not a spatialite one though, so this seems to be some OGR abstraction
#21 Updated by Sandro Santilli over 3 years ago
`ways` identifiers go from 26553528 to 471592927.
`nodes` identifiers go from 291056122 to 4657544184.
2^32 is 4294967296 so the problem of integer overflow could be with nodes, not ways.
Adding debug lines I see 32 ways being analyzed and 23 of them being considered invalid by the QgsOSMDatabase::exportSpatiaLite method. One is skip for being non-area. This leaves us with the 8 polygons as reported.
For comparison, "Add Vector Layers" finds a total of 28 polygons from those 32 ways.
Now, this should be the list of ways that have nodes with identifiers above 2^32:
spatialite> select w.id from ways w where exists ( select * from ways_nodes where way_id = w.id and node_id > pow(2,32) );
But the 8 polygons which are loaded are all in the above set (marked with an asterisk)
#22 Updated by Sandro Santilli over 3 years ago
The kind of invalidity spot by QgsOSMDatabase::exportSpatiaLite is that wayPoints( w.id() ) returns an empty polyline which in turn happens because sqlite3_column_type returns SQLITE_NULL. It isn't clear to me what that means, anyway skipping those points rather than aborting the operation makes 22 polygons appear instead of just 8. Still a bit shorter than the OSM direct extraction, but looks like a step forward.
#23 Updated by Andre Joost over 3 years ago
ogr2ogr (which works in the background of Add vector layer) stores all fields as VARCHAR, while the QGIS importer uses INTEGER for the id field and TEXT for the rest. I suggest to follow the ogr2ogr way and store the id as TEXT too.
If you need an integer field for a primary key, you may create your own (e.g. qgis_id) from just auto-counting through all features. Noone will import the whole world with QGIS to exceed the 2^32 limit of that counter.
This will prevent to have some nodes dropped, and get invalid geometries dropped as a consequence.
#25 Updated by Sandro Santilli over 3 years ago
- Assignee deleted (
I don't know if Spatialite provider supports TEXT identifiers. Or negative integer identifiers (which might be the limit of this case, due to overflow).
In the long run it would seem useful to follow the path of PostGIS provider, using the FidMap strategy, to support any kind of identifier (even multi-column one).
I'm de-assigning this from myself as I don't plan to work on it in the near future, and there are probably others with more knowledge of the spatialite provider to deal with this (Jurgen?)