Find all buildings having at least four wall surfaces

XML Query

Not supported

SQL Query for 3DCityDB v4 (PostgreSQL)

select
  distinct b.id,
  b.objectclass_id,
  b.gmlid
from
  citydb.building a
  inner join citydb.cityobject b on a.id = b.id
  inner join citydb.thematic_surface c on (
    a.id = c.building_id
    and c.objectclass_id = 34
  )
  GROUP BY b.id   
having
  count(c.id) >= 4

SQL Query for 3DCityDB v5 (PostgreSQL - with feature_relation table)

SELECT bldg.id
  ,bldg.objectclass_id
  ,bldg.gmlid
FROM feature AS ftr_wall
INNER JOIN feature_relation AS ftr_rlt_wall
  --Since relation is association, following expression can be also used.
  --ON (ftr_rlt_wall.to_feature = ftr_wall.id OR ftr_rlt_wall.from_feature = ftr_wall.id)
  ON ftr_rlt_wall.to_feature = ftr_wall.id
    AND namespace='core'
    AND name='boundary'
    --AND relationtype='association'
INNER JOIN feature AS bldg
  ON ftr_rlt_wall.from_feature = bldg.id
    AND bldg.objectclass_id IN (25,26)
WHERE
  ftr_wall.objectclass_id = 34
GROUP BY bldg.id, bldg.objectclass_id, bldg.gmlid
HAVING count(bldg.id) >= 4

SQL Query for 3DCityDB v5 (PostgreSQL - with property table)

SELECT bldg.id
  ,bldg.objectclass_id
  ,bldg.gmlid
FROM feature AS ftr_wall
INNER JOIN property AS pro_wall
  ON pro_wall.val_feature = ftr_wall.id
    AND namespace='core'
    AND name='boundary'
INNER JOIN feature AS bldg
  ON pro_wall.feature_id = bldg.id
    AND bldg.objectclass_id IN (25, 26)
WHERE
  ftr_wall.objectclass_id = 34
GROUP BY bldg.id, bldg.objectclass_id, bldg.gmlid
HAVING count(bldg.id) >= 4