Open r9zzai opened 2 weeks ago
Following Python Code should change layer type to polygon if multipolygon. but it does not split multipolygons. if there is a real multipolygon only the first polygon part will be used.
from qgis.core import QgsProject
import psycopg2
from psycopg2 import sql
import re
import xml.etree.ElementTree as ET
# layer to check the geometry type (e.g. change multipolygon to polygon on an editable db layer)
layer=iface.activeLayer()
# Get the URI of the layer
uri = layer.dataProvider().dataSourceUri()
if "dbname" in uri: #db layer hopefully
# Extract connection parameters
params = uri.split(' ')
param_dict = {}
for param in params:
try:
key, value = param.split('=')
param_dict[key] = value
except:
pass
# Get individual parameters
host = param_dict.get('host')
port = param_dict.get('port')
dbname = param_dict.get('dbname').replace("'","")
user = param_dict.get('user').replace("'","")
password = param_dict.get('password').replace("'","")
type=param_dict.get('type')
table=param_dict.get('table')
srid=param_dict.get('srid')
geom=params[-1].replace("(","").replace(")","")
pkey=param_dict.get('key').replace("'","")
if type=="MultiPolygon":
#logic here to inform user that is multipolygon
#change type to Polygon
qstr=f"""
drop table if exists lizmap_temppolygons;
CREATE temporary TABLE lizmap_temppolygons AS
SELECT (ST_Dump({geom})).{geom} AS geom, {pkey}
FROM {table};
ALTER TABLE {table} ADD COLUMN lizmap_geom_polygon geometry(POLYGON, {srid});
UPDATE {table}
SET lizmap_geom_polygon = lizmap_temppolygons.geom
FROM lizmap_temppolygons
WHERE {table}.{pkey} = lizmap_temppolygons.{pkey};
ALTER TABLE {table} DROP COLUMN {geom};
ALTER TABLE {table} RENAME COLUMN lizmap_geom_polygon TO {geom};
"""
#change layer type in qgs project
# Get the current project instance
project = QgsProject.instance()
# Get the file path of the current project
project_path = project.fileName()
# Load the QGS file
tree = ET.parse(project_path)
root = tree.getroot()
# Define the specific id you want to check
target_id = layer.id()
# Update layer-tree-layer tag
for elem in root.findall(f".//layer-tree-layer[@id='{target_id}']"):
if elem.attrib['type'] == 'MultiPolygon':
elem.set('type', 'Polygon')
# Update maplayer and datasource tag
for maplayer in root.findall(".//maplayer"):
layer_id = maplayer.find("id").text.strip()
if layer_id == "2_3_1":
for datasource in maplayer.findall(".//datasource"):
if 'type=MultiPolygon' in datasource.text:
datasource.text = datasource.text.replace('type=MultiPolygon', 'type=Polygon')
# Save the changes
tree.write(project_path)
print("layer type changed in qgs file. close qgis without saving and open project to take changes effect")
# Connect to the PostgreSQL database
conn = psycopg2.connect(
dbname=dbname,
user=user,
password=password,
host=host,
port=port
)
# Create a cursor object
cur = conn.cursor()
retry_change_geom=0
try:
# Execute an SQL query
cur.execute(sql.SQL(qstr))
except Exception as e:
#try rebuild views
print("err " , e)
e=str(e)
ee=e.split("Sicht ")
if ee[0]==e:
e=ee[0]
ee=e.split("ue ")
if ee[0]==e:
ee=e.split("iew ")
print(ee)
#rollback errorous transaction and close cursor
conn.rollback()
retry_change_geom=1
if retry_change_geom:
viewdefinitions={}
for f in range(1,len(ee),1):
viewnamefull=ee[f].split(" ")[0]
viewname=viewnamefull.split(".")
if len(viewname)==1:
viewname=viewname[0]
else:
viewname=viewname[1]
rqstr=f"""
SELECT view_definition
FROM information_schema.views
WHERE table_name LIKE '{viewname}';
"""
try:
# Execute an SQL query
cur.execute(rqstr)
# Fetch and print the results
rows = cur.fetchall()
viewdef=rows[0][0]
print("viewdef: ", viewdef)
viewdefinitions[viewnamefull]=viewdef
except Exception as e:
print(e)
pass
#delete views
for f in viewdefinitions.keys():
dqstr=f"""
drop view if exists {f};
"""
cur.execute(dqstr)
conn.commit()
#retry change geom type
try:
cur.execute(sql.SQL(qstr))
conn.commit()
except Exception as e:
print("ERR",e)
conn.rollback()
#recreate views
for f in viewdefinitions.keys():
cqstr=f"""
create view {f} as {viewdefinitions[f]}
"""
cur.execute(cqstr)
# Close the cursor and connection
conn.commit()
cur.close()
conn.close()
Add infos to doc that layer type multipolygon cannot reshape/split in edit digitation :
[ ] Maybe grey out buttons if polygon type is multipolygon (lizmap-web-client).
[ ] Add option to change layer type to polygon if type multipolygon if edition. If errors (e.g. dependencies like views) return on textbox (e.g. on checks tab, if layer is editable). This makes reshape tool available.
[ ] Add option to add "trigger function to increase
pkey
if exists before insert" (should be one global PostgreSQL trigger function). Then add trigger function to specific layer (e.g. named by id). This should be done in checks table. But the user should be informed about this because multiple inserts could slow down sql inserts because of trigger.Both should make buttons active in edit digitation in webgis (lizmap-web-client). This would afford new keys in editon layers cfg.
For split tool it depends. If many features it would be better in sql rather than in js?
As talking to cfg, it would be great to have shortname tag of layer in cfg too as shortname is used in lizmap getmap request per layer.