Find all buildings having a spatial representation in at least one of a selected LoDs e.g. [1, 2, 3] 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>1</lod>
    <lod>2</lod>
    <lod>3</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
      o7.building_root_id
    from
      citydb.building o7
    where
      (
        (
          (
            o7.lod1_solid_id is not null
            or o7.lod1_multi_surface_id is not null
            or o7.lod1_terrain_intersection is not null
          )
          or (
            o7.lod2_solid_id is not null
            or o7.lod2_multi_surface_id is not null
            or o7.lod2_multi_curve is not null
            or o7.lod2_terrain_intersection is not null
          )
          or (
            o7.lod3_solid_id is not null
            or o7.lod3_multi_surface_id is not null
            or o7.lod3_multi_curve is not null
            or o7.lod3_terrain_intersection is not null
          )
        )
        or exists (
          select
            1
          from
            citydb.building_installation q7
          where
            q7.building_id = o7.id
            and (
              (
                (
                  q7.lod2_brep_id is not null
                  or q7.lod2_other_geom is not null
                  or q7.lod2_implicit_rep_id is not null
                )
                or (
                  q7.lod3_brep_id is not null
                  or q7.lod3_other_geom is not null
                  or q7.lod3_implicit_rep_id is not null
                )
              )
              or exists (
                select
                  1
                from
                  citydb.thematic_surface r7
                where
                  r7.building_installation_id = q7.id
                  and (
                    (
                      r7.lod2_multi_surface_id is not null
                      or r7.lod3_multi_surface_id is not null
                    )
                    or exists (
                      select
                        1
                      from
                        citydb.opening s7
                        inner join citydb.opening_to_them_surface t7 on s7.id = t7.opening_id
                      where
                        t7.thematic_surface_id = r7.id
                        and (
                          s7.lod3_multi_surface_id is not null
                          or s7.lod3_implicit_rep_id is not null
                        )
                    )
                  )
              )
            )
        )
        or exists (
          select
            1
          from
            citydb.thematic_surface d8
          where
            d8.building_id = o7.id
            and (
              (
                d8.lod2_multi_surface_id is not null
                or d8.lod3_multi_surface_id is not null
              )
              or exists (
                select
                  1
                from
                  citydb.opening e8
                  inner join citydb.opening_to_them_surface f8 on e8.id = f8.opening_id
                where
                  f8.thematic_surface_id = d8.id
                  and (
                    e8.lod3_multi_surface_id is not null
                    or e8.lod3_implicit_rep_id is not null
                  )
              )
            )
        )
      )
  )