Query Geo coordinates in 2.33

Hi team,
how can I query (SQL view) and display geo-coordinates in 2.33 like it used to be in previous versions like this:

select ou.name, ou.uid, ou.code, ou.coordinates, oustr.level, gsstr.“Facility Type” as factype, gsstr.“Facility Ownership” as facownership, (select name from organisationunit where organisationunitid=oustr.idlevel2) as chiefdom, (select name from organisationunit where organisationunitid=oustr.idlevel3) as district from _orgunitstructure as oustr join organisationunit as ou on oustr.organisationunitid=ou.organisationunitid join _organisationunitgroupsetstructure as gsstr on ou.organisationunitid=gsstr.organisationunitid where oustr.level >= 4 order by chiefdom, district, factype, facownership, ou.name limit 300;

I have replaced coordinates with geometry but I can’t get it to work, and a normal query brings unreadable format in results.
I need to get the latitude and longitude in the results.

Thanks

1 Like

Hi @mutali,

Sorry for the delay on getting back to you on this!

I have reached out to the @dhis2-backend team and hope that they will be able to help you out very soon.

Best regards,
Karoline

Hi Jean Paul, you have to use the PostGIS functions, e.g. https://postgis.net/docs/ST_AsGeoJSON.html.

Here is an example:

SELECT name, ST_AsGeoJSON(geometry) FROM organisationunit

2 Likes

Thanks @Knut_Staring,

I ended up using SELECT name,code, ST_X(geometry) AS LONG, ST_Y(geometry) AS LAT FROM organisationunit where ST_GeometryType(geometry)=‘ST_Point’

I wanted to exclude other types of geometries.

1 Like