6. Database management - jkall/qgis-midvatten-plugin GitHub Wiki

Create a new database

To create a new database of predefined design, select:

  1. Select Midvatten - Database management - Create a new empty Midvatten DB
  2. Select the locale for the new database. Currently, only sv_SE (Swedish) has g
  3. In the pop-up window, select desired coordinate reference system, CRS
  4. Set filepath and -name of database file (.sqlite)
  5. A new database will be created with predefined design. *

* Warning! If you are running QGIS built on top of SpatiaLite 4.x, then your database will be created according to SpatiaLite 4.x standard. This means the database is NOT backwards compatible in that way it will not run, at least not without some problems, with a QGIS installation built on top of SpatiaLite < 4. If in doubt of your version, create a database and have a look into first row of table about_db. If you are running QGIS on top of SpatiaLite 4.x and want to assure backwards compatibility with SpatiaLite 4.x, then there is a conversion tool named spatialite_convert where you can change a database from spatialite version 4 to version 3. For windows you may download it from here: http://www.gaia-gis.it/gaia-sins/windows-bin-x86/

Populate a postgis database to a new Midvatten project DB

(introduced in plugin version 1.5)

Populates an existing PostgreSQL database with tables according to the Midvatten plugin.

IMPORTANT NOTES

  • Midvatten PostGIS support is developed using PostgreSQL 9.5.10 and PostGIS version 2.3.3. Other versions are untested.
  • The Midvatten plugin uses the QGIS db manager for connecting to a PostgreSQL database.
  • The username and password for the PostgreSQL is not stored in or by the Midvatten plugin. It requests the username and password from the QGIS db manager for each connection attempt. Username and password is often requested of the user only once at the start of the QGIS session.
  • PostGIS extension will be created by Midvatten plugin if it isn't already existing.
  • All Midvatten plugin features (except "Export to another spatialite db") are assumed to work, but there might be bugs.
  • The vacuum feature uses 'VACUUM ANALYZE' when Midvatten plugin is set to PostGIS.
  • The Midvatten plugin has no backup feature when set to PostGIS. Backups are important, but should be handled outside of the plugin! (The database information could be backuped easily using the export csv feature of the Midvatten plugin).

Prerequisites

  • A PostgreSQL database. Users and permissions must be set up beforehand.

Howto

  1. Select QGIS Layer - Add Layer - Add PostGIS Layers...
  2. Click "New" and create a new PostGIS connection. Remember the given name. 2b. Click "Test Connection" to make sure it works.
  3. In Midvatten Settings, select DB.
  4. Chose "Database type" > "postgis".
  5. Chose the new PostGIS connection created in step 2 in the "Connections" list.
  6. Select Midvatten - Database Management- Populate a postgis database to a new Midvatten project DB.
  7. select desired locale and coordinate reference system, CRS.
  8. A new database will be created with predefined design.

Backup the database

It is highly recommended that you backup your database file regularly. You may use a predefined plugin feature for this purpose:
Midvatten - Database management - Backup database
This feature will create a zipped copy of the database, with date and time stamp, in the same folder as the original database.

Vacuuming the database

If you for some reason have deleted big amounts of data from the database, or if you have performed a number of data imports without vacuuming, it is recommended to perform av vacuum of the database to reduce size on disk. You may use the predefined plugin feature:
Midvatten - Database management - Vacuum the database
This will defragment the file, reducing the size on disk. (Vacuuming is highly recommended, and per default performed, after each one of the import functions since these import functions keeps temporary data in the database which is deleted afterwards - hence causing a possible need for vacuuming.)

Upgrade database

Suggested methods for upgrading an old database to a new plugin version.

Upgrade Spatialite database

Upgrade PostGIS database

  • Create a new PostGIS database and fill with Midvatten plugin tables using populate PostGIS database
  • Load tables from the old database to the QGIS project using QGIS3 Layer/Add Layer/Add PostGIS layers.
  • Import one layer at the time using general csv import, load from active layer, starting with zz_* tables and obs_points and obs_lines and then all other tables.

Migrate database between Spatialite and PostGIS

  • Create the target database as a PostGIS or Spatialite database.
  • Load tables from the old database to the QGIS project using QGIS3 Layer/Add Layer/Add XXXX layers. (where xxxx is Spatialite or PostGIS depending on the source database)
  • Import one layer at the time using general csv import, load from active layer, starting with zz_* tables and obs_points and obs_lines and then all other tables.

Add view_obs_points as workaround for qgis bug #20633

(introduced in version 1.6.0)

  • There have been some issues with the compability between QGIS and Midvatten databases (see bug reports #20633 and #21447).
  • This function is a work around that creates two editable views, view_obs_points and view_obs_lines, that replaces tables obs_points and obs_lines in QGIS layer list.
  • The views are still named obs_points and obs_lines in the list and works just like the original tables in every way except that they have a rowid column in the attribute table.
  • These views might not work with very old databases. If the database is old, it might be a good idea to update it see Upgrade Spatialite database