Liczba mieszkańców miejscowości, w której znajduje się szkoła przypisywana jest z informacji o liczbie mieszkańców miejscowości pobranej z Banku Danych Lokalnych (BDL).
Szkołom znajdującym się na wsiach przypisywana jest wielkość miejscowości 0.
Szkołom dla których brak informacji adresowych przypisywana jest wielkość miejscowości NULL (brak danych).
Ponieważ dane w BDL publikowane są z systematycznym opóźnieniem, przyjęto, że informacja o liczbie mieszkańców:
Pobranie z BDL informacji o liczbie mieszkańców jedynie w gminach miejskich oraz miastach w gminach miejsko-wiejskich (będzie to istotne dla gmin miejsko-wiejskich i wiejskich).
rok = 2019 # dostosować rokBdl = 2018 # dostosować plikDanych = '~/ludność.csv' # dostosować, plik musi mieć kolumny 'teryt' i 'miasto' library(dplyr) library(stringr) # aby upewnić się, że baza jest spójna z naszymi oczekiwaniami sqlQuery(P, "UPDATE szkoly_dane SET miejscowosc = poczta WHERE miejscowosc = '' or miejscowosc is null") dane = read.csv2(plikDanych, stringsAsFactors=F) %>% as.tbl() %>% filter(Rok == rokBdl & grepl('([(][148][)]|Powiat m. st. Warszawa)$', Nazwa) & !is.na(Wartosc)) %>% select(Kod, Nazwa, Wartosc) %>% dplyr::rename(teryt = Kod, miasto = Nazwa, ludnosc = Wartosc) %>% mutate(teryt = as.integer(teryt / 10)) %>% mutate(miasto = sub('[*]', '', miasto)) %>% mutate(miasto = sub('[(][0-9][)]', '', miasto)) %>% mutate(miasto = tolower(str_trim(sub('- miasto', '', miasto)))) %>% mutate(miasto = tolower(str_trim(miasto))) szkoly = sqlExecute( P, "SELECT id_szkoly, rodzaj_gminy, lower(miejscowosc) AS miejscowosc, rok, id_wojewodztwa * 10000 + id_powiatu * 100 + id_gminy AS teryt, pna FROM szkoly_dane JOIN teryt_gminy USING (rok, id_wojewodztwa, id_powiatu, id_gminy) WHERE rok = ? AND id_szkoly > 0", rok, fetch = T, stringsAsFactors = F ) ### Warszawa warszawa = szkoly[szkoly$rodzaj_gminy == 'dzielnica m.st. Warszawy', ] warszawa = warszawa %>% left_join(dane) ### gminy miejskie łączą się wprost po kodzie TERYT miejskie = szkoly[szkoly$rodzaj_gminy == 'miejska', ] miejskie = left_join(miejskie, dane) ### gminy miejsko-wiejskie złączamy po kodzie TERYT i nazwie miejscowości równej nazwie gminy mieszane = szkoly[szkoly$rodzaj_gminy == 'miejsko-wiejska', ] dane$miejscowosc = dane$miasto mieszane = left_join(mieszane, dane) mieszane$ludnosc[is.na(mieszane$ludnosc)] = 0 mieszane$ludnosc[is.na(mieszane$pna) | mieszane$pna == ''] = NA ### szkoły teoretycznie leżące w gminach wiejskich mogą tak naprawdę leżeć w gminach miejskich o tej samej nazwie - złączamy po terycie powiatu i nazwie miejscowości równej nazwie gminy dane2 = dane dane2$teryt = paste0( substr(dane2$teryt, 1, str_length(dane2$teryt) - 2), dane2$miasto ) wiejskie = szkoly[szkoly$rodzaj_gminy == 'wiejska', ] wiejskie$teryt = paste0( substr(wiejskie$teryt, 1, str_length(wiejskie$teryt) - 2), wiejskie$miejscowosc ) wiejskie = left_join(wiejskie, dane2 %>% select(-miejscowosc)) wiejskie$ludnosc[is.na(wiejskie$ludnosc)] = 0 ### generowanie zapytań SQL d = bind_rows( warszawa %>% select('ludnosc', 'id_szkoly', 'rok'), mieszane %>% select('ludnosc', 'id_szkoly', 'rok'), wiejskie %>% select('ludnosc', 'id_szkoly', 'rok') ) zap = sprintf("UPDATE szkoly_dane SET wielkosc_miejscowosci = %s WHERE id_szkoly = %d AND rok = %d;", coalesce(as.character(d$ludnosc), 'null'), d$id_szkoly, rok)