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
'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?