1

In the session, Anne calculated the distances to the closest hospitals located within North-Rhine Westphalia (NRW). Still, she did not show how she subsetted the original file, which contains all hospitals in Germany. What did she do? Subset the data file yourself by relying on the spatial information of the csv-file “hospital_points” and the polygon of North-Rhine Westphalia. How many hospitals are located within the borders of NRW?

You need two shapefiles for that: the point layer hospital_points in the data folder and a shapefile of NRW. For NRW, you can either use Stefans OSM syntax or use the “german_states” shapefile and filter on AGS=="05".

The default of st_join will leave you with a ‘left-join’ and returns a data object with all hospitals and matching district information for those which are located within NRW. You can reset the option to perform an ‘inner-join’ and keep only the observation which lay within the predefined area (st_join(x , y, join = "", left = FALSE)).

solution 

# load hospitals
hospitals <- read.csv("../data/hospital_points.csv", 
                      header = T, fill = T, sep = ",") %>%
            sf::st_as_sf(., coords = c("X", "Y"),
            crs = 3035)


#  use the OSM function provided by Stefan
nrw <-
  osmdata::getbb(
    "Nordrhein-Westfalen", 
    format_out = "sf_polygon"
  ) %>% 
  .$multipolygon %>% 
  sf::st_transform(3035)

# or import shapefile nrw
nrw <- sf::st_read(dsn = "../data",
                         layer = "GER_STATES",
                         quiet = T) %>%
                        sf::st_transform(.,3035) %>% 
                        filter( AGS == "05")

# spatial join
nrw_hospitals <-
  hospitals %>% 
  sf::st_join(., 
          # point layer nrw
          nrw, 
          # chose intersect or within
          join = sf::st_intersects,
          # option false will 
          # keep only the hospital 
          # which could be joined
          left = FALSE)

nrw_hospitals
## Simple feature collection with 344 features and 29 fields
## geometry type:  POINT
## dimension:      XY
## bbox:           xmin: 4039950 ymin: 3058862 xmax: 4277224 ymax: 3246338
## projected CRS:  ETRS89-extended / LAEA Europe
## First 10 features:
##                                                                                           name
## 319                                                                         Ev. Krankenhaus   
## 320 Fliedner Klinik Ambulanz und Tagesklinik für Psychiatrie, Psychotherapie und Psychosomatik
## 321                          Johanniter-Tagesklinik Klinik für Psychiatrie und Psychotherapie 
## 322                                                    Krankenanstalten Florence Nightingale  
## 323                                                           Krankenhaus Mörsenbroich-Rath   
## 324               LVR-Klinikum Düsseldorf Klinikum der Heinrich-Heine- Universität Düsseldorf 
## 325                                                                         Marien-Hospital   
## 326                                            Medical Center Düsseldorf -Luisenkrankenhaus-  
## 327                     Paracelsus Klinik Golzheim Fachklinik für Urologie und Kinderurologie 
## 328                                                                Sana Kliniken Düsseldorf   
##     year beds     ags ADE GF BSG RS AGS       SDV_RS                 GEN  BEZ
## 319 2017  568 5111000   2  4   1 05  05 051110000000 Nordrhein-Westfalen Land
## 320 2017    . 5111000   2  4   1 05  05 051110000000 Nordrhein-Westfalen Land
## 321 2017    . 5111000   2  4   1 05  05 051110000000 Nordrhein-Westfalen Land
## 322 2017  568 5111000   2  4   1 05  05 051110000000 Nordrhein-Westfalen Land
## 323 2017  718 5111000   2  4   1 05  05 051110000000 Nordrhein-Westfalen Land
## 324 2017  495 5111000   2  4   1 05  05 051110000000 Nordrhein-Westfalen Land
## 325 2017  461 5111000   2  4   1 05  05 051110000000 Nordrhein-Westfalen Land
## 326 2017   42 5111000   2  4   1 05  05 051110000000 Nordrhein-Westfalen Land
## 327 2017   81 5111000   2  4   1 05  05 051110000000 Nordrhein-Westfalen Land
## 328 2017  490 5111000   2  4   1 05  05 051110000000 Nordrhein-Westfalen Land
##     IBZ BEM NBD SN_L SN_R SN_K SN_V1 SN_V2 SN_G FK_S3 NUTS         RS_0
## 319  20  --  ja   05    0   00    00    00  000     0  DEA 050000000000
## 320  20  --  ja   05    0   00    00    00  000     0  DEA 050000000000
## 321  20  --  ja   05    0   00    00    00  000     0  DEA 050000000000
## 322  20  --  ja   05    0   00    00    00  000     0  DEA 050000000000
## 323  20  --  ja   05    0   00    00    00  000     0  DEA 050000000000
## 324  20  --  ja   05    0   00    00    00  000     0  DEA 050000000000
## 325  20  --  ja   05    0   00    00    00  000     0  DEA 050000000000
## 326  20  --  ja   05    0   00    00    00  000     0  DEA 050000000000
## 327  20  --  ja   05    0   00    00    00  000     0  DEA 050000000000
## 328  20  --  ja   05    0   00    00    00  000     0  DEA 050000000000
##        AGS_0        WSK      EWZ      KFL         DEBKG_ID
## 319 05000000 2009-11-01 17912134 34112.32 DEBKGDL20000E6GR
## 320 05000000 2009-11-01 17912134 34112.32 DEBKGDL20000E6GR
## 321 05000000 2009-11-01 17912134 34112.32 DEBKGDL20000E6GR
## 322 05000000 2009-11-01 17912134 34112.32 DEBKGDL20000E6GR
## 323 05000000 2009-11-01 17912134 34112.32 DEBKGDL20000E6GR
## 324 05000000 2009-11-01 17912134 34112.32 DEBKGDL20000E6GR
## 325 05000000 2009-11-01 17912134 34112.32 DEBKGDL20000E6GR
## 326 05000000 2009-11-01 17912134 34112.32 DEBKGDL20000E6GR
## 327 05000000 2009-11-01 17912134 34112.32 DEBKGDL20000E6GR
## 328 05000000 2009-11-01 17912134 34112.32 DEBKGDL20000E6GR
##                    geometry
## 319 POINT (4095599 3127379)
## 320 POINT (4096285 3128719)
## 321 POINT (4101713 3121737)
## 322 POINT (4094348 3137304)
## 323 POINT (4099466 3133197)
## 324 POINT (4100738 3130402)
## 325 POINT (4096526 3129923)
## 326 POINT (4099035 3129691)
## 327 POINT (4094841 3132032)
## 328 POINT (4101452 3130494)
# 344 hospitals in NRW

2

Did the operationalization of health care provision convince you? Don’t you think it might be more important how many hospitals are close to the respondents? To test this, we want to calculate the number of hospitals per district in North-Rhine Westphalia. Use the syntax below to prep the hospital data.

Earn extra points by counting not only the number of hospitals but also the sum of hospital beds within a district.

nrw_districts <- sf::st_read(dsn = "../data",
                            layer = "GER_DISTRICTS",
                            # quiet is optional if you
                            # not want to print the metainformation
                            quiet = T) %>% 
                    sf::st_transform(. , 3035) %>% 
                    dplyr::rename(., district_id = id) %>% 
                    # filter the districts of NRW
                    dplyr::filter( district_id >= 5000 & district_id < 6000 ) 

nrw_hospitals <-  
  nrw_hospitals %>% 
    # beds were character, now numeric
     dplyr::mutate(beds = as.numeric(beds)) %>%
    # replace NAs as zeros for simplification
     replace(., is.na(.), 0)
## Warning: Problem with `mutate()` input `beds`.
## i NAs introduced by coercion
## i Input `beds` is `as.numeric(beds)`.
## Warning in mask$eval_all_mutate(dots[[i]]): NAs introduced by coercion

You need a as_tibble() data frame to use the functions group_by() and summarise().

The function n() allows summarising the total count of hospitals. sum(beds) for summarizing the bed total per district.

solution 

district_hospital_join <-
  nrw_hospitals %>% 
    # join the hospitals 
    # within districts
    sf::st_join(., nrw_districts, join = sf::st_within) %>% 
    # use as tibble to perform
    # group by & summarise
    dplyr::as_tibble() %>% 
    dplyr::group_by(district_id) %>% 
    dplyr::summarise(hospital_ct = n(), 
              hospital_bed_ct = sum(beds)) %>% 
    # left join the new information
    # to the original data frame
    left_join(nrw_districts,.)
## `summarise()` ungrouping output (override with `.groups` argument)
## Joining, by = "district_id"
district_hospital_join
## Simple feature collection with 53 features and 3 fields
## geometry type:  MULTIPOLYGON
## dimension:      XY
## bbox:           xmin: 4031313 ymin: 3029642 xmax: 4283898 ymax: 3269981
## projected CRS:  ETRS89-extended / LAEA Europe
## First 10 features:
##    district_id hospital_ct hospital_bed_ct                       geometry
## 1         5111          13            5017 MULTIPOLYGON (((4094868 314...
## 2         5112           8            4634 MULTIPOLYGON (((4092415 316...
## 3         5113          12            5681 MULTIPOLYGON (((4114154 316...
## 4         5114           4            2078 MULTIPOLYGON (((4080863 314...
## 5         5116           5            2131 MULTIPOLYGON (((4075096 313...
## 6         5117           2             932 MULTIPOLYGON (((4105756 315...
## 7         5119           3            1456 MULTIPOLYGON (((4103124 316...
## 8         5120           3            1020 MULTIPOLYGON (((4128163 312...
## 9         5122           4            1046 MULTIPOLYGON (((4117277 312...
## 10        5124           3            2070 MULTIPOLYGON (((4130282 313...