Find all buildings having an external reference to an information system ‘http://somewhere’ , where there exists an external object with the name ‘FOO’

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>
    <propertyIsEqualTo>
      <valueReference>core:externalReference[core:informationSystem="http://somewhere"]/core:externalObject/core:name</valueReference>
      <literal>FOO</literal>
    </propertyIsEqualTo>
  </filter>
</query>

SQL Query for 3DCityDB v4 (PostgreSQL)

select
  distinct b.id,
  b.objectclass_id,
  b.gmlid
from
  citydb.cityobject b
  inner join citydb.external_reference c on b.id = c.cityobject_id
where
  b.objectclass_id = 26
  and c.infosys = 'FOO'
  and c.name = 'http://somewhere'

SQL Query for 3DCityDB v5 (PostgreSQL)

sql SELECT pro.feature_id ,ftr.objectclass_id ,ftr.gmlid FROM property AS pro INNER JOIN feature AS ftr ON pro.feature_id = ftr.id WHERE pro.data_valtype = 102 --or namespace and name can be used (core:ExternalReference) AND ftr.objectclass_id = 26 AND pro.val_string = 'duckduckgo.com' -- INFOSYS or use LIKE/ILIKE operator AND pro.val_uri = 'duckduckgo.com/URI'; --URI or user LIKE/ILIKE operator