"Duplicate column name" error when calculating length of shared boundaries between multiple polygons using Virtual Layer in QGIS

Question

Similarly to the poster from this question, I am trying to calculate the length of the boundaries between several polygons. I have two types of polygons (1:blue and 2:pink), but I don't think that really matters.

I went as far as the user in post, i.e., I calculated the neighboring polygons, and according to the comment in the post, I tried using the code in the second answer:

select 
    a.ID as poly1_id,
    b.ID as poly2_id, 
    st_intersection(a.geometry, b.geometry) as geometry, 
    st_length(st_intersection(a.geometry, b.geometry)) as border_length
from
    groupe_layers a
join
    groupe_layers b ON st_intersects(a.geometry, b.geometry)
                        AND a.ID < b.ID

I replaced id by ID, and the layer name by "groupe_layers" (my layer name). Other than that, I left it as is.

Here is my table of attributes:

When I enter Ok after adding the query, a red ruban appears saying:

la couche n'est pas valide = the layer is not valid

"La couche n'est pas valide: La couche ?query=select%20a.ID%20as%20poly1_id,%20b.ID%20as%20poly2_id,%20%0D%0A%20%20%20%20%20%20st_intersection(a.geometry,%20b.geometry)%20as%20geometry,%20%0D%0A%20%20%20%20%20%20st_length(st_intersection(a.geometry,%20b.geometry))%20as%20border_length%0D%0Afrom%20groupe_layers%20a%0D%0A%20%20%20%20join%20groupe_layers%20b%20%0D%0A%20%20%20%20%20%20%20%20ON%20st_intersects(a.geometry,%20b.geometry)%0D%0A%20%20%20%20%20%20%20%20%20%20%20%20AND%20a.ID%20%3C%20b.ID&uid=ID n'est pas une couche valide et ne peut pas être ajoutée à la carte. Raison : virtual Query execution error on DROP TABLE IF EXISTS "groupe_layers"; CREATE VIRTUAL TABLE "groupe_layers" USING QgsVLayer(Géométries_simples_9e08b9a6_57fe_4dda_92d2_cfc74989e619);: 1

• duplicate column name: geometry"

Here is my layer with the data. The projection system is RGF93 / Lambert93.

Does someone have a lead on where my problem is? Something with the geometry column?

Answer 1

After bringing a sample of your data into QGIS

and using the following query

SELECT
    a.ID as poly1_id,
    b.ID as poly2_id,
    st_intersection(a.geometry, b.geometry) as geometry,
    st_length(st_intersection(a.geometry, b.geometry)) as border_length
FROM
    "groupe_layers" a
JOIN
    "groupe_layers" b ON st_intersects(a.geometry, b.geometry)
                        AND a.ID < b.ID

I was able to recreate the initial error

Query execution error on DROP TABLE IF EXISTS "groupe_layers"; CREATE VIRTUAL TABLE "groupe_layers" USING QgsVLayer(groupe_layers_0504744b_5da5_48f2_bdc4_eecdf081e566);: 1 - duplicate column name: geometry_1994

QGIS induces this error because you already have a column in you original layer that is called "geometry".

Solution: Renaming the "geometry" field. There are many approaches available on this topic, however, I will encourage using the RMC > Fields > Edit... and renaming the "geometry" column into e.g. "geom2". Another approach is the "Rename field" algorithm, but then you will work with a new layer i.e. the result of the "Rename field" algorithm.

And then it is possible to achieve the desired output: