dhis2-devs team mailing list archive

[Jim Grace <jimgrace@xxxxxxxxx>]

Hi Pepe,

The resource table _orgunitstructure can help you out. There is one row in
this table for every organisation unit in the system, and it has references
to all the higher-level organisation units (if any) above that org unit.

Just make sure that the resource tables are refreshed after you add,
rename, or reorganize any organisation units, and before you run the query.
The resource tables are refreshed as part of the analytics refresh, or they
can be refreshed separately by Apps -> Data Administration -> Resource
Table -> Generate tables, or through a Web API call (see section 1.30 of
the developer manual). Then your query can be something like:

select o1.name, o2.name, o3.name, o4.name, o5.name, o6.name, o6.coordinates
from _orgunitstructure o
join organisationunit o1 on o1.organisationunitid = o.idlevel1
join organisationunit o2 on o2.organisationunitid = o.idlevel2
join organisationunit o3 on o3.organisationunitid = o.idlevel3
join organisationunit o4 on o4.organisationunitid = o.idlevel4
join organisationunit o5 on o5.organisationunitid = o.idlevel5
join organisationunit o6 on o6.organisationunitid = o.idlevel6
where o.level = 6

In this case the 'where' clause may not even be needed. The inner joins on
idlevel1-6 will filter out any table records that apply to higher-level org
units, because these will have a null value in one or more of these columns.

Cheers,
Jim


On Thu, Dec 17, 2015 at 8:06 AM, Pepe ken mvulu <pepekento1@xxxxxxxxx>
wrote:

> Hi Jim,
>
> Currently we are using version 2.20, we have 7 levels of organisation
> unit, a map is available at Level 4 (Zone de Santé), level 6 contains the
> coordinates of the villages that I want to export as CVS or Excel format
> using a query or other procedure available.
>
> Here is a screenshot of Level 6
>
> Here is the picture I want to download for all villages
>
>
>
> *Name level 1*
>
> *Name level 2*
>
> *Name level 3*
>
> *Name level 4*
>
> *Name level 5*
>
> *Name level 6*
>
> *Coordinates level 6 (decimal)*
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Regards,
>
> Pepe Ken Mvulu
>
>
>
> *De :* Jim Grace [mailto:jimgrace@xxxxxxxxx]
> *Envoyé :* mercredi 16 décembre 2015 19:09
> *À :* Pepe Ken Mvulu
> *Cc :* Dhis2-User; DHIS 2 developers
> *Objet :* Re: [Dhis2-users] Export hierarchy coordinates
>
>
>
> Hi Pepe,
>
>
>
> Can you say more about what you mean by "hierarchy coordinates"? Are you
> talking about the GIS location of the organisation units? Or the path of
> ancestor organisation units from each organisation unit up to the root?
> What exactly would you like to see and, ideally, in what format? (An
> example could help.)
>
>
>
> Which version of DHIS 2 are you using. (We've been adding some new
> features relating to organisation unit hierarchy.)
>
>
>
> Cheers,
>
> Jim
>
>
>
> On Wed, Dec 16, 2015 at 9:11 AM, Pepe Ken Mvulu <pepekento1@xxxxxxxxx>
> wrote:
>
> Hi All,
> I need to export the hierarchy coordinates of organisation units, but
> I do not know what to do. Any ideas? any query ?
> Best Regards
>
> Pepe Ken Mvulu
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-users
> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-users
> More help   : https://help.launchpad.net/ListHelp
>
>
>