search

roam-qgis / Roam

Simple data collection built using QGIS.
http://roam-docs.readthedocs.org/en/latest/
GNU General Public License v2.0
166 stars 60 forks source link

Uploading data collected offline #412

Closed TonyFMCMC closed 6 years ago

TonyFMCMC commented 6 years ago

I am working mostly online to an MSSQL server. But when out of range I want to be able to collect data and then send it to the server when I have a connection. Windows 10.

So I am using a geopackage file to collect offline records, and I have a bat file using ogr2ogr to upload the records to MSSQL server. That part works fine. But of course the records in the geopackage remain. An easy way to delete them would be to simply replace the geopackage file with an empty one. But unless I explicitly close the project, the file is in use, and a copy command fails.

How are other people managing this process? Is there a way in the script to close the project? Any suggestions?

bzeeb commented 6 years ago

We built a custom .sqlite -> mssql sync process as we had lots of issues with mssql replication.

We only delete/insert the records of sqlite table rather than delete the entire file, (avoids the locking issue).

Originally did use ogr2ogr however over time moved away from that as it had lots of its own issues, particularly around datetime values.

You could run an ogrinfo command for now against the local file and backup then delete the records.

Our process is more of a mass deployment / many users syncing together at the same time... sounds like you only have one field user so no need for anything fancy.

TonyFMCMC commented 6 years ago

We will have 3 field users in the immediate future. But one user per local file. how are you deleting records in the local file while it is in use by roam? And I noticed the datetime issue with ogr2ogr. worked around it by storing the datetime as a string in the local file.

bzeeb commented 6 years ago

It should allow you to do this fine, the file lock shouldn't be blocking this?

There are a few different modes you can open a connection to SQLite in, I've not worked on that part myself in a few years but I didn't recall it being an issue.

We are definitely editing the .sqlite file while Roam has an open project.

TonyFMCMC commented 6 years ago

Ah yes, sorry didn't realise that ogrinfo had capability to edit file. 

C:\IntraMaps Roam\projects\Works_reviewing>ogrinfo -sql "delete from OfflineRepo
rts" .\_data\offlinereports2.gpkg

worked a treat. I can confirm that it could do it while Roam has the project open. Thanks for your help!

bzeeb commented 6 years ago

That's the one, no problem