Adressregister INSPIRE Stichtag 01.10.2022 – View für Adressen erzeugen und Indizes anlegen

Das Adressregister liegetbeim BEV als GeoPackage vor. Es handelt sich also um eine sqlite-Datenbank. Der Zugriff mit QGIS, speziell die Filterung nach Attributen, stellt bisweilen eine Geduldsprobe dar. Eine Inspektion der .gpkg-Datei mit dem DB Browser for SQLite zeigt, dass es jenseits der Primärschlüssel keinen Index in der Datenbank gibt.

Mit taktisch günstig gewählten Indizes kann die Geschwindigkeit beim Zugriff deutlich erhöht werden.

Um in einer Atlas-Anwendung Hausnummern in die Karte zeichnen zu können, und eine Liste von Adressen in einem Kartenausschnitt zu erhalten, ist es notwendig die Tabellen in der .gpkg-Datei zu verknüpfen. Es sieht in der Karte schöner aus, wenn die Hausnummern nicht an der Zugangskoordinate sondern am Gebäude gerendert werden. Leider haben nicht alle Adressen ein Gebäude zugeordnet (auch wenn dieses schon vor mehr als einem Jahrzehnt errichtet wurde). Es gilt also einen entsprechenden View zu erzeugen.

Was ist eigentlich eine Adresse?

Rechtsgültige Adressen in Österreich sind aktuell in der Adressregisterverordnung 2016 (BGBl. II Nr. 51/2016) definiert und bestehen aus (hier sinngemäß widergegeben):

Straßennamen oder Straßennamen abgekürzt
daneben stehend die Orientierungsnummer (Hausnummer) und die Adressdaten des Gebäudes
darunter die Postleitzahl und
daneben der Zustellort

Diese Informationen finden sich in:

AD_ThoroughfareName.text
AD_AddressLocatorMatchingTable.addressIdentifierGeneral
AD_BuildingGeometry.buildingIdentifier
AD_PostalDescriptor.postCode
AD_AddressAreaName.text

Der eindeutige Verweis auf eine Gemeinde erfolgt entweder über den geografischen Bezug (Verschneidung mit einer Administrative Boundary) oder über die Gemeindekennzahl GKZ:

AD_Municipality.localId_GKZ bzw.
AD_Municipality.text

Verknüpft werden diese mit den Punkten aus AD_AddressGeometry und AD_BuildingGeometry über die Tabelle AD_AddressLocatorMatchingTable mit dem Attribut localId_ADRCD.

Indizes

Um den Zugriff auf den Datenbestand zügig zu gestalten sollte zumindest für die verknüpfungsrelevanten Attribute ein Index erstellt werden. Diese sind:

AD_AddressLocatorMatchingTable.localId_ADRCD
AD_AddressLocatorMatchingTable.inspireID_SKZ
AD_AddressLocatorMatchingTable.inspireID_ZO
AD_AddressLocatorMatchingTable.inspireID_PLZ
AD_AddressLocatorMatchingTable.inspireID_OKZ (nicht zwingend notwendig)
AD_AddressLocatorMatchingTable.inspireID_GKZ (nicht zwingend notwendig)
AD_AddressAreaName.inspireId_ZO
AD_AreaName.inspireId_OKZ (nicht zwingend notwendig)
AD_Municipality.inspireId_GKZ (nicht zwingend notwendig)
AD_Municipality.localId_GKZ (nicht zwingend notwendig)
AD_Municipality.text (nicht zwingend notwendig)
AD_PostalDescriptor.inspireId_PLZ
AD_ThoroughfareName.inspireId_SKZ
AD_AddressGeometry.localId_ADRCD
AD_BuildingGeometry.localId_ADRCD

CREATE INDEX "index_AD_AddressLocatorMatchingTable_localId_ADRCD" ON "AD_AddressLocatorMatchingTable" (
	"localId_ADRCD"
);
CREATE INDEX "index_AD_AddressLocatorMatchingTable_inspireId_SKZ" ON "AD_AddressLocatorMatchingTable" (
	"inspireId_SKZ"
);
CREATE INDEX "index_AD_AddressLocatorMatchingTable_inspireId_ZO" ON "AD_AddressLocatorMatchingTable" (
	"inspireId_ZO"
);
CREATE INDEX "index_AD_AddressLocatorMatchingTable_inspireId_PLZ" ON "AD_AddressLocatorMatchingTable" (
	"inspireId_PLZ"
);
CREATE INDEX "index_AD_AddressLocatorMatchingTable_inspireId_OKZ" ON "AD_AddressLocatorMatchingTable" (
	"inspireId_OKZ"
);
CREATE INDEX "index_AD_AddressLocatorMatchingTable_inspireId_GKZ" ON "AD_AddressLocatorMatchingTable" (
	"inspireId_GKZ"
);

CREATE INDEX "index_AD_AddressAreaName_inspireId_ZO" ON "AD_AddressAreaName" (
	"inspireId_ZO"
);

CREATE INDEX "index_AD_AreaName_inspireId_OKZ" ON "AD_AreaName" (
	"inspireId_OKZ"
);

CREATE INDEX "index_AD_Municipality_inspireId_GKZ" ON "AD_Municipality" (
	"inspireId_GKZ"
);

CREATE INDEX "index_AD_Municipality_localId_GKZ" ON "AD_Municipality" (
	"localId_GKZ"
);

CREATE INDEX "index_AD_Municipality_text" ON "AD_Municipality" (
	"text"
);

CREATE INDEX "index_AD_PostalDescriptor_inspireId_PLZ" ON "AD_PostalDescriptor" (
	"inspireId_PLZ"
);

CREATE INDEX "index_AD_ThoroughfareName_inspireId_SKZ" ON "AD_ThoroughfareName" (
	"inspireId_SKZ"
);

CREATE INDEX "index_AD_AddressGeometry_localId_ADRCD" ON "AD_AddressGeometry" (
	"localId_ADRCD"
);

CREATE INDEX "index_AD_BuildingGeometry_localId_ADRCD" ON "AD_BuildingGeometry" (
	"localId_ADRCD"
);

AddressLocatorMatchingTable_flat

Entsteht durch die Verknüpfung von AD_AddressLocatorMatchingTable mit

AD_ThoroughfareName
AD_AddressAreaName
AD_PostalDescriptor
AD_AreaName
AD_Municipality

CREATE VIEW AddressLocatorMatchingTable_flat
AS
SELECT
	AD_AddressLocatorMatchingTable.localId_ADRCD,
	AD_AddressLocatorMatchingTable.addressIdentifierGeneral Hausnummer,
	AD_ThoroughfareName.text Strasse,
	AD_AddressAreaName.text Zustellort,
	AD_PostalDescriptor.postCode PLZ,
	AD_AreaName.text Ortsname,
	AD_Municipality.localId_GKZ GKZ,
	AD_Municipality.text Gemeinde
FROM
	AD_AddressLocatorMatchingTable
JOIN
	AD_ThoroughfareName ON AD_ThoroughfareName.inspireId_SKZ = AD_AddressLocatorMatchingTable.inspireId_SKZ
JOIN
	AD_AddressAreaName ON AD_AddressAreaName.inspireId_ZO = AD_AddressLocatorMatchingTable.inspireId_ZO
JOIN
	AD_PostalDescriptor ON AD_PostalDescriptor.inspireId_PLZ = AD_AddressLocatorMatchingTable.inspireId_PLZ
JOIN
	AD_AreaName ON AD_AreaName.inspireId_OKZ = AD_AddressLocatorMatchingTable.inspireId_OKZ
JOIN
	AD_Municipality ON AD_Municipality.inspireId_GKZ = AD_AddressLocatorMatchingTable.inspireId_GKZ

AdressGeometry_flat

Entsteht durch die Verknüpfung von AddressLocatorMatchingTable_flat mit AD_AddressGeometry. Für das Endergebnis nicht relevant, aber für Analysen hilfreich.

CREATE VIEW AddressGeometry_flat
AS
SELECT
AD_AddressGeometry.id,
AD_AddressGeometry.geom,
AddressLocatorMatchingTable_flat.*
FROM
AD_AddressGeometry
JOIN
AddressLocatorMatchingTable_flat ON AddressLocatorMatchingTable_flat.localId_ADRCD = AD_AddressGeometry.localId_ADRCD

BuildingGeometry_flat

Entsteht durch die Verknüfung von AddressLocatorMatchingTable_flat mit AD_BuildingGeometry. Für das Endergebnis nicht relevant, aber für Analysen hilfreich.

CREATE VIEW BuildingGeometry_flat
AS
SELECT
	AD_BuildingGeometry.id,
	AD_BuildingGeometry.geom,
	AD_BuildingGeometry.buildingIdentifier,
	AddressLocatorMatchingTable_flat.*
FROM
	AD_BuildingGeometry
JOIN
	AddressLocatorMatchingTable_flat ON AddressLocatorMatchingTable_flat.localId_ADRCD = AD_BuildingGeometry.localId_ADRCD

Address_flat: Finale Adressen für die Kartografie

Für Adressen ohne Gebäude soll die Koordinate aus AD_AddressGeometry verwendet werden. Andernfalls die Koordinate aus AD_BuildingGeometry. Gibt es eine Koordinate in AD_BuildingGeometry, dann ist auch der buildingIdentifier Teil der Adresse.

CREATE VIEW Address_flat
AS
SELECT
	CASE WHEN AD_BuildingGeometry.geom IS NOT NULL THEN
		AD_BuildingGeometry.geom
	ELSE
		AD_AddressGeometry.geom
	END AS geom,
	AD_AddressGeometry.localId_ADRCD,
	AD_BuildingGeometry.localId_ADRCD_SUBCD,
	CASE WHEN AD_BuildingGeometry.buildingIdentifier IS NOT NULL THEN
		AddressLocatorMatchingTable_flat.Hausnummer || ' ' || AD_BuildingGeometry.buildingIdentifier
	ELSE
		AddressLocatorMatchingTable_flat.Hausnummer
	END AS HausUndGebNummer,
	AddressLocatorMatchingTable_flat.*
FROM
	AD_AddressGeometry
LEFT JOIN
	AD_BuildingGeometry ON AD_AddressGeometry.localId_ADRCD = AD_BuildingGeometry.localId_ADRCD
JOIN
	AddressLocatorMatchingTable_flat ON AD_AddressGeometry.localId_ADRCD = AddressLocatorMatchingTable_flat.localId_ADRCD

Damit der View als Geometrie erkannt wird muss der GeoPackage-Spezifikation noch genüge getan werden und gpkg_contents und gpkg_geometry_columns angepasst werden.

INSERT INTO gpkg_contents
	("table_name",
	"data_type", "identifier",
	"min_x",
	"min_y",
	"max_x",
	"max_y",
	"srs_id")
VALUES
	('Address_flat',
	'features',
	'Address_flat',
	(SELECT MIN(min_x) from gpkg_contents),
	(SELECT MIN(min_y) from gpkg_contents),
	(SELECT MAX(max_x) from gpkg_contents),
	(SELECT MAX(max_y) from gpkg_contents),
	(SELECT srs_id FROM gpkg_contents WHERE table_name = 'AD_AddressGeometry')
	);

INSERT INTO gpkg_geometry_columns
	("table_name",
	"column_name",
	"geometry_type_name",
	"srs_id",
	"z",
	"m")
VALUES
	('Address_flat',
	'geom',
	(SELECT geometry_type_name FROM gpkg_geometry_columns WHERE table_name = 'AD_AddressGeometry'),
	(SELECT srs_id FROM gpkg_geometry_columns WHERE table_name = 'AD_AddressGeometry'),
	0,
	0
	);

Fazit

Leider erkennt QGIS noch immer nicht das CRS (srs_id), das kann man aber bei einem eingefügten Layer auch noch nachträglich festlegen.
Unangenehmer ist die Tatsache, dass die Geometrie durch den Wahlausdruck scheinbar den View verlangsamt.

Future Work: Address_flat als tatsächliche Tabelle ggf. in ein separates .gpkg-File exportieren.

chbw's notebook

my external storage