Hi,
Most of you know by now that all the old “featuretype” and “coordinates” fields in DHIS2 are being replaced by “geometry” fields that uses PostGIS - this process started in 2.30 and is now nearly complete. For instance, the most common table that users extract data from in .csv format, usually using SQL view, is the organisationunit table - which changed in 2.32.
But while the database field format has changed, the CSV export/import format has NOT changed - so when exporting, you have to convert the geometry data into featuretype and coordinates fields
Below is a little script you can use in SQL view to extract facility (point) organisation unit records (use the where clause to extract whatever sub-sets you want) - this example assumes the facility level is level 5:
SELECT ou.name, ou.uid, ou.code, oup.uid as parentuid, ou.shortname, ou.description, ou.openingdate, ou.closeddate, ou.comment,
‘POINT’ as featuretype,
replace(replace(replace(ST_AsText(ou.geometry,6),’ ‘,’,‘),‘POINT(’,’[‘),’)‘,’]') as coordinates,
ou.url, ou.contactperson, ou.address, ou.email, ou.phonenumber
FROM organisationunit ou
JOIN organisationunit oup on oup.organisationunitid = ou.parentid
WHERE ou.hierarchylevel=5
;
If you for some reason want to extract centroid coordinates from a polygon or multi-polygon layer, replace the ST_AsText parameter with the ST_Centroid function, like this:
ST_AsText(ST_Centroid(ou.geometry),6)
Best regards
Calle