Find multipolygons in spatialite

Question

How could I identify and select the rows with multiparts? How could I copy/save the polygons from a table with geom MULTIPOLYGON into a table with geom POLYGON?

Answer 1

Read http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html

Find function ST_GeometryType. Use it like

SELECT * from my_table
WHERE ST_GeometryType(GEOMETRY)='MULTIPOLYGON';

How to create a new table with ElementaryGeometries has an answer already in Find and split Multipolygons in spatialite and the usage of the ElementaryGeometries function is

SELECT ElementaryGeometries('input_table','input_geometry','output_table','new_id_1', 'new_id_2');

The ElementaryGeometries is a special function and it requires that the input table is a real table (not a view) that has its spatial metadata inserted into SpatiaLite database.

Thus creating a new table from just multipolygons requires some interim steps.

CREATE TABLE interim as
SELECT * from input_table 
WHERE ST_GeometryType(GEOMETRY)='MULTIPOLYGON';

Then use RecoverGeometryColumn as documented in https://www.gaia-gis.it/stored-procs-win-test/spatialite-sql-latest.html.

And finally convert multipolygons from the interim table into polygons into a final table

SELECT ElementaryGeometries('interim','GEOMETRY','final_table','new_id_1', 'new_id_2');

It may feel a bit complicated to do this task with SpatiaLite but all the steps can be written into a script. But I wonder why you want to find just the multipolygons. It would be much easies to push the whole table with both polygons and multipolygons through ElementaryGeometries.

Myself I guess I would do your exact task of selecting and exploding multipolygons with ogr2ogr https://gdal.org/programs/ogr2ogr.html