DHIS2 Upgrade from 2.31.5 to 2.32.2 fails with Flyway error(ERROR: Geometry has Z dimension but column does not)

Implementation - Implémentation
1

I have deployed dhis2.32.2.war for upgrade but have encountered the following error:-

-INF/lib/dhis-support-db-migration-2.32.2.jar!/META-INF/dhis/beans.xml]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException:
Migration V2_32_8__OrgUnit_geometry_field.sql failed

SQL State : 22023
Error Code : 0
Message : ERROR: Geometry has Z dimension but column does not
Location : org/hisp/dhis/db/migration/2.32/V2_32_8__OrgUnit_geometry_field.sql
(\WEB-INF\lib\dhis-support-db-migration-2.32.2.jar!\org\hisp\dhis\db\migration\2.32\V2_32_8__OrgUnit_geometry_field.sql)
Line : 10
Statement : – update the ‘geometry’ field with existing data from ‘coordinates’ column (Polygon/Multipolygon type)
update organisationunit
set geometry = ST_SetSRID(ST_GeomFromGeoJSON(‘{“type”:“MultiPolygon”, “coordinates”:’ || coordinates || ‘}’), 4326)
where featuretype in (‘POLYGON’, ‘MULTI_POLYGON’)

Kindly, help figure out the cause of the problem.

Thanks.

1 Like
2

Hi,
This is a rookie question but is PostGIS installed?

1 Like
3

@dmaritim This error typically occurs when there are coordinate values not expected by dhis2. The db column does not support z dimensions, and it looks like some of your org unit coordinate values are having those unsupported values.

You will get the same error when you try this

select ST_SetSRID(ST_GeomFromGeoJSON('{"type":"MultiPolygon", "coordinates":' || coordinates || '}'), 4326) from organisationunit
where featuretype in ('POLYGON', 'MULTI_POLYGON');

If its possible to share your organisation unit data by email, we can take a look at what the incorrect values are.
Or if you are okay with deleting all coordinate data for “POLYGON” and “MULTI_POLYGON”, that should also sort this issue out (but you will lose coordinate data for those org units)

1 Like
4

PostGIS is already installed.

1 Like
5

Same error for us.

The migration assumes that polygons and multiple polygons are the same “beast” but by definition they don’t have the same “depth” of arrays GeoJSON — Wikipédia

the extra/missing array might lead postgres to think we are in 3d

IMO the good migration is to

update organisationunit
set geometry = ST_SetSRID(ST_GeomFromGeoJSON('{"type":"Polygon", "coordinates":' ||     coordinates || '}'), 4326)  where featuretype in ('POLYGON');

update organisationunit
set geometry = ST_SetSRID(ST_GeomFromGeoJSON('{"type":"MultiPolygon", "coordinates":' ||     
 coordinates || '}'), 4326)  where featuretype in ('MULTI_POLYGON');

I think we also have “data quality” issue where some polygons are multi polygons. As the previous schema was not that strict, this was possible.

 select uid, coordinates, ST_SetSRID(ST_GeomFromGeoJSON('{"type":"Polygon", "coordinates":[[[[6.6643,6.5275],[6.6656,6.5229],[6.6220,6.4866],[6.5087,6.4831],[6.3765,6.4004],[6.2578,6.3502],[6.1927,6.2771],[6.1405,6.3401],[6.0831,6.3429],[6.0518,6.2553],[6.0818,6.2302],[6.1232,6.0942],[6.2213,6.0185],[6.2147,5.9378],[6.0318,5.7653],[5.9180,5.7389],[5.8688,5.8033],[5.9018,5.8683],[5.7988,5.9657],[5.6458,6.0500],[5.5330,5.9814],[5.4680,6.0199],[5.3201,6.0155],[5.3252,5.9565],[5.2321,5.9032],[5.2326,6.0450],[5.1152,6.1270],[4.9786,6.2855],[5.0160,6.3680],[5.0635,6.3819],[5.1245,6.5295],[5.0728,6.6000],[5.0842,6.6977],[5.2282,6.8770],[5.5134,6.8698],[5.5090,6.7998],[5.5429,6.7115],[5.6159,6.7632],[5.7130,6.7481],[5.7568,6.8869],[5.7788,7.0220],[5.8234,7.0606],[5.8817,7.2382],[5.9447,7.2846],[5.9618,7.4237],[5.9235,7.4597],[6.0283,7.5735],[6.1260,7.5242],[6.1111,7.4408],[6.1661,7.4250],[6.2589,7.4668],[6.3555,7.3764],[6.4453,7.3852],[6.5039,7.2518],[6.6251,7.2819],[6.6984,7.1748],[6.6707,6.9584],[6.6049,6.8231],[6.6106,6.6994],[6.6643,6.5275]]]]}'), 4326)::geometry(Geometry, 4326) from organisationunit where featuretype in ('POLYGON');

==> ERROR: Geometry has Z dimension but column does not

select uid, coordinates, ST_SetSRID(ST_GeomFromGeoJSON('{"type":"MultiPolygon", "coordinates":[[[[6.6643,6.5275],[6.6656,6.5229],[6.6220,6.4866],[6.5087,6.4831],[6.3765,6.4004],[6.2578,6.3502],[6.1927,6.2771],[6.1405,6.3401],[6.0831,6.3429],[6.0518,6.2553],[6.0818,6.2302],[6.1232,6.0942],[6.2213,6.0185],[6.2147,5.9378],[6.0318,5.7653],[5.9180,5.7389],[5.8688,5.8033],[5.9018,5.8683],[5.7988,5.9657],[5.6458,6.0500],[5.5330,5.9814],[5.4680,6.0199],[5.3201,6.0155],[5.3252,5.9565],[5.2321,5.9032],[5.2326,6.0450],[5.1152,6.1270],[4.9786,6.2855],[5.0160,6.3680],[5.0635,6.3819],[5.1245,6.5295],[5.0728,6.6000],[5.0842,6.6977],[5.2282,6.8770],[5.5134,6.8698],[5.5090,6.7998],[5.5429,6.7115],[5.6159,6.7632],[5.7130,6.7481],[5.7568,6.8869],[5.7788,7.0220],[5.8234,7.0606],[5.8817,7.2382],[5.9447,7.2846],[5.9618,7.4237],[5.9235,7.4597],[6.0283,7.5735],[6.1260,7.5242],[6.1111,7.4408],[6.1661,7.4250],[6.2589,7.4668],[6.3555,7.3764],[6.4453,7.3852],[6.5039,7.2518],[6.6251,7.2819],[6.6984,7.1748],[6.6707,6.9584],[6.6049,6.8231],[6.6106,6.6994],[6.6643,6.5275]]]]}'), 4326)::geometry(Geometry, 4326) from organisationunit where featuretype in ('POLYGON');

you might be able to spot these errors with

 select uid, name, coordinates from organisationUnit where coordinates like '[[[[%' and featureType='POLYGON'
1 Like
6

I made an issue in JIRA for this: [DHIS2-7546] - Jira

2 Likes
7

Any news on this?

1 Like
9

Hey @pjaspers,

this is a tricky issue to solve with our flyway scripts, but we are looking into it. In the meantime I highly suggest manually updating these fields if you encounter the issue. The queries @Stephan_Mestach provided seems like a good starting point.

We will update the ticket as soon as we find a resolution for this issue.

1 Like