Troubleshooting the use of OpenLayers Plugin layers with Midvatten Plugin in QGIS

Question

I have been struggling to get a Google physical layer that has been loaded using the OpenLayers Plugin (Version 1.4.3) to work with Observation Points loaded using the Midvatten Plugin. The data points are located in East Anglia, but when I do manage to get the Google Physical layer to load it displays the data points located in the Gulf of Guinea off the West coast of Africa. I am new to GIS so expect that it is something quite trivial that I am getting wrong. I have followed the following steps to try to rectify this problem:

I converted the CRS of my Midvatten db from the original EPSG 4326 that the Midvatten tutorial recommended to EPSG 3857 that Google Physical uses (see Is it possible to reset a database's coordinate reference system in the QGIS Midvatten plugin?).

In the Midvatten obs_point attribute table I converted from using 12 figure grid references split between the East and North fields in the attribute table to longitude and latitude. The screen shot below shows the website that I used to do the conversion and the real locations of a few of my data points:

This conversion still results in data points located in the Gulf of Guinea (these are the letters located in the sea towards the bottom left of the map) In the screenshot below the obs_point attribute table is open showing the content of the East and North Columns :

It is likely I am making some novices mistake. The only other potential clue (I don't know if it is related or something else that needs fixing) is that when I load the Midvatten database then load default db-layers to QGIS I get a string of error messages:

Error executing query: SELECT styleQML FROM layer_styles WHERE f_table_catalog='EAQ_strat_EPSG_3857_2_midv_obsdb.sqlite' AND f_table_schema='' AND f_table_name='stratigraphy' (first 'stratigraphy', then for subsequent error messages iterates through: 'w_levels' 'w_flow' 'w_qual_lab' 'w_qual_field' 'comments' 'obs_lines' 'obs_points' 'obs_p_w_qual_field' 'obs_p_w_qual_lab' 'obs_p_w_lvl' 'obs_p_w_strat' 'w_lvls_last_geom' 'zz_staff' 'zz_flowtype' 'zz_meteoparam' 'zz_strat' 'zz_stratigraphy_plots' 'zz_capacity' 'zz_capacity_plots') AND f_geometry_column=NULL (or by iteration, AND f_geometry_column='Geometry') ORDER BY CASE WHEN useAsDefault THEN 1 ELSE 2 END,update_time DESC LIMIT 1

What do I need to change to get my data points to display in the correct UK, East Anglia locations on a Google physical layer?

Following CSK's comment that my data points were being located at lat/long (0,0) because my geometry was null, I remembered having seen this post: Midvatten plugin used with qgis2threejs. There they suggest changing 'TOC-GS' and 'ground surface' from 'null' to '0'. I thought that maybe if the z coordinate was set to zero rather than null it might stop ignoring the x and y coordinates. So I tried first editing the obs_point attribute table. Then when this did not work, I tried deleting and reloading the data sets having first added two extra columns of zeros to the csv:

obsid;name;place;type;east;north;h_tocags;h_gs;com_onerow; Bdat;Bdat;Beeston;datum;1.2255526;52.94407;0;0;; BG;BG;Beeston;section;1.2263631;52.943958;0;0;16.5 metres East of datum Figure 18 West (1980) Point id 19- Notebook 2-p57;

When I loaded the csv with the extra columns I still got the warning that I had loaded obs_points without geometry and the map location was still (0,0) in the Gulf of Guinea. So zeros instead of null in h_tocags and h_gs is not the fix I was looking for.

Answer 1

It looks like the coordinates in 3857 should be something like -289846,7122530, but your coordinates in your east-north column looks like their still WGS84 (4326), something like 1.2255526;52.94407.

So you need to convert the obs_points east-north columns into 3857.

I would probably fix it by:
1. Select all observations in obs_points.
2. Export them using the csv export.
3. Create a new database using the CRS 4326.
4. Import the obs_points.csv to your new database.
5. Select all observations in obs_points in your new database.
6. Export to spatialite database using the midvatten export tool and select 3857 as new crs.
7. If the obs_points are located at the correct place, import all other csv-files from the export at step 2 into the database created at step 6.