Convert multipolygon to polygon with holes using ST_MakePolygon and ogr2ogr

Question

I have a shapefile that is exported by Surfer. This shapefile is invalid and has 3 polygons. 1 large polygon and two smaller ones. The smaller ones lay on top of the large one, instead of in the large one. The large one has no holes.
I want to fix this by creating a new shapefile with the large polygon as the exterior ring and the two smaller ones as the interior rings.
Luckily I can distinguish the polygons by an attribute: BLN_Flag When this value is 0 it is an exterior ring and when it is 1 they are interior rings.

I tried looking at the ST_MakePolygon() function. When I only use the exterior ring this command produces a valid shapefile:

ogr2ogr -f "ESRI Shapefile" -dialect SQLite 
-sql "SELECT (ST_MakePolygon(ST_ExteriorRing((SELECT geometry FROM digitizedPoly WHERE BLN_Flag=0)))) AS geometry, 0 AS BLN_Flag" 
"temp.shp" "digitizedPoly.shp"

The signature of ST_MakePolygon is: ST_MakePolygon( geom1 Geometry [ , geom2 Geometry ] ) : Geometry
And the description:

The first input Geometry is always expected to represent a closed LINESTRING assumed to identify the output polygon's Exterior Ring. The second input Geometry (if any) is expected to be a LINESTRING or MULTILINESTRING identifying any polygon's Interior Ring

So I tried adding the interior rings using this command:

ogr2ogr -f "ESRI Shapefile" -dialect SQLite 
-sql "SELECT (ST_MakePolygon(
    ST_ExteriorRing((SELECT geometry FROM digitizedPoly WHERE BLN_Flag=0)),
    ST_Collect((SELECT geometry FROM digitizedPoly WHERE BLN_Flag=1))
  )) AS geometry, 0 AS BLN_Flag" 
"temp.shp" "digitizedPoly.shp"

No .shp file is created, only the .dbf file.
When I save to GeoJSON I can see geometry is null.

So I'm doing something wrong, please advise.

Answer 1

Using this sample https://labs.webgeodatavore.com/partage/digitizedPoly.zip, I'm able to do what you want

ogr2ogr -dialect SQLite -sql "WITH holes AS (
  SELECT ST_union(ST_ExteriorRing(geometry)) AS geom_holes
  FROM digitizedPoly
  WHERE BLN_Flag=1
), poly AS (
  SELECT ST_ExteriorRing(geometry) AS geom_poly
  FROM digitizedPoly
  WHERE BLN_Flag=0
)
SELECT ST_MakePolygon(geom_poly, geom_holes) AS geometry, 0 AS BLN_Flag
FROM holes, poly" temp3.shp "digitizedPoly.shp"

This is not exactly a real world example as you may need to group by each large polygon and holes to make each large polygon with their holes in one step.