Change geometry type to Polygon and MultiPolygon in PostGIS

Question

I try to insert geometry values into a column in PostGIS. However, the type of values is a mix of MultiPolygon and Polygon. I tried to change the column type to Multipolygon with:

Alter table clc10_landwirtschaft_fr_25832 alter column geom type geometry (multipolygon,25832) using ST_Multi(geom);

But I get the following error:

Traceback (most recent call last):

  File "C:\Users\lucas\Desktop\Master Thesis\Python\Code\Code Bitterich geändert neu\A10 clcackersinglepolygons_neu.py", line 67, in <module>
    cur.execute(sql)

InvalidParameterValue: FEHLER:  Geometry type (Polygon) does not match column type (MultiPolygon)

Is there a way to assign a column type in a way that it accepts polygon and multipoygon? Here is my code:

"""
extract fields from clc10 and disaggregate multipolygons into regular polygons

"""
import psycopg2
import os
import datetime

os.chdir(r"C:\Users\lucas\Desktop\Master Thesis\Python\Data_New\Geo Data")
print(os.path.basename(__file__))
filename = os.path.basename(__file__)
filename1 = filename[:-3]
#fehlerfile=open(filename+'_fehler.txt', 'a')

fehlerliste = []

logfile = open(filename1+'_logfile.txt', 'a')
logfile.write("\n====================================================================\n")
logfile.write("\n"+filename +"\n")
logfile.write('Startzeit: {:%H:%M:%S}'.format(datetime.datetime.now()))
logfile.write("\n")
logfile.close()

# Connect to an existing database
conn = psycopg2.connect(****)
cur = conn.cursor()

sql = "select gid, ST_NumGeometries(geom), st_astext(geom), st_area(st_transform(geom, 3055)) from clc10_landwirtschaft_fr_25832 where clc = '211' order by gid;"
cur.execute(sql)
data = cur.fetchall()

gidcounter = 0
g= data[0]
for g in data:
    gidcounter +=1
    gid = g[0]
    numgeom = g[1]
    geom = g[2]
    area = g[3]
    if numgeom ==1:
        if area <= 1000000:
            sql = "insert into clc10_acker_fr_25832_singlepolygons (clc_gid, geom) values("+str(gid)+", (select st_geomfromtext('"+geom+"', 25832)));"
            cur.execute(sql)
            conn.commit()
            print("gid %i von %i als ganzes eingetragen" %(gid, len(data)))
        else:
            sql = "select st_astext(st_transform(geom, 25832)) from klima_31467_shape where st_intersects(st_transform(geom,25832), st_geomfromtext('"+geom+"', 25832)) = True;"
            cur.execute(sql)
            teile = cur.fetchall()
            teil=teile[0]
            for teil in teile:
                rastergeom = teil[0]
                sql= "select st_astext(st_intersection(st_geomfromtext('"+rastergeom+"', 25832), st_geomfromtext('"+geom+"', 25832)))"
                cur.execute(sql)
                newgeom = cur.fetchone()[0]
                # in einzelne Polygone zerlegen:
                sql = "select GeometryType((g.geometrie).geom), st_area(st_transform((g.geometrie).geom,3035)), st_astext((g.geometrie).geom) from (select st_dump(st_geomfromtext('"+newgeom+"', 25832)) as geometrie) as g"
                cur.execute(sql)
                dump = cur.fetchall()
                Teilecounter = 0
                for info in dump:
                    Teilecounter +=1
                    if info[1]>=0.0001: # Fläche mind. 1 cm^2
                        **sql = "insert into clc10_acker_fr_25832_singlepolygons (clc_gid, geom) values ("+str(gid)+", (select st_geomfromtext('"+info[2]+"', 25832)));"
                        cur.execute(sql)**
                        conn.commit()
                        print("Teil %i von %i von gid %i eingetragen" %(Teilecounter, len(dump), gid))
                        logfile = open(filename1+'_logfile.txt', 'a')
                        logfile.write("\nTeil %i von %i von gid %i eingetragen" %(Teilecounter, len(dump), gid))
                        logfile.close()
    else: # Multipolygon
        i = 4
        polygoncounter=0
        for i in range(1,numgeom+1):
            polygoncounter +=1
            sql = "select st_astext(ST_GeometryN(geom, " +str(i)+ ")), st_area(st_transform(geom,3055)) from clc10_landwirtschaft_fr_25832 where gid = "+str(gid)+";"
            cur.execute(sql)
            singlegeominfo = cur.fetchone()
            singlegeom=singlegeominfo[0]
            area = singlegeominfo[1]
            if singlegeom[:7] != 'POLYGON':
                fehlerliste.append((gid, numgeom, singlegeom[:20]))
            else:
                if area <= 1000000:
                    sql = "insert into clc10_acker_fr_25832_singlepolygons (geom, clc_gid) values((select st_geomfromtext('"+singlegeom+"', 25832)), "+str(gid)+");"
                    cur.execute(sql)
                    conn.commit()
                    print("Polygon %i von %i als ganzes eingetragen" %(polygoncounter, numgeom))
                    logfile = open(filename1+'_logfile.txt', 'a')
                    logfile.write("\npolygon %i von gid %i als ganzes eingetragen" %(polygoncounter, gid))
                    logfile.close()
                else:
                    sql = "select st_astext(st_transform(geom, 25832)) from klima_31467_shape where st_intersects(st_transform(geom,25832), st_geomfromtext('"+singlegeom+"', 25832)) = True;"
                    cur.execute(sql)
                    teile = cur.fetchall()
                    teil=teile[0]
                    for teil in teile:
                        rastergeom = teil[0]
                        sql= "select st_astext(st_intersection(st_geomfromtext('"+rastergeom+"', 25832), st_geomfromtext('"+singlegeom+"', 25832)))"
                        cur.execute(sql)
                        newgeom = cur.fetchone()[0]
                        # in einzelne Polygone zerlegen:
                        sql = "select GeometryType((g.geometrie).geom), st_area(st_transform((g.geometrie).geom,3035)), st_astext((g.geometrie).geom) from (select st_dump(st_geomfromtext('"+newgeom+"', 25832)) as geometrie) as g"
                        cur.execute(sql)
                        dump = cur.fetchall()
                        Teilecounter = 0
                        for info in dump:
                            Teilecounter +=1
                            if info[1]>=0.0001: # Fläche mind. 1 cm^2
                                sql = "insert into clc10_acker_fr_25832_singlepolygons (clc_gid, geom) values ("+str(gid)+", (select st_geomfromtext('"+info[2]+"', 25832)));"
                                cur.execute(sql)
                                conn.commit()
                                print("Teil %i von %i von polygon %i von gid %i eingetragen" %(Teilecounter, len(dump), polygoncounter, gid))
                                logfile = open(filename1+'_logfile.txt', 'a')
                                logfile.write("\nTeil %i von %i von polygon %i von gid %i eingetragen" %(Teilecounter, len(dump), polygoncounter, gid))
                                logfile.close()
    print(os.path.basename(__file__))
    print('Timestamp: {:%H:%M:%S}'.format(datetime.datetime.now()))
    print("gid %i eingetragen (Nr. %i von %i)" %(gid, gidcounter, len(data)))
    print("\n\n")
 
logfile = open(filename1+'_logfile.txt', 'a')
logfile.write('\nEndtime: {:%H:%M:%S}'.format(datetime.datetime.now()))
logfile.close()  

print("fertig"   )       
            
# Close communication with the database
conn.commit()
cur.close()
conn.close()   

"""
sql = "insert into testtabelle (geom) values ((select st_geomfromtext('"+singlegeom+"', 25832)))"
cur.execute(sql)
conn.commit()
"""

I also tried a workaround in QGIS with the Geometry tool Multipart to singleparts, but this does not transform all MultiPolygons.

Answer 1

Changing the column type to Geometry worked perfectly!

Alter table clc10_acker_fr_25832_singlepolygons alter column geom type geometry (Geometry,25832);