[cebewee]

The poi positions are stored in the poi_index table; the poi_index_node, poi_index_rowid, poi_index_parent tables are just an artifact of the poi_index table using an R*Tree index. See https://www.sqlite.org/draft/rtree.html.

That means, all the information you need can be obtained by quering the poi_index table. The tables poi_index_node and so on can be safely ignored.

[YPNA]

I made some progress with my pet project – adding geocaches to a Locus POI database.
The tools used were a Powershell script to generate points and the Spatialite GUI to load them into a Locus POI database.
I was, at this stage, interested in finding out if I could make Locus show an icon of choice. While I failed with this one, I found that the icon is determined by the value selected from the FoldersSub table regardless of what value I select from the FoldersRoot table. Both of these values are associated with a point via an entry in the Points_Root_Sub table.
I also found that there must be at least one entry in the Points_Key_Value table for the point to be usable in the Locus dialogs. It does not matter which combination is selected.
The folowing Powershell script generates the 13 times 77 points representing all combinations of FoldersRoot and FolderSub values. The values I selected for the Points_Key_Value represent a URL. The location is calculated from the root and sub values. Please refer to the attached screenshot for the result.

#---------------------------------------------------------------------------------------------------
# Path and name of XML file:
#---------------------------------------------------------------------------------------------------
$PathOut   = $Drive + "Temp\AllIcon.osm.sql";
#---------------------------------------------------------------------------------------------------
Remove-Item -Path $PathOut 
#---------------------------------------------------------------------------------------------------
$CurWPT = 0;
$FR = 1; # 1 -  13
$FS = 1; # 1 -  77
$TK = 1; # 1 -  37
$TV = 1; # 1 - 208
#---------------------------------------------------------------------------------------------------
# Empty Points, Points_Key_Value, and Points_Folder_Sub tables for a clean start
#---------------------------------------------------------------------------------------------------
$LineP   = $("DELETE FROM 'Points';");
$LineP   | Out-File -FilePath $PathOut -Append;
$LinePKV = $("DELETE FROM 'Points_Key_Value';");
$LinePKV | Out-File -FilePath $PathOut -Append;
$LinePFS = $("DELETE FROM 'Points_Root_Sub';");
$LinePFS | Out-File -FilePath $PathOut -Append;
#---------------------------------------------------------------------------------------------------
# SELECT * FROM POINTS_KEY_VALUE WHERE TAGVALUES_ID IN (SELECT ROWID FROM TAGVALUES WHERE NAME LIKE 'HTTP%')
#---------------------------------------------------------------------------------------------------
$TKArray = 37; # 1,2,8;
$TVArray = 83; # 5,13,16;
Foreach ($TK in $TKArray)
{
  Foreach ($TV in $TVArray)
  {
    For ($FR = 1; $FR -le 13; $FR++)
    {
      For ($FS = 1; $FS -le 77; $FS++)
      {
        $CurWPT = $CurWPT + 1;
        $WPTLocation = $("GEOMFROMTEXT('POINT(8.81" + '{0:d2}'-f $FR + " 47.22" + '{0:d2}' -f $FS + ")', 4326)");
        #-----------------------------------------------------------------------------------------------
        # Compose output lines 
        #-----------------------------------------------------------------------------------------------
        # Compose output line for Points table
        #-----------------------------------------------------------------------------------------------
        $LineP = $("INSERT INTO 'Points' VALUES(" + $CurWPT + ",'P','PT-FR" + '{0:d2}'-f $FR + "-FS" + '{0:d2}' -f $FS + "-TK" + '{0:d2}' -f + $TK + "-TV" + '{0:d2}' -f + $TV  + "'," + $WPTLocation + ");");
        $LineP | Out-File -FilePath $PathOut -Append;
        #-----------------------------------------------------------------------------------------------
        # Compose output line for Points_Key_Value table
        #-----------------------------------------------------------------------------------------------
        $LinePKV = $("INSERT INTO 'Points_Key_Value' VALUES(" + $CurWPT + "," + $TK + "," + $TV + ");");
        $LinePKV | Out-File -FilePath $PathOut -Append;
        #-----------------------------------------------------------------------------------------------
        # Compose output line for Points_Root_Sub table
        #-----------------------------------------------------------------------------------------------
        $LinePFS = $("INSERT INTO 'Points_Root_Sub' VALUES(" + $CurWPT + "," + $FR + "," + $FS + ");");
        $LinePFS | Out-File -FilePath $PathOut -Append;
        #-----------------------------------------------------------------------------------------------
      }
    }
  }
}
#---------------------------------------------------------------------------------------------------
# Report row counts for Points, Points_Key_Value, and Points_Folder_Sub tables
#---------------------------------------------------------------------------------------------------
$LineP   = $("SELECT COUNT(*) FROM 'Points';");
$LineP   | Out-File -FilePath $PathOut -Append;
$LinePKV = $("SELECT COUNT(*) FROM 'Points_Key_Value';");
$LinePKV | Out-File -FilePath $PathOut -Append;
$LinePFS = $("SELECT COUNT(*) FROM 'Points_Root_Sub';");
$LinePFS | Out-File -FilePath $PathOut -Append;

Write-Host "Press any key to continue ...";
$Pause = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown");

In my case, I needed to reformat the output file to remove the BOM. Otherwise, the Spacialite GUI program throws up when it tries to process the file.
BTW… I could also create an empty DB using the Spacialite GUI program. After some fiddling around with the table contents I was able to display my points in Locus, too. The fiddling involved copying some of the metadata tables over from the Locus POI DB.
Hope this helps to progress things… with the approaches shown above for extracting points from an OSM POI DB it is a piece of cake to add them to your homemade POI DB… if you find a suitable icon or if you are happy with the default icon (which ma be OK if you only have one category that does not fit into the Locus world).

[mictie]

@Gaouargas:
Thank you for providing the kml/kmz-extraction program!
It’s working almost perfectly under W10 / 64 and I am able to extract the POIs of interest.
The only thing which is not working is the ‚restriction to gepgraphical map‘. It seem that the web-map can not be accessed. Instead of a map, I just see the message the the remote server returned an error 403. (see attachment).
It would be great if you could fix this (after a very loooong time) …
Thank you!

[Gaouargas]

@mictie
Thanks for your return.
I think that there is a problem with the library which access to the map.
I will have a look on it. Stay tuned ;-).

[Gaouargas]

@mictie
Try this new version : https://drive.google.com/open?id=1se4dz1VTwB_MMmzF-3jWscGel6HiYhrI.
Attention, all map providers do not work. But OpenStreetMap and GoogleMap are OK.
Say me what.
Thanks.

[mictie]

Thank you!
I am currently travelling with no PC access, and will only be able to test it next weekend.

[mictie]

@Gaouargas
I was able to test the new program, and it’s working – Thank you!!!
Google Maps, Bing Maps, OpenCycle Maps, WikiMapia Map, Yandex and possible others are working fine.
OpenStreetMaps tells me that they don’t have imagery at this zoom level (I am looking at Singapore / Malaysia / Indonesia.)

Thank you again for updating this very useful program!

[juergenl]

I made a python script that can convert the OpenAndroMaps POI fies into the format that is used by Locus.
You can find it here: https://github.com/lieblerj/poi_converter

[mictie]

@juergenl: Thank you – looks very interesting!
Question1: when trying I get this error message: „spatialite.connection.LoadExtensionError: Failed to load SpatiaLite extension. Verify that your python module sqlite3 has load_extension support and check that libspatialite is installed. Tried extension names: mod_spatialite, mod_spatialite.so, mod_spatialite.dylib“
It seems I am missing something (running it on Win10/64)
Question2: do you prefer questions here or via github?
Thanks!

[mictie]

@juergenl: Problem solved! had to install libspatialite via System32…

[juergenl]

Thanks for pointing out, i think i need the installation section needs some additional details.
Regarding Question 2: General topics can be discussed here for issues and observations with the code i would recommend github.

[mictie]

@juergenl: I have done some tests and it’s working – thank you!

It seems that the information per POI is reduced quite a lot. Eg. I don’t see opening-hours, or bus-lines (for bus-stops) … here, although this information is included in the original OAM-poi-file.
Is there a chance you can include the complete info into the database, or give me some hint how to do it?

[juergenl]

@mictie: Currently i include only those tags that are in the TagKeys table (see config/init.sql line 153++ for the values). Opening-hours should be included, bus-lines are not.
Please submit an issue on github with a link to an OAM-poi-file and some POI so i can have a look.

[mictie]

Thanks for the quick reply!
Sorry, I was wrong about ‚opening hours‘ and manged to include bus-lines (route_ref) – it’s working!

There is one issue which seems to be related either to Locus or OAM: The POIs also include a link to ‚More on openstreetmap.org‘ which seems to wrong for POIs coming from OAM.poi-files. (I tried a couple of locations both in Austria as well as in Singapore).
When using different map-sources (.osm converted to .pbf) these links look ok.
No idea if these links are part of the db or generated by Locus…

[juergenl]

@mictie: I could not find any source for this special link in the database file. I think the link is automatically added by Locus itself.

[Autor]

Beiträge