Geopoint data - import records to QGIS using virtual layers

Showcase
1

I've recently started using this method to link data from ODK to QGIS and thought it might be of use to others...

I used to import csv files as 'delimited text', which allows you to select the geopoint fields as geometry. But what if you have a record with multiple geopoints? In my case I take a number of photos in different locations (geopoint for each), and mark the start and finish of my 'transect' with a track running in the background on a 'companion app'- so multiple points in one record. I could import the csv 6 times using a different set of coordinates for geometry. Or...

Virtual layers in QGIS 3 allows me to query (and join) datasets, so I can create a query that selects any of the geopoints in that record and turns them into QGIS geometry. As this isn't a tutorial in how QGIS works, I'm going to skip through the exact steps or prerequisites and focus on the query...

Assuming you have added your csv from Briefcase or Central (or xlsx if you've downloaded from Kobotoolbox) as a simple table (no geometry). Choose 'Create Layer' > 'New Virtual Layer'.

In the dialog, import the Embedded Layer above (and any others that you want to join). The MakePoint( ) and cast () are the cunning bits for creating the geometry (in my case the field name in my form was 'End', and I called the layer 'Transect'):

Select MakePoint(cast("End-Longitude" as decimal), cast("End-Latitude" as decimal), 4326) as geometry, KEY as index from "Transect" where "End-Latitude" notnull

This gives me a simple point layer with one attribute. But I can repeat that (the joy of cut-paste-replace) for each layer I want on the canvas with as many fields and joins as my SQL knowledge will allow. For example if I want to plot the point of a photo (field name for the photo is P1 and geopoint for it P1_location):

Select MakePoint(cast("P1_location-Longitude" as decimal), cast("P1_location-Latitude" as decimal), 4326) as geometry, P1 as image, from "Transect" where P1 notnull

Then I can view the image on the canvas using standard QGIS forms...

Note the notnull - if you don't always have every field completed in your form this avoids errors in QGIS!

When I import my GPX track from the field, I can split it at the Start of each transect (save it as a Geopackage or SHP layer), then associate the data collected using the KEY I've identified above (hence my simple point layer)...

Then using another Virtual layer I can combine everything using join statements and have the 'Track' showing the ODK data for that transect. It's a bit clumsy, but gets me round the currently intractable problem of Parallel collection of geotraces while recording other data...

This apporach also allows me to progressively add data to a project because I am only linking to the csv, rather than importing it to another format - so each time I open QGIS it updates new records - no duplicates or missing records. Another hard won lesson! Providing you can manage the export of data from Central / Briefcase...

Not the prettiest Showcase in the book, but functional :slight_smile: Hope it's of use - it's about time I contributed something!

3 Likes
2

Great write-up, thanks for sharing!

There was a discussion on GH a while back.

QGIS doesn't have an OData plugin (bait: yet), but the recent addition of filtering to the Central API sounds like Central can introspect the form schema now. Maybe there's a path to nominating a field as GeoJSON geometry for an "export to GeoJSON".

You can of course always go through ruODK and wrangle the output to GeoJSON, but that's not real time.

You could also export to PostGIS as @mathieubossaert showed and open that in QGIS without much hassle.

3

To follow up:

for any QGIS enthusiasts that can't get ODK-flavoured plug-in's to work, and moving from Points to Shapes...

If you have a csv that has a geoshape field exported from Central it might be a headache to work out how to get that into a LineString without multiple tasks. I finally cracked it using a Virtual Layer with the following SQL statement that works in QGIS 3. Note that I've used || to concatenate the statement, which is probably a bit naughty / lazy, so feel free to adapt! The difficulty I was having was converting from Lat/Long format to X,Y - then I found 'flip_coordinates' function...

It also translates the GPS coordinates to UK Grid (EPSG: 27700), so if you don't need that, you can excluded the 'transform' part of the statement, but I've left it in so that it's a single step transformation to whatever projection you might need.

This assumes your layer has already been imported to a QGIS project as a csv or a delimited text layer called [ODK-Export] with a field called [GeoShape],

select transform(flip_coordinates(geom_from_wkt('LINESTRING ZM (('||regexp_replace("[Geoshape]",';',',')||'))')),'EPSG:4326','EPSG:27700') as geometry, * from "[ODK-Export]"

This produces a line layer with all the fields from the original ODK-Export. Again, it is automatically updated any time you open QGIS so will always load the latest version of the CSV data you have pulled from Central / Briefcase

I use this with the Append Features to Layer plug-in to accumulate my dataset (as a GPKG), obviously using KEY as the unique field to avoid duplicates. Which means all of this can be done off-line if necessary.

4 Likes