2

I'm calculating the length of common borders of 10 sets of polygons with the solution posted in this question: https://gis.stackexchange.com/a/360776

In 8 of the 10 datasets everything works fine, but in two of them I have the problem that the output is a point layer, instead of a line layer. In one of the two cases, I was able to fix this by running "Repair Geometries". In the other case, repairing doesn't help.

Any ideas what could be the issue and how I could find a woraround?

Here's the code I'm using:

SELECT
  a.UUID_GIS AS poly1_id,
  b.UUID_GIS AS poly2_id,
  st_intersection(a.geometry, b.geometry) AS geometry,
  st_length(st_intersection(a.geometry, b.geometry)) AS border_length
FROM "input1" a
  JOIN "input1" b
    ON st_intersects(a.geometry, b.geometry)
      AND a.UUID_GIS < b.UUID_GIS

If needed, you can download the data here.

Improve this question
4
  • I get a mix of points, linestrings, multilinestrings running this query on both your sample layers. Is your issue getting only points ? (Points are expected as there are polygons assembled in grids in your layer, with points as only common vertex).
    – Kasper
    Commented Jan 4, 2023 at 15:26
  • yes, there have to be points in the output, due to polygons that only touch at their vertex. In fact I don´t need the points, as the length of common borders is 0. The issue is as you said, that sometimes I get an output with only points, with an error massage that linestrings/multilinestrings cannot be added to the point layer. But sometimes it´s the other way around and I get the output as line layer with the information of the border length. Maybe it has something to do with the order of appearance of point / lines? So if a point is identified first, the output is a point layer?
    – Denise
    Commented Jan 5, 2023 at 16:26
  • One thing you can do is add a condition for : Where St_geometryType(st_intersection(a.geom,b.geom)) IN ('ST_LineString','ST_MultiLineString') to exclude Points or Point collections
    – Kasper
    Commented Jan 5, 2023 at 17:03
  • I tried to add the code, but there is no output. Did I integrated at the right position? I´m not really good with SQL... SELECT a.ID_FINAL as poly1_id, b. ID_FINAL as poly2_id, st_intersection(a.geometry, b.geometry) as geometry, st_length(st_intersection(a.geometry, b.geometry)) as border_length FROM "TestAusschnitt" a JOIN "TestAusschnitt" b ON st_intersects(a.geometry, b.geometry) AND a. ID_FINAL < b. ID_FINAL WHERE St_geometryType(st_intersection(a.geometry,b.geometry)) IN ('ST_LineString','ST_MultiLineString')
    – Denise
    Commented Jan 17, 2023 at 17:40

0

Reset to default

Your Answer

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

Browse other questions tagged or ask your own question.