Can we bump organisationunitid from int to bigint?

Support - Assistance technique
1

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

2

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

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

3

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:

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

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

--
Cheers,
Knut Staring

4

Sorry, the last sentence should say " full precision GEOMETRIES".

···

On Fri, May 21, 2010 at 11:00 AM, Knut Staring <knutst@gmail.com> 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:
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@gmail.com> 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@gmail.com> 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
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

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

--
Cheers,
Knut Staring

--
Cheers,
Knut Staring

5

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

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

--
Cheers,
Knut Staring

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

6

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

Post to : dhis2-devs@lists.launchpad.net

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

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

–

–

Jason P. Pickering

email: jason.p.pickering@gmail.com

tel:+260968395190

–

Cheers,

Knut Staring

–

Jason P. Pickering

email: jason.p.pickering@gmail.com

tel:+260968395190

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

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

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

7

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@gmail.com> 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@gmail.com> 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@gmail.com> 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:
> 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@gmail.com> 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@gmail.com> 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&lt;https://launchpad.net/~dhis2-devs&gt;
>>> Post to : dhis2-devs@lists.launchpad.net
>>> Unsubscribe :
>>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>>> More help : https://help.launchpad.net/ListHelp
>>>
>>
>>
>> --
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@gmail.com
>> tel:+260968395190
>>
>
>
>
> --
> Cheers,
> Knut Staring
>

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

_______________________________________________
Mailing list:
https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe :
https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
More help : https://help.launchpad.net/ListHelp

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

8

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@who.int|Tel: +41 788216897

Website: www.healthmetricsnetwork.org

Better Information. Better Decisions. Better Health.

···

On 21 May 2010 11:42, Jason Pickering jason.p.pickering@gmail.com 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@gmail.com 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@gmail.com 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@gmail.com 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@gmail.com 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@gmail.com 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@lists.launchpad.net

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

tel:+260968395190

–

Cheers,

Knut Staring

–

–

Jason P. Pickering

email: jason.p.pickering@gmail.com

tel:+260968395190

Mailing list:

https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>
Post to : dhis2-devs@lists.launchpad.net

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

tel:+260968395190

9

Lars, I seem to remember there was another student group that was
going to have a look at solving the "population from shape" problem
again last year - did they get anywhere?

I do think it is a good idea to offer (super) users the ability to
upload shapefiles. We still have the problem of generalization, which
users sort of can do with Mapshaper before uploading, but it would be
interesting to know if we can use GeoKettle or Talend to offer this
service.

For us here, it makes most sense to go directly from PostGIS and not
via shapefiles, and we can populate Postgres or Mysql directly from
FME, but its fine to do this into temporary staging tables and have
another query inside the db as you suggest.

···

On Fri, May 21, 2010 at 11:42 AM, Jason Pickering <jason.p.pickering@gmail.com> 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@gmail.com> 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@gmail.com> 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@gmail.com> 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:
> 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@gmail.com> 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@gmail.com> 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&lt;https://launchpad.net/~dhis2-devs&gt;
>>> Post to : dhis2-devs@lists.launchpad.net
>>> Unsubscribe :
>>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>>> More help : https://help.launchpad.net/ListHelp
>>>
>>
>>
>> --
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@gmail.com
>> tel:+260968395190
>>
>
>
>
> --
> Cheers,
> Knut Staring
>

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

_______________________________________________
Mailing list:
https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe :
https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
More help : https://help.launchpad.net/ListHelp

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

--
Cheers,
Knut Staring

10

You are quite right that we need to coordinate this with bulk orgunit
import - to some extent the hope is that if the admin boundaries we
have access to are reasonably current, the map import would obviate
the need for bulk orgunit import.

It does seem best to route everything through the import functionality
more than the ad-hoc student code (if we have it), though perhaps we
can use good pieces of it. However, because of the particular issues
of simplification and conversion to geojson, I'm not sure it makes
sense to go via Excel. What is the current status, Bob? Does it make
sense to try and splice these processes now, or should we defer this?

Knut

···

On Fri, May 21, 2010 at 11:48 AM, Ola Hodne Titlestad <olatitle@gmail.com> wrote:

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@who.int|Tel: +41 788216897
Website: www.healthmetricsnetwork.org

Better Information. Better Decisions. Better Health.

On 21 May 2010 11:42, Jason Pickering <jason.p.pickering@gmail.com> 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@gmail.com> 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@gmail.com> >> > 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@gmail.com> 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:
>> >
>> > 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@gmail.com> 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@gmail.com> 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&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> Post to : dhis2-devs@lists.launchpad.net
>> >>> Unsubscribe :
>> >>>
>> >>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> More help : https://help.launchpad.net/ListHelp
>> >>>
>> >>
>> >>
>> >> --
>> >> --
>> >> Jason P. Pickering
>> >> email: jason.p.pickering@gmail.com
>> >> tel:+260968395190
>> >>
>> >
>> >
>> >
>> > --
>> > Cheers,
>> > Knut Staring
>> >
>>
>>
>> --
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@gmail.com
>> tel:+260968395190
>>
>> _______________________________________________
>> Mailing list:
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> Post to : dhis2-devs@lists.launchpad.net
>> Unsubscribe :
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> More help : https://help.launchpad.net/ListHelp
>>
>

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

--
Cheers,
Knut Staring

11

I would think that the recommended procedure would be to transform the
source data to DXF. That way, we do not need to worry about the
internals of the database. Is this not an option Knut? Using Talend or
(Geo)Kettle for this purpose would certainly seem possible and
relatively straightforward.

I would think that this would be a better staging mechanism than a
temporary table followed by direct insertion into the DB? It is more
work, but it would be worth it, if this process would be documented.

···

On 5/21/10, Knut Staring <knutst@gmail.com> wrote:

You are quite right that we need to coordinate this with bulk orgunit
import - to some extent the hope is that if the admin boundaries we
have access to are reasonably current, the map import would obviate
the need for bulk orgunit import.

It does seem best to route everything through the import functionality
more than the ad-hoc student code (if we have it), though perhaps we
can use good pieces of it. However, because of the particular issues
of simplification and conversion to geojson, I'm not sure it makes
sense to go via Excel. What is the current status, Bob? Does it make
sense to try and splice these processes now, or should we defer this?

Knut

On Fri, May 21, 2010 at 11:48 AM, Ola Hodne Titlestad > <olatitle@gmail.com> wrote:

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@who.int|Tel: +41 788216897
Website: www.healthmetricsnetwork.org

Better Information. Better Decisions. Better Health.

On 21 May 2010 11:42, Jason Pickering <jason.p.pickering@gmail.com> 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@gmail.com> 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@gmail.com> >>> > 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@gmail.com> 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:
>> >
>> > 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@gmail.com> 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@gmail.com> 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&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> Post to : dhis2-devs@lists.launchpad.net
>> >>> Unsubscribe :
>> >>>
>> >>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> More help : https://help.launchpad.net/ListHelp
>> >>>
>> >>
>> >>
>> >> --
>> >> --
>> >> Jason P. Pickering
>> >> email: jason.p.pickering@gmail.com
>> >> tel:+260968395190
>> >>
>> >
>> >
>> >
>> > --
>> > Cheers,
>> > Knut Staring
>> >
>>
>>
>> --
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@gmail.com
>> tel:+260968395190
>>
>> _______________________________________________
>> Mailing list:
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> Post to : dhis2-devs@lists.launchpad.net
>> Unsubscribe :
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> More help : https://help.launchpad.net/ListHelp
>>
>

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

--
Cheers,
Knut Staring

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

12

I agree, this is probably the best approach - especially if the ETL
tool can handle both the generalization, geojsonificaton and dress it
up as DXF (which I'm sure it can)

camp2camp's SpatialDataIntegrator on top of Talend seems promising:
http://www.talendforge.org/wiki/doku.php?id=sdi:version1_3_0

···

On Fri, May 21, 2010 at 12:16 PM, Jason Pickering <jason.p.pickering@gmail.com> wrote:

I would think that the recommended procedure would be to transform the
source data to DXF. That way, we do not need to worry about the
internals of the database. Is this not an option Knut? Using Talend or
(Geo)Kettle for this purpose would certainly seem possible and
relatively straightforward.

I would think that this would be a better staging mechanism than a
temporary table followed by direct insertion into the DB? It is more
work, but it would be worth it, if this process would be documented.

On 5/21/10, Knut Staring <knutst@gmail.com> wrote:

You are quite right that we need to coordinate this with bulk orgunit
import - to some extent the hope is that if the admin boundaries we
have access to are reasonably current, the map import would obviate
the need for bulk orgunit import.

It does seem best to route everything through the import functionality
more than the ad-hoc student code (if we have it), though perhaps we
can use good pieces of it. However, because of the particular issues
of simplification and conversion to geojson, I'm not sure it makes
sense to go via Excel. What is the current status, Bob? Does it make
sense to try and splice these processes now, or should we defer this?

Knut

On Fri, May 21, 2010 at 11:48 AM, Ola Hodne Titlestad >> <olatitle@gmail.com> wrote:

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@who.int|Tel: +41 788216897
Website: www.healthmetricsnetwork.org

Better Information. Better Decisions. Better Health.

On 21 May 2010 11:42, Jason Pickering <jason.p.pickering@gmail.com> 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@gmail.com> 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@gmail.com> >>>> > 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@gmail.com> 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:
>> >
>> > 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@gmail.com> 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@gmail.com> 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&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> Post to : dhis2-devs@lists.launchpad.net
>> >>> Unsubscribe :
>> >>>
>> >>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> More help : https://help.launchpad.net/ListHelp
>> >>>
>> >>
>> >>
>> >> --
>> >> --
>> >> Jason P. Pickering
>> >> email: jason.p.pickering@gmail.com
>> >> tel:+260968395190
>> >>
>> >
>> >
>> >
>> > --
>> > Cheers,
>> > Knut Staring
>> >
>>
>>
>> --
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@gmail.com
>> tel:+260968395190
>>
>> _______________________________________________
>> Mailing list:
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> Post to : dhis2-devs@lists.launchpad.net
>> Unsubscribe :
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> More help : https://help.launchpad.net/ListHelp
>>
>

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

--
Cheers,
Knut Staring

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

--
Cheers,
Knut Staring

13

I would think this would be the best practice for this situation.

There could be others, which would require some sort of bi-directional
syncronization, that is automated. I guess this is the big problem
with DXF imports, as it requires a human to intervene. However, the
ETL would have to be pretty smart in order to take into account
everything that the database requires, especially because there is no
business logic in the DB itself. It would be good to try both ways
and DOCUMENT the process!

···

On 5/21/10, Knut Staring <knutst@gmail.com> wrote:

I agree, this is probably the best approach - especially if the ETL
tool can handle both the generalization, geojsonificaton and dress it
up as DXF (which I'm sure it can)

camp2camp's SpatialDataIntegrator on top of Talend seems promising:
http://www.talendforge.org/wiki/doku.php?id=sdi:version1_3_0

On Fri, May 21, 2010 at 12:16 PM, Jason Pickering > <jason.p.pickering@gmail.com> wrote:

I would think that the recommended procedure would be to transform the
source data to DXF. That way, we do not need to worry about the
internals of the database. Is this not an option Knut? Using Talend or
(Geo)Kettle for this purpose would certainly seem possible and
relatively straightforward.

I would think that this would be a better staging mechanism than a
temporary table followed by direct insertion into the DB? It is more
work, but it would be worth it, if this process would be documented.

On 5/21/10, Knut Staring <knutst@gmail.com> wrote:

You are quite right that we need to coordinate this with bulk orgunit
import - to some extent the hope is that if the admin boundaries we
have access to are reasonably current, the map import would obviate
the need for bulk orgunit import.

It does seem best to route everything through the import functionality
more than the ad-hoc student code (if we have it), though perhaps we
can use good pieces of it. However, because of the particular issues
of simplification and conversion to geojson, I'm not sure it makes
sense to go via Excel. What is the current status, Bob? Does it make
sense to try and splice these processes now, or should we defer this?

Knut

On Fri, May 21, 2010 at 11:48 AM, Ola Hodne Titlestad >>> <olatitle@gmail.com> wrote:

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@who.int|Tel: +41 788216897
Website: www.healthmetricsnetwork.org

Better Information. Better Decisions. Better Health.

On 21 May 2010 11:42, Jason Pickering <jason.p.pickering@gmail.com> >>>> 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@gmail.com> 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@gmail.com> >>>>> > 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@gmail.com> 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:
>> >
>> > 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@gmail.com> 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@gmail.com> 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&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> Post to : dhis2-devs@lists.launchpad.net
>> >>> Unsubscribe :
>> >>>
>> >>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> More help : https://help.launchpad.net/ListHelp
>> >>>
>> >>
>> >>
>> >> --
>> >> --
>> >> Jason P. Pickering
>> >> email: jason.p.pickering@gmail.com
>> >> tel:+260968395190
>> >>
>> >
>> >
>> >
>> > --
>> > Cheers,
>> > Knut Staring
>> >
>>
>>
>> --
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@gmail.com
>> tel:+260968395190
>>
>> _______________________________________________
>> Mailing list:
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> Post to : dhis2-devs@lists.launchpad.net
>> Unsubscribe :
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> More help : https://help.launchpad.net/ListHelp
>>
>

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

--
Cheers,
Knut Staring

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

--
Cheers,
Knut Staring

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

14

Generating an orgunit hierarchy in dxf is relatively straightforward.
As long as you have some kind of parent id reference to work with.

I am not sure of the algorithm to use for the simplification part but
I guess it must be pretty standard? You are just reducing the number
of points on a polygon right? My rusty maths could probably figure
out an algorithm but this has got to already exist. Does anyone have
any pointers? If there is some reasonable java code to do this, then
I would create a java class to do it somewhere in dhis and make that
class available as a an extension to the xalan xslt processor. That
sounds complicated but its not really. I did something similar with
calculating dates off excel's (dodgy) date representation.

Can you dump your data source (or some of it) into some kind of xml
and I can take a quick look at it.

Regarding importing orgunits from excel templates I have some
unfinished work which has been lying on the backburner while I have
been negotiating message formats with openmrs/ihris folk this week.
Basically still need to tidy up importing from zip containers. I hope
to get back to that soon. But that should not prevent this scenario
from working. Regarding ids I think its right to avoid getting
complicated with the database stuff. In the dxf we can just number
them 1,2,3,4.. etc. The DHIS convertor will worry about what the
actual database ids are.

Regards
Bob

···

On 21 May 2010 11:09, Knut Staring <knutst@gmail.com> wrote:

You are quite right that we need to coordinate this with bulk orgunit
import - to some extent the hope is that if the admin boundaries we
have access to are reasonably current, the map import would obviate
the need for bulk orgunit import.

It does seem best to route everything through the import functionality
more than the ad-hoc student code (if we have it), though perhaps we
can use good pieces of it. However, because of the particular issues
of simplification and conversion to geojson, I'm not sure it makes
sense to go via Excel. What is the current status, Bob? Does it make
sense to try and splice these processes now, or should we defer this?

Knut

On Fri, May 21, 2010 at 11:48 AM, Ola Hodne Titlestad > <olatitle@gmail.com> wrote:

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@who.int|Tel: +41 788216897
Website: www.healthmetricsnetwork.org

Better Information. Better Decisions. Better Health.

On 21 May 2010 11:42, Jason Pickering <jason.p.pickering@gmail.com> 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@gmail.com> 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@gmail.com> >>> > 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@gmail.com> 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:
>> >
>> > 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@gmail.com> 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@gmail.com> 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&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> Post to : dhis2-devs@lists.launchpad.net
>> >>> Unsubscribe :
>> >>>
>> >>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> More help : https://help.launchpad.net/ListHelp
>> >>>
>> >>
>> >>
>> >> --
>> >> --
>> >> Jason P. Pickering
>> >> email: jason.p.pickering@gmail.com
>> >> tel:+260968395190
>> >>
>> >
>> >
>> >
>> > --
>> > Cheers,
>> > Knut Staring
>> >
>>
>>
>> --
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@gmail.com
>> tel:+260968395190
>>
>> _______________________________________________
>> Mailing list:
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> Post to : dhis2-devs@lists.launchpad.net
>> Unsubscribe :
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> More help : https://help.launchpad.net/ListHelp
>>
>

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

--
Cheers,
Knut Staring

_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

15

Generating an orgunit hierarchy in dxf is relatively straightforward.
As long as you have some kind of parent id reference to work with.

I am not sure of the algorithm to use for the simplification part but
I guess it must be pretty standard? You are just reducing the number
of points on a polygon right? My rusty maths could probably figure
out an algorithm but this has got to already exist. Does anyone have
any pointers?

Yes - there is Douglas-Peucker, Visvalingam and Special Visvalingam,
you can test them at the following link. But some of these create
cracks between polygons.
http://mapshaper.com/test/demo.html

If there is some reasonable java code to do this, then
I would create a java class to do it somewhere in dhis and make that
class available as a an extension to the xalan xslt processor. That
sounds complicated but its not really. I did something similar with
calculating dates off excel's (dodgy) date representation.

Can you dump your data source (or some of it) into some kind of xml
and I can take a quick look at it.

Will send you something soon, just let me get to know Talend SDI a bit first.

Regarding importing orgunits from excel templates I have some
unfinished work which has been lying on the backburner while I have
been negotiating message formats with openmrs/ihris folk this week.
Basically still need to tidy up importing from zip containers. I hope
to get back to that soon. But that should not prevent this scenario
from working. Regarding ids I think its right to avoid getting
complicated with the database stuff. In the dxf we can just number
them 1,2,3,4.. etc. The DHIS convertor will worry about what the
actual database ids are.

True. We may want to keep the 21 character LVLIDs in the CODE field.

Knut

···

On Fri, May 21, 2010 at 2:25 PM, Bob Jolliffe <bobjolliffe@gmail.com> wrote:

Regards
Bob

On 21 May 2010 11:09, Knut Staring <knutst@gmail.com> wrote:

You are quite right that we need to coordinate this with bulk orgunit
import - to some extent the hope is that if the admin boundaries we
have access to are reasonably current, the map import would obviate
the need for bulk orgunit import.

It does seem best to route everything through the import functionality
more than the ad-hoc student code (if we have it), though perhaps we
can use good pieces of it. However, because of the particular issues
of simplification and conversion to geojson, I'm not sure it makes
sense to go via Excel. What is the current status, Bob? Does it make
sense to try and splice these processes now, or should we defer this?

Knut

On Fri, May 21, 2010 at 11:48 AM, Ola Hodne Titlestad >> <olatitle@gmail.com> wrote:

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@who.int|Tel: +41 788216897
Website: www.healthmetricsnetwork.org

Better Information. Better Decisions. Better Health.

On 21 May 2010 11:42, Jason Pickering <jason.p.pickering@gmail.com> 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@gmail.com> 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@gmail.com> >>>> > 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@gmail.com> 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:
>> >
>> > 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@gmail.com> 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@gmail.com> 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&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> Post to : dhis2-devs@lists.launchpad.net
>> >>> Unsubscribe :
>> >>>
>> >>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> More help : https://help.launchpad.net/ListHelp
>> >>>
>> >>
>> >>
>> >> --
>> >> --
>> >> Jason P. Pickering
>> >> email: jason.p.pickering@gmail.com
>> >> tel:+260968395190
>> >>
>> >
>> >
>> >
>> > --
>> > Cheers,
>> > Knut Staring
>> >
>>
>>
>> --
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@gmail.com
>> tel:+260968395190
>>
>> _______________________________________________
>> Mailing list:
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> Post to : dhis2-devs@lists.launchpad.net
>> Unsubscribe :
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> More help : https://help.launchpad.net/ListHelp
>>
>

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

--
Cheers,
Knut Staring

_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

--
Cheers,
Knut Staring

16

Generating an orgunit hierarchy in dxf is relatively straightforward.
As long as you have some kind of parent id reference to work with.

I am not sure of the algorithm to use for the simplification part but
I guess it must be pretty standard? You are just reducing the number
of points on a polygon right? My rusty maths could probably figure
out an algorithm but this has got to already exist. Does anyone have
any pointers?

Google seems to have knowledge of some such classes ... someone with
greater GIS knowledge would have to assess what is required.
Something like this:
http://edndoc.esri.com/arcobjects/9.0/Samples/Geodatabase/Creating_and_Converting_Data/Simplify_feature_geometry_for_a_shapefile/Simplify_feature_geometry_for_a_shapfile.htm

···

On 21 May 2010 13:25, Bob Jolliffe <bobjolliffe@gmail.com> wrote:

If there is some reasonable java code to do this, then
I would create a java class to do it somewhere in dhis and make that
class available as a an extension to the xalan xslt processor. That
sounds complicated but its not really. I did something similar with
calculating dates off excel's (dodgy) date representation.

Can you dump your data source (or some of it) into some kind of xml
and I can take a quick look at it.

Regarding importing orgunits from excel templates I have some
unfinished work which has been lying on the backburner while I have
been negotiating message formats with openmrs/ihris folk this week.
Basically still need to tidy up importing from zip containers. I hope
to get back to that soon. But that should not prevent this scenario
from working. Regarding ids I think its right to avoid getting
complicated with the database stuff. In the dxf we can just number
them 1,2,3,4.. etc. The DHIS convertor will worry about what the
actual database ids are.

Regards
Bob

On 21 May 2010 11:09, Knut Staring <knutst@gmail.com> wrote:

You are quite right that we need to coordinate this with bulk orgunit
import - to some extent the hope is that if the admin boundaries we
have access to are reasonably current, the map import would obviate
the need for bulk orgunit import.

It does seem best to route everything through the import functionality
more than the ad-hoc student code (if we have it), though perhaps we
can use good pieces of it. However, because of the particular issues
of simplification and conversion to geojson, I'm not sure it makes
sense to go via Excel. What is the current status, Bob? Does it make
sense to try and splice these processes now, or should we defer this?

Knut

On Fri, May 21, 2010 at 11:48 AM, Ola Hodne Titlestad >> <olatitle@gmail.com> wrote:

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@who.int|Tel: +41 788216897
Website: www.healthmetricsnetwork.org

Better Information. Better Decisions. Better Health.

On 21 May 2010 11:42, Jason Pickering <jason.p.pickering@gmail.com> 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@gmail.com> 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@gmail.com> >>>> > 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@gmail.com> 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:
>> >
>> > 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@gmail.com> 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@gmail.com> 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&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> Post to : dhis2-devs@lists.launchpad.net
>> >>> Unsubscribe :
>> >>>
>> >>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> More help : https://help.launchpad.net/ListHelp
>> >>>
>> >>
>> >>
>> >> --
>> >> --
>> >> Jason P. Pickering
>> >> email: jason.p.pickering@gmail.com
>> >> tel:+260968395190
>> >>
>> >
>> >
>> >
>> > --
>> > Cheers,
>> > Knut Staring
>> >
>>
>>
>> --
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@gmail.com
>> tel:+260968395190
>>
>> _______________________________________________
>> Mailing list:
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> Post to : dhis2-devs@lists.launchpad.net
>> Unsubscribe :
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> More help : https://help.launchpad.net/ListHelp
>>
>

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

--
Cheers,
Knut Staring

_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

17

Generating an orgunit hierarchy in dxf is relatively straightforward.
As long as you have some kind of parent id reference to work with.

I am not sure of the algorithm to use for the simplification part but
I guess it must be pretty standard? You are just reducing the number
of points on a polygon right? My rusty maths could probably figure
out an algorithm but this has got to already exist. Does anyone have
any pointers?

Yes - there is Douglas-Peucker, Visvalingam and Special Visvalingam,
you can test them at the following link. But some of these create
cracks between polygons.
http://mapshaper.com/test/demo.html

Ok. This an online shaper. Can't see any code to download :slight_smile: I
guess the three names above are the algorithms? Any idea what the
cracks are about? Faulty algorithm, faulty implementation of
algorithm or faulty shapefiles to start with. Might be the shapefiles
need to be pre-processed (cleaned) before transforming.

···

On 21 May 2010 13:30, Knut Staring <knutst@gmail.com> wrote:

On Fri, May 21, 2010 at 2:25 PM, Bob Jolliffe <bobjolliffe@gmail.com> wrote:

If there is some reasonable java code to do this, then
I would create a java class to do it somewhere in dhis and make that
class available as a an extension to the xalan xslt processor. That
sounds complicated but its not really. I did something similar with
calculating dates off excel's (dodgy) date representation.

Can you dump your data source (or some of it) into some kind of xml
and I can take a quick look at it.

Will send you something soon, just let me get to know Talend SDI a bit first.

Regarding importing orgunits from excel templates I have some
unfinished work which has been lying on the backburner while I have
been negotiating message formats with openmrs/ihris folk this week.
Basically still need to tidy up importing from zip containers. I hope
to get back to that soon. But that should not prevent this scenario
from working. Regarding ids I think its right to avoid getting
complicated with the database stuff. In the dxf we can just number
them 1,2,3,4.. etc. The DHIS convertor will worry about what the
actual database ids are.

True. We may want to keep the 21 character LVLIDs in the CODE field.

Knut

Regards
Bob

On 21 May 2010 11:09, Knut Staring <knutst@gmail.com> wrote:

You are quite right that we need to coordinate this with bulk orgunit
import - to some extent the hope is that if the admin boundaries we
have access to are reasonably current, the map import would obviate
the need for bulk orgunit import.

It does seem best to route everything through the import functionality
more than the ad-hoc student code (if we have it), though perhaps we
can use good pieces of it. However, because of the particular issues
of simplification and conversion to geojson, I'm not sure it makes
sense to go via Excel. What is the current status, Bob? Does it make
sense to try and splice these processes now, or should we defer this?

Knut

On Fri, May 21, 2010 at 11:48 AM, Ola Hodne Titlestad >>> <olatitle@gmail.com> wrote:

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@who.int|Tel: +41 788216897
Website: www.healthmetricsnetwork.org

Better Information. Better Decisions. Better Health.

On 21 May 2010 11:42, Jason Pickering <jason.p.pickering@gmail.com> 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@gmail.com> 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@gmail.com> >>>>> > 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@gmail.com> 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:
>> >
>> > 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@gmail.com> 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@gmail.com> 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&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> Post to : dhis2-devs@lists.launchpad.net
>> >>> Unsubscribe :
>> >>>
>> >>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> More help : https://help.launchpad.net/ListHelp
>> >>>
>> >>
>> >>
>> >> --
>> >> --
>> >> Jason P. Pickering
>> >> email: jason.p.pickering@gmail.com
>> >> tel:+260968395190
>> >>
>> >
>> >
>> >
>> > --
>> > Cheers,
>> > Knut Staring
>> >
>>
>>
>> --
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@gmail.com
>> tel:+260968395190
>>
>> _______________________________________________
>> Mailing list:
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> Post to : dhis2-devs@lists.launchpad.net
>> Unsubscribe :
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> More help : https://help.launchpad.net/ListHelp
>>
>

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

--
Cheers,
Knut Staring

_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

--
Cheers,
Knut Staring

18

There are open source implementations, embedded in tools like OpenJump
and TalendSDI
http://www.vividsolutions.com/jts/javadoc/com/vividsolutions/jts/simplify/TopologyPreservingSimplifier.html

Given the lincenses line up we could probably integrate parts (rather
than the whole Talend SDI)

···

On Fri, May 21, 2010 at 2:30 PM, Bob Jolliffe <bobjolliffe@gmail.com> wrote:

On 21 May 2010 13:25, Bob Jolliffe <bobjolliffe@gmail.com> wrote:

Generating an orgunit hierarchy in dxf is relatively straightforward.
As long as you have some kind of parent id reference to work with.

I am not sure of the algorithm to use for the simplification part but
I guess it must be pretty standard? You are just reducing the number
of points on a polygon right? My rusty maths could probably figure
out an algorithm but this has got to already exist. Does anyone have
any pointers?

Google seems to have knowledge of some such classes ... someone with
greater GIS knowledge would have to assess what is required.
Something like this:
Esri | There has been a problem

If there is some reasonable java code to do this, then
I would create a java class to do it somewhere in dhis and make that
class available as a an extension to the xalan xslt processor. That
sounds complicated but its not really. I did something similar with
calculating dates off excel's (dodgy) date representation.

Can you dump your data source (or some of it) into some kind of xml
and I can take a quick look at it.

Regarding importing orgunits from excel templates I have some
unfinished work which has been lying on the backburner while I have
been negotiating message formats with openmrs/ihris folk this week.
Basically still need to tidy up importing from zip containers. I hope
to get back to that soon. But that should not prevent this scenario
from working. Regarding ids I think its right to avoid getting
complicated with the database stuff. In the dxf we can just number
them 1,2,3,4.. etc. The DHIS convertor will worry about what the
actual database ids are.

Regards
Bob

On 21 May 2010 11:09, Knut Staring <knutst@gmail.com> wrote:

You are quite right that we need to coordinate this with bulk orgunit
import - to some extent the hope is that if the admin boundaries we
have access to are reasonably current, the map import would obviate
the need for bulk orgunit import.

It does seem best to route everything through the import functionality
more than the ad-hoc student code (if we have it), though perhaps we
can use good pieces of it. However, because of the particular issues
of simplification and conversion to geojson, I'm not sure it makes
sense to go via Excel. What is the current status, Bob? Does it make
sense to try and splice these processes now, or should we defer this?

Knut

On Fri, May 21, 2010 at 11:48 AM, Ola Hodne Titlestad >>> <olatitle@gmail.com> wrote:

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@who.int|Tel: +41 788216897
Website: www.healthmetricsnetwork.org

Better Information. Better Decisions. Better Health.

On 21 May 2010 11:42, Jason Pickering <jason.p.pickering@gmail.com> 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@gmail.com> 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@gmail.com> >>>>> > 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@gmail.com> 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:
>> >
>> > 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@gmail.com> 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@gmail.com> 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&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> Post to : dhis2-devs@lists.launchpad.net
>> >>> Unsubscribe :
>> >>>
>> >>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> More help : https://help.launchpad.net/ListHelp
>> >>>
>> >>
>> >>
>> >> --
>> >> --
>> >> Jason P. Pickering
>> >> email: jason.p.pickering@gmail.com
>> >> tel:+260968395190
>> >>
>> >
>> >
>> >
>> > --
>> > Cheers,
>> > Knut Staring
>> >
>>
>>
>> --
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@gmail.com
>> tel:+260968395190
>>
>> _______________________________________________
>> Mailing list:
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> Post to : dhis2-devs@lists.launchpad.net
>> Unsubscribe :
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> More help : https://help.launchpad.net/ListHelp
>>
>

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

--
Cheers,
Knut Staring

_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

--
Cheers,
Knut Staring

19

Generating an orgunit hierarchy in dxf is relatively straightforward.
As long as you have some kind of parent id reference to work with.

I am not sure of the algorithm to use for the simplification part but
I guess it must be pretty standard? You are just reducing the number
of points on a polygon right? My rusty maths could probably figure
out an algorithm but this has got to already exist. Does anyone have
any pointers?

Yes - there is Douglas-Peucker, Visvalingam and Special Visvalingam,
you can test them at the following link. But some of these create
cracks between polygons.
http://mapshaper.com/test/demo.html

Ok. This an online shaper. Can't see any code to download :slight_smile: I
guess the three names above are the algorithms? Any idea what the
cracks are about? Faulty algorithm, faulty implementation of
algorithm or faulty shapefiles to start with. Might be the shapefiles
need to be pre-processed (cleaned) before transforming.

Not quite sure, maybe it is linked to polygons not really sharing
borders, just overlapping, and then Switzerland's border with Germany
gets simplified differently from Germany's border with Switzerland.
Though I could be completely wrong - and it may also depend a lot on
the parameters one chooses. But it seems that the commercial tools
like FME and ArcToolbox may handle it better, or maybe the data was
just clearner, or that the tools do some automatic precleaning, as
opposed to the default versions of the algorithms as implemented in
the online Mapshaper and also in PostGIS (see below link) don't quite
preserve common borders between polygons.

If there is some reasonable java code to do this, then
I would create a java class to do it somewhere in dhis and make that
class available as a an extension to the xalan xslt processor. That
sounds complicated but its not really. I did something similar with
calculating dates off excel's (dodgy) date representation.

I think this is interesting, as it would potentially allow people to
upload their own shapefiles without worrying about simplification and
conversion to GeoJSON. What will also be needed is a matching
algorithm - though Jan Henrik has already implemented identical
matching and manual visual matching in the client.

By the way, in addition to simplification, there is "smoothing". Most
of this is available as part of the Java Topology Suite:
http://www.vividsolutions.com/jts/jtshome.htm
http://lists.refractions.net/pipermail/jump-users/2005-July/002564.html

Knut

···

On Fri, May 21, 2010 at 2:35 PM, Bob Jolliffe <bobjolliffe@gmail.com> wrote:

On 21 May 2010 13:30, Knut Staring <knutst@gmail.com> wrote:

On Fri, May 21, 2010 at 2:25 PM, Bob Jolliffe <bobjolliffe@gmail.com> wrote:

Can you dump your data source (or some of it) into some kind of xml
and I can take a quick look at it.

Will send you something soon, just let me get to know Talend SDI a bit first.

Regarding importing orgunits from excel templates I have some
unfinished work which has been lying on the backburner while I have
been negotiating message formats with openmrs/ihris folk this week.
Basically still need to tidy up importing from zip containers. I hope
to get back to that soon. But that should not prevent this scenario
from working. Regarding ids I think its right to avoid getting
complicated with the database stuff. In the dxf we can just number
them 1,2,3,4.. etc. The DHIS convertor will worry about what the
actual database ids are.

True. We may want to keep the 21 character LVLIDs in the CODE field.

Knut

Regards
Bob

On 21 May 2010 11:09, Knut Staring <knutst@gmail.com> wrote:

You are quite right that we need to coordinate this with bulk orgunit
import - to some extent the hope is that if the admin boundaries we
have access to are reasonably current, the map import would obviate
the need for bulk orgunit import.

It does seem best to route everything through the import functionality
more than the ad-hoc student code (if we have it), though perhaps we
can use good pieces of it. However, because of the particular issues
of simplification and conversion to geojson, I'm not sure it makes
sense to go via Excel. What is the current status, Bob? Does it make
sense to try and splice these processes now, or should we defer this?

Knut

On Fri, May 21, 2010 at 11:48 AM, Ola Hodne Titlestad >>>> <olatitle@gmail.com> wrote:

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@who.int|Tel: +41 788216897
Website: www.healthmetricsnetwork.org

Better Information. Better Decisions. Better Health.

On 21 May 2010 11:42, Jason Pickering <jason.p.pickering@gmail.com> 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@gmail.com> 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@gmail.com> >>>>>> > 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@gmail.com> 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:
>> >
>> > 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@gmail.com> 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@gmail.com> 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&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> Post to : dhis2-devs@lists.launchpad.net
>> >>> Unsubscribe :
>> >>>
>> >>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> More help : https://help.launchpad.net/ListHelp
>> >>>
>> >>
>> >>
>> >> --
>> >> --
>> >> Jason P. Pickering
>> >> email: jason.p.pickering@gmail.com
>> >> tel:+260968395190
>> >>
>> >
>> >
>> >
>> > --
>> > Cheers,
>> > Knut Staring
>> >
>>
>>
>> --
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@gmail.com
>> tel:+260968395190
>>
>> _______________________________________________
>> Mailing list:
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> Post to : dhis2-devs@lists.launchpad.net
>> Unsubscribe :
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> More help : https://help.launchpad.net/ListHelp
>>
>

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

--
Cheers,
Knut Staring

_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

--
Cheers,
Knut Staring

--
Cheers,
Knut Staring

20

Generating an orgunit hierarchy in dxf is relatively straightforward.
As long as you have some kind of parent id reference to work with.

I am not sure of the algorithm to use for the simplification part but
I guess it must be pretty standard? You are just reducing the number
of points on a polygon right? My rusty maths could probably figure
out an algorithm but this has got to already exist. Does anyone have
any pointers?

Yes - there is Douglas-Peucker, Visvalingam and Special Visvalingam,
you can test them at the following link. But some of these create
cracks between polygons.
http://mapshaper.com/test/demo.html

Ok. This an online shaper. Can't see any code to download :slight_smile: I
guess the three names above are the algorithms? Any idea what the
cracks are about? Faulty algorithm, faulty implementation of
algorithm or faulty shapefiles to start with. Might be the shapefiles
need to be pre-processed (cleaned) before transforming.

Not quite sure, maybe it is linked to polygons not really sharing
borders, just overlapping, and then Switzerland's border with Germany
gets simplified differently from Germany's border with Switzerland.
Though I could be completely wrong - and it may also depend a lot on
the parameters one chooses. But it seems that the commercial tools
like FME and ArcToolbox may handle it better, or maybe the data was
just clearner, or that the tools do some automatic precleaning, as
opposed to the default versions of the algorithms as implemented in
the online Mapshaper and also in PostGIS (see below link) don't quite
preserve common borders between polygons.

PostGIS Simplify

If there is some reasonable java code to do this, then
I would create a java class to do it somewhere in dhis and make that
class available as a an extension to the xalan xslt processor. That
sounds complicated but its not really. I did something similar with
calculating dates off excel's (dodgy) date representation.

I think this is interesting, as it would potentially allow people to
upload their own shapefiles without worrying about simplification and
conversion to GeoJSON. What will also be needed is a matching
algorithm - though Jan Henrik has already implemented identical
matching and manual visual matching in the client.

By the way, in addition to simplification, there is "smoothing". Most
of this is available as part of the Java Topology Suite:
http://www.vividsolutions.com/jts/jtshome.htm
http://lists.refractions.net/pipermail/jump-users/2005-July/002564.html

Yes well if someone else can provide the simplification/smoothing or
what have you I can make sure its available to the input transform.
Other than that I'm a bit out of my depth with these processes at the
moment.

Cheers
Bob

···

On 21 May 2010 13:58, Knut Staring <knutst@gmail.com> wrote:

On Fri, May 21, 2010 at 2:35 PM, Bob Jolliffe <bobjolliffe@gmail.com> wrote:

On 21 May 2010 13:30, Knut Staring <knutst@gmail.com> wrote:

On Fri, May 21, 2010 at 2:25 PM, Bob Jolliffe <bobjolliffe@gmail.com> wrote:

Knut

Can you dump your data source (or some of it) into some kind of xml
and I can take a quick look at it.

Will send you something soon, just let me get to know Talend SDI a bit first.

Regarding importing orgunits from excel templates I have some
unfinished work which has been lying on the backburner while I have
been negotiating message formats with openmrs/ihris folk this week.
Basically still need to tidy up importing from zip containers. I hope
to get back to that soon. But that should not prevent this scenario
from working. Regarding ids I think its right to avoid getting
complicated with the database stuff. In the dxf we can just number
them 1,2,3,4.. etc. The DHIS convertor will worry about what the
actual database ids are.

True. We may want to keep the 21 character LVLIDs in the CODE field.

Knut

Regards
Bob

On 21 May 2010 11:09, Knut Staring <knutst@gmail.com> wrote:

You are quite right that we need to coordinate this with bulk orgunit
import - to some extent the hope is that if the admin boundaries we
have access to are reasonably current, the map import would obviate
the need for bulk orgunit import.

It does seem best to route everything through the import functionality
more than the ad-hoc student code (if we have it), though perhaps we
can use good pieces of it. However, because of the particular issues
of simplification and conversion to geojson, I'm not sure it makes
sense to go via Excel. What is the current status, Bob? Does it make
sense to try and splice these processes now, or should we defer this?

Knut

On Fri, May 21, 2010 at 11:48 AM, Ola Hodne Titlestad >>>>> <olatitle@gmail.com> wrote:

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@who.int|Tel: +41 788216897
Website: www.healthmetricsnetwork.org

Better Information. Better Decisions. Better Health.

On 21 May 2010 11:42, Jason Pickering <jason.p.pickering@gmail.com> 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@gmail.com> 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@gmail.com> >>>>>>> > 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@gmail.com> 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:
>> >
>> > 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@gmail.com> 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@gmail.com> 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&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> Post to : dhis2-devs@lists.launchpad.net
>> >>> Unsubscribe :
>> >>>
>> >>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> More help : https://help.launchpad.net/ListHelp
>> >>>
>> >>
>> >>
>> >> --
>> >> --
>> >> Jason P. Pickering
>> >> email: jason.p.pickering@gmail.com
>> >> tel:+260968395190
>> >>
>> >
>> >
>> >
>> > --
>> > Cheers,
>> > Knut Staring
>> >
>>
>>
>> --
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@gmail.com
>> tel:+260968395190
>>
>> _______________________________________________
>> Mailing list:
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> Post to : dhis2-devs@lists.launchpad.net
>> Unsubscribe :
>> https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> More help : https://help.launchpad.net/ListHelp
>>
>

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

--
Cheers,
Knut Staring

_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

--
Cheers,
Knut Staring

--
Cheers,
Knut Staring