Export hierarchy coordinates

Support - Assistance technique
1

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

2

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@gmail.com 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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

3

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@gmail.com]
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@gmail.com 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@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp

4

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

image

image

···

On Thu, Dec 17, 2015 at 8:06 AM, Pepe ken mvulu pepekento1@gmail.com 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@gmail.com]
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@gmail.com 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@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp

5

Hi Jim,
Great, query worked perfectly !

Thanks a lot,

Pepe Ken Mvulu

image

image

···

2015-12-17 16:28 GMT+01:00 Jim Grace jimgrace@gmail.com:

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@gmail.com 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@gmail.com]
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@gmail.com 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@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp