I am using QGIS 3.18. I have two shapefiles. A point layer representing tree positions ("tree_pt") and a building layer representing a building ("building") containing multiple lines. I wish to compute the minimum distance between the trees and the building.
I can do this in other packages, e.g. Manifold GIS, but cannot get the SQL working in QGIS.
My SQL statement is as follows (for simplicity this is not yet aggregated to the minimum distance, so I know I will get multiple results per tree point) :
SELECT "tree_pt"."num", ST_Distance("tree_pt"."geometry", "building"."geometry")
FROM "tree_pt", "building"
GROUP BY "tree_pt"."num"
The error I am receiving is
Query preparation error on SELECT "ST_Distance("tree_pt"."geometry",
"building"."geometry")" FROM _tview LIMIT 1: near ""."": syntax error
Although the input files are shapefiles, I believe that I should still be able to use the ST_Distance() function.
How can I correct my SQL?
Best Answer
With using the following SQL expression:
I was able to recreate the same error
To overcome it, there are several suggestions:
use an alias e.g.
AS dist
i.e.ST_Distance("random_points_test"."geometry", "poly_test"."geometry") AS dist
, because the string is too long for a field name.However, this may lead to next problem:
,
to cope with it
refer to features' geometry via
"poly_test".geometry
instead of"poly_test"."geometry"
(When deploying a spatial function which contains two or more attributes with geometry it may cause the syntax error described in 1).So, the working SQL query may look as following:
or like