Find all buildings having at least a LoD2 spatial representation (nested features are considered)

XML Query

<query xmlns="http://www.3dcitydb.org/importer-exporter/config">
  <typeNames>
    <typeName xmlns:bldg="http://www.opengis.net/citygml/building/1.0">bldg:Building</typeName>
  </typeNames>
  <lods mode="or" searchMode="all">
    <lod>2</lod>
  </lods>
</query>

SQL Query for 3DCityDB v4 (PostgreSQL)

select
  b.id,
  b.objectclass_id,
  b.gmlid
from
  citydb.cityobject b
where
  b.objectclass_id = 26
  and b.id in (
    select
      r4.building_root_id
    from
      citydb.building r4
    where
      (
        (
          r4.lod2_solid_id is not null
          or r4.lod2_multi_surface_id is not null
          or r4.lod2_multi_curve is not null
          or r4.lod2_terrain_intersection is not null
        )
        or exists (
          select
            1
          from
            citydb.building_installation t4
          where
            t4.building_id = r4.id
            and (
              (
                t4.lod2_brep_id is not null
                or t4.lod2_other_geom is not null
                or t4.lod2_implicit_rep_id is not null
              )
              or exists (
                select
                  1
                from
                  citydb.thematic_surface u4
                where
                  u4.building_installation_id = t4.id
                  and u4.lod2_multi_surface_id is not null
              )
            )
        )
        or exists (
          select
            1
          from
            citydb.thematic_surface d5
          where
            d5.building_id = r4.id
            and d5.lod2_multi_surface_id is not null
        )
      )
  )              

SQL Query for 3DCityDB v5 (PostgreSQL)

SELECT ftr_root.id
  ,ftr_root.objectclass_id
  ,ftr_root.gmlid
FROM feature AS ftr_root
WHERE ftr_root.objectclass_id = 26
  AND ftr_root.is_toplevel IS true
  AND ftr_root.id IN (

    SELECT DISTINCT ftr_rlt_bldg.to_feature AS "id"
    FROM feature AS ftr_ins
    INNER JOIN feature_relation AS ftr_rlt_bldg
      ON ftr_rlt_bldg.from_feature = ftr_ins.id
        AND ftr_rlt_bldg.namespace = 'bldg'
        AND ftr_rlt_bldg.name = 'buildingInstallation'
    LEFT JOIN property AS pro_ins
      ON pro_ins.feature_id = ftr_ins.id
        AND pro_ins.namespace = 'core'
        AND pro_ins.name IN ('lod3MultiSurface', 'lod2ImplicitGeometry')
    LEFT JOIN feature_relation AS ftr_rlt_ins
      ON ftr_rlt_ins.from_feature = ftr_ins.id
        AND ftr_rlt_ins.namespace = 'core'
        AND ftr_rlt_ins.name = 'boundary'
    LEFT JOIN property AS pro_ins_thm
      ON pro_ins_thm.feature_id = ftr_rlt_ins.to_feature
        AND pro_ins_thm.namespace = 'core'
        AND pro_ins_thm.name IN ('lod3MultiSurface', 'lod2ImplicitGeometry')
    WHERE ftr_ins.objectclass_id = 27
      AND (pro_ins.id IS NOT NULL OR pro_ins_thm.id IS NOT NULL)

    UNION

    SELECT DISTINCT ftr_bldg.id AS "id"
    FROM feature AS ftr_bldg
    LEFT JOIN property AS pro_bldg
      ON pro_bldg.feature_id = ftr_bldg.id
        AND pro_bldg.namespace = 'core'
        AND pro_bldg.name IN ('lod3MultiSurface', 'lod2MultiCurve', 'lod2TerrainIntersectionCurve')
    LEFT JOIN feature_relation AS ftr_rel_thm
      ON ftr_rel_thm.from_feature = ftr_bldg.id
        AND ftr_rel_thm.namespace = 'core'
        AND ftr_rel_thm.name = 'boundary'
    LEFT JOIN property AS pro_bldg_thm
      ON pro_bldg_thm.feature_id = ftr_rel_thm.to_feature
        AND pro_bldg_thm.namespace = 'core'
        AND pro_bldg_thm.name = 'lod3MultiSurface'
    WHERE ftr_bldg.objectclass_id = 26
      AND (pro_bldg.id IS NOT NULL OR pro_bldg_thm.id IS NOT NULL)
  );