query the index with an extended class list and derive the super objects from the children

gistfile1.txt

with recursive derived_index(ocid,oid,acid,aid,depth) as (
    select class_id,object_id,cast (null as int), cast (null as int),0
        from GEOSUCHE where class_id in
        (
 
        with recursive derived_child(father,child,depth) as (
            select father,father,0 from cs_class_hierarchy where father=183
        union all
            select ch.father,ch.child,dc.depth+1  from derived_child dc,cs_class_hierarchy ch where ch.father=dc.child)
        select distinct father from derived_child limit 100
 
        )
 
        and geo_field && GeometryFromText('SRID=-1;POLYGON ((2582867.0671496447 5682153.983731044, 2582867.0671496447 5682114.748257412, 2582905.113719469 5682114.748257412, 2582905.113719469 5682153.983731044, 2582867.0671496447 5682153.983731044))') and intersects(geo_field,GeometryFromText('SRID=-1;POLYGON ((2582867.0671496447 5682153.983731044, 2582867.0671496447 5682114.748257412, 2582905.113719469 5682114.748257412, 2582905.113719469 5682153.983731044, 2582867.0671496447 5682153.983731044))'))
union all
    select aam.class_id,aam.object_id, aam.attr_class_id, aam.attr_object_id,di.depth+1 from cs_all_attr_mapping aam,derived_index di where aam.attr_class_id=di.ocid and aam.attr_object_id=di.oid)
select * from derived_index where ocid=183 limit 1000