1

I have created a hierarchy table in my SQL Server. I have a column hierarchyId. Each level of hierarchy represent a geographical/political level of a country:

  • Countries
  • Regions
  • Provinces

For each row I can fill some boundaries or not. To simplify I replace in the example the geometry column with a bit column.

I need to get lowest level that has boundaries filled. If at least one child has boundaries, surely also parent has boundaries.

I make an example:

enter image description here

For example, I have that tree. In my query I should get green and red areas. In this moment I get only green areas.. so, I should get:

  • Valle d'aosta because is the lowest level and it has boundaries (and that is OK);
  • Milano and Brescia because they are the lowest level with boundaries. I should not get Bergamo because it has no boundaries, but I should get Lombardia instead of Bergamo;
  • Italy because both Piemonte and Torino have no boundaries;
  • Lazio because Roma has no boundaries.

My query is partially correct.. I get all lowest levels. But I do not get the minimum high level that respect my condition..

I share a link with the example: http://sqlfiddle.com/#!18/878577/1

Here also the query you can see in sql fiddler:

select * from country_subdivisions cs
   where IsoCode IN(
        select cs.IsoCode
          from parent p
         where cs.Level.IsDescendantOf(p.Level) = 1
           and p.CountryISOAlpha2Code = cs.CountryISOAlpha2Code
        --   and (cs.Level.GetLevel() = p.Level.GetLevel() + 1 or cs.Level.GetLevel() = p.Level.GetLevel())
            and cs.Level.GetLevel() = (SELECT MAX(leaf.Level.GetLevel()) FROM country_subdivisions leaf WHERE leaf.Level.IsDescendantOf(cs.Level) = 1 and leaf.HasBoundaries = 1)
           )

As you can see I correctly get the green areas but not the red one.

Any Idea? Do I have been clear?

Thank you

Simone
  • 2,304
  • 6
  • 30
  • 79
  • Is it possible to have a node with HasBoundaries=0, which has a child with HasBoundaries=1? If possible, what would be the expected output when TORINO would have had HasBoundaries=1? Would IT still be in the output? – trincot Jul 12 '21 at 12:58
  • i am sorry.. i havent specified it. no, it is not possibile... if at least a child has boundaries, surely also parent has boundaries – Simone Jul 12 '21 at 13:54

1 Answers1

2

I think the logic is summarized as follows:

Return a parent when:

  • That parent has boundaries, and
  • Either:
    • it has no children, or
    • it has has at least one child that has no boundaries.

This could be formulated as follows:

select parent.*
from   country_subdivisions parent
where  parent.HasBoundaries = 1
and    0 < (select case 
                   when  count(*) = 0 then 1
                   else  count(case when child.HasBoundaries = 0 then 1 end)
                   end
            from   country_subdivisions child
            where  child.Level.GetAncestor(1) = parent.Level
           );
trincot
  • 317,000
  • 35
  • 244
  • 286
  • While the sample in the data is structured such that one needs to traverse at most one level in the tree to reach a node that has boundaries, I'm not sure that would necessarily be true (using the sample data, say that Lombardia similarly did not have boundaries) and the `GetAncestor(1)` seems to have an implicit assumption. Am I misunderstanding? – Ben Thul Jul 12 '21 at 15:50
  • 1
    I asked the OP about this kind of situation: they say it cannot occur. – trincot Jul 12 '21 at 15:58
  • 1
    yes, I confirm it cannot happen... It's perfect your solution... wow, really great job... it was days I worked on it, but with no success – Simone Jul 12 '21 at 16:15