DROP TABLE tmp_shapefile;
CREATE TABLE tmp_shapefile AS
SELECT structure.id, pinpoint, name, area_usablefloor, costs_construction, height_floortoceiling AS floor_ceil, height_floortofloor AS floor_floor,
  height_architectural, height_tip, volume, height_enclosure, height_mainroof, height_topfloor, address, floors_og, stat.name_en AS statusname, 
  floors_ug, form.name_en AS formname, styl.name_en AS arcstyle,
  city.name_en AS city,

  rtrim(textcat_all(color.name_en||','),',') AS color,
  rtrim(textcat_all(framesystem.name_en||','),',') AS framesystem,
  rtrim(textcat_all(framematerial.name_en||','),',') AS framematerial,
  rtrim(textcat_all(feature.name_en||','),',') AS features,
  rtrim(textcat_all(facadematerial.name_en||','),',') AS facadematerials

FROM structure
  INNER JOIN category AS stat ON status = stat.categoryid 
  LEFT JOIN category AS form on structural_form = stat.categoryid
  LEFT JOIN category AS styl on style = styl.categoryid

  LEFT JOIN category AS color on color.categoryid = ANY(facade_color)
  LEFT JOIN category AS facadematerial on facadematerial.categoryid = ANY(facade_material)
  LEFT JOIN category AS framesystem on framesystem.categoryid = ANY(frame_system)
  LEFT JOIN category AS framematerial on framematerial.categoryid = ANY(frame_material)
  LEFT JOIN category AS feature on feature.categoryid = ANY(feature)
  
  LEFT JOIN object_region AS citycon ON citycon.objectid = structure.id INNER JOIN region AS city ON (citycon.regionid = city.id AND city.regiongroupid = 6)

WHERE not structure.deleted and structure.visible

GROUP BY structure.id, pinpoint, name, area_usablefloor, costs_construction, height_floortoceiling, height_floortofloor,
  height_architectural, height_tip, volume, height_enclosure, height_mainroof, height_topfloor, address, floors_og, stat.name_en,
  floors_ug, form.name_en, styl.name_en, city.name_en
;


ALTER TABLE tmp_shapefile ADD COLUMN state TEXT;
ALTER TABLE tmp_shapefile ADD COLUMN country TEXT;
ALTER TABLE tmp_shapefile ADD COLUMN district1 TEXT;
ALTER TABLE tmp_shapefile ADD COLUMN district2 TEXT;

UPDATE tmp_shapefile SET state = state.name_en FROM structure INNER JOIN object_region AS statecon ON statecon.objectid = structure.id 
  INNER JOIN region AS state ON (statecon.regionid = state.id AND state.regiongroupid = 4) 
  WHERE structure.id = tmp_shapefile.id;
UPDATE tmp_shapefile SET country = country.name_en FROM structure INNER JOIN object_region AS countrycon ON countrycon.objectid = structure.id 
  INNER JOIN region AS country ON (countrycon.regionid = country.id AND country.regiongroupid = 3) 
  WHERE structure.id = tmp_shapefile.id;
UPDATE tmp_shapefile SET district1 = region.name_en FROM structure INNER JOIN object_region ON objectid = structure.id INNER JOIN region ON (regionid = region.id AND region.regiongroupid = 7) 
  WHERE structure.id = tmp_shapefile.id;
UPDATE tmp_shapefile SET district2 = region.name_en FROM structure INNER JOIN object_region ON objectid = structure.id INNER JOIN region ON (regionid = region.id AND region.regiongroupid = 8) 
  WHERE structure.id = tmp_shapefile.id;
