POIs from POI format v3 files not shown

[JFritzle]

Describe the bug

While POI old format files are still working, POIs from POI v3 format files found at locations mentioned by QMS Wiki page DocGisItemsPOI
o Mapsforge and
o GWDG
are no longer shown.

What have you done to circle down the problem?

Currently, POI old format files are still available at GWDG, which are processed correctly as well as in recent QMS version 1.17.1 as in previous QMS versions. POIs from POI v3 format files are not shown even in recent and previous QMS versions.

To Reproduce

1. Download e.g. Mapsforge POI file berlin.poi or POI file Berlin_oam.osm.poi.
2. Activate POI in QMS
3. Select one or more POI categories
4. Notice that no POI is shown

Expected behavior

As POI old format files are no longer available at Mapsforge and may become unavailable at GWDG in future, QMS should be able to process recent POI v3 format files too.

Screenshots

POI old format file Berlin, showing railway stations:

POI format v3 file Berlin, not showing railway stations:

Attachments

-

Tracebacks

-

Desktop

• OS: Windows
• QMapShack Version: 1.17.1 and 1.16.1

Additional context

Internal data structure of recent Mapsforge/OpenAndroMaps POI files has been changed to format v3 as described here.

[mitxel-m]

I have also been looking at this issue in the last few days.I bring here what I have been checking and hope it helps as a starting point.

The internal structure of the .poi file has changed, it is no longer using an r-tree, and the structure of the poi_index table is different.

In V2

where poi_index_node, poi_index_parent,poi_index_rowid, are internales tables used by r-tree

In V3

In poi_indextable columns latand lonare indexed, but there is no r-tree structure.

Since in QMS a query is made based on the columns minLat, maxLat, minLon, maxLon from V2, and they no longer exist in V3, then the query does not return any results.

In principle the solution is to modify the query according to the new lat and lon column names. I have already tried this and it works, but with two factors that we should study better:

1. By doing this, the poi V2 files cannot be read by QMS anymore. So if we want compatibility with V2 and V3 we would first have to check which is the version of the POI file, (in the metadata table), and consequently launch one query or another. ... Or else decide to jump to V3 and drop V2.

2. It seems to me that after this change QMS takes much longer to load the V3 POI than the V2, possibly because it is not taking advantage of the index of the poi_index table. For now I don`t know if this has a solution or how to improve it. (needs further study)

I've opened a branch here ( https://github.com/mitxel-m/qmapshack/tree/QMS-659) that already contains the changes to the query so you can try it out. It`s a draft and only a starting point.

Alternatively

There is another way, which is possible but maybe not very elegant.
Using a sql script we can convert a v3 file to v2 very quickly. I have already made and tested a script and it works for me:

BEGIN TRANSACTION;
ALTER TABLE poi_index RENAME TO poi_index_ori;
CREATE VIRTUAL TABLE poi_index USING rtree(id, minLat, maxLat, minLon, maxLon);
INSERT INTO poi_index select id, lat as minLat, lat as maxLat, lon as minLon, lon as maxLon from poi_index_ori where poi_index_ori.id is not null;
DROP TABLE poi_index_ori;
UPDATE metadata SET value = 2 where name = 'version';
COMMIT;
VACUUM;

So you can put this in a ' POI_V3toV2.sql' text file (attached here) and then in a terminal do:

sqlite3 Spain-Portugal.poi < POI_V3toV2.sql

For reference It takes a few seconds to convert Canary_islands, and about 90 seconds to convert Spain-Portugal (over 1.777.000 items). With this we would keep the current speed loading pois and no change is necessary in the code, but it requires the user to convert v3 files in the future, so it would be a last chance remedy, or in case we see that with V3 QMS slows down a lot.

[mitxel-m]

I have added a new commit in my branch QMS-659. With these latest changes it is possible to load POI files in both v2 and v3 versions. QMS first checks the file version by looking at the metadata table and then prepares the query according to that version.

So far everything is fine, but for now QMS works much faster using POI v2 files than using v3 files. Using a file for a small region (eg: Canary islands 59.000 items) the differences in load time are very small from v2 to v3, however if the POI file is large (eg: the whole Spain and Portugal 1.777.000 items) differences are notorius especially when:

• more than one category is checked at the same time
• the zoom is more distant, even if that area does not include new items.

As a reference, with a zoom adjusted to the island of Mallorca, and marking the whole tree of the Accomadation category (1722 items / 6 subcategories) : V2 takes 4 seconds to load, and v3 takes 30 seconds. If I zoom out a bit more the difference increases (5 seconds vs 55 seconds).

This leads me to think that the current code works very well with the v2 files that have an r-tree, but I don't know if we will be able to get the same efficiency with the v3 files without making deep changes in the code, and in that aspect I'm a bit lost. @pingurus did the development for using POIs in QMS and his help would be very much appreciated at this point.

I have also tried to modify the query for the v3 files trying to take advantage of the built-in index, but without success.

It's also worth remembering that converting a v3 to v2 is relatively easy and fast.

To summarize we are at this point:

• with the last changes on branch QMS-659 you can load v2 and v3.
• QMS works slower with v3 and to improve it would require more changes in the code.
• alternatively it is possible to convert v3 files to v2 with a script.

[d029940]

My experiences are:

https://ftp.gwdg.de/pub/misc/openstreetmap/openandromaps/mapsV4/ are working.
https://ftp.gwdg.de/pub/misc/openstreetmap/openandromaps/mapsV5/ are not working.

I wonder, which is the preferred version.

Tested on Windows 11 and MacOS 14 (Sonoma). On both OS with QMS V 1.17.1

[JFritzle]

The MAP format of the current OpenAndroMaps has been V5 for some time. Maps at https://ftp.gwdg.de/pub/misc/openstreetmap/openandromaps/mapsV5/ are bundled with POI files. As of December 2023, the POI format of the bundled POI files has changed from V2 to V3 format. See announcement.

For QMS version 1.17.1 and before, these choices are currently available:

• download outdated V4 format MAP files bundled with outdated V2 format POI files
• download recent V5 format MAP files bundled with recent V3 format POI files and convert V3 format POI files to V2 format POI files using SQL script mentioned above.
• as long as V2 format POI files are still supplied, seperately download recent V2 format POI files from https://ftp.gwdg.de/pub/misc/openstreetmap/openandromaps/pois/ORUX_old/

[kiozen]

I would vote for converting the new format into the old one unless someone volunteers to analyze the new one to find a more optimized way to read it. And is doing the implementation.

Users are greedy collectors. They hoard every bit of data they can get. And they expect that software can cope with that senseless amount of data. Therefore a performance hit is not acceptable. The POI stuff is already slow with the old format.

[JFritzle]

Installing a new POI file is not an everyday task. Therefore, the one-time conversion from the V3 format to the V2 format is a reasonable effort.

In my opinion, it is acceptable to require POI files in V2 format as long as there is no implementation that speeds up reading POI files in V3 format. But when trying to activate a V3 POI file, a message should pop up telling that this format is not supported. Including a hint on how the V3 format can be converted easily to the V2 format using an sqlite3 script.

It would of course be very nice if the user could decide within QMS to convert the V3 format file into the V2 format on the fly, assuming write permission.

[d029940]

As for conversion:

• On Linux and MacOS I found it an easy process.
• On Windows, you need to install sqlite first.
• I think Linux users are more tied to system administration than MacOS- / Windows users.

Since it is not too difficult, it is a matter of documentation. Especially that you can download up-to-date POIs in v2 format.
I didn't know, that this exists, and so will a lot of users.

Sure, ultimately, support of the V3 format out of the box, would be very nice.
But I fully understand the issue that someone needs to develop it. And we are all volunteers working in our free time.

[JFritzle]

On Windows, you will find a usable sqlite3.exe in QMS installation folder, at least with QMS v1.17.1. Therefore no installation is required. Using sqlite3 script POI_V3toV2.sql mentioned above, simple Windows command
"C:\Program Files\QMapShack\sqlite3.exe" poi_v3_file.poi < POI_V3toV2.sql
will successfully convert POI format V3 file poi_v3_file.poi to POI format V2.

[d029940]

Even better.