Querying data associated with hierarchies

I'm having trouble in finding a way to generalized querying data
associated with a hierarchical, geographical structure.

Here goes a US-based example.

For the client company's purposes, the US is organized into Regions,
States, and Counties (the hierarchy in my case is six levels deep).

There are business objects (data) associated with each level in the
hierarchy. Different data is associated with different levels. Driver
licenses are by state, so they can be consulted by state, region, or
national. Birth certificates are by county (or whatever), so they can
be consulted by county, state, region, or national. Etc.

The client wants to be able to consult the information and have reports
at every applicable level above the one in which they are defined.

My business objects are defined as plain object using an ORM tool (Java
beans and Hibernate). Being able to generalize this type of
hierarchical querying would save as a great deal of effort on the
project. I've thought about a solution much, but none of the ones I've
come up with seem right (at times because Java ideosyncracies about
static methods get in the way).

I know there must be an acceptable solution because GIS software lives
by this kind of queries.

Any help would be welcome.

There's the obvious but dangerous solution of building a specific
method (SQL code) for each query. That's what I want to avoid.

TIA,

Juanco

Juancarlo Añez wrote:

> I'm having trouble in finding a way to generalized querying data
> associated with a hierarchical, geographical structure.
>
> Here goes a US-based example.
>
> For the client company's purposes, the US is organized into Regions,
> States, and Counties (the hierarchy in my case is six levels deep).

So let's put them all into an Area table. Each Area has a Name, an AreaID,
and a ContainerAreaID. The latter stores the AreaID of

So the US Sales Office record has an AreaID pointing to an Area whose Name
is "US". This Area's ContainerAreaID points to the Area named "North
America", and its ContainerAreaID points to "Planet Earth". (A null-object
makes a good sentinel value for these kinds of linked lists.)

Notice this scheme throws away the area type. An AreaType or AreaTypeID
would be a harmless addition to the Area table, and it would contain or
point to strings like "Region", "State", etc.

Now the problem is how to query a linked list in SQL.

--
Phlip
http://www.c2.com/cgi/wiki?ZeekLand

Philip,

I don't have any problems with managing the geographic hierarchy. It
could be a single table, or one per concrete class. It doesn't matter.

My problem is with having the conceptually same report aggregated by
different hierarchy levels (graduates by school, by school area, by
county, by state, national... that kind of thing, sometimes with
several aggregations performe in the same report [compare state
averages to county averages]).

I intuitivelly think that it will be a waste to code each combination
by hand, but I haven't been able to find a generic solution.

Regards,

Juanco

Juancarlo Añez wrote:

> I intuitivelly think that it will be a waste to code each combination
> by hand, but I haven't been able to find a generic solution.

Start with a simple data model like I suggested.

Code each combination by hand, and write unit tests as you do. Run all tests
after every few edits.

Now refactor to remove all the duplication from the special-case code that
covers each combination. You will probably achieve code with the quality of
a design pattern.

--
Phlip
http://www.c2.com/cgi/wiki?ZeekLand

Juancarlo Añez wrote:
> There's the obvious but dangerous solution of building a specific
> method (SQL code) for each query. That's what I want to avoid.

The words "Querying" in your message hints that you might want to
investigate database (rather than OOP) perspective.
http://troels.arvin.dk/db/rdbms/links/#hierarchical
is a good starting point.

Mikito,

| The words "Querying" in your message hints that you might want to
| investigate database (rather than OOP) perspective.
| http://troels.arvin.dk/db/rdbms/links/#hierarchical
| is a good starting point.

I thought about that, but these "queries" must be interactive. Users
should be able to drill down from one level to the other by simple HTML
links.

For me that means that there will be coding involved, and I'd rather it
be Java/JavaScript code than one of some specific SQL query tool.

--
Juanco

Juancarlo Añez wrote:

> I thought about that, but these "queries" must be interactive. Users
> should be able to drill down from one level to the other by simple HTML
> links.

Are you trying to think of one object model to satisfy several different
kinds of querying situations?

Why not have two object models, one to support drilling and the other for
reports.

--
Phlip
http://www.c2.com/cgi/wiki?ZeekLand

Juancarlo Añez wrote:
> Mikito,
>
> | The words "Querying" in your message hints that you might want to
> | investigate database (rather than OOP) perspective.
> | http://troels.arvin.dk/db/rdbms/links/#hierarchical
> | is a good starting point.
>
> I thought about that, but these "queries" must be interactive. Users
> should be able to drill down from one level to the other by simple HTML
> links.

What in these queries make them "non-interactive"? If your front end
displays hierarchy locally (as one level, with all the branches
"collapsed") then you don't even have to perform hierarchical queries.
On the other hand, if you display the whole path from a node to the
root of the hierarchy, then it's hierarchical query.

> For me that means that there will be coding involved, and I'd rather it
> be Java/JavaScript code than one of some specific SQL query tool.

I don't understand. If your data is in the database, then you have to
query it. The query could be "static", of course, wired into your
presentation code.

If the data is not in the database, then you can fantasize how to
organize your objects.