Vorig jaar heb ik behoorlijk wat vragen gekregen over of er een tool was, een methodiek, om BAG data van het Nederlandse Kadaster in een Oracle database te krijgen voor allerlei doeleinden. Basisregistraties Adressen en Gebouwen (BAG) data wordt onder andere uitgeleverd door het Kadaster in XML bestanden waarin alle Kadaster gegevens zijn vastgelegd. Deze XML bestanden voldoen qua structuur aan een aan te vragen XML Schema. Het is behoorlijk wat XML data zoals je misschien zou verwachten. Het betreft dat ook alle gegevens over adressen en gebouwen in Nederland, aangeleverd aan het Nederlandse Kadaster. Eind vorig jaar was de gecomprimeerde XML data (zip) file rond de 1,25 Gigabyte.
Zoals op de pagina van het Kadaster wordt uitgelegd:
Wat is de BAG?
De BAG (Basisregistraties adressen en gebouwen) is onderdeel van het overheidsstelsel van basisregistraties. Gemeenten zijn bronhouders van de BAG. Zij zijn verantwoordelijk voor het opnemen van de gegevens in de BAG en voor de kwaliteit ervan. Alle gemeenten stellen gegevens over adressen en gebouwen centraal beschikbaar via de Landelijke Voorziening (BAGLV). Het Kadaster beheert de BAGLV en stelt de gegevens beschikbaar aan de diverse afnemers.
Gebruik van de BAG
De BAG kent op dit moment vier producten: BAG Web, BAG Extract, BAG Bevragen en BAG Compact (BAGLV).
BAG Web is voor iedereen inzichtelijk. BAG Extract, BAG Bevragen en BAG Compact kunnen besteld worden. Via de voorlopige voorziening BAG Viewer zijn BAG gegevens nu ook te bekijken met een kaart als ondergrond.
De BAG Viewer (demo) webapplicatie geeft je een goed gevoel wat je met de data kunt doen en/of over welke data het gaat. Probeer hem eens uit door bijvoorbeeld de raster kaart info te genereren voor je bedrijf of huis. Hieronder een BAG Viewer voorbeeld t.a.v de AMIS kantoor/adres gegevens in Nieuwegein.
Zoals gewoonlijk, als je weet hoe, is het importeren en beschikbaar maken van de genoemde BAG data in een Oracle database, helemaal niet zo moeilijk.
Importeren van Kadaster BAG data in Oracle
De hieronder genoemde methodiek maakt gebruik van de Oracle XMLDB functionaliteit in een Oracle database. Deze functionaliteit is beschikbaar in alle database varianten: Express Edition (XE), Standard Edition (SE), Enterprise Edition (EE), Personal Edition (PE), Standard One (SO), versies 9.2.0.3 en hoger. Oracle XMLDB is een “no cost” option die gratis beschikbaar is bij de aanschaf van een Oracle database en voorziet in alle mogelijkheden die je nodig hebt om XML data in een Oracle database te genereren, consumeren, te transformeren, op te slaan, etc. De Oracle database XMLDB functionaliteit ondersteunt alle W3C XML standaarden zoals onder andere: XPath, XSLT, XQuery, XQuery Update en XML Repository vereisten (ACL Security, Versioning en HTTP(s), FTP & WebDAV toegang tot de database data). Voor meer info over de mogelijkheden van Oracle XMLDB kun je o.a. hier terecht.
Het toepassen van Oracle XMLDB functionaliteit als oplossing i.p.v. bijvoorbeeld een Java oplossing buiten de database zijn:
- Oracle XMLDB functionaliteit is geschreven in C en embedded in de Oracle Database kernel, i.p.v. een Java toepassing en zal daardoor bijna altijd beter performen.
- Alle XMLDB functionaliteit wordt door Oracle onderhouden, i.p.v. de eigen gecreëerde data laad Java XML Parser toepassing, dus dat scheelt in onderhoud.
- En, zoals ik hieronder probeer aan te bewijzen, is het laden van XML data ook snel en simpel te realiseren via Oracle XMLDB functionaliteit.
Een laad voorbeeld
Voordat de data geladen wordt, is het belangrijk om goed na te denken over de te gebruiken opslag methodiek. Het in Oracle XMLDB gebruikte XMLType datatype, komt in 3 smaken: CLOB (deprecated optie vanaf 11.2), Binary XML (Securefile) en Object Relational. De whitepaper “Oracle XML DB : Choosing the Best XMLType Storage Option for Your Use Case (PDF)” op de hoofdpagina van de Oracle XMLDB site, geeft een goed overzicht van de verschillende voor en nadelen van deze 3 opslag mogelijkheden. Indien noodzakelijk kan er zowel bij XMLType Object Relational als XMLType Securefile Binary XML partitionering binnen de XML data zelf worden toegepast op basis van “locally equipartitioned” (HASH, RANGE en LIST partitioning indelingen).
In het hieronder getoonde voorbeeld, wordt van partitioning geen gebruik gemaakt, om het voorbeeld niet te complex te maken (qua DDL syntax). De DDL, voornamelijk de creatie van de view op het eind, is gebaseerd op een iets verouderd XML Schema. Met wat kleine aanpassingen in de gebruikt XPath paden werkt de code ook voor de huidige BAG data XML structuur.
Het laden gebeurt in 3 stappen, voor het gemak even met een Oracle user met de DBA rol:
- Het creëren van een Oracle directory
- Het creëren van een tabel met een XMLType Binary XML kolom
- Het laden van de data middels een INSERT statement.
De bijbehorende DDL code/statements in een SQL*Plus sessie, Oracle database versie 11.2.0.3.0.
Creatie van de Oracle directory waar zich de BAG XML data bevind (c:\temp\xml):
define XSDDIR = 'C:\temp' create or replace directory XMLDIR as '&XMLDIR/XML' /
Het creëren van de tabel waar de data in geladen wordt:
CREATE TABLE BAG_DATA (BAG xmltype) NOCOMPRESS NOLOGGING TABLESPACE USERS xmltype COLUMN BAG STORE AS SECUREFILE BINARY XML (TABLESPACE USERS NOCOMPRESS KEEP_DUPLICATES) ;
Het te gebruiken SQL statement voor de INSERT:
INSERT into BAG_DATA
(BAG)
VALUES
(XMLTYPE(bfilename('XMLDIR','9999OPR08092011-000001.xml'),NLS_CHARSET_ID('AL32UTF8')))
;
Het laden van XML kan sneller door gebruikt te maken van een SQL*Loader laadactie, maar de getoonde bfilename constructie is simpel toe te passen en waar nodig ook programmeer technisch te verwerken in een PL/SQL procedure of package.
Dat het laden snel gaat tonen de hieronder genoemde tijden (~ anderhalve seconde per file) van enkele BAG XML documenten van 20 MB per stuk, in totaal zijn er 13 files a 20 MB geladen t.b.v. dit voorbeeld):
SQL> INSERT into BAG_DATA
2 (BAG)
3 VALUES
4 (XMLTYPE(bfilename('XMLDIR','9999OPR08092011-000002.xml'),NLS_CHARSET_ID('AL32UTF8')))
5 ;
1 rij is aangemaakt.
Verstreken: 00:00:01.32
SQL> --
SQL> INSERT into BAG_DATA
2 (BAG)
3 VALUES
4 (XMLTYPE(bfilename('XMLDIR','9999OPR08092011-000003.xml'),NLS_CHARSET_ID('AL32UTF8')))
5 ;
1 rij is aangemaakt.
Verstreken: 00:00:01.30
SQL> --
SQL> INSERT into BAG_DATA
2 (BAG)
3 VALUES
4 (XMLTYPE(bfilename('XMLDIR','9999OPR08092011-000004.xml'),NLS_CHARSET_ID('AL32UTF8')))
5 ;
1 rij is aangemaakt.
Verstreken: 00:00:01.67
SQL> --
SQL> INSERT into BAG_DATA
2 (BAG)
3 VALUES
4 (XMLTYPE(bfilename('XMLDIR','9999OPR08092011-000005.xml'),NLS_CHARSET_ID('AL32UTF8')))
5 ;
1 rij is aangemaakt.
Verstreken: 00:00:01.30
SQL> --
SQL> INSERT into BAG_DATA
2 (BAG)
3 VALUES
4 (XMLTYPE(bfilename('XMLDIR','9999OPR08092011-000006.xml'),NLS_CHARSET_ID('AL32UTF8')))
5 ;
1 rij is aangemaakt.
...
...
SQL> select count(*) from BAG_VW;
COUNT(*)
----------
274237
Verstreken: 00:00:00.40
Relationeel beschikbaar maken van XML opslag in Oracle via een view
Niet iedereen is bekwaam in de XML talen zoals XPath of XQuery. Om de data te kunnen doorzoeken via, bijvoorbeeld tools zoals SQL*Developer of Toad, is het handig om een view te definiëren bovenop de XMLType kolom die deze XPath en XQuery uitvragingen al in zich heeft. Hieronder een voorbeeld waarbij alle XML Schema namespace definities al zijn opgenomen ook al worden ze niet allemaal gebruikt door de view. Er wordt gebruik gemaakt van een XMLTABLE die uitermate geschikt is om XML beschikbaar te maken in relationele vorm en zolang de XML structuur niet verandert is dit een eenmalige actie. Via de “PASSING” clausule wordt de XML kolom data aangeboden aan de XMLTABLE functie en via de COLUMNS sectie worden de XPath uitvragingen gemapped met de “identificatie”, “openbareRuimteType”, “openbareRuimteType” kolom definities van de view. De XMLTABLE functie ondersteunt XPath V2 en XQuery V1 statements.
CREATE OR REPLACE view BAG_VW
( identificatie
, openbareRuimteType
, openbareRuimteNaam
)
AS
SELECT xt1.identificatie
, xt1.openbareRuimteType
, xt1.openbareRuimteNaam
FROM BAG_DATA bd
, XMLTABLE (xmlnamespaces( 'http://www.kadaster.nl/schemas/imbag/lvc/v20090901' as "bag_LVC"
, 'http://www.kadaster.nl/schemas/bag-verstrekkingen/extract-deelbestand-lvc/v20090901' as "xb"
, 'http://www.kadaster.nl/schemas/imbag/imbag-types/v20090901' as "bagtype"
, 'http://www.kadaster.nl/schemas/imbag/nen5825/v20090901' as "nen5825"
, 'http://www.w3.org/1999/xlink' as "xlink"
, 'http://www.kadaster.nl/schemas/bag-verstrekkingen/extract-selecties/v20090901' as "selecties-extract"
, 'http://www.opengis.net/gml' as "gml"
, 'http://www.kadaster.nl/schemas/bag-verstrekkingen/extract-producten-lvc/v20090901' as "product_LVC"
)
, '/xb:BAG-Extract-Deelbestand-LVC/xb:antwoord/xb:producten/product_LVC:LVC-product/bag_LVC:OpenbareRuimte'
PASSING bd.BAG
COLUMNS
identificatie VARCHAR2(20) PATH 'bag_LVC:identificatie'
, openbareRuimteNaam VARCHAR2(20) PATH 'bag_LVC:openbareRuimteNaam'
, openbareRuimteType VARCHAR2(25) PATH 'bag_LVC:openbareRuimteType'
) xt1
;
Hierna is het mogelijk om de in de view gedefinieerde kolommen normaal via SQL uit te vragen:
SQL> set autotrace on explain SQL> set lines 170 SQL> select openbareRuimteType, count(*) 2 from bag_vw 3 group by openbareRuimteType 4 / OPENBARERUIMTETYPE COUNT(*) ------------------------- ---------- Kunstwerk 3728 Terrein 875 Weg 263046 Spoorbaan 50 Water 3053 Administratief gebied 2706 Landschappelijk gebied 779 7 rijen zijn geselecteerd. Verstreken: 00:00:00.88 Uitvoeringspan ---------------------------------------------------------- Plan hash value: 122337239 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 106K| 202M| 366 (3)| 00:00:05 | | 1 | HASH GROUP BY | | 106K| 202M| 366 (3)| 00:00:05 | | 2 | NESTED LOOPS | | 106K| 202M| 359 (1)| 00:00:05 | | 3 | TABLE ACCESS FULL| BAG_DATA | 13 | 26026 | 3 (0)| 00:00:01 | | 4 | XPATH EVALUATION | | | | | | -------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2)
Data toegang versnelling via een XMLIndex
Door een XMLDB Domain index aan te maken, een zogenaamde Structured XMLIndex, beschikbaar vanaf versie Oracle database versie 11.2, kunnen de “COST”‘s nog verder worden teruggebracht waar door het gebruikte SQL statement nog verder kan worden versneld.
SQL> CREATE INDEX XIDX_BAG_01
2 ON BAG_DATA(BAG)
3 INDEXTYPE IS XDB.XMLINDEX
4 PARAMETERS ('GROUP BAG_VIEW_GROUP
5 XMLTABLE CONTENT_BAG_SXI_TABLE
6 XMLNAMESPACES( ''http://www.kadaster.nl/schemas/imbag/lvc/v20090901'' as "bag_LVC"
7 , ''http://www.kadaster.nl/schemas/bag-verstrekkingen/extract-deelbestand-lvc/v20090901'' as "xb"
8 , ''http://www.kadaster.nl/schemas/bag-verstrekkingen/extract-producten-lvc/v20090901'' as "product_LVC"
9 )
10 , ''/xb:BAG-Extract-Deelbestand-LVC/xb:antwoord/xb:producten/product_LVC:LVC-product/bag_LVC:OpenbareRuimte''
11 COLUMNS
12 identificatie NUMBER(20) PATH ''bag_LVC:identificatie''
13 , openbareRuimteNaam VARCHAR2(60) PATH ''bag_LVC:openbareRuimteNaam''
14 , openbareRuimteType VARCHAR2(38) PATH ''bag_LVC:openbareRuimteType''
15 ');
Index is aangemaakt.
Verstreken: 00:00:26.92
SQL> select openbareRuimteType, count(*)
2 from bag_vw
3 group by openbareRuimteType
4 /
OPENBARERUIMTETYPE COUNT(*)
-------------------------------------- ----------
Kunstwerk 3728
Water 3053
Terrein 875
Weg 263046
Administratief gebied 2706
Spoorbaan 50
Landschappelijk gebied 779
7 rijen zijn geselecteerd.
Verstreken: 00:00:00.13
Uitvoeringspan
----------------------------------------------------------
Plan hash value: 2870885695
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 247K| 10M| 18 (84)| 00:00:01 |
| 1 | HASH GROUP BY | | 247K| 10M| 18 (84)| 00:00:01 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 247K| 10M| 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | BAG_DATA | 13 | 156 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SYS81836_81837_RID_IDX | 19069 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| CONTENT_BAG_SXI_TABLE | 19069 | 614K| 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("BD".ROWID="SYS_ALIAS_0"."RID")
Note
-----
- dynamic sampling used for this statement (level=2)
De index brengt de “COST”en terug van 266 naar 18 voor het gebruikte SQL statement (doorlooptijd 0.13 seconden).
Ik hoop dat ik heb kunnen aantonen hoe simpel het laden van XML data in een Oracle XMLDB omgeving is als het gaat om, bijvoorbeeld de Kadaster BAG data. Natuurlijk komt er uiteindelijk toch nog wel iets meer bij kijken. Er moet nagedacht worden over de juiste opslag methodiek, creaties van misschien meerdere views en de bijbehorende ondersteunende (XML)Indexen. Het investeren in een goed design is echter nooit weggegooide tijd en levert op een later tijdstip veel voordeel op. Op de Oracle XMLDB hoofdpagina vind je nog meer informatie (en voorbeelden) die je verder helpen. Op www.xmldb.nl zijn ook veelvuldige gebruikte “HOWTO”‘s aanwezig die je ondersteunen bij de leercurve van het begrijpen hoe Oracle XMLDB in elkaar zit.
De data, de voorbeelden hier gebruikt, zijn uitgevoerd op een standaard OTN Developer Days Virtualbox omgeving. De in deze door mij gebruikte Virtualbox omgeving gedefinieerde database, maakt gebruik van de volgende default instellingen: het Operationele Systeem 1GB en de database claimt hiervan 400 MB als cache.
