GLE-Layer-Grünflächen

SET @group_id = 1;
SET @connection = 'user=xxxx password=xxxx dbname=kvwmapsp';

INSERT INTO layer (`Name`,`Datentyp`,`Gruppe`,`pfad`,`Data`,`schema`,`document_path`,`tileindex`,`tileitem`,`labelangleitem`,`labelitem`,`labelmaxscale`,`labelminscale`,`labelrequires`,`connection`,`printconnection`,`connectiontype`,`classitem`,`filteritem`,`tolerance`,`toleranceunits`,`epsg_code`,`template`,`queryable`,`transparency`,`drawingorder`,`minscale`,`maxscale`,`offsite`,`ows_srs`,`wms_name`,`wms_server_version`,`wms_format`,`wms_connectiontimeout`,`wms_auth_username`,`wms_auth_password`,`wfs_geom`,`selectiontype`,`querymap`,`logconsume`) VALUES('Grünflächen','2',@group_id,'SELECT gruenflaechen.oid AS gruenflaechen_oid, gruenflaechen.id, bezeichnung, objnr, lage, pflegekategorie, pflegegaenge, bewirtschafter, bemerkung, dokument, \'Teilflächen\' as teilflaechen, (select round(area(memgeomunion(the_geom))) from teilflaechen where teilflaechen.id = gruenflaechen.id) as flaeche, (select memgeomunion(the_geom) from teilflaechen where teilflaechen.id = gruenflaechen.id) as the_geom FROM gruenflaechen WHERE 1=1 GROUP BY gruenflaechen.id, bezeichnung, objnr, lage, pflegekategorie, pflegegaenge, bewirtschafter, bemerkung, dokument,gruenflaechen_oid','the_geom from (select gruenflaechen.gruenflaechen.id, memgeomunion(the_geom) as the_geom from gruenflaechen.teilflaechen, gruenflaechen.gruenflaechen where gruenflaechen.teilflaechen.id = gruenflaechen.gruenflaechen.id GROUP BY gruenflaechen.gruenflaechen.id) as foo using unique id using srid=2398','gruenflaechen','','','','','',NULL,NULL,'',@connection,'','6','','','3','pixels','2398','','1',NULL,NULL,NULL,NULL,'','EPSG:2398','','1.1.0','image/png','60','','','','','0','');
SET @last_layer_id488=LAST_INSERT_ID();
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id488,'bemerkung','bemerkung','gruenflaechen','gruenflaechen','text','','','1',NULL,'Textfeld','','Bemerkung','','8');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id488,'bewirtschafter','bewirtschafter','gruenflaechen','gruenflaechen','varchar','','','1','100','Text','','Bewirtschafter','','7');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id488,'bezeichnung','bezeichnung','gruenflaechen','gruenflaechen','varchar','','','1','50','Text','','Bezeichnung','','2');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id488,'dokument','dokument','gruenflaechen','gruenflaechen','text','','','1',NULL,'Dokument','','Datei','','9');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id488,'flaeche','flaeche','','','float8','','',NULL,NULL,'Text','','Fläche','','11');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id488,'gruenflaechen_oid','oid','gruenflaechen','gruenflaechen','oid','','','1',NULL,'Text','','','','0');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id488,'id','id','gruenflaechen','gruenflaechen','int4','','','1',NULL,'Text','','','','1');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id488,'lage','lage','gruenflaechen','gruenflaechen','varchar','','','1','100','Text','','Lage','','4');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id488,'objnr','objnr','gruenflaechen','gruenflaechen','int4','','','1',NULL,'Text','','Objektnummer','','3');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id488,'pflegegaenge','pflegegaenge','gruenflaechen','gruenflaechen','int4','','','1',NULL,'Text','','Pflegegänge','','6');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id488,'pflegekategorie','pflegekategorie','gruenflaechen','gruenflaechen','int4','','','1',NULL,'Text','','Pflegekategorie','','5');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id488,'teilflaechen','teilflaechen','','','unknown','','',NULL,NULL,'SubFormEmbeddedPK','489,id,teilstueck;no_new_window','Teilflächen','','10');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id488,'the_geom','the_geom','','','geometry','','',NULL,NULL,'Geometrie','','','','12');
INSERT INTO classes (`Name`,`Layer_ID`,`Expression`,`drawingorder`,`text`) VALUES('alle',@last_layer_id488,'','1','');
SET @last_class_id=LAST_INSERT_ID();
INSERT INTO styles (`symbol`,`symbolname`,`size`,`color`,`backgroundcolor`,`outlinecolor`,`minsize`,`maxsize`,`angle`,`angleitem`,`antialias`,`width`,`minwidth`,`maxwidth`,`sizeitem`) VALUES(NULL,'circle','3','39 210 0','','','3','3','360','',NULL,NULL,NULL,NULL,'');
 SET @last_style_id=LAST_INSERT_ID();
INSERT INTO u_styles2classes (style_id, class_id) VALUES (@last_style_id, @last_class_id);

INSERT INTO layer (`Name`,`Datentyp`,`Gruppe`,`pfad`,`Data`,`schema`,`document_path`,`tileindex`,`tileitem`,`labelangleitem`,`labelitem`,`labelmaxscale`,`labelminscale`,`labelrequires`,`connection`,`printconnection`,`connectiontype`,`classitem`,`filteritem`,`tolerance`,`toleranceunits`,`epsg_code`,`template`,`queryable`,`transparency`,`drawingorder`,`minscale`,`maxscale`,`offsite`,`ows_srs`,`wms_name`,`wms_server_version`,`wms_format`,`wms_connectiontimeout`,`wms_auth_username`,`wms_auth_password`,`wfs_geom`,`selectiontype`,`querymap`,`logconsume`) VALUES('Teilgrünflächen','2',@group_id,'select teilflaechen.*, bezeichnung as gruenflaeche, round(area(the_geom))||\' m² \'||t.typ as teilstueck FROM teilflaechentypen as t, teilflaechen, gruenflaechen WHERE teilflaechen.id = gruenflaechen.id AND t.id = teilflaechen.typ','the_geom from (select oid, typ, the_geom from gruenflaechen.teilflaechen) as foo using unique oid using srid=2398','gruenflaechen','','','','','',NULL,NULL,'',@connection,'','6','typ','oid','3','pixels','2398','','1','30',NULL,NULL,NULL,'','EPSG:2398','','1.1.0','image/png','60','','','','','1','');
SET @last_layer_id489=LAST_INSERT_ID();
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id489,'gruenflaeche','bezeichnung','gruenflaechen','gruenflaechen','varchar','','','1','50','SubFormFK','488,id;no_new_window','übergeordnete Grünfläche','','3');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id489,'id','id','teilflaechen','teilflaechen','int4','','','0',NULL,'Text','','','','0');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id489,'teilstueck','the_geom','teilflaechen','teilflaechen','text','','','1',NULL,'Text','','Teilstück','','4');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id489,'the_geom','the_geom','teilflaechen','teilflaechen','geometry','MULTIPOLYGON','','1',NULL,'Geometrie','','','','2');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id489,'typ','typ','teilflaechen','teilflaechen','int4','','','1',NULL,'Auswahlfeld','select id as value, typ as output from teilflaechentypen','Typ','','1');
INSERT INTO classes (`Name`,`Layer_ID`,`Expression`,`drawingorder`,`text`) VALUES('Wege',@last_layer_id489,'4','1','');
SET @last_class_id=LAST_INSERT_ID();
INSERT INTO styles (`symbol`,`symbolname`,`size`,`color`,`backgroundcolor`,`outlinecolor`,`minsize`,`maxsize`,`angle`,`angleitem`,`antialias`,`width`,`minwidth`,`maxwidth`,`sizeitem`) VALUES(NULL,'','1','168 102 0','','0 0 0',NULL,'1','360','',NULL,NULL,NULL,NULL,'');
 SET @last_style_id=LAST_INSERT_ID();
INSERT INTO u_styles2classes (style_id, class_id) VALUES (@last_style_id, @last_class_id);
INSERT INTO classes (`Name`,`Layer_ID`,`Expression`,`drawingorder`,`text`) VALUES('Bäume',@last_layer_id489,'3','1','');
SET @last_class_id=LAST_INSERT_ID();
INSERT INTO styles (`symbol`,`symbolname`,`size`,`color`,`backgroundcolor`,`outlinecolor`,`minsize`,`maxsize`,`angle`,`angleitem`,`antialias`,`width`,`minwidth`,`maxwidth`,`sizeitem`) VALUES(NULL,'','1','59 157 0','','0 0 0',NULL,'1','360','',NULL,NULL,NULL,NULL,'');
 SET @last_style_id=LAST_INSERT_ID();
INSERT INTO u_styles2classes (style_id, class_id) VALUES (@last_style_id, @last_class_id);
INSERT INTO classes (`Name`,`Layer_ID`,`Expression`,`drawingorder`,`text`) VALUES('Rasen',@last_layer_id489,'1','2','');
SET @last_class_id=LAST_INSERT_ID();
INSERT INTO styles (`symbol`,`symbolname`,`size`,`color`,`backgroundcolor`,`outlinecolor`,`minsize`,`maxsize`,`angle`,`angleitem`,`antialias`,`width`,`minwidth`,`maxwidth`,`sizeitem`) VALUES(NULL,'','1','55 241 0','','0 0 0',NULL,'1','360','',NULL,NULL,NULL,NULL,'');
 SET @last_style_id=LAST_INSERT_ID();
INSERT INTO u_styles2classes (style_id, class_id) VALUES (@last_style_id, @last_class_id);
INSERT INTO classes (`Name`,`Layer_ID`,`Expression`,`drawingorder`,`text`) VALUES('Gehölz',@last_layer_id489,'2','1','');
SET @last_class_id=LAST_INSERT_ID();
INSERT INTO styles (`symbol`,`symbolname`,`size`,`color`,`backgroundcolor`,`outlinecolor`,`minsize`,`maxsize`,`angle`,`angleitem`,`antialias`,`width`,`minwidth`,`maxwidth`,`sizeitem`) VALUES(NULL,'','1','232 153 0','','0 0 0',NULL,'1','360','',NULL,NULL,NULL,NULL,'');
 SET @last_style_id=LAST_INSERT_ID();
INSERT INTO u_styles2classes (style_id, class_id) VALUES (@last_style_id, @last_class_id);

UPDATE layer_attributes SET options = REPLACE(options, '488', @last_layer_id488) WHERE layer_id IN(@last_layer_id488,@last_layer_id489) AND form_element_type IN ('SubFormPK', 'SubFormFK', 'SubFormEmbeddedPK');
UPDATE layer_attributes SET options = REPLACE(options, '489', @last_layer_id489) WHERE layer_id IN(@last_layer_id488,@last_layer_id489) AND form_element_type IN ('SubFormPK', 'SubFormFK', 'SubFormEmbeddedPK');