Find all buildings having ONLY a LoD2 spatial representation and no other LoDs (nested features are not considered)

XML Query

Not supported

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
      c4.building_root_id
    from
      citydb.building c4
    where
      (
        c4.lod2_solid_id is not null
        or c4.lod2_multi_surface_id is not null
        or c4.lod2_multi_curve is not null
        or c4.lod2_terrain_intersection is not null
      )
  )
  and b.id not in (
    select
      h4.building_root_id
    from
      citydb.building h4
    where
      (
        (
          h4.lod0_footprint_id is not null
          or h4.lod0_roofprint_id is not null
        )
        or (
          h4.lod1_solid_id is not null
          or h4.lod1_multi_surface_id is not null
          or h4.lod1_terrain_intersection is not null
        )
        or (
          h4.lod3_solid_id is not null
          or h4.lod3_multi_surface_id is not null
          or h4.lod3_multi_curve is not null
          or h4.lod3_terrain_intersection is not null
        )
        or (
          h4.lod4_solid_id is not null
          or h4.lod4_multi_surface_id is not null
          or h4.lod4_multi_curve is not null
          or h4.lod4_terrain_intersection is not null
        )
      )
  )             

SQL Query for 3DCityDB v5 (PostgreSQL)

SELECT ftr.* 
FROM feature AS ftr
INNER JOIN property AS pro2
  ON pro2.feature_id = ftr.id
    AND pro2.namespace = 'core'
    AND pro2.name = 'lod2MultiSurface'
LEFT OUTER JOIN property AS pro_others
  ON pro_others.feature_id = ftr.id
    AND pro_others.namespace = 'core'
    AND pro_others.name IN (
        'lod1MultiSurface', 'lod3MultiSurface', 'lod4MultiSurface'
        , 'lod1Solid', 'lod3Solid', 'lod4Solid'
        , 'lod3MultiCurve', 'lod4MultiCurve'
        , 'lod1TerrainIntersectionCurve', 'lod3TerrainIntersectionCurve', 'lod4TerrainIntersectionCurve'
        )
LEFT OUTER JOIN property AS pro_prints
  ON pro_prints.feature_id = ftr.id
    AND pro_prints.namespace = 'con'
    AND pro_prints.name IN ('GroundSurface', 'RoofSurface')
WHERE ftr.objectclass_id=26
  AND ftr.is_toplevel IS true
  AND pro_others.id IS NULL
  AND pro_prints.id IS NULL;