Access

 MS Access & XML & Baza miejscowości i ulic TERYT.

Krajowy Rejestr Urzędowy Podziału Terytorialnego Kraju (TERYT)

SIMC (system identyfikatorów i nazw miejscowości)

• Stan na dzień 03 marca 2018 roku

Zbiór SIMC jest zbiorem zawierającym identyfikatory i nazwy miejscowości. Przetwarzanie zbioru z systemu SIMC następuje łącznie ze zbiorem z systemu TERC (zawierającym identyfikatory i nazwy jednostek podziału terytorialnego) i zbiorem WMRODZ (zawierającym wykaz symboli i nazw rodzajów miejscowości).
Na stronach Zbiór TERC z rejestru TERYT oraz Zbiór WMRODZ z rejestru TERYT omówiona została struktura plików TERC.xml oraz WMRODZ.xml i zapis danych zawartych w tych plikach w tabelach MS Access.

Teraz przyszła kolej na następny plik z rejestru TERYT. Jest nim plik SIMC.xml zawierający identyfikatory i nazwy jednostek podziału terytorialnego. Plik ten, jak i pozostałe, możemy pobrać ze strony Głównego Urzędu Statystycznego (GUS): Pliki pełne rejestru TERYT.

System identyfikatorów i nazw miejscowości SIMC zawiera:
• urzędowe nazwy miejscowości,
• stałe, niepowtarzalne identyfikatory miejscowości,
• określenia rodzajowe miejscowości,
• przynależność miejscowości do gminy, powiatu i województwa.

Przetwarzanie pliku SIMC.xml

Plik SIMC.xml zawiera zbiór identyfikatorów i nazw miejscowości. Plik ten, został pobrany do bieżącego katalogu bazy danych, do folderu o nazwie TERYT. Na stronie Opis struktury zbioru SIMC możemy zapoznać się ze strukturą tego pliku.

Opis struktury zbioru SIMC
WOJ - symbol województwa - 2 zn. C
POW - symbol powiatu - 2 zn. C
GMI - symbol gminy - 2 zn. C
RODZ_GMI (*) - symbol rodzaju jednostki - 1 zn. C
cyfra określa symbol rodzaju jednostki i oznacza:
  1. 1 - gmina miejska,
  2. 2 - gmina wiejska,
  3. 3 - gmina miejsko-wiejska,
  4. 4 - miasto w gminie miejsko-wiejskiej,
  5. 5 - obszar wiejski w gminie miejsko-wiejskiej,
  6. 8 - dzielnica w m.st. Warszawa,
  7. 9 - delegatury miast: Kraków, Łódź, Poznań i Wrocław
RM - rodzaj miejscowości - 2 zn. C
obecnie funkcjonuje 12 określeń rodzajowych, którym nadano dwucyfrowe symbole:
  1. 00 - część miejscowości
  2. 01 - wieś
  3. 02 - kolonia
  4. 03 - przysiółek
  5. 04 - osada
  6. 05 - osada leśna
  7. 06 - osiedle
  8. 07 - schronisko turystyczne
  9. 95 - dzielnica m. st. Warszawy
  10. 96 - miasto
  11. 98 - delegatura
  12. 99 - część miasta
MZ - występowanie nazwy zwyczajowej (0-tak,1-nie) - 1 zn. C
NAZWA - nazwa miejscowości - 56 100 zn. C
SYM - identyfikator miejscowości - 7 zn. C
  1. Każda miejscowość wiejska tzn. każda wieś, kolonia, osada, przysiółek, itp. oraz integralna część miejscowości wiejskiej, a także każde miasto i część miasta otrzymały niepowtarzalny siedmiocyfrowy identyfikator.
    Identyfikator miejscowości jest stały. Miejscowość zachowuje swój raz nadany identyfikator nawet wtedy, gdy w wyniku zmian administracyjnych zmieniła swą przynależność do gminy czy powiatu oraz gdy zmieniła swój charakter, np. nadano jej status miasta. Zmiana nazwy i rodzaju również nie wpływa na identyfikator. Identyfikator ten posiada wyłącznie identyfikujący charakter i nie spełnia funkcji informacyjnych
SYMPOD - identyfikator miejscowości podstawowej - 7 zn. C
 
- dla części miejscowości wiejskich - identyfikator miejscowości, do której dana część należy,
- dla części miast - identyfikator danego miasta (w miastach posiadających dzielnice/delegatury - identyfikator tej jednostki).
W system identyfikatorów i nazw miejscowości (SIMC) relacja identyfikatora miejscowości SYM i identyfikatora miejscowości podstawowej SYMPOD określa, czy miejscowości jest samodzielna, czy niesamodzielna,
  • SYM = SYMPOD - miejscowość samodzielna
  • SYM ≠ SYMPOD - miejscowość niesamodzielna
STAN_NA - data aktualizacji danych w systemie TERC w formacie RRRR-MM-DD - 10 zn. C

(*) - RODZ_GMI - w opisie struktury zbioru TERC element nazwany jest: RODZ.

Struktura pliku SIMC.xml

Ponieważ struktura pliku SIMC.xml została zmieniona, funkcja przetwarzający plik SIMC.xml musiała zostać dostosowana do obowiązującej struktury pliku (nazw zmienionych elementów) przetwarzanego pliku SIMC.xml.

Jeżeli chodzi o tabele to zmiana dotyczy elementu [NAZWA] określającego nazwę miejscowości dla którego zwiększono ilość znaków z 56 do 100 znaków. W tabeli tblSIMC_Miejscowosci należy zmienić rozmiar pola [tNazwa] z 56 znaków na 100 znaków.

Plik SIMC.xml zawiera 102 940 elementów <row>, a każdy z nich zawiera 10 elementów <col>, co daje 1 029 400 pojedynczych elementów. Każdy element <col> zawiera dane (nie jest pusty).

Poniżej struktura starego pliku SIMC.xml z dnia 2015-01-01 i struktura aktualnego pliku SIMC.xml dla dwóch pierwszych elementów <row> zbioru SIMC.xml. 

<?xml version="1.0" encoding="UTF-8"?>
<teryt>
	<catalog name="SIMC" type="all" date="2015-01-01">
		<row>
			<col name="WOJ">18</col>								
			<col name="POW">16</col> 								
			<col name="GMI">13</col>								
			<col name="RODZ_GMI">2</col>						
			<col name="RM">00</col>									
			<col name="MZ">1</col>									
			<col name="NAZWA">Na Polu</col>
			<col name="SYM">0664326</col>						
			<col name="SYMPOD">0664310</col>				
			<col name="STAN_NA">2015-01-01</col>
		</row>
		<row>
			<col name="WOJ">28</col>							
			<col name="POW">06</col>							
			<col name="GMI">05</col>							
			<col name="RODZ_GMI">2</col>					
			<col name="RM">03</col>								
			<col name="MZ">1</col>								
			<col name="NAZWA">Majerka</col>
			<col name="SYM">0761615</col>					
			<col name="SYMPOD">0761609</col>			
			<col name="STAN_NA">2015-01-01</col>
		</row>

		

	</catalog>
</teryt>

<?xml version="1.0" encoding="utf-8"?>
<SIMC>
  <catalog name="SIMC" type="ALL" date="2018-01-02">
		<row>
			<WOJ>18</WOJ>								
			<POW>16</POW> 							
			<GMI>13</GMI>								
			<RODZ_GMI>2</RODZ_GMI>			
			<RM>00</RM>									
			<MZ>1</MZ>									
			<NAZWA>Na Polu</NAZWA>
			<SYM>0664326</SYM>					
			<SYMPOD>0664310</SYMPOD>		
			<STAN_NA>2018-01-02</STAN_NA>
		</row>
		<row>
			<WOJ>28</WOJ>								
			<POW>06</POW>								
			<GMI>05</GMI>								
			<RODZ_GMI>2</RODZ_GMI>			
			<RM>03</RM>									
			<MZ>1</MZ>									
			<NAZWA>Majerka</NAZWA>
			<SYM>0761615</SYM>					
			<SYMPOD>0761609</SYMPOD>		
			<STAN_NA>2018-01-02</STAN_NA>
		</row>

		

	</catalog>
</SIMC>

Struktura tabeli tblSIMC_Miejscowosci.


Znając strukturę zbioru SIMC musimy utworzyć tabelę na przyjęcie danych z pliku SIMC.xml. Struktura tabeli przedstawiona jest poniżej.

Tabela tblSIMC_Miejscowosci - struktura
Nazwa pola*) Typ Rozmiar**) Wymagane Zerowa długość Uwagi
ID_Sym Tekst 7 Tak Nie PrimaryKey
Id_Gmi Tekst 7 Tak Nie Indeks. Duplikaty (OK)
Id_RM Tekst 2 Tak Nie Indeks. Duplikaty (OK)
tMZ Tekst 1 Tak Nie  
tNazwa Tekst 56 100 Tak Nie Indeks. Duplikaty (OK)
tSymPod Tekst 7 Tak Nie  
tStan_Na Tekst 10 Tak Nie  
ID_Gmi = jest połączeniem elementów: <WOJ> & <POW> & <GMI> & <RODZ_GMI> ze zbioru SIMC

 

Uwagi dodatkowe.
Dotyczą wszystkich tabel na tej stronie.
*)   - Dla pola będącego kluczem głównym, stosuję prefiks „ID_”,
      - Klucz obcy zawsze poprzedzam prefiksem „Id_”
      - Nazwy pól nie będące kluczem głównym i kluczem obcym poprzedzam prefiksem „t”
**) Minimalna długość pola. Można użyć większego rozmiaru pola.
       MS Access nie dopełnia pól tekstowych do zadeklarowanej długości.

Tworzenie tabeli tblSIMC_Miejscowosci.

Tabelę tblSIMC_Miejscowosci możemy utworzyć korzystając z metod klasy clsTeryt ze strony Klasa clsTeryt 

Dim clsSIMC As clsTeryt
  Set clsSIMC = New clsTeryt
    With clsSIMC
      .terytDeleteTable "tblSIMC_Miejscowosci"
      .terytCreateTable "tblSIMC_Miejscowosci"
        .terytCreateField "ID_Sym", dbText, 7
        .terytCreateField "Id_Gmi", dbText, 7
        .terytCreateField "Id_RM", dbText, 2
        .terytCreateField "tMZ", dbText, 1
        .terytCreateField "tNazwa", dbText, 100
        .terytCreateField "tSymPod", dbText, 7
        .terytCreateField "tStan_Na", dbText, 10
      .terytAppendTable
      ' utwórz Indeks Primary
      .terytCreateIndex "ID_Sym", True
      ' utwórz Indeksy (duplikaty OK)
      .terytCreateIndex "Id_Gmi"
      .terytCreateIndex "Id_RM"
      .terytCreateIndex "tNazwa"
    End With
  Set clsSIMC = Nothing

Skoro mamy utworzoną nową tabelę, to możemy pobrać dane z pliku SIMC.xml i zapisać je w tabeli tblSIMC_Miejscowosci. Jak to zrobić ? Po prostu skorzystać z gotowej funkcji fXmlSimcToTable_DOM(...) zamieszczonej poniżej.

Funkcja wczytująca dane z pliku SIMC.xml do tabeli MS Access

• Stan na dzień 03 marca 2018 roku

  
Public Function fXmlSimcToTable_DOM( _
                  ByVal sFileXmlPath As String, _
                  ByVal sTblSIMC As String) As Boolean
#If pbl_fEarly = True Then
  Dim xmlDoc        As MSXML2.DOMDocument60
  Dim oNode         As MSXML2.IXMLDOMNode
  Dim oChildNode    As MSXML2.IXMLDOMNode
#Else
  Dim xmlDoc        As Object
  Dim oNode         As Object
  Dim oChildNode    As Object
#End If
Dim colNodesValue     As Collection
Dim sXPath            As String

Dim dbs               As DAO.Database
Dim rstSimc           As DAO.Recordset

Const cParser_XML     As String = "MSXML2.DOMDocument"
Const cModule_Name    As String = "fXmlSimcToTable_DOM (...)"

On Error GoTo Err_Handler

  ' Utwórz obiekt analizatora składni XML (parsera XML)
  #If pbl_fEarly = True Then
    ' Wczesne wiązanie (early binding)
    Set xmlDoc = New MSXML2.DOMDocument60
  #Else
    ' Późne wiązanie (late binding)
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
  #End If

  With xmlDoc
    .Load (sFileXmlPath)
    ' sprawdź poprawność wczytanego XML'a
    If (.parseError.errorCode <> 0) Then
      Err.Raise .parseError.errorCode, cParser_XML, .parseError.reason
    End If

    ' utwórz adres do elementów <row>
    sXPath = "/teryt/catalog/row"

    ' utwórz zmienną obiektową  odwołującą się do pojedynczego elementu <row>
    Set oNode = .selectSingleNode(sXPath)

    Set dbs = CurrentDb
    Set rstSimc = dbs.OpenRecordset(sTblSIMC, dbOpenDynaset, dbAppendOnly)

    Do Until oNode Is Nothing
      ' utwórz tymczasową kolekcję colNodesValue
      Set colNodesValue = New Collection
        ' przejdź po elementach <row> i zapisz wartości poszczególnych elementów do tabeli
        For Each oChildNode In oNode.childNodes
          ' i wypełnij tymczasową kolekcję colNodesValue danymi:
          colNodesValue.Add oChildNode.Text, oChildNode.Attributes(0).Text
        Next

        rstSimc.AddNew
          With colNodesValue
            rstSimc!ID_Gmi = .Item("WOJ") & .Item("POW") & .Item("GMI") & .Item("RODZ_GMI")
            rstSimc!Id_RM = .Item("RM")
            rstSimc!tMZ = .Item("MZ")
            rstSimc!tNazwa = .Item("NAZWA")
            rstSimc!ID_Sym = .Item("SYM")
            rstSimc!tSymPod = .Item("SYMPOD")
            rstSimc!tStan_Na = .Item("STAN_NA")
          End With
        rstSimc.Update
      ' utwórz zmienną obiektową  odwołującą się do natępnego elementu <row>
      Set oNode = oNode.nextSibling
    Loop
  End With

  fXmlSimcToTable_DOM = True

Exit_Here:
    ' zniszcz zmienne obiektowe
    If Not (rstSimc Is Nothing) Then rstSimc.Close
    Set rstSimc = Nothing
    Set dbs = Nothing

    Set colNodesValue = Nothing
    Set oNode = Nothing
    Set oChildNode = Nothing
    Set xmlDoc = Nothing
  Exit Function

Err_Handler:
  MsgBox "Błąd nr " & Err.Number & vbNewLine & _
          Err.Description & vbNewLine & _
          "Źródło: " & Err.Source & vbNewLine & _
          "Moduł: " & cModule_Name
  Resume Exit_Here

End Function

Czas przetwarzania przez funkcję fXmlSIMCToTable_DOM (...) pliku SIMC.xml, wielkości 28 MB, zawierającego 103 940 elementów <row> w sumie ponad 1 000 000 elementarnych danych wynosi:

  • ok. 2260 sekund dla „wczesnego wiązania” (ok. 40 minut)
  • ok. 3900 sekund dla „późnego wiązania” (ok. 65 minut)

Testowałem zapis pierwszych 10 000 rekordów do tabeli MS Access i wynik pomnożyłem przez 10. Co prawda, plik SIMC.xml wczytujemy jednokrotnie, ale czekać na przetworzenie pliku ok. 1 godziny to trochę za dużo.

Chcąc się upewnić, że w miarę poprawnie napisałem funkcję fXmlSIMCToTable_DOM (...), znalazłem na stronie Dariusza Żelazko Import pliku xml do bazy MyAQL za pomocą metody LOAD XML, że czas przetwarzania pliku ULIC.xml o wielkości 80 MB trwał ok. 6 godzin.

Ulic do bazy MySQL

Nie jest tak źle. U mnie 100 000 rekordów w 1 godzinę, więc przetworzenie pliku ULIC.xml o podobnej strukturze, zawierający ok. 270 000 rekordów zajmie ok. 2 do 3 godzin.

Wydaje mi się, że wniosek może być tylko jeden. Sposób przetwarzania dużych plików *.xml z wykorzystaniem biblioteki Microsoft XML,v6.0 nie zdaje egzaminu i należy z niej zrezygnować na rzecz czegoś innego☺.

Jeżeli zrezygnujemy całkowicie z obiektu "MSXML2.DOMDocument", zyskamy ok. 8 sekund na wczytanie pliku, co nie rekompensuje wygody jaką daje metodą Load obiektu "MSXML2.DOMDocument", m.in. konwersja zawartości pliku na aktualną stronę kodową oraz obsługa ewentualnych błędów (nieprawidłowa ścieżka do pliku, uprawnienia do pliku, nieprawidłowa struktury pliku XML itp.).

Znak Informacja dodatkowa Wydaje mi się, że wniosek może być tylko jeden. Sposób przetwarzania dużych plików *.xml z wykorzystaniem biblioteki Microsoft XML,v6.0 nie zdaje egzaminu i należy ograniczyć jej zastosowanie wyłącznie do wczytania pliku *.xml, a przetwarzanie wczytanego tekstu zrobić za pomocą VBA☺.

• VBA. Nowa funkcja wczytująca plik SIMC.xml do tabeli MS Access


By sobie ograniczyć kłopoty z dostępem do biblioteki obiektów Microsoft XML,v6.0, skorzystam z „późnego wiązania”. Po wczytaniu plikuSIMC.xml metodą Load, źródłowy .xml rozdzielimy na poszczególne linie, względem znaku końca linii za pomocą funkcji Split 

Split(expression[, delimiter[, limit[, compare]]]) 

zwracającej indeksowaną od zera jednowymiarową tablicę, zawierającą podciągi, które były rozdzielone, w wejściowym ciągu znaków expression, separatorem delimiter . 

  ' Utwórz obiekt analizatora składni XML (parsera XML)
  Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    With xmlDoc
      .Load (sFileXmlPath)
      ' sprawdź poprawność wczytanego XML'a
      If (.parseError.errorCode <> 0) Then
        Err.Raise .parseError.errorCode, cParser_XML, .parseError.reason
      End If
      ' rozdziel wczytany tekst .xml na elementy (linie) względem znaku końca linii
      aLines() = Split(.xml, vbNewLine, , vbBinaryCompare)

    End With
  ' obiekt "MSXML2.DOMDocument" jest już nam niepotrzebny
  Set xmlDoc = Nothing
 

i dalej przetwarzać elementy tablicy aLines() metodami VBA, za pomocą dodatkowej funkcji własnej Function getTagValue(...), korzystającej z dwóch funkcji VBA:

InStr([start, ]string1, string2[, compare])

- zwracającej pozycję pierwszego wystąpienia szukanego ciągu string2 w ciągu wejściowym string1

Mid$(string, start[, length])

- zwracającą określoną liczbę znaków (length) z ciągu wejściowego string począwszy od pozycji start

Czas najwyższy pokazać funkcję zapisującą dane z pliku SIMC.xml do tabeli MS Access metodą mieszaną:
• „późne wiązanie” obiektu "MSXML2.DOMDocument" i wczytanie pliku
• przetwarzanie wczytanego tekstu za pomocą funkcji VBA i zapis danych do tabeli.   

Public Function fXmlSimcToTable_VBA( _
                  ByVal sFileXmlPath As String, _
                  ByVal sTblSIMC As String) As Boolean

Dim xmlDoc          As Object ' MSXML2.DOMDocument60
Dim dbs             As DAO.Database
Dim rstSimc         As DAO.Recordset

Dim aLines()        As String
Dim i               As Long

Const cParser_XML   As String = "MSXML2.DOMDocument"
Const cModule_Name  As String = "Function fXmlSimcToTable_VBA (...)"

On Error GoTo Err_Handler

  ' Utwórz obiekt analizatora składni XML (parsera XML)
  Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    With xmlDoc
      .Load (sFileXmlPath)
      ' sprawdź poprawność wczytanego XML'a
      If (.parseError.errorCode <> 0) Then
        Err.Raise .parseError.errorCode, cParser_XML, .parseError.reason
      End If
      ' rozdziel wczytany tekst .xml na elementy (linie) względem znaku końca linii
      aLines = Split(.xml, vbNewLine, , vbBinaryCompare)
    End With
  ' obiekt "MSXML2.DOMDocument" jest już nam niepotrzebny
  Set xmlDoc = Nothing

  Set dbs = CurrentDb
  Set rstSimc = dbs.OpenRecordset(sTblSIMC, dbOpenDynaset, dbAppendOnly)

    ' nie przetwarzaj trzech pierwszych i dwóch ostatnich linii
    For i = LBound(aLines) + 3 To UBound(aLines) - 5 Step 12
      With rstSimc
        ' zapisz poszczególne wartości elementów składowych <row> do tabeli
        .AddNew
          ' ID_Gmi = WOJ & POW & GMI & RODZ_GMI
          !ID_Gmi = getTagValue(aLines(i + 1)) & _
                    getTagValue(aLines(i + 2)) & _
                    getTagValue(aLines(i + 3)) & _
                    getTagValue(aLines(i + 4))
          !ID_RM = getTagValue(aLines(i + 5))
          !tMZ = getTagValue(aLines(i + 6))
          !tNazwa = getTagValue(aLines(i + 7))
          !Id_Sym = getTagValue(aLines(i + 8))
          !tSymPod = getTagValue(aLines(i + 9))
          !tStan_Na = getTagValue(aLines(i + 10))
        .Update
      End With
    Next

  fXmlSimcToTable_VBA = True

Exit_Here:
    ' zniszcz zmienne obiektowe
    If Not (rstSimc Is Nothing) Then rstSimc.Close
    Set rstSimc = Nothing
    Set dbs = Nothing
  Exit Function

Err_Handler:
  MsgBox "Błąd nr " & Err.Number & vbNewLine & _
          Err.Description & vbNewLine & _
          "Źródło: " & Err.Source & vbNewLine & _
          "Moduł: " & cModule_Name
  Resume Exit_Here

End Function

Pomocnicza funkcja getTagValue (...) zwracająca wartości elementów pliku .xml

Private Function getTagValue( _
                  ByRef sLineText As String) As String
Dim lStart              As Long
Dim lEnd                As Long
Const cStartText        As String = ">"
Const cEndText          As String = "<"

Const cModule_Name      As String = "Function getTagValue (...)"
Const ERR_NOT_NODE      As Long = vbObjectError + 500
Const ERR_NOT_NODE_DSCR As String = "Nie można znaleźć węzła."

  lStart = InStr(1, sLineText, cStartText, vbBinaryCompare)

  If lStart = 0 Then
    Err.Raise ERR_NOT_NODE, cModule_Name, ERR_NOT_NODE_DSCR
  End If

  lEnd = InStr(lStart + 1, sLineText, cEndText, vbBinaryCompare)
  ' Pusty element
  If lEnd = 0 Then Exit Function

  ' pobierz wartość elementu
  lStart = lStart + 1
  getTagValue = Mid$(sLineText, lStart, lEnd - lStart)

End Function

Mogę powiedzieć jeszcze raz: Jak na razie, nie ma się chyba do czego przyczepić ☺. Czas przetwarzania pliku SIMC.xml przez funkcję fXmlSimcToTable_VBA (...), wynosi ok. 22 sekund, co w porównaniu do 40 minut, dla funkcji fXmlSimcToTable_DOM(...) to przepaść.
Funkcja fXmlSimcToTable_DOM(...) korzystająca tylko i wyłącznie z parsera "MSXML2.DOMDocument" jest ok. 100x wolniejsza.

  • Całkowity czas przetwarzania pliku SIMC.xml wynosi ok. 22 sekundy
  • Załadowanie i sprawdzenie pliku SIMC.xml przez parser "MSXML2.DOMDocument" wynosi ok. 4,7 sekundy
  • Rozdzielenie tekstu .xml na poszczególne linie za pomocą funkcji Split(...) wynosi ok. 5,2 sekundy
  • Przetworzenie i zapis danych do tabeli wynosi ok. 12 sekund

Ale może być znacznie gorzej podczas przetwarzania 80 MB pliku ULIC.xml, który zawiera 267 028 elementów <row>, a każdy z nich zawiera 10 elementów <col> co w\ sumie daje 2 567 028 pojedynczych elementów. O przetwarzaniu zbioru ULIC.xml i problemach z tym związanych będzie na stronie: Zbiór ULIC z rejestru TERYT