Skip to content
TheChatArchiveThe Chat Archive
Claudeclaude-opus-4-20250514· AnthropicHugging Face Arena Expert 5K

ERROR: lwgeomconcavehull: GEOS Error: IllegalStateException: Unable to find shell join…

  1. User

    ERROR: lwgeom_concavehull: GEOS Error: IllegalStateException: Unable to find shell join index with interior join line

    CREATE TABLE schema.ap_cluster as SELECT country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi, (st_dump(st_union(geom))).geom AS geom FROM ( SELECT country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi, st_buildarea( st_exteriorring( (st_dump( st_union( st_buffer(geom, 20, 'quad_segs=1 join=mitre mitre_limit=15')) )).geom ) ) AS geom FROM ( SELECT country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi, clst, count(1), st_concavehull(st_collect(geom), 0.99) AS geom FROM ( SELECT country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi, buffered AS geom, st_clusterdbscan(buffered, 100 , 3) over () AS clst FROM schema.ap_buffer_rotation ) d WHERE clst is not null GROUP BY country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi, clst having count(1) > 3 ) e GROUP BY country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi ) f GROUP BY country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi ;

  2. Claude Assistant

    This GEOS error typically occurs when st_concavehull encounters complex or problematic geometries that it cannot process correctly. The error suggests that the algorithm is having trouble determining the proper shell (outer boundary) when trying to create the concave hull.

    Here are several approaches to fix this issue:

    1. Add Geometry Validation and Cleaning

    First, try cleaning the geometries before processing:

    CREATE TABLE schema.ap_cluster as
    SELECT
        country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi,
        (st_dump(st_union(geom))).geom AS geom
    FROM (
        SELECT
            country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi,
            st_buildarea(
                st_exteriorring(
                    (st_dump(
                        st_union(
                            st_buffer(geom, 20, 'quad_segs=1 join=mitre mitre_limit=15'))
                    )).geom
                )
            ) AS geom
        FROM (
            SELECT
                country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi, clst, count(1),
                -- Clean and validate geometries before concave hull
                CASE 
                    WHEN st_isvalid(st_collect(geom)) THEN
                        st_concavehull(st_collect(geom), 0.99)
                    ELSE
                        st_concavehull(st_collect(st_makevalid(geom)), 0.99)
                END AS geom
            FROM (
                SELECT
                    country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi, 
                    st_makevalid(buffered) AS geom,  -- Ensure valid geometries
                    st_clusterdbscan(buffered, 100, 3) over () AS clst
                FROM
                    schema.ap_buffer_rotation
            ) d
            WHERE
                clst is not null
            GROUP BY
                country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi, clst
            HAVING count(1) > 3
        ) e
        GROUP BY
            country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi
    ) f
    GROUP BY
        country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi;
    

    2. Use Convex Hull as Fallback

    If concave hull continues to fail, use convex hull as a fallback:

    CREATE TABLE schema.ap_cluster as
    SELECT
        country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi,
        (st_dump(st_union(geom))).geom AS geom
    FROM (
        SELECT
            country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi,
            st_buildarea(
                st_exteriorring(
                    (st_dump(
                        st_union(
                            st_buffer(geom, 20, 'quad_segs=1 join=mitre mitre_limit=15'))
                    )).geom
                )
            ) AS geom
        FROM (
            SELECT
                country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi, clst, count(1),
                -- Try concave hull first, fall back to convex hull if it fails
                COALESCE(
                    st_concavehull(st_collect(geom), 0.99),
                    st_convexhull(st_collect(geom))
                ) AS geom
            FROM (
                SELECT
                    country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi, buffered AS geom,
                    st_clusterdbscan(buffered, 100, 3) over () AS clst
                FROM
                    schema.ap_buffer_rotation
            ) d
            WHERE
                clst is not null
            GROUP BY
                country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi, clst
            HAVING count(1) > 3
        ) e
        GROUP BY
            country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi
    ) f
    GROUP BY
        country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi;
    

    3. Adjust Concave Hull Parameters

    Try a less aggressive concave hull parameter (closer to 1.0 means more convex):

    -- Change from 0.99 to 0.95 or 0.9
    st_concavehull(st_collect(geom), 0.95) AS geom
    

    4. Simplify Geometries Before Processing

    Add geometry simplification to reduce complexity:

    SELECT
        country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi, clst, count(1),
        st_concavehull(
            st_collect(
                st_simplifypreservetopology(geom, 1.0)  -- Simplify with 1 meter tolerance
            ), 
            0.99
        ) AS geom
    FROM (
        -- rest of your query
    

    5. Debug Specific Problem Clusters

    To identify which clusters are causing issues:

    -- First, identify problematic clusters
    SELECT 
        country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi, clst,
        count(1) as point_count,
        st_isvalid(st_collect(geom)) as is_valid_collection
    FROM (
        SELECT
            country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi, buffered AS geom,
            st_clusterdbscan(buffered, 100, 3) over () AS clst
        FROM
            schema.ap_buffer_rotation
    ) d
    WHERE clst is not null
    GROUP BY country_code, a3, a3_nimi, a2, a2_nimi, a1, a1_nimi, clst
    HAVING count(1) > 3
    ORDER BY is_valid_collection, point_count DESC;
    

    The most robust solution would be to combine several of these approaches, particularly adding geometry validation and using a fallback to convex hull when concave hull fails.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.