- Posts
Thanks :-).
It is very hard to answer because at the end of the conversion in KMZ, I have 253 categories. I had to import one by one in Locus to see the result. It is a very long job… If you want to try, I can send you all the KMZ.
To give you an information at all, I tried to import the biggest KMZ file from my Belgium POI export.
It is the Bus Stop POI :- Points : 108.946 points
- Size : 3.548.216 bytes (+/- 3.55 Mb text compressed in KMZ but the original text uncompressed KML size was +/- 35 Mb)
Before the import (which take a long time dues to the number of points), the Locus DB points (STORAGE\EMULATED\0\LOCUS\DATA\DATABASE\WAYPOINT.DB) size was 294.912 bytes.
After the import, it was 42.799.104 bytes.
So, for importing +/- 3.55 Mb compressed, the DB grows of +/- 42 Mb… I think that SQLite is not running a compression algorithm.
That’s why I develop the geographical restriction area in my application, to export only what I need.
The size also depends on the content of the description. for some categories, the description is a small text (with the name for exemple) but for others categories it can be bigger (hôtels for exemple, with name, address, phone, internet site,…).I hope to have answered a little to your question.
1 user thanked author for this post.
Hallo, bin neu hier im Forum und mehr oder weniger ein Locus-Anfänger.. Mein Fragen richten sich an Poi-Spezialisten: wenn ich zu den Teilkarten die dazu angebotenen Pois downloade und entpacke, wo auf meinem S6- Android-Gerät kopiere ich die entpackte Datei hinein? In “Locus\maps Vector” oder in eine andere Datei? Wenn sie im richtigen Ordner sind, werden sie dann automatisch bei Annäherung angezeigt, oder muß ich die Abfrage dazu starten?
Für ein Beantwortung sage ich jetzt schon Danke
ghostbikerDie MapsForge/OAM-POIs funktionieren nicht direkt mit Locus – LEIDER.
Gaouargas hat aber ein Programm entwickelt mit dem er diese POIs umwandlet in KM*, die Du nach Locus importieren kannst. Das sind dann aber POIs in Deiner provaten Datenbank, NICHT wie POIs die Locus integriert verwalten und selektieren kann (“LoMpas” zum Kaufen).
Ich hoffe ja immer noch, dass jemand die Locus-POI-DB-Mechanik komplett reverse engineered, so dass man die Locus-POI-DBs direkt selbst erstellen kann … Es ist auf jeden Fall nicht einfach. Gaouargas und ich haben uns bisher die Zähne daran ausgebissen …Danke für die rasche Antwort. Das ist mir etwas zu kompliziert. Da werde ich wohl wieder Speicherplatz freigeben.
Beste Grüße
GhostbikerIch hoffe ja immer noch, dass jemand die Locus-POI-DB-Mechanik komplett reverse engineered, so dass man die Locus-POI-DBs direkt selbst erstellen kann … Es ist auf jeden Fall nicht einfach. Gaouargas und ich haben uns bisher die Zähne daran ausgebissen …
Ich habe mir heute das Format der Datenbank mal kurz angeschaut. Auf den ersten Blick sieht das Format nicht zu schlimm aus. Wenn man sich die Blobs in den “geom”-Feldern anschaut, findet man raus, dass die Werte alle mit dem Wert 0001E6100000 (in Hex-Darstellung) anfangen. Wenn man mal danach googlet, findet man relativ schnell heraus, dass das vermutlich irgendwas mit spatialite zu tun hat (eine Bibliothek für spatial data in SQLite).
Öffnet man jetzt die Datei in spatialite gui, so gibt es da nur eine begrenzte Zahl an Nutzer-Tabellen: Cities, Cities_Names, FoldersRoot, FoldersSub, MetaData, Points, Points_Key_Value, Points_Root_Sub, Postcodes, Regions, Regions_Names, Street_In_Cities, Streets, TagKeys, TagValues sowie einen View View_Cities_Def_Names. Das Datenmodell dieser Tabellen sieht auf den ersten Blick erstmal recht einfach zu verstehen aus — in der GUI sieht man insbesondere, das die “geom”-Spalte nicht einfach nur ein Blob ist, sondern z.B. ein MULTIPOLYGON (z.B. in Cities, also die Umrisse der Stadt) oder ein POINT (z.B. in Points).
Man kann die Datei auch in QGIS laden (als spatialite-Datei) und dann z.B. die Städte als Layer einblenden
Der große Rest der Tabellen erscheinen mir interne Tabellen von bzw. für spatialite zu sein.
Soweit als Abendanalyse der POI-Daten. Vielleicht hilft das schon mal jemandem weiter, aber zum Erzeugen muss man sicher noch mal tiefer einsteigen.
Viele Grüße, cebewee
1 user thanked author for this post.
I am a Locus Map user too and I like OpenAndroMaps very much, because I participate to OpenStreetMap myself and like to have recent maps to avoid collecting data, that someone else already collected.
I also like like the POI in LoMaps but their are always older than the OpenAndroMaps that’s why I had a look at the Locus-DB-format too a few month ago. Here a few things I found out.As Cebewee wrote, it is not just a sqlite format but a spatialite format and it helps a lot to take that into consideration:
* The blob in the table Points are just coordinates. Using spatialite it is easy to read or write them.
* It you edit the file (on the command lind with sqlite3 or with a GUI tool like SpatialiteStudio) don’t forget to load the spatialite extension or else you will probably corrupt the file.
* The idx_* tables are geospacial indexes and the spatialite extension will take care of them automatically.How to add a POI to an existing file?
1. Don’t forget to load the spatialite extension (select load_extension(‘mod_spatialite’)) after opening the file.
2. For your new POI get the index of the category from FoldersRoot and the subcategory from FoldersSub.
Example: “food_drink” / “drinking_water” = 9 / 52
3. Add your new POI to the table Points:
id = ID of the “node” in OpenStreetMap (but you can probably use any new value)
name = name of the POI
type = P (Probably when it is a “node” in OpenStreetMap. It might be something else for a “way”)
geom = coordinates of the point
4. Add a record to Points_Root_Sub to set the category and subcategory of your new POI:
Points_id = Index of the POI in the table Points (Caution: it is the index, not the ID!)
FoldersRoot_id = 9
FoldersSub_id = 52A POI can have more properties than a name and some coordinates. They can be added in the Table Points_Key_Value.
As attachement a python script that adds a POI like described in my previous post (tested with an old version of the poi db, one without addresses).
Now if you want to convert a .poi file to a .osm.db file you will get a least a few difficulties:
* You need to match the OAM categories to Locus categories. Althrough it is possible to add new categories to a locus file, the icons seems not to be part of the .osm.db file. If you define new categories, they won’t have an icon in Locus and won’t be really usable.
* You need an empty file to start. I don’t how to generate one. It might be easier to empty an existing one.
* A simple script like that is too slow to add a lot of POIs.
* For the tables TagKey/TagValues you will probably want to deduplicate values that occurs several times. You might need a temporary index to do that with an acceptable speed.I made a few more test a few months ago, but I haven’t get an usable solution and I’m not sure I will have time to continue further tests, that’s why I wanted to share, what I found until now.
I have been able to successfully create an empty database from scratch. I wrote down the description on https://gitlab.com/noschinl/locus-poi-db. The input subdirectory contains a Jupyter notebook creating such a file. I don’t know when I’ll find time to continue, so I’ll just put this out here.
Entries can be added just like Lmu described.
3 users thanked author for this post.
Dear Gaourgas
I tried your program and it works flawlessly. Thanks a lot for making it available.
I wanted to do something similar but simpler to get the POI information out of the database using the spatialite-gui for testing purposes. I simly use a select statement as follows (with the “where” clause to limit the amount of output):select nodeno ,hex(data) from poi_index_node where substr(hex(data),1,8) = "00000020";I am struggling with the BLOB in the “poi_index_node” table. I assume it contains the POI details but I cannot find the definition for the structure. Based on your output, you seem to have solved this step. Could you point me to the relevant description.
Many thanks for a hint and best regards
Peter from the YPNA Geocaching teamPeter, as Gaourgas hasn’t started this thread, he can’t read your message as you set it private, only mods and the thread starter can. Should I change your message to public?
Developer of Elevate mapstyle
2 users thanked author for this post.
Dear Tobias
As you can see, I am a forum newbie (not limited to this forum only). Yes, I appreciate if you change the scope of the message!
Many thanks and best regards
Peter1 user thanked author for this post.
Dear Peter,
I saw your post. I am happy to see that my application is useful for you. Thank you for using it.
I haven’t had my sources with me for the moment.
I will have a look as soon as possible and send you the definition I used for the structure.
Just wait some days.Gaouargas
1 user thanked author for this post.
Dear Peter,
In fact, I don’t use the table poi_index_node because I could not reverse engineer it to understand the structure.
But the data I need (type of POI, description ans coordinates) can be found by an other way.First, I retreive all the categories from poi_categories (I keep fields “name” and “id” from this table).
The field “name” contains the type of POI, first need.
Then, with a poi_categories.id values (it is a technical ID, exemple : 14) I do a query in the poi_data table
like SELECT * FROM poi_data WHERE category = 14.
I keep the fields “data” and “id”. The field “data” contains the description of the POI, second need.
Finally, with the poi_data.id values (also technical ID, exemple 146) I do a query in the poi_index table
like SELECT * FROM poi_index WHERE id = 146.
I keep the fields “minlat” and “minlon” which contains the coordinates of the POI, last need.I hope that will help you Peter.
Greetings.1 user thanked author for this post.
Dear Gaourgas
Thank you very much for the detailed explanations.
You are right, this is sufficient to get the core pieces out of the database. I shall use your approach to get the information I need when I need it. Basically, I just wanted to output selected points in a format other than KML (e.g. CSV) and aggregated rather than as individual files. I can do that directly with your approach rather than by processing your KMLs. Of course my approach does not have a nice GUI 😉
Thanks again and best regards
Peter1 user thanked author for this post.
- You must be logged in to reply to this topic.