Calculating distance from centroid points to multiple polygon edges with SpatialLite

Question

My question builds on a question previously asked (Calculating all distances from single points to multiple polygons). User @alexandre-neto provided an answer that I've worked on, but it isn't giving me the result I need. I'm new to SpatialLite and using QGIS 3.6 on a Windows OS.

I've created a SpatialLite database that has a point and a polygon layer. The point layer is the centroid of a sampling grid and the polygon layer comes from a government database called the 'Vegetation Resource Inventory (VRI)', which includes lots of data on stand age, species composition, wetlands, etc...

I want to create a new layer or a table of distances from the centroid of my sampling grid (Named: "Grid Pothole Center") to the nearest edge of the VRI polygon layer (Named: "Pothole VRI"). It looks like SpatialLite offers a way to do this. I used the following code from instructions on the link provided above:

SELECT
  f.id,
  g.id,
  st_distance(f.geom, st_centroid(g.geom)) AS distance

FROM
 'Grid Pothole Center' AS f,
 'Pothole VRI' AS g

Note: 'Grid Pothole Center' and 'Pothole VRI' have a common field called 'id'. This runs from the SpatiaLite DB manager plugin (select the database and ran the SQL statement above) without any error messages and a result is given. However, I get an empty table x with the following headings: id, id:1, distance - but no data in the table. What have I done wrong here?

Answer 1

Works for me with test data that I created according to your description. I made a successful test both with spatialite_gui program and with QGIS 3.4.4 (see the image). Is the name of your geometry column certainly "geom"?

Notice that your SQL is actually wrong because by the SQL standard SELECT … FROM 'Grid Pothole Center' means "select from string Grid Pothole Center" The table name should be enclosed between double quotation marks, but SQLite is lenient and really accepts also single quotes in this case. Still, take a habit to use double quotes in the future.