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