4

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.

enter image description here

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: enter image description here

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?

12
  • Welcome to Geographic Information Systems! Welcome to GIS SE! We're a little different from other sites; this isn't a discussion forum but a Q&A site. Your questions should as much as possible describe not just what you want to do, but precisely what you have tried and where you are stuck trying that. Please check out our short tour for more about how the site works
    – Ian Turton
    Commented Oct 13, 2021 at 9:57
  • what happens when you run the sql?
    – Ian Turton
    Commented Oct 13, 2021 at 9:57
  • Hello, thank you! there is a ruban appearing saying that the layer is not valid. The message is too long to be pasted here, so I will edit my post to include the error message. Commented Oct 13, 2021 at 10:01
  • Hello, I just copied the code from the post I was trying to get the solution from, but it may not be useful here (it's more of a postcode). I tried removing all that section, and it is still not working though. 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 Commented Oct 13, 2021 at 10:36
  • 1
    Make sure to enter the SQL query in the virtual layer query box, not in the upper embedded layers section
    – JGH
    Commented Oct 13, 2021 at 13:22

1 Answer 1

4

After bringing a sample of your data into QGIS

input

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

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:

output

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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