ERROR: lwgeomconcavehull: GEOS Error: IllegalStateException: Unable to find shell join…
- 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 ;
Assistant
This GEOS error typically occurs when
st_concavehullencounters 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 geom4. 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 query5. 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.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.