Not important: Possible SQLite mismatch field type in waypoint.db

Alf Onico shared this problem 5 years ago
Closed

Hello


Observed on Locus versions 3.5.3 on Android 2.3 and 3.21.0 on Android 4.0.4.


I have been trying (eventually with success) to merge Locus waypoint.db databases coming from 2 of my phones.

I faced a problem when eliminating duplicates based on longitude and latitude: many duplicates were still in the resulting merged database. I tried several database managers with exactly the same results.

The issue is perhaps due to the following:

The longitude and latitude values are stored properly as decimal degrees, as demonstrated when the database is viewed with some SQlite managers (for exemple Firefox SQlite manager). But with some other managers, ( for example SQlite 2009) the values appear as the integer part only of the value.

On all managers, when viewing the type of the waypoint table fields longitude and latitude, the type is INTEGER (on both Locus 3.5.3 and 3.21.0 waypoints.db files).

Should the type of longitude and latitude fields be set to FLOAT (as the fields elevation and speed)?

Or I am missing something ?

I attached a screen dump of SQLite2009 showing the data types of the waypoints table fields.

This issue is not important as the longitude and latitude values are stored with all their decimal places despite being declared as INTEGER type, even after manipulation with a SQLite database manager. There is no impact on the functionality of Locus. The only and minimal possible problem only arises when manipulating the waypoints databases outside of Locus using the longitude and latitude fields..


Regards

Alf

Replies (3)

photo
1

Good day Alf,

this is correct observation. Coordinates are indeed created as INTEGER type during "Create query". Don't ask me why I did this few years ago when few lines below are parameters like altitude etc. correctly created as FLOAT.


From a little I know about SQLite database, this should (and as we may see, it isn't ) a real problem for Locus Map itself. And well ... merging of databases is not recommended, but if you wants to risk it, now you know it's dangerous places even better then me :). Good luck and thanks for notification.

photo
1

Hello Alf,

once all this is sorted, could you make your merging procedure available to the community ?

Locus still lacks a valid cross device synch/merge feature. And your's would help out. Not for everybody, but those who dare.


Menion,

with your knowledge of the "side effects" of your data model, you could add some consistency checks :-) ?

photo
1

Hello Michael

A) Waypoint database "waypoints.db"

This is what worked for me to merge two databases of waypoints (waypoints.db) eliminating the duplicates.

You need a PC SQlite database manager. If you are using a Windows PC, I suggest to use Firefox addon "SQLite manager", but any manager allowing to run SQLite commands should do. I have used with success SQLite 2009, but this manager doesn't show the decimal part of data that has been declared as integer (not a major problem, the manager will keep the decimal part of the values).

The process implies 6 steps:

1- Make a copy of the target database in case something unwanted happens. Copy the source and target "waypoints.db" databases to your PC. Change their name to clearly identify them.

2- Modify the unique identifier field of the target database "categories" table to avoid SQlite errors.

3- Add from the source database the categories that are not in the target database.

4- Modify the unique identifier field of the target database "waypoints" table to avoid SQlite errors.

5- Add all records from the source database to the target database

6- Remove duplicate records

I should be possible to do steps 4 and 5 with a single command. I didn't manage to have this working perfectly, I was missing 3 records (out of 1500). Hence the 2 separate commands.

Detailed steps:

1- Once a copy of the target database has been made, copy the source and target "waypoints.db" databases to your PC. Change their name to clearly identify them, for example "target.db" and "source.db". In the rest of the instructions below, I will use "target" and "source". If you are using SQlite 2009 manager, in the commands described in the following steps you will need to use "main" for the target database and "db2" for the source database, whatever are the actual database names.

In the database manager open (connect) the target database and attach the source database. If your SQlite manager seems to be unable to see the databases, this is due to the fact that it is expecting some specific extensions at the end of the database names (for example db3, or sqlite). You can then either change the database extension, or simply type * (star) in the SQlite manager file explorer to show all files. You can then select the desired database. Visualize and note the number of records of the table "waypoints" for each database.

2- In the SQlite manager command window, type and run the commands

update target.categories set _id = _id + 100

update target.waypoints set parent_id = parent_id + 100

This will add 100 to the unique identifier number of the table "categories". "The "waypoints" table field "parent_id" is the same that the "categories" table "_id" field. 100 is any number that will make the smaller unique identifier value of the "categories" table of the target database bigger than the largest identifier value of the source database. If in the following step an error message similar to "Unique constrain failed" appears then increase this number. You can visualise the "categories" table of the target database to see if the "-id" field has been modified, and the "waypoints" table to see if the "parent_id" field has been modified.

3- In the SQlite manager command window, type and run the command

insert into target.categories select * from source.categories where not exists (select * from target.categories where (target.categories.name=source.categories.name))

This will add the categories of the source database that don't exist in the target database. You can visualise the "categories" table of the target database to see if the categories have been added. If an error message similar to "Unique constrain failed" appears, go back to step 2 and increase the number you used to shift the unique identifier of the target database table "categories".

4- In the SQlite manager command window, type and run the command

update target.waypoints set _id = _id + 5000

This will add 5000 to the unique identifier number. 5000 is any number that will make the smaller unique identifier value bigger than the largest identifier value of the source database. If in the following step an error message similar to "Unique constrain failed" appears then increase this number. You can visualise the "waypoints" table of the target database to see if the "-id" field has been modified.

5- In the SQlite manager command window, type and run the command

insert into target.waypoints select * from source.waypoints

This will add all the waypoints of the source database table "waypoints" to the target database table waypoints". If an error message similar to "Unique constrain failed" appears, go back to step 4 and increase the number you used to shift the unique identifier of the target database table "waypoints". You can visualize the number of records of the table "waypoints" of the target database. It should be the sum of the records of the original target and of the source databases.

6- In the SQlite manager command window, type and run the command

delete from target.waypoints where _id not in (select min(_id ) from target.waypoints group by time_created)

This will eliminate duplicate records based on the date and time the record was created. If you think that it possible that in the source and target databases there is a chance that two different waypoints were created within less one second of one another, you can add additional rejection criteria fields, for example

delete from target.waypoints where _id not in (select min(_id ) from target.waypoints group by time_created, elevation, parent_id)

This should reject duplicates only if they have same creation time, elevation, and category (I have not tested this). Beware that the longitude and latitude fields, being declared as integers, will be taken into account by SQlite commands only for their integer part, and therefore are not very good duplicate rejection fields.

You can now rename the target database "waypoints.db" and copy it to the locus/data/database Android folder (be sure that you have a copy of the original database).

To check if everything went smoothly, check on Locus if additional categories and points were added. For each category, you should have at least the same number of points than before the merge. Beware that you need to open each category and close it to update the count of points per category that is shown on the Locus point window

There are probably many other ways to achieve the same. When I will have time, I will see if I can change the type of the latitude and longitude fields from integer to float, and if this brings any problems to Locus. If no, then the duplication rejection could be based on longitude and latitude.

B) For the offline map sqlite files, the procedure to merge and reject duplicates is much simpler. A single command is needed:

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))

Regards

Alf

Leave a Comment
 
Attach a file