Finding the distance between sets of coordinates (using API requests and Openrouteservice)

[#1]

Sometimes when working with geospatial data, it might be of interest to calculate the distance from point A to point B, both specified by sets of coordinates. Usually, we rely on Google Maps for this, but it might not be the most convenient when dealing with a large amount of data.

In this article, we explain how to calculate distances between sets of coordinates using API requests to the Openrouteservice website.

The coordinates in the article are used as an example and censored to avoid revealing real locations.

Distance between two sets of coordinates

Let’s start by calculating the distance between two sets of coordinates. To do this, we use the POST method of the HTTP procedure, which allows us to send an HTTP request (using an API key) to the Openrouteservice server, specifying the coordinates of the two places and the desired mode of transport in the URL.

filename jsn " P:\Json\dist.json" 

proc http method="POST" 
url="https://api.openrouteservice.org/v2/directions/driving-car/json" ct="application/json" 
oauth_bearer=<my_key> 
in="{""coordinates"": [[11.8xxxx, 45.4xxxx], [11.9xxxx, 45.4xxxx]]}" 
out = jsn; 
run; 

libname test JSON fileref= jsn; 

/*saving the distance and duration into a new dataset*/
data dist; 
set test.routes_summary; 
run; 

In response to the HTTP request, the Openrouteservice server provides a JSON file, which contains the bounding box of the route, the shortest path between the two points, and the details of the original request, i.e., the coordinates entered in the HTTP procedure.

The distance and duration of the route are specified within the file in meters and seconds, respectively.

Distance between multiple pairs of coordinates

Let’s now move on to the case where we have multiple pairs of coordinates for which we want to calculate the distances.

To do this, the dataset start_ds is created; the variables indicate the ID, latitude, and longitude of the starting point and the ID, latitude, and longitude of the destination.

data start_ds;
input start_ID  lat_start lon_start dest_ID lat_dest lon_dest;
datalines;
1 39.1xxxx 10.6xxxx 1 40.3xxxx 11.2xxxx
2 39.2xxxx 10.7xxxx 4 39.9xxxx 10.9xxxx
3 39.3xxxx 10.8xxxx 7 40.5xxxx 11.2xxxx
…
;
run;

Another dataset is created to store the distance and duration of the routes between the sets of coordinates. The durdist dataset contains the distance and the duration of the route, the ID, latitude, and longitude of the starting point and the ID, latitude, and longitude of the destination.

data durdist(keep=start_ID dest_ID lat_start lon_start lat_dest
lon_dest meters second); 
length start_ID $8 dest_ID $8  lat_start lon_start lat_dest lon_dest 
meters seconds $20;
run;

To calculate the distances for all pairs of coordinates, a macro is used. The macro parameters are the variables from the start_ds dataset and the name of the dataset where the results will be saved. As previously done, the HTTP procedure is utilized to make API calls to the Openrouteservice website. The results, contained in the JSON file obtained from these calls, are saved in the temporary dataset XX. These will later be combined with other route information and inserted into the final dataset.

%macro distance
  (start_ID=
  ,lat_start=
  ,lon_start=
  ,dest_ID=
  ,lat_dest=
  ,lon_dest=
  ,out=  
  );

filename js "P:\Json\dist_&START_ID. _&DEST_ID..json"

proc http  
url="https://api.openrouteservice.org/v2/directions/driving-car?start=
&lon_start,&lat_start%nrstr(&)end=&lon_dest,&lat_dest%nrstr(&)api_key=<my-key>"
method='get' 
out=js
ct="application/x-www-form-urlencoded";
run;

libname street JSON fileref=js;  

/*saving the distance and duration into a temp dataset*/
data XX; 
set street.properties_summary; 
run;  
 
%let dist=; %let dur=; 
proc sql;
select Distance, Duration  into :dist, :dur from XX;
quit;  

/* temp dataset with info about the current route*/
data __distance(keep=START_ID DEST_ID Lat_start Lon_start 
Lat_dest Lon_dest meters seconds) ; 
length START_ID $8 DEST_ID $8 Lat_start Lon_start Lat_dest Lon_dest 
meters seconds $20;
START_ID="&START_ID" ;
DEST_ID="&DEST_ID" ;
Lat_start="&Lat_start" ;
Lon_start="&Lon_start" ;
Lat_dest="&Lat_dest" ;
Lon_dest="&Lon_dest" ;
meters = "&dist";
seconds = "&dur";
run;

proc append base=&out data=__distance;
run;

proc delete data=XX; 
run;


/*Openrouteservice has a limit of the amount of request per minute, so the program is paused for 2 secs*/
data _null_; 
call sleep(2,1); 
run; 

%mend distance;

data _null_;
set starts_ds;
call execute 
(
'%nrstr('
    ||  '%distance'
    ||    '('
    ||    ' start_ID='  || trim(start_ID)
    ||    ',lat_start=' || trim(lat_start)
    ||    ',lon_start=' || trim(lon_start)
    ||    ',DEST_ID='   || trim(dest_ID)
    ||    ',lat_dest='  || trim(lat_dest)
    ||    ',lon_dest='  || trim(lon_dest)
    ||    ',out=durdist'
    ||    ' )'
    || ')'          
);
run;