2

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?

2
  • The whole expression seems to get wrapped in another set of " when prepared as the meta data query. I would assume this is an internal string literal conversion/escaping issue - a bug - but it should help to avoid SQL identifier escaping, i.e. omit all the " - this particular query doesn't require them). It does need an aggregation expression, though, to satisfy the GROUP BY.
    – geozelot
    Oct 3 at 19:40
  • Sorry Taras, now marked as answered.
    – Oisin
    Dec 1 at 10:28
2

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

error_1

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:

    error_2,

    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
4
  • 1
    Why is it wrong?
    – user30184
    Oct 3 at 20:38
  • This does seem to be a problem if someone has a layer name with a space in it that also has fields with spaces in them - impossible to refer to those fields unless you rename the layer in QGIS.
    – she_weeds
    Oct 4 at 7:53
  • It is possible to refer to a layer via an alias "layer name with spaces" AS layer :)
    – Taras
    Oct 4 at 7:54
  • Taras - thanks. I've marked it as answered. For clarity, the following worked:
    – Oisin
    Oct 4 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.