[MANUAL] - using SQL map files bigger then 2GB

Started by Menion, January 17, 2011, 06:53:14

0 Members and 2 Guests are viewing this topic.

Menion

needed version - 0.9.22 and higher

Android limitation
After some testing, seems that Android cannot handle files bigger then 2^31 b, so around 2GB!! Here I slowly create solution how to use maps bigger then this limitation in Locus software, so ...

ACTUAL STATE
OK, solution is out in version 0.9.22. It need to be tested!

How dows it work
In Locus/maps directory create new directory for your new SQL map. Into this directory upload all files that you want to be as one map. After Locus start, it initialize all map parts (you have to wait for a while, 1GB map takes around 2 minutes, so count with it) and then you can use it. All files in this directory now work as ONE MAP!

Also, for perfect usage, there should be any tool that help to create these maps.

Locus
In locus you should create maps lower then 2GB. Bigger cannot be created due to Android limitation. I should create some tool that help separate maps into more parts directly in Locus, but will not be used by many people as well as I have important thinks to do. So actually, to create these maps in Locus, you ahve to create every part separate and manually and then also manually copy them into one directory in Locus/maps!

Mobac
Much better tool to create big maps. Only one disadvantage. It have no tool to separate maps into parts needed by Locus. So, best solution as I can see, if anyone wrote tool (in Python for example) that separate big maps into 2GB parts!


NEXT METHOD IS DEPRECATED, SO DON'T USE IT
[s:1vycvo9h]In android device seems to be some limitation on external SQL database (on internal also). This limitation don't allow to go through simple sql query on whole table so you have to use this alternative method! (It's also useful if you want faster initialization or you have any other troubles with SQL map initialization process)

  • Download simple SQL database browser. I use this (//http://sqlitebrowser.sourceforge.net/) ... It's fast and easy to handle. You can download it here.
  • After download, extract (install) and run.
  • In program, press File->Open Database and choose you map file. Every loading and action takes a little time due to size of your map file so be a little patient!
  • After file load, do to last tab called Execute SQL and into SQL String insert this text
    SELECT DISTINCT z from tiles and press Execute query
  • After process complete, you'll see in bottom Data returned area, zoom levels in map file. Copy all these values into separate text file named same as your map file and separate values with ";", not just new line.
  • So after whole process, you'll have one file MyMap.sqlitedb (original map file) and one file MyMap.txt which contains for example this text
    -1;1;3;5;7;9
  • Copy both into Locus/maps directory or add them on Custom map page as externel map. Hope this works for you. Comments are welcome!

IMPORTANT
If you change map file, initialization process will run for this map again (it depend on time of last edit of map file) so TXT file will be loaded again.
But when you for example download any new layer into map file, but you do not change TXT file before initialization process run in Locus, no new layer will be visible. If this problem occur, change layers in TXT file to correct values and also DELETE cache directory. Initialization process will run again and now ... correctly![/s:1vycvo9h]
- Official help (ideas, questions, problems): help.locusmap.eu
- Advanced topics, sharing of knowledges: you're here!
- LM 4 Beta download, LM 4 Release download
  •  

naxan

#1
Hi,

unfortunately, with a 4.5GB map, it doesn't work.. in the end:
With help by the .txt-file, I can import the map and also show it.
but: in highest zoom level, somewhere in my map theres a 'vertical border': if I cross it from left to right, I just get a white screen.
If I swype back the left, tiles are shown again.

I think all tiles beyond a certain point in the db cannot be accessed. Here an excerpt from logcat:

I/Database( 3045): sqlite returned: error code = 266, msg = prepared statement aborts at 11: [SELECT image FROM tiles WHERE x=='17224' AND Y=='11749' AND z=='2']
E/FileMapTypeAbstract( 3045): appendRequest(), filePath: /mnt/storage/sdcard/Norditalien.sqlitedb
E/FileMapTypeAbstract( 3045): android.database.sqlite.SQLiteDiskIOException: disk I/O error
E/FileMapTypeAbstract( 3045): at android.database.sqlite.SQLiteQuery.native_fill_window(Native Method)
E/FileMapTypeAbstract( 3045): at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:70)
E/FileMapTypeAbstract( 3045): at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:283)
E/FileMapTypeAbstract( 3045): at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:264)
E/FileMapTypeAbstract( 3045): at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:171)
E/FileMapTypeAbstract( 3045): at android.database.AbstractCursor.moveToFirst(AbstractCursor.java:248)
E/FileMapTypeAbstract( 3045): at menion.android.maps.filemaps.FileMapTypeSql.directDownload(FileMapTypeSql.java:233)
E/FileMapTypeAbstract( 3045): at menion.android.maps.filemaps.FileMapTypeAbstract.appendRequests(FileMapTypeAbstract.java:254)
E/FileMapTypeAbstract( 3045): at menion.android.maps.FileMapLayer.drawMap(FileMapLayer.java:155)
E/FileMapTypeAbstract( 3045): at menion.android.maps.MapLayer.drawMap(MapLayer.java:120)
E/FileMapTypeAbstract( 3045): at menion.android.maps.MapContent.drawMap(MapContent.java:310)
E/FileMapTypeAbstract( 3045): at menion.android.maps.background.SimpleMapScreenBackground.drawContent(SimpleMapScreenBackground.java:29)
E/FileMapTypeAbstract( 3045): at menion.android.maps.background.AbstractMapScreenBackground.onDraw(AbstractMapScreenBackground.java:50)
E/FileMapTypeAbstract( 3045): at android.view.View.draw(View.java:6740)
E/FileMapTypeAbstract( 3045): at android.view.ViewGroup.drawChild(ViewGroup.java:1640)
E/FileMapTypeAbstract( 3045): at android.view.ViewGroup.dispatchDraw(ViewGroup.java:1367)
E/FileMapTypeAbstract( 3045): at android.view.ViewGroup.drawChild(ViewGroup.java:1638)
E/FileMapTypeAbstract( 3045): at android.view.ViewGroup.dispatchDraw(ViewGroup.java:1367)
E/FileMapTypeAbstract( 3045): at android.view.ViewGroup.drawChild(ViewGroup.java:1638)
E/FileMapTypeAbstract( 3045): at android.view.ViewGroup.dispatchDraw(ViewGroup.java:1367)
E/FileMapTypeAbstract( 3045): at android.view.View.draw(View.java:6743)
E/FileMapTypeAbstract( 3045): at android.widget.FrameLayout.draw(FrameLayout.java:352)
E/FileMapTypeAbstract( 3045): at android.view.ViewGroup.drawChild(ViewGroup.java:1640)
E/FileMapTypeAbstract( 3045): at android.view.ViewGroup.dispatchDraw(ViewGroup.java:1367)
E/FileMapTypeAbstract( 3045): at android.view.View.draw(View.java:6743)
E/FileMapTypeAbstract( 3045): at android.widget.FrameLayout.draw(FrameLayout.java:352)
E/FileMapTypeAbstract( 3045): at com.android.internal.policy.impl.PhoneWindow$DecorView.draw(PhoneWindow.java:1876)
E/FileMapTypeAbstract( 3045): at android.view.ViewRoot.draw(ViewRoot.java:1407)
E/FileMapTypeAbstract( 3045): at android.view.ViewRoot.performTraversals(ViewRoot.java:1163)
E/FileMapTypeAbstract( 3045): at android.view.ViewRoot.handleMessage(ViewRoot.java:1727)
E/FileMapTypeAbstract( 3045): at android.os.Handler.dispatchMessage(Handler.java:99)
E/FileMapTypeAbstract( 3045): at android.os.Looper.loop(Looper.java:123)
E/FileMapTypeAbstract( 3045): at android.app.ActivityThread.main(ActivityThread.java:4627)
E/FileMapTypeAbstract( 3045): at java.lang.reflect.Method.invokeNative(Native Method)
E/FileMapTypeAbstract( 3045): at java.lang.reflect.Method.invoke(Method.java:521)
E/FileMapTypeAbstract( 3045): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
E/FileMapTypeAbstract( 3045): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
E/FileMapTypeAbstract( 3045): at dalvik.system.NativeStart.main(Native Method)
D/PersistentEventStore/putEvent( 3045): Row ID: 648, Event ID: 648
I/Database( 3045): sqlite returned: error code = 266, msg = prepared statement aborts at 11: [SELECT image FROM tiles WHERE x=='17225' AND Y=='11749' AND z=='2']
E/FileMapTypeAbstract( 3045): appendRequest(), filePath: /mnt/storage/sdcard/Norditalien.sqlitedb
E/FileMapTypeAbstract( 3045): android.database.sqlite.SQLiteDiskIOException: disk I/O error
E/FileMapTypeAbstract( 3045): at android.database.sqlite.SQLiteQuery.native_fill_window(Native Method)
E/FileMapTypeAbstract( 3045): at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:70)
E/FileMapTypeAbstract( 3045): at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:283)
E/FileMapTypeAbstract( 3045): at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:264)
E/FileMapTypeAbstract( 3045): at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:171)
E/FileMapTypeAbstract( 3045): at android.database.AbstractCursor.moveToFirst(AbstractCursor.java:248)
E/FileMapTypeAbstract( 3045): at menion.android.maps.filemaps.FileMapTypeSql.directDownload(FileMapTypeSql.java:233)
E/FileMapTypeAbstract( 3045): at menion.android.maps.filemaps.FileMapTypeAbstract.appendRequests(FileMapTypeAbstract.java:254)
E/FileMapTypeAbstract( 3045): at menion.android.maps.FileMapLayer.drawMap(FileMapLayer.java:155)
E/FileMapTypeAbstract( 3045): at menion.android.maps.MapLayer.drawMap(MapLayer.java:120)
E/FileMapTypeAbstract( 3045): at menion.android.maps.MapContent.drawMap(MapContent.java:310)
E/FileMapTypeAbstract( 3045): at menion.android.maps.background.SimpleMapScreenBackground.drawContent(SimpleMapScreenBackground.java:29)
E/FileMapTypeAbstract( 3045): at menion.android.maps.background.AbstractMapScreenBackground.onDraw(AbstractMapScreenBackground.java:50)
E/FileMapTypeAbstract( 3045): at android.view.View.draw(View.java:6740)
E/FileMapTypeAbstract( 3045): at android.view.ViewGroup.drawChild(ViewGroup.java:1640)
E/FileMapTypeAbstract( 3045): at android.view.ViewGroup.dispatchDraw(ViewGroup.java:1367)
E/FileMapTypeAbstract( 3045): at android.view.ViewGroup.drawChild(ViewGroup.java:1638)
E/FileMapTypeAbstract( 3045): at android.view.ViewGroup.dispatchDraw(ViewGroup.java:1367)
E/FileMapTypeAbstract( 3045): at android.view.ViewGroup.drawChild(ViewGroup.java:1638)
E/FileMapTypeAbstract( 3045): at android.view.ViewGroup.dispatchDraw(ViewGroup.java:1367)
E/FileMapTypeAbstract( 3045): at android.view.View.draw(View.java:6743)
E/FileMapTypeAbstract( 3045): at android.widget.FrameLayout.draw(FrameLayout.java:352)
E/FileMapTypeAbstract( 3045): at android.view.ViewGroup.drawChild(ViewGroup.java:1640)
E/FileMapTypeAbstract( 3045): at android.view.ViewGroup.dispatchDraw(ViewGroup.java:1367)
E/FileMapTypeAbstract( 3045): at android.view.View.draw(View.java:6743)
E/FileMapTypeAbstract( 3045): at android.widget.FrameLayout.draw(FrameLayout.java:352)
E/FileMapTypeAbstract( 3045): at com.android.internal.policy.impl.PhoneWindow$DecorView.draw(PhoneWindow.java:1876)
E/FileMapTypeAbstract( 3045): at android.view.ViewRoot.draw(ViewRoot.java:1407)
E/FileMapTypeAbstract( 3045): at android.view.ViewRoot.performTraversals(ViewRoot.java:1163)
E/FileMapTypeAbstract( 3045): at android.view.ViewRoot.handleMessage(ViewRoot.java:1727)
E/FileMapTypeAbstract( 3045): at android.os.Handler.dispatchMessage(Handler.java:99)
E/FileMapTypeAbstract( 3045): at android.os.Looper.loop(Looper.java:123)
E/FileMapTypeAbstract( 3045): at android.app.ActivityThread.main(ActivityThread.java:4627)
E/FileMapTypeAbstract( 3045): at java.lang.reflect.Method.invokeNative(Native Method)
E/FileMapTypeAbstract( 3045): at java.lang.reflect.Method.invoke(Method.java:521)
E/FileMapTypeAbstract( 3045): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
E/FileMapTypeAbstract( 3045): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
E/FileMapTypeAbstract( 3045): at dalvik.system.NativeStart.main(Native Method)
D/PersistentEventStore/putEvent( 3045): Row ID: 649, Event ID: 649
I/Database( 3045): sqlite returned: error code = 266, msg = prepared statement aborts at 11: [SELECT image FROM tiles WHERE x=='17225' AND Y=='11749' AND z=='2']
D/dalvikvm( 3045): GC_FOR_MALLOC freed 5566 objects / 546296 bytes in 50ms
E/Cursor  ( 3045): Finalizing a Cursor that has not been deactivated or closed. database = /mnt/storage/sdcard/Norditalien.sqlitedb, table = tiles, query = SELECT image FROM tiles WHERE x=='17225' AND Y=='11749' AND z=='2'
E/Cursor  ( 3045): android.database.sqlite.DatabaseObjectNotClosedException: Application did not close the cursor or database object that was opened here
E/Cursor  ( 3045): at android.database.sqlite.SQLiteCursor.<init>(SQLiteCursor.java:210)
E/Cursor  ( 3045): at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:53)
E/Cursor  ( 3045): at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1345)
E/Cursor  ( 3045): at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1229)
E/Cursor  ( 3045): at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1184)

Due to
  http://www.mail-archive.com/android-dev ... 79647.html
I already tried adding another index to the db, which resulted in the above error happening _all_ the time in every zoom level...
Now trying other constellations, but without much hope..
  •  

Menion

#2
oh crap ... seems that my idea that for maps now will not be any limit is not comming to be true ...

hmm, thanks naxan for excelent test. No it's important to figure out if problems starts on 4GB map files or even on 2GB files.
- Official help (ideas, questions, problems): help.locusmap.eu
- Advanced topics, sharing of knowledges: you're here!
- LM 4 Beta download, LM 4 Release download
  •  

naxan

#3
Up to 2GB do work for me (they already worked before; largest one has 2.045.807).
But I didn't test larger than 2GB (besides 4,5GB).
Actually, in the optimum case, I'd have wanted  something like 14GB ;)
  •  

Menion

#4
Heh, 14GB seems to be good :) but unfortunately as you can see, this is not in my power!

so with this troubles, comes two questions!
1) which is maximum size allowed to read correctly and why is this limit!
2) is here any other solution on this problem? Maybe some special format with separated files working all together ...

anyway ... did anyone tried this huge maps on any other supported software how they handle them? RMaps for example ...
- Official help (ideas, questions, problems): help.locusmap.eu
- Advanced topics, sharing of knowledges: you're here!
- LM 4 Beta download, LM 4 Release download
  •  

mmcyklo

#5
Hi there,

any news in the possibility of over 2GB maps (at least 2-4GB as Android does not support NTFS format so only 4GB is possible to put on SD)?

Thanks
  •  

GOF007

#6
I have no SQL knowledge but just a simple idea.
If there is 2 GB limit per DB file why dont just split the file?

For large original map file, it should be possible to run a script to split them.....
  •  

Menion

#7
I'm already working on it, don't worry, solution will be available. Anyway I'll not do direct support into Locus to download and automatically separate this big map files. It should be better to create this by Mobac and then separate to more files as you wrote.
- Official help (ideas, questions, problems): help.locusmap.eu
- Advanced topics, sharing of knowledges: you're here!
- LM 4 Beta download, LM 4 Release download
  •  

mmcyklo

#8
This woud be realy perfect solution - I have created approx 10 GB full resolution map for CZSK, so if it would be possible to split it into 5 x 2GB files and to use it in Locus (like one map) would be excelent!
  •  

ykf

#9
Hi, I'm a java developer, and I can help to write a small tool to split the sqlite db into separate files. Menion can you tell me how Locus now read the database of more than 1 file?

Menion

#10
Hi,
  hmm excellent! What do you exactly need to know?

I just object that hold list of database files sorted by filename (just in Arraylist). When request for any tile come, I'm doing through all files and check if this tile is in database.

When second request on tile come, I then check firstly the last used database file. If nothing there, then I go as in first case, from first to last (except the one already tested).

So question is, what is the best separate algorithm right? There is not much big problem count of files. Best for performance will be if whole zoom will be in same file. You should create some simple solution as

  • create first file
  • count files for first zoom and put them into file
  • count files for second zoom, if small enough to put into first, add. Otherwise create second file
  • and so on. There should be of course any problems ...

When zoom level is bigger then 2GB, then you should separate it into more then one file also by any algorithm. If only to two parts, it's simple. If more, this should be interesting ... :)

And last think. Initializing of 2GB SQLite db file (just executing query "select distinct z from tiles") takes on android cca 3 minutes!! It will be good to on the end, create some txt file with zoom values in every file. Best will be for every file, same file with "txt" ending, that contain numbers separate by comma with zoom values. Initializing of this map will then be incredibly fast ...

uff, long story from me. Ask whatever you need. I hope you understand whole concept, how Locus handle these maps. I expect then this will not change in future. Looks fast enough ...
- Official help (ideas, questions, problems): help.locusmap.eu
- Advanced topics, sharing of knowledges: you're here!
- LM 4 Beta download, LM 4 Release download
  •  

mmcyklo

#11
Hi there,

seems to be very interesting.

BTW I have to come with the results of testing huge maps:

 - CZSK with relief full resol
 - 25 files each zoom separately (z1-13), zooms 14 and 15 in few parts less than 2 GB
 - in total 10GB !
 - initialisation very slow (also with white screen) - 20 min
 - but after initialisation very fast and I must say ecxelent (only now it is initialisation every time when Locus is opened but only for 1-2 sec, so no problem).

So thanks a lot menion for this module "Separate map (SQL type)", and of course for the whole app!!!
  •  

Menion

#12
Yes, it's exactly as I wrote. Initializing takes too much time. It have to be done with some help of external file.

You have initializing maps every start? That means that you have any file in maps directory that have some problem with initializing. You have file in maps directory but missing in list of offline maps! Check it ...

and thanks for tests! This show that whole system works fine only needs some small improvements. Fine fine ...
- Official help (ideas, questions, problems): help.locusmap.eu
- Advanced topics, sharing of knowledges: you're here!
- LM 4 Beta download, LM 4 Release download
  •  

ykf

#13
I never worked on sqlite, and after I go though the usage of it, it seems that if you have a zoom level which across several db files, you need to have multiple connections to query for that zoom level and consolidate the result sets together. This is not very efficient is it? Is there any chance to split db files based on x instead of z? This of course depends on your querying needs on Locus, I dunno how you query the files of course  :lol:

Btw, I'm looking at the MOBAC source code and seems I can add a new atlascreator impl, although I guess MOBAC authors will not be too happy on that because I saw them rejecting your new format requests on their forum... :oops:

mmcyklo

#14
Hi there,

I am continuing testing of huge files and I have one questions:

This 2GB limitaion is exactely 2GB or 2 000 000 000 B or 2 000 000 kB or 2 000 MB?

Thx
  •