1

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?

4

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

enter image description here

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.

  • 1
    Thank you for explanations, especially using "double quotes" – Taras May 28 at 4:25
  • I rebooted and re-ran with success. I checked and the geometry column was "geom". I'll add a more comprehensive response when I get this complete, because there are a few more steps I've followed to get the data I'm after. I think this is a useful post, because I have not seen a full description of this posted here. – Mark Thompson May 31 at 18:10

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.