Geopoint data - import records to QGIS using virtual layers

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

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.