Różnice

Różnice między wybraną wersją a wersją aktualną.

--- ewdprocedury_bazaszkol_ludnosc [2014/10/30 13:42]
zozlak
+++ ewdprocedury_bazaszkol_ludnosc [2019/09/07 16:16]
zozlak
@@ Linia 1: / Linia 1: @@
 ====== Przypisywanie szkołom liczby mieszkańców w miejscowości ======
+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 lata przypisywana jest informacja z BDL dla odpowiadającego im roku.
+  * jednocześnie dla wcześniejszych latach przypisywana jest informacja z BDL dla odpowiadającego im roku.
+===== Algorytm =====
+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)
 ====== Pobieranie danych z BDL ======
 http://stat.gov.pl/bdl
-  * dane roczne -> ludność -> stan ludności i prognozy -> ludność wg grup wieku i płci
+  * dane wg dziedzin -> ludność -> stan ludności -> ludność wg grup wieku i płci
-  * //wybór jednostek terytorialnych wg poziomu NTS//: //gminy miejskie, miasta w gminach miejsko-wiejskich//
+  * lata wg uznania, wiek //ogółem//, płeć //ogółem//
-  * //wiek//: //ogółem//
+  * jednostki - wszystkie z poziomu ogólnopolskiego w podziale //układ administracyjny// (który jest domyślny)
-  * //płeć//: //ogółem//
+  * wyeksportować jako //CSV - tablica relacyjna//
-  * //dodatkowe agregaty//: brak
-I zapisać jako CSV.
 ====== Import do bazy ======
 <code>
-rok = 2013                 # dostosować
+rok = 2019                   # dostosować
-kolumnaLudnosc = 'X2012'   # dostosować
+rokBdl =  2018               # dostosować
-plikDanych = '~/ludnosc.csv' # dostosować, plik musi mieć kolumny 'teryt' i 'miasto'
+plikDanych = '~/ludność.csv' # dostosować, plik musi mieć kolumny 'teryt' i 'miasto'
-zrodloODBC = 'EWD'      # dostosować
+library(dplyr)
 library(stringr)
-library(plyr)
-library(RODBCext)
-P = odbcConnect(zrodloODBC)
-dane = read.csv2(plikDanych, stringsAsFactors=F)
+# aby upewnić się, że baza jest spójna z naszymi oczekiwaniami
-dane$teryt = as.numeric(paste0(substring(dane$teryt, 2, 3), substring(dane$teryt, 6, 9)))
+sqlQuery(P, "UPDATE szkoly_dane SET miejscowosc = poczta WHERE miejscowosc = '' or miejscowosc is null")
-dane$miasto = tolower(str_trim(sub('(- miasto )?[(][0-9][)]( [*])?$', '', dane$miasto)))
-dane[, kolumnaLudnosc] = as.numeric(dane[, kolumnaLudnosc]) # jeśli dane z kilku lat i zmieniała się liczba gmin
+dane = read.csv2(plikDanych, stringsAsFactors=F) %>%
-dane = dane[!is.na(dane[, kolumnaLudnosc]), ]
+  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_szkoly, rodzaj_gminy, lower(miejscowosc) AS miejscowosc, rok,
-  id_wojewodztwa * 10000 + id_powiatu * 100 + id_gminy AS teryt
+    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 = ?",
+  WHERE rok = ? AND id_szkoly > 0",
   rok,
   fetch = T, stringsAsFactors = F
@@ Linia 48: / Linia 73: @@
 ### Warszawa
 warszawa = szkoly[szkoly$rodzaj_gminy == 'dzielnica m.st. Warszawy', ]
-warszawa[, kolumnaLudnosc] = dane[dane$teryt %in% c(146501, 143101), kolumnaLudnosc]
+warszawa = warszawa %>% left_join(dane)
 ### gminy miejskie łączą się wprost po kodzie TERYT
 miejskie = szkoly[szkoly$rodzaj_gminy == 'miejska', ]
-miejskie = join(miejskie, dane, type = 'inner')
+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 = join(mieszane, dane, type = 'left')
+mieszane = left_join(mieszane, dane)
-mieszane[is.na(mieszane[, kolumnaLudnosc]), kolumnaLudnosc] = 0
+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
@@ Linia 71: / Linia 97: @@
   wiejskie$miejscowosc
 )
-wiejskie = join(wiejskie, dane2, type = 'left', by = 'teryt')
+wiejskie = left_join(wiejskie, dane2 %>% select(-miejscowosc))
-wiejskie[is.na(wiejskie[, kolumnaLudnosc]), kolumnaLudnosc] = 0
+wiejskie$ludnosc[is.na(wiejskie$ludnosc)] = 0
-### zapis do bazy
+### generowanie zapytań SQL
-odbcSetAutoCommit(P, F)
+d = bind_rows(
-sqlExecute(
+  warszawa %>% select('ludnosc', 'id_szkoly', 'rok'),
-  P,
+  mieszane %>% select('ludnosc', 'id_szkoly', 'rok'),
-  "UPDATE szkoly_dane SET wielkosc_miejscowosci = ? WHERE id_szkoly = ? AND rok = ?",
+  wiejskie %>% select('ludnosc', 'id_szkoly', 'rok')
-  rbind(
-    warszawa[, c(kolumnaLudnosc, 'id_szkoly', 'rok')],
-    miejskie[, c(kolumnaLudnosc, 'id_szkoly', 'rok')],
-    mieszane[, c(kolumnaLudnosc, 'id_szkoly', 'rok')],
-    wiejskie[, c(kolumnaLudnosc, 'id_szkoly', 'rok')]
-  )
 )
-odbcEndTran(P, T)
+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)
-odbcClose(P)
 </code>

Zespół Pomiaru Dydaktycznego

Narzędzia użytkownika

Narzędzia witryny

Różnice

Narzędzia strony