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:

• w bieżącym roku pobierana jest dla danych, które są dostępne w BDL, czyli dla poprzedniego roku (mówiąc inaczej wg stanu w momencie rozpoczęcia roku szkolnego, którego to roku szkolnego dotyczą dane teleadresowe szkoły);
• jednocześnie dla wcześniejszych latach przypisywana jest informacja z BDL dla odpowiadającego im roku.

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).

• dzielnice Warszawy - złączenie po kodzie TERYT Warszawy (146501 / 143101)
• gminy miejskie - złączenie z danymi z BDL po kodzie TERYT gminy
• gminy miejsko-wiejskie
  • złączenie z danymi z BDL po parze {TERYT gminy, miejscowość}
  • przypisanie 0 szkołom, które nie połączyły się z danymi BDL
  • przypisanie braku danych szkołom, które nie posiadają kodu pocztowego
• gminy wiejskie
  • złączenie z danymi z BDL po parze {TERYT powiatu, miejscowość}
    • ponieważ z BDL pobrano tylko informacje o gminach miejskich i miastach w gminach miejsko-wiejskich, dołączyć w ten sposób może się tylko informacja o odpowiadającej danej gminie wiejskiej gminie miejskiej
    • są to przypadki, gdy szkoła siedzibę ma w gminie miejskiej, choć np. organem prowadzącym jest gmina wiejska
    • w skali kraju, dla wszystkich typów szkół, jest takich przypadków ok. 15 do 30.
  • przypisanie 0 szkołom, które nie połączyły się z danymi BDL (tzn. faktycznie znajdują się na wsi)

http://stat.gov.pl/bdl

• dane wg dziedzin → ludność → stan ludności → ludność wg grup wieku i płci
• lata wg uznania, wiek ogółem, płeć ogółem
• jednostki - wszystkie z poziomu ogólnopolskiego w podziale układ administracyjny (który jest domyślny)
• wyeksportować jako CSV - tablica relacyjna

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)