Find all buildings having a nested bldg:GroundSurface feature whose bldg:lod2MultiSurface property intersects a given 2D polygon

XML Query

<query xmlns="http://www.3dcitydb.org/importer-exporter/config">
  <typeNames>
    <typeName xmlns:bldg="http://www.opengis.net/citygml/building/2.0">bldg:Building</typeName>
  </typeNames>
  <filter>
    <intersects>
      <valueReference>bldg:boundedBy/bldg:GroundSurface/bldg:lod2MultiSurface</valueReference>
      <polygon>
        <exterior>35 10 45 45 15 40 10 20 35 10</exterior>
      </polygon>
    </intersects>
  </filter>
</query>

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 = 35
  )
where
  b.objectclass_id = 26
  and c.lod2_multi_surface_id is not null
  and (
    b.envelope & & 'SRID=3857;POLYGON((10 10,45 10,45 45,10 45,10 10))'
    and exists (
      select
        d.id
      from
        citydb.surface_geometry d
      where
        d.root_id = c.lod2_multi_surface_id
        and d.geometry is not null
        and ST_Intersects(
          d.geometry,
          'SRID=3857;POLYGON((35 10,45 45,15 40,10 20,35 10))'
        ) = 'TRUE'
    )
  )

SQL Query for 3DCityDB v5 (PostgreSQL)

WITH buildings_having_grounds AS (
  SELECT DISTINCT ftr_rel_ground.to_feature AS "id", pro_lod2_thm.val_surface_geometry
  FROM feature AS ftr_ground
  INNER JOIN feature_relation AS ftr_rel_ground
    ON ftr_rel_ground.to_feature = ftr_ground.id
      AND ftr_rel_ground.namespace = 'core'
      AND ftr_rel_ground.name = 'boundary'
  LEFT JOIN property AS pro_lod2_thm
    ON pro_lod2_thm.feature_id = ftr_ground.id
      AND pro_lod2_thm.namespace = 'core'
      AND pro_lod2_thm.name = 'lod3MultiSurface'
  WHERE ftr_ground.objectclass_id = 35)
SELECT ftr.id
  ,ftr.objectclass_id
  ,ftr.gmlid
  --,st_astext(st_centroid(srf_geo_bldg.geometry))
  --,st_astext(st_centroid(srf_geo_grnd.geometry))
FROM feature AS ftr, buildings_having_grounds
LEFT JOIN property AS pro_lod2
  ON pro_lod2.feature_id = buildings_having_grounds.id
    AND pro_lod2.namespace = 'core'
    AND pro_lod2.name = 'lod3MultiSurface'
LEFT JOIN surface_geometry AS srf_geo_bldg
  ON srf_geo_bldg.parent_id = pro_lod2.val_surface_geometry
LEFT JOIN surface_geometry AS srf_geo_grnd
  ON srf_geo_grnd.parent_id = buildings_having_grounds.val_surface_geometry
WHERE ftr.id  = buildings_having_grounds.id
  AND (
    -- It can be used also with ST_intersects or ST_DWithin (with 0 distance)
    -- Please consider st_dwithin would be more efficient because it uses spatial indexes.
    srf_geo_bldg.geometry && ST_GeomFromText('POLYGON((0.5 0.6,0.5 7.6,12.7 7.7,12.7 0.6,0.5 0.6))',5254)
    OR srf_geo_grnd.geometry && ST_GeomFromText('POLYGON((0.5 0.6,0.5 7.6,12.7 7.7,12.7 0.6,0.5 0.6))',5254)
    );