Use of ST_Distance in QGIS SQL Statement

Question

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?

Answer 1

With using the following SQL expression:

SELECT ST_Distance("random_points_test"."geometry", "poly_test"."geometry")
FROM "random_points_test", "poly_test"

I was able to recreate the same error

To overcome it, there are several suggestions:

1. 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.

   SELECT ST_Distance("random_points_test"."geometry", "poly_test"."geometry") AS dist
   FROM "random_points_test", "poly_test"
   

   However, this may lead to next problem:

   ,

   to cope with it

2. 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:

SELECT ST_Distance("random_points_test".geometry, "poly_test".geometry) AS dist
FROM "random_points_test", "poly_test"

or like

SELECT ST_Distance(poi.geometry, poly.geometry)
FROM "random_points_test" AS poi, "poly_test" AS poly