0

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:

enter image description here

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 :

enter image description here

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.

  • Since you have the latitude and longitude values in a table, you can save the table as a CSV file and load the CSV into QGIS. – csk Mar 22 '18 at 16:27
  • I figured out what went wrong, but I'm not exactly sure how to fix it. Your points are loading without any geometry (notice that the error message includes several mentions of a geometry column). That's why they show up on your map at lat/long (0,0). google.com/maps/place/… – csk Mar 22 '18 at 16:32
  • Thanks @csk, when I imported the obs_point data csv into Midvatten I got a msg 344 rows successfully imported.A second or two later a warning: some datapoints had been loaded without geometry. I assumed that this was because there was no elevation data and the warning was about missing z coordinates. So from what you are saying it appears not to be accepting the lat/long fields as geometry. The data that I imported is in the right columns in the attribute table. Can somebody with experience of Midvatten suggest possible reasons why the data in the columns is not being accepted as geometry? – S. OConnor Mar 22 '18 at 17:55
2

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.

  • Thanks Henrik, a beginners mistake, I was continuously using coordinates for the wrong CRS starting with OSGB 1936 / British National Grid (27700) when I thought that was what required for 4326. I have started again from scratch loading a csv with 4326 coordinates and then converting the database to 3857. The Google Physical map loads in the write locations. The odd thing is when I imported the corrected obs_points it still said I had imported points without geometry and I still get the error messages. I guess I will leave fixing that for another day as my question has been answered. – S. OConnor Mar 23 '18 at 14:17

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.