dhis2-devs team mailing list archive

[Pepe Ken Mvulu <pepekento1@xxxxxxxxx>]

Hi Jim,
Great, query worked perfectly !

 Thanks a lot,


Pepe Ken Mvulu

2015-12-17 16:28 GMT+01:00 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
>>
>>
>>
>
>