Merging SQL databases

[alf]

Hello

Not sure if I have to put this as a wish or as a technical discussion.

Is there any way, within Locus, or using an external application on a PC, to merge 2 SQL database maps ?

Example:
I have offline maps A and B as SQL databases within Locus.
They come from the same source, but cover different areas and/or zoom levels.
I want to combine them in a single map, without having to download on line again all the data.


Thanks and regards

[sherman]

Hi,

if you do not insist on resulting sqlite format you can merge two or more sqlite map databases into .gemf format (by the way more memory efficient and quicker loading then sqlite) using this tool: http://koti.welho.com/tstenbe3/GemfTool

In case you insist on resulting sqlite format it is a matter of opening two database connections in whichever scripting/programming language and importing the data from one to the other. I guess no more then 10 lines of code.

[alf]

Sherman, hello

Thanks for the advises.

I was not able to get any usable result from GemfTool after several attempts (sometimes it plainly crashes, sometimes it generates a GEMF file with errors, sometimes the file is generated but unreadable by Locus, I could not find a consistent pattern of the software behavior).

What did work:

SQLite command:

insert into target.tiles select * from source.tiles where not exists (select * from target.tiles where (target.tiles.x=source.tiles.x and target.tiles.y=source.tiles.y and target.tiles.z=source.tiles.z))

"target" being the name of the database where the new tiles from the database "source" will be copied.
It is necessary to eliminate the tiles with duplicates x y z, if not the command will come with errors as the combination x y z  constitutes the unique key of the databases.

I used the software SQLite2009 (free for home users) to run the command after opening the "target" database and attaching the "source" database.
I did try the command in other SQlite packages, but it failed (perhaps due to the non-supported type of the image file).

[willdashwood]

Sorry to reopen an old thread but I wanted to do the same thing as alf. I was trying to merge all my "personal" sqllite maps generated by Locus in to the cache sqlite database for the online map from which the personal ones were generated.

It seemed to work well. I opened the .sqlitedb file in SQLite2009 and then attached all 11 of the personal maps. One by one I ran the command:

Code Select Expand

insert into main.tiles select * from db1.tiles where not exists (select * from main.tiles where (main.tiles.x=db1.tiles.x and main.tiles.y=db1.tiles.y and main.tiles.z=db1.tiles.z))

changing "db1" to "db2", "db3", etc etc. It seemed to work as the original .sqlitedb file grew to nearly 2.5GB. Also files where I expected there to be a lot of overlap didn't alter many rows which would make sense.

However, when I came to open the file in Locus on the device, the cache was empty. Does anyone know why that might be?

Is it the file size is too big? Could it be something to do with the .sqlitedb-journal file? How does that relate to the .sqlitedb file?

Many thanks,
Will

[prawns]

I have x3 offline maps, ranging from 3.2gig-3.5gig big (and they work fine) so I assume file size isnt your issue. I used MOBAC to join my maps together without issue a while back - I wasnt trying to turn the "online maps" into "offline maps" though like you are by forcing a cache

[willdashwood]

Thanks prawns. Were the maps you generated via MOBAC in sqlite format?

[prawns]

Yep   in sql

I end up with x2 sql files per map source for my system of doing things.

One sql file is for my whole state in 0-13 zoom level.  And the other  file for select areas of my state in zoom 14+

  I can just add more of the map to the 14+ sql file as I find more spots where I want more zoom (so for example if i want more of XXX town in a higher zoom, I just select that area from within locus, check the boxes for zoom 14,15,16,17. And then in the "download type" option dropbox I select "External map sql type" and point it at the existing 14+sql file so I can just add extra data to that file instead of creating a new one, prevents downloading the same tiles twice for no gain

Thats my system anyway. Originally I had about half a dozen different files for each map source, but I ended up with alot of overlaping duplicate areas in the files so I went with a 2 file system for each map source based on zoom levels to avoid the duplicate tile issue. I used MOBAC to join the existing half dozen sql files together to match my new system (it was alot of arsing about and a little confusing joining SQL files in MOBAC but I managed it )