Narzędzia użytkownika

Narzędzia witryny


ewdprocedury_bazaszkol_ludnosc

Różnice

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

Odnośnik do tego porównania

Both sides previous revision Poprzednia wersja
Nowa wersja
Poprzednia wersja
ewdprocedury_bazaszkol_ludnosc [2014/10/30 13:41]
zozlak
ewdprocedury_bazaszkol_ludnosc [2019/09/07 16:16] (aktualna)
zozlak
Linia 1: Linia 1:
 ====== Przypisywanie szkołom liczby mieszkańców w miejscowości ====== ====== 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:​ 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);   * 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 ====== ====== Pobieranie danych z BDL ======
  
 http://​stat.gov.pl/​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 miejskiemiasta 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 ====== ====== Import do bazy ======
  
 <​code>​ <​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(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$teryt2, 3), substring(dane$teryt, ​69))) +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(KodNazwa, Wartosc) %>% 
 +  dplyr::​rename(teryt ​= Kodmiasto = Nazwaludnosc = 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( szkoly = sqlExecute(
   P,    P, 
   "​SELECT ​   "​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)   FROM szkoly_dane JOIN teryt_gminy USING (rok, id_wojewodztwa,​ id_powiatu, id_gminy)
-  WHERE rok = ?",+  WHERE rok = ? AND id_szkoly > 0",
   rok,   rok,
   fetch = T, stringsAsFactors = F   fetch = T, stringsAsFactors = F
Linia 48: Linia 73:
 ### Warszawa ### Warszawa
 warszawa = szkoly[szkoly$rodzaj_gminy == '​dzielnica m.st. Warszawy',​ ] warszawa = szkoly[szkoly$rodzaj_gminy == '​dzielnica m.st. Warszawy',​ ]
-warszawa[, kolumnaLudnosc] ​dane[dane$teryt ​%inc(146501, 143101), kolumnaLudnosc]+warszawa = warszawa ​%>left_join(dane)
  
 ### gminy miejskie łączą się wprost po kodzie TERYT ### gminy miejskie łączą się wprost po kodzie TERYT
 miejskie = szkoly[szkoly$rodzaj_gminy == '​miejska',​ ] 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 ### gminy miejsko-wiejskie złączamy po kodzie TERYT i nazwie miejscowości równej nazwie gminy
 mieszane = szkoly[szkoly$rodzaj_gminy == '​miejsko-wiejska',​ ] mieszane = szkoly[szkoly$rodzaj_gminy == '​miejsko-wiejska',​ ]
 dane$miejscowosc = dane$miasto 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 ### 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$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(PT) +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) 
- +</​code>​
-odbcClose(P)+
ewdprocedury_bazaszkol_ludnosc.1414672872.txt.gz · ostatnio zmienione: 2014/10/30 13:41 przez zozlak