How to combine spatial and attribute data set?

[Teketo]

Hello,

I have got three spatial datasets.

1. Polygon shape file of a country x (including all administrative regions)
2. Demographic and Health Survey (DHS) data (population characteristics and cluster coordinates)
3. Health facility data (health facilities characteristics and facilities coordinate)

I have been trying to merge these datasets using SAS; however, I have got problem in merging the population data (DHS) with health facility data. Both the sampled health facility and population data were collected all over the country.

The DHS data has 622 clusters (each cluster has one coordinate information) and an average of 23 individuals (14,300 people in total) were interviewed per cluster. On the other hand, in the second dataset, 1,020 health facilities were interviewed along with their geographic coordinates.  

The merge should be done using both geographic coordinates (1,020 health facility vs 622 clusters) (using the nearest health facility distance to each DHS cluster) and regions (11 regions where both the data were collected). 

The merge that I want is not only by minimum distance between clusters and health facilities, but it should also consider the regional administration boundary. In other words, all nearest distance merges must not cross regional admin boundary. 

During merge, how do I manage the multiple observations per cluster in the population dataset; 14,300 people in 622 clusters? There are more location coordinates of health facility (1,020) as compared to 622 clusters.

How can I merge the DHS data with the health facility data? How do I manage the attribute data (an average of 23 individuals information per one cluster) while combining it with a single health facility data?

Data x; *Health facility dataset;

Set a;

Keep LAT LONG REGION FACTYPE Q102_04 GR1 GR2 FA1 FA2 FR1 FR2 FR3

Run;

Data y; *Population dataset;

Set b;

Keep V001 LAT_DHS LONG_DHS V002 V012 REGION V190 V218 M14_1 V501 V313M;

Run;

Kind regards

Teketo

[Reeza]

GeoDIST will calculate distances and GINSIDE will help you with the boundary issue. There’s an example in the docs. 

This may be easier if you provide sample data. If your data is confidential then make fake data. 

[Teketo]

Hi Reeza,

Many thanks.

I have made some fictitious data set; DHS and Health facility, in excel. 

Here attached to get some demonstration how I can manage the data sets using minimum distance and administrative boundary.

In addition to this, I love to see how I can manage the multiple observations per cluster which also impact the merging process and analysis in later stage.

Regards

Teketo 

[Reeza]

This isn't a merge problem. 

[Teketo]

Hi Reeza,

Once the two data sets are merged properly, I have to do descriptive and regression analysis.  My main concern is how do these data sets be merged to run my statistical analysis?

Kind regards

Teketo 

[Reeza]

1. Use GINSIDE to determine which facilities are in which boundaries. The documentation has an example of that. 

2. Once you've assigned each to the proper region, create a distance matrix that calculates the distance between the facility and the clusters, using GEODIST() and a cross join. 

3. Once you have the distance matrix you can find the nearest cluster and use that as needed for your analysis. 

4. Then join the data needed. 

Unfortunately this is not a trivial problem and not something I can answer in a few minutes. I would suggest you break down the problem into smaller portions and try to solve each one first and then pull it all together. You can then ask more targeted questions that are likely to be answered. Asking a fairly complex question that requires a significant time investment is likely to be answered. 

Also, you can prep your sample data for this or other questions using the techniques outlined here:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

If you provide sample data and code as data steps you're much much more likely to get help compared to when some has to download files, import them, figure out if its correct and try and interpret the problem and then solve it.

[Teketo]

Hi Reeza,

Many thanks for your unreserved support.

How do the GEODIST function work in calculating the distance between data set ‘x’ and data set ‘y’?

Do the two data sets merged first to do the distance calculation?  If that is the case, how can I merge them?

The two data sets should be merged considering the minimum distance between cluster and health facility, and each cluster and health facility merge using the minimum distance should be within the respective regions.

For instance, the nearest health facility to a cluster might be in other region, in this case, the merging is not appropriate. Each merge shouldn’t extend beyond region boundary.

I used the below SAS code:

*Distance calculation using GEODIST function;

Data dist;

Set x y;

distance = geodist (lat1, lon1, lat2, lon2, 'DK');

put 'Distance = ' distance 'kilometres';

run;

But this brought nothing, I haven’t got the distance matrix. It gives me a merged data set, which is inappropraite. 

Here attached the SAS code for the fictitious data set.

*Population data;

Data x;

Input Cluster Lat1 Lon1 region age FP;

datalines;

1 14.0 38.7 1 19 0

1 14.0 38.7 1 21 1

2 11.6 41.5 2 30 0

2 11.6 41.5 2 48 0

2 11.6 41.5 2 25 1

3 12.7 37.5 3 32 1

3 12.7 37.5 3 21 1

4 7.5 39.1 4 40 0

4 7.5 39.1 4 33 1

4 7.5 39.1 4 29 0

5 12.7 37.6 1 40 0

5 12.7 37.6 1 31 1

6 10.4 40.1 2 27 1

6 10.4 40.1 2 29 0

7 12.1 40.0 2 26 0

7 12.1 40.0 2 24 1

8 12.2 37.0 3 32 0

8 12.2 37.0 3 37 1

9 9.9 39.9 3 42 0

9 9.9 39.9 3 37 1

10 9.1 40.7 4 33 0

10 9.1 40.7 4 35 1

10 9.1 40.7 4 36 0

;

*Health facility data;

Data y;

Input ID Lat2 Lon2 region HFMA FPRS;

Datalines;

1 13.6 39.1 1 1 0

2 13.3 38.9 1 2 1

3 10.7 40.1 2 1 1

4 10.3 40.2 2 1 1

5 11.4 40.7 2 1 1

6 10.5 39.5 3 2 0

7 11.6 39.6 3 1 1

8 11.3 37.9 3 2 1

9 9.1 35.6 4 2 0

10 9.5 34.8 4 1 1

11 14.3 37.8 1 2 0

12 13.5 39.4 1 1 1

13 10.4 40.2 2 2 0

14 9.2 40.3 2 1 1

15 9.9 39.8 3 2 1

16 11.0 37.1 3 1 1

17 10.6 39.4 3 2 0

18 8.5 34.5 4 2 1

19 7.2 38.2 4 1 1

20 8.8 35.8 4 2 1

;

run;

Kind regards

Teketo

[Reeza]

It’s not a merge, a merge needs keys to link and you don’t have that yet. You first need a distance matrix, which means all points compared with all other points so that you can find the nearest location. Once you have that location you can then merge the data sets to get the remaining variables.

A SQL cross join is required, but since you’re also limiting within region that’s not too bad.

See an example here but add a WHERE clause indicating that the regions must be the same.
https://communities.sas.com/t5/SAS-Studio/Finding-minimum-distance-between-a-set-of-coordinates/td-p...

[Reeza]

Your sample data doesn't make sense. You mentioned that the clusters only had one location, so why do you have multiple entries and if you do how do you want the facility location matched to the clusters? This type of multiplicity can cause your table size to get big fast. 

Here's how to calculate the distance:

Data clusters;
Input Cluster Lat1 Lon1 region age FP;
datalines;
1 14.0 38.7 1 19 0
1 14.0 38.7 1 21 1
2 11.6 41.5 2 30 0
2 11.6 41.5 2 48 0
2 11.6 41.5 2 25 1
3 12.7 37.5 3 32 1
3 12.7 37.5 3 21 1
4 7.5 39.1 4 40 0
4 7.5 39.1 4 33 1
4 7.5 39.1 4 29 0
5 12.7 37.6 1 40 0
5 12.7 37.6 1 31 1
6 10.4 40.1 2 27 1
6 10.4 40.1 2 29 0
7 12.1 40.0 2 26 0
7 12.1 40.0 2 24 1
8 12.2 37.0 3 32 0
8 12.2 37.0 3 37 1
9 9.9 39.9 3 42 0
9 9.9 39.9 3 37 1
10 9.1 40.7 4 33 0
10 9.1 40.7 4 35 1
10 9.1 40.7 4 36 0
;
*Health facility data;
Data health_facility;
Input ID Lat2 Lon2 region HFMA FPRS;
Datalines;
1 13.6 39.1 1 1 0
2 13.3 38.9 1 2 1
3 10.7 40.1 2 1 1
4 10.3 40.2 2 1 1
5 11.4 40.7 2 1 1
6 10.5 39.5 3 2 0
7 11.6 39.6 3 1 1
8 11.3 37.9 3 2 1
9 9.1 35.6 4 2 0
10 9.5 34.8 4 1 1
11 14.3 37.8 1 2 0
12 13.5 39.4 1 1 1
13 10.4 40.2 2 2 0
14 9.2 40.3 2 1 1
15 9.9 39.8 3 2 1
16 11.0 37.1 3 1 1
17 10.6 39.4 3 2 0
18 8.5 34.5 4 2 1
19 7.2 38.2 4 1 1
20 8.8 35.8 4 2 1
;
run;

proc sql;
create table dist_table as
select h.id, h.lat2 as lat_facility, h.lon2 as long_facility,
     h.region, 
     c.lat1 as lat_cluster, c.lon1 as long_cluster, c.cluster,
     geodist(h.lat2, h.lon2, c.lat1, c.lon1, 'k' ) as dist_km
from clusters as c
cross join health_facility as h
where c.region=h.region
order by h.id, dist_km;
quit;
     

[Teketo]

Hi Reeza,

Many thanks. 

Yes, each cluster has one location information; however, there are a number of people interviewed per each cluster. For example, in the fictitious data set, two women were interviewed in cluster 1. Since they were in the same cluster, they have the same location information; that is, lat = 14.0 and long = 38.7 is the same for both women.

In my actual data set, an average of 23 women were interviewed per one cluster; that is, a single coordinate or location information works for all the 23 women. Therefore, one location information is duplicated for 23 times for each individual woman in that particular cluster.

What is the importance of cross join or Cartesian product here? I don’t need a duplicate merge. I just need each cluster to link to one or more health facilities based on the minimum distance that each facility is located from a cluster. Therefore, one health facility will be linked to only one cluster; a single health facility can’t be linked to more than one cluster. But a single cluster can be linked to one or more health facilities depending on each facility proximity taking into consideration that both the health facility and the cluster are within the same region.  

Therefore, the cross join, in this case, can’t work; it gives me a cross product.

Kind regards

Teketo

[Reeza]

---

@Teketo wrote:

I just need each cluster to link to one or more health facilities based on the minimum distance that each facility is located from a cluster. Therefore, one health facility will be linked to only one cluster; a single health facility can’t be linked to more than one cluster. But a single cluster can be linked to one or more health facilities depending on each facility proximity taking into consideration that both the health facility and the cluster are within the same region.  

Therefore, the cross join, in this case, can’t work; it gives me a cross product.

Kind regards

Teketo

How do you know what the minimum distance is unless you calculate them all?

---

[Teketo]

Hi Reeza,

Many thanks for your unreserved kindly support. 

Oh, I have got the main idea of the cross join. However, after the cross join, I am still worried about how to select health facilities that are linked to respective clusters based on the minimum distance and administrative boundary limit. Therefore, at the end, the joined dataset should fulfil the following conditions:

1. One health facility should be linked to one cluster
2. One cluster can be linked to one or more health facilities
3. All health facility and cluster linkage should be within regions boundary
4. The least one minimum distance should be taken among a couple of cross joins

Once these conditions are fulfilled, I can do statistical analysis. Otherwise, the analysis will not give the correct figure and information about the problem I am studying.

Kind regards

Teketo

[Reeza]

I understand, but I assumed region was your administrative boundaries, which is why the cross join does have that WHERE condition. If that's not the case, you missed the GINSIDE step I mentioned at the beginning and you need the SHP file and GINSIDE to first determine your regions and then in the lookup table add that condition to your WHERE statement.

I *think* all you need to do know is first to pick the minimum based on the code I provided. I assumed you can select the minimum by your particular lookup rules from the big table. 

[Teketo]

Hi Reeza,

Thank you indeed!  

Yes, region is my administrative boundary and the WHERE condition works well for it.

Now, my main question is: how to pick a single minimum distance for each health facility having in mind the following criteria?

1. One health facility should be linked to one cluster in a region
2. One cluster can be linked to one or more health facilities in a region

Kind regards

Teketo

[Reeza]

Sort by health facility and distance

Take the first record using BY group and FIRST.