Find all buildings having a spatial representation in all of a selected LoDs e.g. [1, 2, 3] (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="and" 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
      b15.building_root_id
    from
      (
        (
          select
            x12.building_root_id
          from
            citydb.building x12
          where
            (
              x12.lod1_solid_id is not null
              or x12.lod1_multi_surface_id is not null
              or x12.lod1_terrain_intersection is not null
            )
        )
        intersect
          (
            select
              b13.building_root_id
            from
              citydb.building b13
            where
              (
                (
                  b13.lod2_solid_id is not null
                  or b13.lod2_multi_surface_id is not null
                  or b13.lod2_multi_curve is not null
                  or b13.lod2_terrain_intersection is not null
                )
                or exists (
                  select
                    1
                  from
                    citydb.building_installation d13
                  where
                    d13.building_id = b13.id
                    and (
                      (
                        d13.lod2_brep_id is not null
                        or d13.lod2_other_geom is not null
                        or d13.lod2_implicit_rep_id is not null
                      )
                      or exists (
                        select
                          1
                        from
                          citydb.thematic_surface e13
                        where
                          e13.building_installation_id = d13.id
                          and e13.lod2_multi_surface_id is not null
                      )
                    )
                )
                or exists (
                  select
                    1
                  from
                    citydb.thematic_surface n13
                  where
                    n13.building_id = b13.id
                    and n13.lod2_multi_surface_id is not null
                )
              )
          )
        intersect
          (
            select
              y13.building_root_id
            from
              citydb.building y13
            where
              (
                (
                  y13.lod3_solid_id is not null
                  or y13.lod3_multi_surface_id is not null
                  or y13.lod3_multi_curve is not null
                  or y13.lod3_terrain_intersection is not null
                )
                or exists (
                  select
                    1
                  from
                    citydb.building_installation a14
                  where
                    a14.building_id = y13.id
                    and (
                      (
                        a14.lod3_brep_id is not null
                        or a14.lod3_other_geom is not null
                        or a14.lod3_implicit_rep_id is not null
                      )
                      or exists (
                        select
                          1
                        from
                          citydb.thematic_surface b14
                        where
                          b14.building_installation_id = a14.id
                          and (
                            b14.lod3_multi_surface_id is not null
                            or exists (
                              select
                                1
                              from
                                citydb.opening c14
                                inner join citydb.opening_to_them_surface d14 on c14.id = d14.opening_id
                              where
                                d14.thematic_surface_id = b14.id
                                and (
                                  c14.lod3_multi_surface_id is not null
                                  or c14.lod3_implicit_rep_id is not null
                                )
                            )
                          )
                      )
                    )
                )
                or exists (
                  select
                    1
                  from
                    citydb.thematic_surface n14
                  where
                    n14.building_id = y13.id
                    and (
                      n14.lod3_multi_surface_id is not null
                      or exists (
                        select
                          1
                        from
                          citydb.opening o14
                          inner join citydb.opening_to_them_surface p14 on o14.id = p14.opening_id
                        where
                          p14.thematic_surface_id = n14.id
                          and (
                            o14.lod3_multi_surface_id is not null
                            or o14.lod3_implicit_rep_id is not null
                          )
                      )
                    )
                )
              )
          )
      ) b15
  )