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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 >> > <[email protected]> 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 <[email protected]> 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 : [email protected] >> >>> Unsubscribe : >> >>> https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs> >> >>> More help : https://help.launchpad.net/ListHelp >> >>> >> >> >> >> >> >> -- >> >> -- >> >> Jason P. Pickering >> >> email: [email protected] >> >> tel:+260968395190 >> >> >> > >> > >> > >> > -- >> > Cheers, >> > Knut Staring >> > >> >> >> -- >> -- >> Jason P. Pickering >> email: [email protected] >> tel:+260968395190 >> >> _______________________________________________ >> Mailing list: >> https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs> >> Post to : [email protected] >> Unsubscribe : >> https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs> >> More help : https://help.launchpad.net/ListHelp >> > -- -- Jason P. Pickering email: [email protected] tel:+260968395190 _______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
