dhis2-devs team mailing list archive

[Ola Hodne Titlestad <olatitle@xxxxxxxxx>]

We have already a blueprint on importing orgunit hierarchies off structured
excel files and this seems very related.
I am sure Bob can do some transformation magic to the shapefile information
using level ids and orgunit names to create orgunit hierarchies off these,
either directly or we find a way to convert the shapefile info to the format
of the structured excel format we recommend for bulk orgunit import.

Ola Hodne Titlestad |Technical Officer|
Health Metrics Network (HMN) | World Health Organization
Avenue Appia 20 |1211 Geneva 27, Switzerland | Email: titlestado@xxxxxxx|Tel:
+41 788216897
Website: www.healthmetricsnetwork.org

Better Information. Better Decisions. Better Health.


On 21 May 2010 11:42, Jason Pickering <jason.p.pickering@xxxxxxxxx> wrote:

> 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>
> <https://launchpad.net/%7Edhis2-devs>
> >> >>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> >> >>> Unsubscribe :
> >> >>> https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-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>
> <https://launchpad.net/%7Edhis2-devs>
> >> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> >> Unsubscribe :
> >> https://launchpad.net/~dhis2-devs <https://launchpad.net/%7Edhis2-devs>
> <https://launchpad.net/%7Edhis2-devs>
> >> More help   : https://help.launchpad.net/ListHelp
> >>
> >
>
>
> --
> --
> Jason P. Pickering
> email: jason.p.pickering@xxxxxxxxx
> tel:+260968395190
>