Getting nearest 'n' organisation units

Development - Développement App Development
1

Hi there,

I am trying to get ‘n’ numbers of organisation units near me. Is there any way using API / CLI to get closest / near organisation units based on Lat/Lng?

Many Thanks,
Utsav Koju

2

Hi @Utsav_Ashish_Koju ,
There is currently no native functionality in the API which would do this, as far as I know. But, it would not be that difficult to implement with an SQL view which accepts parameters.

Modern versions of DHIS2 should have the PostGIS database extension available, and using the ST_Distance function, and then sorting based on distance, you should be able to quite easily calculate the closest facilities to a given point. You would obviously need to supply the location and n facilities as a parameter to the query, but something like this might work.

2.38=# WITH foo as (
  SELECT uid,name,
  ST_Distance('SRID=4326;POINT(-72.1235 42.3521)'::geometry,
              geometry) as distance FROM organisationunit
  where hierarchylevel = 4 --Only take the lowest level (facilities)
)
SELECT uid,name, distance from foo
WHERE distance IS NOT NULL
ORDER BY distance ASC 
LIMIT 5;
     uid     |          name           |     distance      
-------------+-------------------------+-------------------
 Eyj2kiEJ7M3 | Bailor CHP              | 67.76160489930858
 weLTzWrLXCO | Bapuya MCHP             | 67.78121338099518
 PcADvhvcaI2 | Kychom CHC              | 67.79232305224538
 QIp6DHlMGfb | Baptist Centre Kassirie | 67.81143913861436
 L3GgannGGKl | Mafufuneh MCHP          |  67.8143287988608
(5 rows)

2.38=#
2 Likes
3

other option do that in the browser with turfjs

const api = await dhis2.api();
const ou = await api.get("organisationUnits", {
  fields: "id,name,geometry",
  paging: false
});

var options = { units: "kilometers" };

const searchPoint = turf.point([-12.94, 9.01]);

const orgunitWithLocations = ou.organisationUnits.filter(
  p => p.geometry && p.geometry.type === "Point"
);

let orgunitWithLocationsNearSearchPoint = orgunitWithLocations.filter(p => {
  const distance = turf.distance(p.geometry.coordinates, searchPoint, options);
  p.distance = distance;
  return distance < 5;
});
orgunitWithLocationsNearSearchPoint = _.sortBy(
  orgunitWithLocationsNearSearchPoint,
  "distance"
);
return orgunitWithLocationsNearSearchPoint;

you can use taskr to run the snippet provided
and visualize the results on the map (red is the search point, blue the orgunits within 5 km)

image
image1721×836 94.1 KB

image
image2087×351 27.6 KB

2 Likes
4

@jason your option looked interesting and wanted to give it a try but I have hard time figuring out how to pass decimals.

I’ve defined the view (as sql query)

WITH orgunitsWithPoints as (
   SELECT uid,name,
   ST_Distance(ST_SetSRID(ST_MakePoint(${a}, ${b}), 4326), geometry) as distance FROM organisationunit where GeometryType(geometry) = 'POINT'
 )
 SELECT uid, name, distance from orgunitsWithPoints
 WHERE distance IS NOT NULL
 ORDER BY distance ASC
 LIMIT 5;

also tried your version with string interpolation but couldn’t make it work.

'SRID=4326;POINT(${a} ${b})'::geometry

I’ve tried multiple ways of passing lat/long in the url/sql

/api/29/sqlViews/ehJBoo0bE8u/data.json?var=a:72&var=b:42.45

but it’s always failing with

{
  "httpStatus": "Conflict",
  "httpStatusCode": 409,
  "status": "ERROR",
  "message": "Variables are invalid: `[42.45]`",
  "errorCode": "E4306"
}

note that with integer it works…

May be it’s the sign that dhis2 should adds some filtering on geometry type and distance in the api ?

1 Like
5

hmm. It should work. I tested just using values without decimals, and it seems to work. So, I suppose for some reason, DHIS2 considers variables with decimal points to be invalid for some reason. There could be a security reason for this. I’ll try and see if I can get an answer from one of the developers.

1 Like