Getting nearest line to point with virtual layer using QGIS?

Question

I have a streetmap and the position of hundred sensorlocations. I am currently trying to calculate the nearest line for each point. I have already done it with the nnjoin plugin. But now I want to do this with a virtual layer. For this, I followed the tutorial: https://pigrecoinfinito.wordpress.com/2017/04/28/qgis-minima-distanza-tra-punti-e-linee/

But unfortunately, I could not get it to work because I am very new to SpatiaLite. To create the virtual layer, my approach looks like followed:

If I want to test this query, I am getting the following error note:

Any suggestions on how to fix this?

---

I have know tried it with renamed tables, but I am still getting the same error message.

Maybe something more is wrong?

Answer 1

The main problem here (maybe you have spotted a bug) is in the memory layer (maybe a processing output?). It contains an SQL keyword into the field definition &field=from:string&field=to:string which is not correctly parsed from the virtual layer provider. So a workaround could be to change fields name for the memory layer named Reprojiiziert. Or save to file the memory layer and using that in your query.

Also, please you feel free to file a ticket in the QGIS bug tracker [0] as this is something that affects the upcoming 3.0 version as well.

[0] - https://issues.qgis.org

Answer 2

Spatialite table names can't contain capital letters unless they are quoted: then retry changing them to "EVIS_Stat_Sensorik" and "Reprojiiziert"or use only lowercase chars naming them.