1

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.

2
  • Well, after you changed that column to typemod constrained (MULTIPOLYGON, 25832), you have to insert MultiPolygons only! I haven't fully read your code, but it seems you want to also wrap your Polygons in ST_Multi. Alternatively, change the column type to (GEOMETRY, 25832), which then allows for any geometry type.
    – geozelot
    Commented Nov 25, 2020 at 21:12
  • mixing types, more often than not, leads to issues... I personally always convert them all to simple or all to multi polygons
    – JGH
    Commented Nov 25, 2020 at 21:34

1 Answer 1

4

Changing the column type to Geometry worked perfectly!

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.