create table tmp_msp as
  select object_region.objectid, structure.name_en, area_grossfloor, area_usablefloor, costs_construction, array_agg(distinct facolorcat.name_en) AS facade_color_name, array_agg(distinct famatcat.name_en) AS facade_material_name,
   array_agg(distinct featurecat.name_en) AS feature_name, array_agg(distinct framematcat.name_en) AS frame_material_name, array_agg(distinct framesyscat.name_en) AS frame_system_name, height_floortoceiling, height_floortofloor,
   pinpoint, height_architectural, height_tip, volume, height_enclosure, height_mainroof, height_topfloor, array_agg(distinct usage_main_cat.name_en) AS usage_main_name,
   address, floors_og, statuscat.name_en AS status_name, construction_end, construction_start, 
   demolition_end, renovation_last, floors_ug, postcode, parkingplaces, name, strformcat.name_en AS structural_form_name, name_other, stylecat.name_en AS style_name,
   elevatordata.value AS elevators, perimeterformcat.name_en AS perimeter_form, lengthdata.value AS length, widthdata.value AS width,
   array_agg(distinct windowtypecat.name_en) AS window_type,
   array_agg(distinct roofcat.name_en) AS roof_form,
   array_agg(distinct roofmatcat.name_en) AS roof_material,
   array_agg(distinct heatcat.name_en) AS heating_type,
   array_agg(distinct foundationsystemcat.name_en) AS foundation_system,
   array_agg(distinct urldata.value) AS url,
   array_agg(distinct facadesystemcat.name_en) AS facade_system
  from object_region inner join structure on structure.id = object_region.objectid
   inner join category as strformcat on strformcat.categoryid = structural_form 
   left join category as stylecat on stylecat.categoryid = style 
   left join category as statuscat on statuscat.categoryid = status 
   left join category as facolorcat on facolorcat.categoryid = ANY(facade_color)
   left join category as famatcat on famatcat.categoryid = ANY(facade_material)
   left join category as framematcat on framematcat.categoryid = ANY(frame_material)
   left join category as framesyscat on framesyscat.categoryid = ANY(frame_system)
   left join category as featurecat on featurecat.categoryid = ANY(feature)
   left join category as usage_main_cat on usage_main_cat.categoryid = ANY(usage_main)
   left join data as elevatordata on (structure.id = elevatordata.objectid and elevatordata.visible and elevatordata.datatypeid = 'elevators')
   left join data as lengthdata on (structure.id = lengthdata.objectid and lengthdata.visible and lengthdata.datatypeid = 'length')
   left join data as widthdata on (structure.id = widthdata.objectid and widthdata.visible and widthdata.datatypeid = 'width')
   left join data as urldata on (structure.id = urldata.objectid and urldata.visible and urldata.datatypeid = 'url')
   left join data as perimeterform on (structure.id = perimeterform.objectid and perimeterform.visible and perimeterform.datatypeid = 'perimeter_form')
   left join category as perimeterformcat on perimeterform.value = perimeterformcat.categoryid
   left join data as windowtypes on (structure.id = windowtypes.objectid and windowtypes.visible and windowtypes.datatypeid = 'window_type')
   left join category as windowtypecat on windowtypes.value = windowtypecat.categoryid
   left join data as roofs on (structure.id = roofs.objectid and roofs.visible and roofs.datatypeid = 'roof_form')
   left join category as roofcat on roofs.value = roofcat.categoryid
   left join data as roofmats on (structure.id = roofmats.objectid and roofmats.visible and roofmats.datatypeid = 'roof_material')
   left join category as roofmatcat on roofmats.value = roofmatcat.categoryid
   left join data as heats on (structure.id = heats.objectid and heats.visible and heats.datatypeid = 'heating_type')
   left join category as heatcat on heats.value = heatcat.categoryid
   left join data as foundationsystems on (structure.id = foundationsystems.objectid and foundationsystems.visible and foundationsystems.datatypeid =$
   left join category as foundationsystemcat on foundationsystems.value = foundationsystemcat.categoryid
   left join data as facadesystems on (structure.id = facadesystems.objectid and facadesystems.visible and facadesystems.datatypeid = 'facade_system')
   left join category as facadesystemcat on facadesystems.value = facadesystemcat.categoryid
  where regionid = 0
   and not structure.deleted and structure.visible
   group by object_region.objectid, structure.name_en, area_grossfloor, area_usablefloor, costs_construction,
   height_floortoceiling, height_floortofloor, pinpoint, height_architectural, height_tip, volume, height_enclosure, height_mainroof, height_topfloor,
   address, floors_og, status_name, construction_end, construction_start, elevators, perimeter_form, length, width,
   demolition_end, renovation_last, floors_ug, postcode, parkingplaces, name, structural_form_name, name_other, style_name;

create table tmp_msp_companies as
  select targetid AS objectid, companyrolecat.name_en AS company_role_name, companystatuscat.name_en AS company_status_name, company.name, phone, company.address, email, url
  from tmp_msp
   inner join company_role on targetid = objectid inner join company on company.id = companyid
   left join category as companyrolecat on companyrolecat.categoryid = company_role.company_role
   left join category as companystatuscat on companystatuscat.categoryid = company_status
  where not company.deleted;
