0

When I started building a database in Midvatten I followed the tutorial:

https://github.com/jkall/qgis-midvatten-plugin/wiki/Tutorial

This tutorial suggests for setting up a new database:

"When prompted, select CRS 4326 (WGS84)"

As I was new to GIS I had not realised that this instruction was specific only to the demonstration data set that can be download to use with the tutorial and there might be reasons why I might choose another CRS.

I am now more up to speed with the question of Geographical versus Projected CRSs, (largely due to http://lyzidiamond.com/posts/4326-vs-3857) and now realise that I should have chosen EPSG 3857 rather than EPSG 4326.

The main reason is the warning in the Midvatten wiki for section plots:

"Please note! This feature assumes that projected coordinate reference systems are used (it will not work with tutorial data where CRS=EPSG 4326). It is also highly recommended to use the same Coordinate Reference System (CRS) for both the project and all involved layers."

I also now wish to use layers from the Openlayers plugin with my data set which also uses EPSG 3857.

I don't mind deleting and reloading the point observations data and stratigraphy data but as I have substantially changed the data domains it would take a significant amount of time to rebuild these.

Do I have to rebuild my database from scratch or is there a way of changing the CRS after the database has been created?

2

The easiest method is probably to export to a new spatial database:

https://github.com/jkall/qgis-midvatten-plugin/wiki/3.-Export-data#export-data-from-your-database-into-a-new-spatialite-database

However, you might need to remove the line for this export (It's needed when creating a new database though):

self.insert_datadomains(set_locale)

from midvatten/tools/create_db.py. Else it might block your altered zz-tables. This is a bug which I realized just now.

  • Thank you @HenrikSp, I tried both with and without removing the line from create_db.y and only selected "obs_points" and "obs_lines" as I can easily import the stratigraphy data afterwards. – S. OConnor Mar 12 '18 at 11:58
  • Without removing line produced differences in the zz tables as you predicted: tablename;exported_db;source_db. zz_capacity;28;20. etc. I also looked at the attribute tables and there was a mixture of my changes plus what existed already. Removing the line removed these differences. There were of course other differences relating to the additional layers that I chose not to export. But I was unsure about the differences: spatial_ref_sys;4924;1 and spatial_ref_sys_aux;4873;1? – S. OConnor Mar 12 '18 at 12:19
  • The differences spatial_ref_sys;4924;1 (from the log) just means that the new exported database contains information about 4924 spatial reference systems while the original one only had 1 (WGS84, epsg 4326). The new database will therefore be slightly larger. If you want to decrease the size you could delete the extra unused reference systems from spatial_ref_sys and spatial_ref_sys_aux. – HenrikSp Mar 12 '18 at 12:51
  • this export process increased the database size from 848 kb to 5.8 mb so if deleting the unused reference systems from spatial_ref_sys and spatial_ref_sys_aux could retrieve some of this space it would be useful. Can you please explain how to access these tables to delete the unused references and how to know which ones need to be deleted or is this done by comparison with the original database? – S. OConnor Mar 13 '18 at 11:41
  • I have now worked out how to do the deletions to the spatial_ref_sys tables. In QGIS menu select Datebase>DB manager>DB manager and select the database that contains the tables in the pop up window. Click on the 'SQL window' icon then in row 1 enter 'DELETE FROM spatial_ref_sys_aux WHERE srid < 3856' then click on Execute (F5) button. You want just the 3857 row to be preserved in both tables so repeat changing the phrase to '> 3858' then repeat for the other table (delete '_aux') and use twice < 3856, > 3858. When you have completed all for deletion go to the menu and select database>vacuum.. – S. OConnor Mar 15 '18 at 21:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.