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.
Best Answer
Changing the column type to
Geometryworked perfectly!