dhis2-devs team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

I have been looking for this code for years, but apparently it was
"lost" when the server crashed a few years back.

Would agree though doing it in Java would be a better idea.
Manipulating the DB is tricky and you need to be very careful, (for
instance, using the hibernate_sequence values for primary keys).  But
in the absence of this code, we may have to document situations where
bulk importation /conversion of external hierarchies is going to be
required.

Regards,
JPP





On 5/21/10, Ola Hodne Titlestad <olatitle@xxxxxxxxx> wrote:
> Knut,
>
> Remember that we some years back had a student group working on converting
> shapefiles (basically a country set of level ids) to a DHIS orgunit
> hierarchy.
> Not sure where that code is now, but they certainly went through a similar
> process like the one you are describing.
> To me it seems it would be more robust to include this in the import module
> and take care of this hierarchy generation in the java code rather than
> manually manipulating the database.
>
> Ola
> --------
>
> On 21 May 2010 11:17, Jason Pickering <jason.p.pickering@xxxxxxxxx> wrote:
>
>> OK, you are all over the place on this one. Lets take it one at a time.
>>
>> Use of the hibernate_sequence is a very good idea (as I found out the
>> hard way) as it is easy to use external tools to generate ids, but you
>> have no guarentee that this will not clash with something that DHIS2
>> inserts into the DB.
>>
>> I think you will need a staged approach. Dump everything into a
>> temporary table, and use the hibernate sequence to get a new primary
>> key. Use the parentID  (mapped to the code field)  to then update the
>> parentID field of the child. It should be pretty easily done, as the
>> parent/child relationship is implicit in the WHO levelid. If you
>> already have a parentID in the WHO information, then it should be even
>> easier.
>>
>> You do not need to use FME to generate the ID. Just use something like..
>>
>> INSERT INTO organisationunit_temp (organisationunitid....)
>> VALUES (nextval('hibernate_sequence',....)
>>
>> You may need to remove the parentid primary key constraint during the
>> initial insert and then reconstruct them using an update statement. I
>> do not know exactly what the statement would be, but I was almost
>> certain I had written this before at some point in time .
>>
>> I do not think that this is enough of a justification to increase the
>> size of the organisationunitid field, as it should be big enough to
>> accommodate any realistic orgunit hierarchy.
>>
>> In general, I would suggest the use of a view to present to PostGIS
>> instead of directly linking to the table itself. Of course, there are
>> other problems with persisting views in DHIS2 which we are aware of,
>> but I do not anticipate that this table would ever be deleted, so it
>> should be pretty safe.
>>
>> Also, you may want to consider GeoKettle or Talend, as something that
>> could be integrated into DHIS2 for processing of the Geodata.
>>
>> Regards,
>> Jason
>>
>>
>> On 5/21/10, Knut Staring <knutst@xxxxxxxxx> wrote:
>> > Using the hibernate_sequence seems like a good idea in most cases, but
>> > for Orgunits it's really crucial to populate the parentid field (which
>> > of course would also have to change to bigint for this to make any
>> > sense).
>> >
>> > So while I agree that the original alphanumeric/string LVLID would fit
>> > well in the Code field, I need to be able to populate the hierarchy
>> > for the whole world from the database. I could conceivably come up
>> > with a script in FME to generate sequential IDs, but that seems quite
>> > complicated, and would also not use hibernate_sequence (possibly I
>> > just don't know enough about how to use that). I use FME mainly
>> > because I have not found a good alternative for simplifying polygons
>> > without causing cracks between them. It would in some ways be nice to
>> > be able to do everything in PostGIS which has functions like
>> > ST_AsGeoJSON and Simplify, but as you can see from the below link, the
>> > results are not quite satisfying:
>> > http://bostongis.org/PrinterFriendly.aspx?content_name=postgis_simplify
>> >
>> > mapshaper.org seem to have some of the same problems, which have been
>> > avoided by Bjørn Sandvik when he made these world datasets:
>> > http://thematicmapping.org/downloads/world_borders.php. The tool he
>> > used for simplfying is ArcToolbox Simplify Polygon tool (see page 19
>> > of this master thesis:
>> > http://thematicmapping.org/downloads/Thematic_Mapping_Engine.pdf).
>> > Unfortunately, FME and ArcToolbox are not integratable to DHIS2.
>> >
>> > While on this topic, I do think we perhaps need to add a LEVEL field
>> > to the ORGANISATIONUNIT table. That would make it quite corresponding
>> > to a PostGIS table (separatable on the LEVEL field in order to
>> > generate layers for Provinces, Districts etc). This is sort of
>> > available in the generated ORGUNITSTRUCTURE table, but that a) needs
>> > to be generated and b) seems a bit inefficient to have to join to
>> > another big table just to get the level. And perhaps we might want to
>> > have a separate table in DHIS2 with the full precision technologies
>> > and a link to the orgunit table.
>> >
>> > Knut
>> >
>> > On Fri, May 21, 2010 at 10:28 AM, Jason Pickering
>> > <jason.p.pickering@xxxxxxxxx> wrote:
>> >> I do not really have a problem with this, but shouldn't this
>> >> information go in the "code" field? Or is it a problem with the number
>> >> of orgunits? It would seem unlikely that we would ever have more than
>> >> 2,147,483,647 orgunits.
>> >>
>> >> Are you inserting the ID as the organisationunitid? This seems this
>> >> might cause problems with possible clashes with the hibernate_sequence
>> >> which is used to generate IDs?
>> >>
>> >> I have run into this issue only once, but since then, I always use the
>> >> hibernate_sequence to generate IDs when I directly insert data into
>> >> the DB.
>> >>
>> >> Regards,
>> >> Jason
>> >>
>> >>
>> >> On 5/20/10, Knut Staring <knutst@xxxxxxxxx> wrote:
>> >>> Hello,
>> >>>
>> >>> In the process of converting WHO identifiers for administrative
>> >>> boundaries (LVLID) to ids usable for DHIS2, I've run into the limits
>> >>> of the integer datatype we use in DHIS2.
>> >>>
>> >>> The LVLID is a three letter ISO code followed by 18 digits. We are
>> >>> converting the alphabetical ISO for the country to an ISO numeric code
>> >>> (preceeded by 1 to make it numeric).
>> >>>
>> >>> Would it be problematic to change the datatype for organisationunitid
>> >>> from integer to bigint?
>> >>>
>> >>> Knut
>> >>>
>> >>> _______________________________________________
>> >>> Mailing list:
>> >>> https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>
>> >>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>> >>> Unsubscribe :
>> >>> https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>
>> >>> More help   : https://help.launchpad.net/ListHelp
>> >>>
>> >>
>> >>
>> >> --
>> >> --
>> >> Jason P. Pickering
>> >> email: jason.p.pickering@xxxxxxxxx
>> >> tel:+260968395190
>> >>
>> >
>> >
>> >
>> > --
>> > Cheers,
>> > Knut Staring
>> >
>>
>>
>> --
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@xxxxxxxxx
>> tel:+260968395190
>>
>> _______________________________________________
>> Mailing list:
>> https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>
>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe :
>> https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>
>> More help   : https://help.launchpad.net/ListHelp
>>
>


-- 
--
Jason P. Pickering
email: jason.p.pickering@xxxxxxxxx
tel:+260968395190