Der vorherige Artikel, in dem FIAS-Adressen und -Funktionen fĂŒr die Arbeit mit ihnen in der PostgreSQL-Umgebung beschrieben wurden, erregte bei einem kleinen Teil der Leser Interesse.
Daher ist es sinnvoll, Ă€hnliche Funktionen in PL / pgSQL zu beschreiben, um mit einer Liste von FIAS-HĂ€usern zu arbeiten, die in eine Datenbank geladen sind, in der PostgreSQL ausgefĂŒhrt wird.
Die erste HĂ€lfte des Artikels enthĂ€lt Kommentare zur Implementierung von Funktionen. Im zweiten Schritt Quellcodes von Funktionen sowie Skripte zum Erstellen einer Tabelle mit FIAS-HausdatensĂ€tzen sowie zum Laden von Daten aus einer CSV-Datei in diese Tabelle. FĂŒr diejenigen Leser, die nur an den Ausgangstexten interessiert sind, empfehlen wir, sofort mit dem Anhang fortzufahren.
Dieser Artikel ist eng verwandt mit den Materialien der Artikelserie âFIAS-Adressen in der PostgreSQL-Umgebungâ (
Anfang ,
Fortsetzung 1 ,
Fortsetzung 2 ,
Ende ).
Stammbaum zu Hause
Beginnen wir mit einem Beispiel.
Das Aufrufen der f-Funktion stf_Houses_AddressObjectTree ('42301ab8-9ead-4f8e-8281-e64f2769a254') fĂŒhrt zu der folgenden Liste von EintrĂ€gen.
Tabelle 1. Das Ergebnis der Funktion.
Bei nĂ€herer Betrachtung stellen Sie möglicherweise fest, dass die Elementkennung ( HOUSEGUID ) âd. 1, GebĂ€ude 2, S. 26 âgingen sechs Aufzeichnungen ein:
- drei ĂŒbergeordnete DatensĂ€tze mit adressbildenden Elementen: ĂŒber Region, Stadt und StraĂe;
- drei DatensÀtze mit den Merkmalen der Hausnummer: Hausnummer, GebÀudenummer und GebÀudenummer.
Die Funktion verfĂŒgt ĂŒber einen weiteren optionalen Parameter - das Ablaufdatum des Datensatzes ( EndDate ), mit dem Sie den Stammbaum nicht nur des aktuellen Datensatzes ĂŒber das Haus, sondern auch bereits veralteter DatensĂ€tze anzeigen können.
Der vollstÀndige Text der Funktion ist im Anhang im Abschnitt Erstellen der Funktion fstf_Houses_AddressObjectTree angegeben .
Von Anfang an
Wenn Sie wissen, wie der FIAS-Haustisch angeordnet ist, kann dieser Abschnitt ĂŒbersprungen werden.
FIAS-HĂ€user ( HOUSES ) sind eine untergeordnete Liste fĂŒr die Liste der adressgenerierenden Elemente von FIAS ( ADDROBJ ). Jeder Hauslisteneintrag bezieht sich durch den Wert des Felds AOGUID auf ein FIAS-Adress generierendes Element. Um festzustellen, in welcher StraĂe und an welchem ââOrt sich das Haus befindet, mĂŒssen Sie den entsprechenden Datensatz mit derselben Listenkennung ADDROBJ anhand des AOGUID- Werts des HOUSES- Datensatzes finden.
Trotz der Ă€uĂerlich einfachen Interaktion zwischen der Liste der HĂ€user und der Liste der adressbildenden Elemente in ihrer Interaktion erschweren Merkmale die Implementierung von Funktionen in HOUSES .
Erstens bezieht sich jeder Datensatz der Liste der HĂ€user durch die Kennung AOGUID auf eine Gruppe von adressbildenden Elementen, von denen eines relevant ist.
Zweitens gibt es mehrere EintrÀge in der FIAS-Liste mit denselben Hausnummernmerkmalen: Hausnummer, GebÀudenummer, GebÀudenummer.
Drittens wird die Aufzeichnung des Hauses nicht immer von der Aufzeichnung der StraĂe des Dorfes geerbt.
Aber das Wichtigste zuerst.
FĂŒr weitere Ăberlegungen zur Speicherung von Informationen ĂŒber HĂ€user im FIAS reicht es aus, uns auf 4 Tabellen (DBF-Dateien) zu beschrĂ€nken:
- ADDROBJ - Liste der adressbildenden Elemente;
- HĂUSER - Liste der HĂ€user;
- STRSTAT - ein Verzeichnis struktureller Merkmale;
- ESTSTAT - ein Verzeichnis der Eigentumszeichen.
ADDROBJ wurde in einer frĂŒheren Veröffentlichung, âFIAS-Adressen in PostgreSQLâ , ausfĂŒhrlich erörtert , sodass seine Funktionen hier genau so ausfĂŒhrlich behandelt werden, wie es zur Beschreibung der Eigenschaften von HĂ€usern erforderlich ist.
Tabelle 2. Geschichte des Hauses âKrasnojarsker Territorium, Bezirk Taimyr Dolgan-Nenetsky, Dudinka, Ul. Dudinskaya, 1
Wie aus der Tabelle hervorgeht, haben Aufzeichnungen ĂŒber die Geschichte des Hauses im Gegensatz zu den adressbildenden Objekten keine besonderen Relevanzzeichen. Der Datensatz mit dem Ă€ltesten Enddatum des Zeitraums, der gröĂer als das aktuelle ist, ist relevant. Bisher sind aktuelle Hausaufzeichnungen mit dem Datum 06.06.2079 gekennzeichnet. Alle anderen Aufzeichnungen ĂŒber das Haus gelten als historisch, und das Start- und Enddatum kennzeichnen den Zeitraum der Relevanz jeder Aufzeichnung.
Die FIAS-Hausliste enthĂ€lt keine Zeiger auf die vorherigen und nĂ€chsten DatensĂ€tze ĂŒber das Haus. Daher wird die Reihenfolge der Aufzeichnungen von der tatsĂ€chlichen Tiefe in die Geschichte des Hauses durch das abnehmende Enddatum und darĂŒber hinaus das Startdatum des Zeitraums bzw. EndDate und StartDate bestimmt .
SELECT * FROM fias_Houses h WHERE h.HOUSEGUID='2fd64ecd-4b4b-4fc8-bd15-4a4a2f310b21' ORDER BY h.ENDDATE DESC,h.STARTDATE DESC;
Aufmerksamer Leser mit Blick auf Abb. 1, ich habe mir wahrscheinlich die Frage gestellt: Warum werden Nachschlagewerke mit Zeichen von Struktur und Eigentum erwĂ€hnt? FIAS verwendet ĂŒber 10 dieser Verzeichnisse. Warum werden diese beiden Verzeichnisse hervorgehoben?
Die Antwort wird viele ĂŒberraschen - aus Sicht der âFIAS-Logikâ wird die Adresse des Hauses nicht vollstĂ€ndig durch die StraĂe, das Haus, das GebĂ€ude und die GebĂ€udenummern identifiziert. Der Begriff âFIAS-Logikâ wurde in der Antwort eines Mitarbeiters des Bundessteuerdienstes auf meine Frage verwendet, warum die Liste der HĂ€user im Krasnojarsker Territorium ĂŒber 250 gepaarte Adressen von HĂ€usern enthĂ€lt. Dieselbe Antwort besagt, dass die Eindeutigkeit des Datensatzes durch die Werte AOGUID, HOUSENUM, BUILDNUM, STRUCNUM, STRSTATUS, ESTSTATUS bereitgestellt wird.
Mit anderen Worten, um ein Objekt zu finden, reicht es nicht aus, den Ort, die StraĂe und die Hausnummer zu kennen. Sie mĂŒssen auch wissen:
- "Besitz" ist oder "Wohneigentum";
- Der Status dieses Objekts ist definiert oder nicht definiert.
- usw.
So sieht ein Beispiel aus der allgemeinen Liste der FIAS-HĂ€user mit doppelten Adressen aus.
Die Tatsache, dass verschiedene Objekte dieselbe Adresse haben, ist nicht ĂŒberraschend. Das GebĂ€ude und das Land darunter; Haus, Garage, Badehaus fĂŒr einen Besitzer. Sie haben alle die gleiche Adresse. FIAS ist jedoch ein Adressregister, d.h. Liste der Adressen. Daher ist es natĂŒrlich zu erwarten, dass Adressen darin eindeutig sind und nicht GebĂ€ude, Strukturen, Strukturen.
Das heiĂt, Die Liste der FIAS-HĂ€user aus der Liste der Hausadressen begann sich in Richtung der Liste der ErdgebĂ€ude zu entwickeln. Und FIAS-Benutzer mĂŒssen dies berĂŒcksichtigen.
Jeder kann das Vorhandensein von HĂ€usern mit doppelten Adressen ĂŒberprĂŒfen, indem er eine SELECT-Anweisung Ă€hnlich der folgenden ausfĂŒhrt. Gleichzeitig kann die Funktion fsfn_Houses_TreeActualName nicht verwendet werden, weil Es wird nur verwendet, um die Anzahl der Spalten im Ergebnis zu reduzieren. Es ist nicht erforderlich, die Verzeichnisse fias_StructureStatus (analog zu STRSTAT) und fias_EstateStatus (analog zu ESTSTAT) als zu verwenden Die ausgeprĂ€gte Wirkung lĂ€sst sich auch an den Codes der Struktur- und Besitzzeichen nachvollziehen.
Operator-Quellcode SELECT fsfn_Houses_TreeActualName(h.AOGUID,h.HOUSEGUID),h.HOUSEGUID,str.StructureStatusName,est.EstateStatusName FROM fias_Houses h INNER JOIN (SELECT AOGUID,HOUSENUM,BUILDNUM,STRUCNUM,COUNT(*) FROM fias_Houses h WHERE EndDate =TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS') GROUP BY AOGUID,HOUSENUM,BUILDNUM,STRUCNUM HAVING COUNT(*)>1) hg ON h.AOGUID=hg.AOGUID AND h.HOUSENUM=hg.HOUSENUM AND COALESCE(h.BUILDNUM,'')=COALESCE(hg.BUILDNUM,'') AND COALESCE(h.STRUCNUM,'')=COALESCE(hg.STRUCNUM,'') LEFT OUTER JOIN fias_StructureStatus str ON h.STRSTATUS=str.StructureStatusID LEFT OUTER JOIN fias_EstateStatus est ON h.ESTSTATUS=est.EstateStatusID WHERE h.EndDate =TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS') ORDER BY h.AOGUID,h.HOUSENUM,h.BUILDNUM,h.STRUCNUM,h.STRSTATUS,h.ESTSTATUS;
Und schlieĂlich ein weiteres Merkmal der FIAS-Home-Liste. Jeder Hausdatensatz dieser Liste enthĂ€lt einen Link zu einem adressbildenden Element, dessen Liste eine Hierarchie solcher Elemente darstellt. Auf jeder Hierarchieebene befinden sich adressbildende Elemente, die zu verschiedenen Typen gehören. Das Wurzelelement ist also die Region (in unserem Fall das Gebiet Krasnojarsk), auf der nĂ€chsten Ebene ein autonomer Okrug, eine Region oder eine Stadt mit regionaler Unterordnung. Usw. (Einzelheiten finden Sie unter "FIAS-Adressen in PostgreSQL").
Formal können Sie mit einem Hausdatensatz auf jeder Ebene auf ein Element der Hierarchie verweisen. GlĂŒcklicherweise gab es in den Daten des Krasnojarsker Territoriums keine HĂ€user, die sich auf einen Bezirk oder eine Region bezogen. Trotzdem beziehen sich nicht alle HĂ€user auf die StraĂe des Dorfes:
- 98% der FIAS-HĂ€user sind mit StraĂen in besiedelten Gebieten verbunden;
- 1,2% der HĂ€user - mit StraĂen in Gartenvereinen;
- 0,3% der HĂ€user sind Siedlungen;
- 0,5% der HĂ€user mit anderen adressierbaren Elementen.
Abb. 2.Weitergabe von Hausadressen durch EigentĂŒmer (FIAS vs Karte)
Hier wird ein Problem beschrieben, das zu einer mehrdeutigen Interpretation des Stammbaums fĂŒhrt. (Igor Leonidovich Timoshchenkov, GIS-Spezialist, Aigeo LLC, Krasnojarsk, machte mich auf dieses Problem aufmerksam.)
Das Obige zeigt, wie mehrere DatensĂ€tze zu Hause dieselbe Adresse enthalten. Was kann durch den Wunsch der Steueraufsicht erklĂ€rt werden, nicht nur Aufzeichnungen ĂŒber ein Privathaus, sondern auch ĂŒber die umliegenden GebĂ€ude zu fĂŒhren: eine Garage, eine Scheune usw. Es gibt jedoch umgekehrte Beispiele, wenn mehrere GebĂ€ude (fias_Houses) einem GebĂ€ude (Haus) mit unterschiedlichen Nummern fĂŒr dieses Haus entsprechen.
Schauen Sie sich dieses Bild an. Auf der linken Seite befindet sich ein Screenshot mit einer Karte des Dorfes, in dem sich die HĂ€user fĂŒr zwei EigentĂŒmer befinden. Dies sind gewöhnliche einstöckige HĂ€user mit zwei EingĂ€ngen. Eine Familie lebt rechts und eine andere links. Sie können sich immer noch als HĂ€user aus zwei Wohnungen vorstellen.
Schauen Sie sich jetzt die Tabelle rechts an. Darin entspricht fast jedes Haus mit zwei EigentĂŒmern 3 EintrĂ€gen. Das heiĂt, Die FIAS-Haustabelle zeigt sowohl die Adresse des einzelnen Hauses (âgest. 1â) als auch die Adressen der Teile des Hauses (âgest. 1/1â, âgest. 1/2â), die einem EigentĂŒmer gehören.
Wie funktioniert es?
Die Funktion fstf_Houses_AddressObjectTree hat zwei Versionen: mit vier oder mit zwei Parametern. In der Version der Funktion mit zwei Parametern werden die Hauskennung ( HouseGUID ) und das Ablaufdatum der Aufzeichnung (EndDate ) ĂŒbergeben. Eine Version mit vier Parametern benötigt zusĂ€tzlich eine Kennung fĂŒr das Adressgenerierungselement ( AOGUID ) und den aktuellen Status ( CurrStatus ).
Operator-Quellcode SELECT INTO v_AOGUID,v_CurrStatus h.AOGUID,CASE WHEN 0 < ALL(SELECT iao.currstatus FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid) THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid) ELSE 0 END FROM fias_Houses h INNER JOIN fias_AddressObjects ao ON h.AOGUID=ao.AOGUID WHERE h.HOUSEGUID=a_HOUSEGUID AND h.ENDDATE= COALESCE(a_ENDDATE, TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS')) ORDER BY h.ENDDATE DESC;
Eine Funktion mit weniger Parametern berechnet die Werte der fehlenden Parameter und ruft eine Funktion mit einer groĂen Anzahl von Parametern auf. Dazu wird die Kennung des adressbildenden Elements einfach aus dem entsprechenden Feld der Haustabelle (f ias_Houses ) abgerufen . Der Wert des aktuellen Status ( CurrStatus ) wird nach folgenden Regeln berechnet:
- Wenn keiner der VerlaufsdatensĂ€tze des adressbildenden Elements 0 im Feld CurrStatus enthĂ€lt, wird der Variablen v_CurrStatus der maximale Feldwert fĂŒr dieses adressbildende Element zugewiesen.
- Andernfalls wird dieser Variablen der Wert 0 zugewiesen.
Eine Funktion mit einer groĂen Anzahl von Parametern ruft zuerst die Funktion fstf_AddressObjects_AddressObjectTree auf , die die ĂŒbergeordneten adressbildenden Elemente fĂŒr das Haus zurĂŒckgibt. Weitere Informationen zur Funktion fstf_AddressObjects_AddressObjectTree finden Sie im Abschnitt Pedigree des Adressbildungselements des Dokuments FIAS Addresses in PostgreSQL
.
AnschlieĂend werden die EintrĂ€ge zu den adressbildenden Elementen durch EintrĂ€ge zu den Nummern des Hauses, des GebĂ€udes und der Struktur (siehe Tabelle 1) ergĂ€nzt, die fĂŒr jedes nicht leere Feld zur Nummer des Hauses, des GebĂ€udes und der Struktur erstellt werden.
Damit alle AusgabedatensĂ€tze dieselbe Struktur haben und nicht ohne einen gewissen Anteil an Foppiness, werden die Werte der Felder Codeebene ( AOLevel ), aktueller Status ( CurrStatus ) und Relevanzstatus ( ActStatus ) kĂŒnstlich im Funktionskörper erstellt.
Der Code fĂŒr die Ebene des Hauses (GebĂ€ude, Struktur) ist immer auf 8 festgelegt (siehe das Nachschlagewerk âEbenen adressierbarer Objekteâ aus dem FIAS-Dokument â Informationen zur Zusammensetzung von Informationenâ ).
Der Relevanzstatus wird auf 1 gesetzt, wenn das Ablaufdatum des Datensatzes ( EndDate ) 06.06.2079 ist , andernfalls auf 0.
CurrStatus - Feldwerte sind komplizierter. Mit seinen Werten werden zwei Aufgaben gleichzeitig gelöst: Die Kennung jeder Version des Datensatzes ĂŒber das adressbildende Element wird gesetzt und das Vorzeichen fĂŒr die Relevanz des Datensatzes zugewiesen. Daher enthĂ€lt der letzte aktuelle Datensatz zu einem Element in diesem Feld den Wert 0 , und alle historischen DatensĂ€tze sind in der Reihenfolge ihres Auftretens nummeriert - "1" ist der frĂŒheste Datensatz, der zeitlich folgt - "2" usw. Die Reihenfolge der Zuweisung von Werten zum Feld CurrStatus wird in der Veröffentlichung FIAS-Adressen in PostgreSQL ausfĂŒhrlicher beschrieben.
Spoiler Ăberschrift SELECT h.AOGUID, h.HOUSEGUID, h.HOUSENUM, h.BUILDNUM, h.STRUCNUM, h.ENDDATE, CASE WHEN COALESCE(h.ENDDATE, TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS')) =TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS') THEN 0 ELSE RANK() OVER (PARTITION BY h.AOGUID, h.HOUSEGUID ORDER BY h.ENDDATE ASC) END AS HouseCurrStatus, CASE WHEN COALESCE (h.ENDDATE, TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS')) =TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS') THEN 1 ELSE 0 END AS HouseActStatus FROM fias_Houses h INNER JOIN fias_AddressObjects ao ON h.AOGUID=ao.AOGUID WHERE h.AOGUID=a_AOGUID AND h.HOUSEGUID=a_HOUSEGUID AND h.ENDDATE= COALESCE(a_ENDDATE, TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS')) ORDER BY h.ENDDATE DESC;
Volle Hausadresse
Die Hauptidee der Funktion fsfn_Houses_TreeActualName besteht darin, die in einer Zeile verbundene Hausnummer zusammen mit den Namen aller ihrer Vorfahren zurĂŒckzugeben - adressbildende Elemente.
Lassen Sie beispielsweise die Stammbaumfunktion (fstf_Houses_AddressObjectTree) die folgende Werteliste zurĂŒckgeben.
Tabelle 4. Das Ergebnis der Funktion fstf_Houses_AddressObjectTree ('0c0d670f-097c-4e1d-bcac-9e9b22fb1b99')
Dann sollte fsfn_Houses_TreeActualName ('0c0d670f-097c-4e1d-bcac-9e9b22fb1b99') zurĂŒckkehren: â Mr. Krasnoyarsk, 34A St. Lazo, bld. 6, S. 17 â.
Die Funktion fsfn_Houses_TreeActualName kann als Aggregatfunktion STRING_AGG ĂŒber das Ergebnis einer Funktion vereinfacht werden, die zu Hause einen Stammbaum zurĂŒckgibt.
Die betreffende Funktion verfĂŒgt ĂŒber einen weiteren optionalen Parameter - ein Array von Masken ( a_MaskArray ), mit denen Sie nicht alle Elementnamen, sondern nur die benötigten in das Ergebnis aufnehmen können.
Tabelle 5. Liste der Funktionsmasken.
Textversion der Tabelle| Wert | Hinweis |
|---|
| {HS} | Maske - Hausnummer |
| {BY} | Maske - Fallnummer |
| {BG} | Maske - GebÀudenummer |
| {ST} | Maske - StraĂe |
| {ZC} | Maske - Postleitzahl |
| {DT} | Maske - Stadtgebiet |
| {LP} | Maske - untergeordnete Stadt |
| {LM} | Maske - die Hauptsiedlung |
| {TP} | Maske - Region des Verbandsgegenstandes |
| {TM} | Maske - Thema des Bundes (Region) |
| {CY} | Maske - Land |
Siehe auch den Abschnitt â
VollstĂ€ndiger Name des adressbildenden Elementsâ der Publikation âFIAS-Adressen in PostgreSQL â.
Der Funktionstext finden Sie im Anwendungsabschnitt â
Erstellen der Funktion fsfn_Houses_TreeActualName â.
FIAS Home Suche
Die Funktion fstf_Houses_SearchByName dient zum Durchsuchen der Adressen von FIAS-HĂ€usern anhand ihrer Nummern und der Namen adressbildender Elemente. DarĂŒber hinaus kann die Suche nicht nur nach dem Namen und Typ des aktuellen Elements durchgefĂŒhrt werden, sondern auch nach den Namen und Typen eines oder zweier seiner nĂ€chsten Vorfahren.
Schauen wir uns einige Beispiele an. Und zunÀchst finden wir alle HÀuser mit der Nummer "220".
Tabelle 6. Ergebnis der Funktion fstf_Houses_SearchByName ('220')
Im Gegensatz zur Funktion der Suche nach adressbildenden Elementen ( fstf_AddressObjects_SearchByName ) enthĂ€lt das Ergebnis dieser Funktion nicht den Effekt des "Schwimmens" durch die Ebenen adressbildender Elemente. Der erste Parameter der Funktion enthĂ€lt immer das Suchmuster fĂŒr die Hausnummer, der zweite - die GebĂ€udenummer, die dritte GebĂ€udenummer.
Ăndern Sie nun die Anfrage. Wir finden alle HĂ€user adressbildender Elemente, deren Nummer die Nummer â1â enthĂ€lt, und das Wort âKrasnojarskâ erscheint in den Namen.
Tabelle 7. Das Ergebnis der Funktion fstf_Houses_SearchByName ('1', NULL, NULL, 'Krasnojarsk')
Der Zweck der verbleibenden Parameter stimmt genau mit dem Zweck der Parameter der Suchfunktion der adressgenerierenden Elemente (fstf_AddressObjects_SearchByName) ĂŒberein.
Der Text der Funktion ist im Anwendungsabschnitt â Erstellen der Funktion fstf_Houses_SearchByName â angegeben.
.
Wie funktioniert es?
Die Implementierung von fstf_Houses_SearchByName Ă€hnelt in vielerlei Hinsicht der Implementierung der Suchfunktion fĂŒr adressgenerierende Elemente (fstf_AddressObjects_SearchByName) . Der Hauptunterschied besteht darin, dass die Suche in zwei verwandten Tabellen durchgefĂŒhrt wird, fias_Houses und fias_AddressObjects .
Die Funktion hat 9 Argumente. Die ersten drei sind die Hausnummern ( a_HouseNum ), das GebĂ€ude (a_BuildNum ) und das GebĂ€ude ( a_StrucNum ). Die verbleibenden 6 ( a_FormalName , a_ShortName , a_ParentFormalName , a_ParentShortName , a_GrandParentFormalName , a_GrandParentShortName ) stimmen vollstĂ€ndig mit den Funktionsparametern ĂŒberein.
Wenn Sie nur den Wert des Parameters âHausnummerâ festlegen, gibt die Funktion alle Adressen in der Hausnummer zurĂŒck, fĂŒr die die angegebene Sequenz ein Symbol enthĂ€lt. Wenn Sie NULL oder eine leere Zeichenfolge ("") als Hausnummer ĂŒbergeben, werden die Adressen aller HĂ€user zurĂŒckgegeben, deren Adresselemente durch eine Reihe anderer Parameter angegeben werden.
Nachwort
Dieser Abschnitt enthÀlt Empfehlungen zum Laden der Liste der FIAS-HÀuser in die Tabelle fias_Houses .
Das Laden von Daten in eine Tabelle mit HĂ€usern erfolgt Ă€hnlich wie das Laden von Daten in eine Tabelle mit adressbildenden Elementen . Nur die Quelldatei ist HOUSE99.DBF , nicht ADDROB99.DBF . Hier ist 99 die Nummer der Region (Republik, Gebiet, Territorium). FĂŒr das Gebiet Krasnojarsk ist die Quelldatei beispielsweise die Datei HOUSE24.DBF .
ZunÀchst wird das nÀchste Archiv mit dem Update von der Seite FIAS- Updates heruntergeladen. Die Datei HOUSE99.DBF wird daraus extrahiert .
.
AnschlieĂend wird die Datei HOUSE99.DBF in das CSV- Format konvertiert und bereits konvertiert. Sie wird von der COPY-Anweisung in die temporĂ€re Tabelle fias_Houses_Temp geladen .
Und schlieĂlich werden die temporĂ€ren Daten verwendet, um die Haupttabelle zu aktualisieren, d.h. In fias_Houses nicht vorhandene werden hinzugefĂŒgt und vorhandene ersetzt.
Ein Beispiel fĂŒr ein Skript zum Aktualisieren einer Haustabelle finden Sie im Abschnitt â Herunterladen von FIAS-Hausaktualisierungen in die Tabelle fias_Houses â.
App
Erstellen der Funktion fstf_Houses_AddressObjectTree
Kommentare zum Funktionsquellcode finden Sie hier .
Funktionscode BEGIN TRANSACTION; DROP FUNCTION IF EXISTS fstf_Houses_AddressObjectTree(a_AOGUID VARCHAR(36), a_HOUSEGUID VARCHAR(36),a_CurrStatus INTEGER,a_ENDDATE TIMESTAMP); CREATE OR REPLACE FUNCTION fstf_Houses_AddressObjectTree( a_AOGUID VARCHAR(36), a_HOUSEGUID VARCHAR(36), a_CurrStatus INTEGER default 0, a_ENDDATE TIMESTAMP default '2079-06-06' ) RETURNS TABLE (rtf_GUID VARCHAR(36), rtf_CurrStatus INTEGER,rtf_ActStatus INTEGER, rtf_AOLevel INTEGER,rtf_ShortTypeName VARCHAR(10),rtf_AddressObjectName VARCHAR(100)) AS $BODY$ DECLARE c_HouseAOLevel CONSTANT INTEGER:=8; c_HouseShortTypeName CONSTANT VARCHAR(10):='.'; c_BuildShortTypeName CONSTANT VARCHAR(10):='.'; c_StructShortTypeName CONSTANT VARCHAR(10):='.'; c_StatusActual CONSTANT INTEGER:=1; c_StatusNotActual CONSTANT INTEGER:=0; c_MAXENDDATE CONSTANT TIMESTAMP:=to_timestamp('2079-06-06 00:00:00', 'YYYY-MM-DD'); v_HouseActStatus INTEGER; v_HouseCurrStatus INTEGER; v_ENDDATE TIMESTAMP; v_HOUSEGUID VARCHAR(36); v_HOUSENUM VARCHAR(10); v_BUILDNUM VARCHAR(10); v_STRUCNUM VARCHAR(10); v_Return_Error Integer :=0;
Erstellen der Funktion fsfn_Houses_TreeActualName
Kommentare zum Funktionsquellcode finden Sie hier .
Funktionscode BEGIN TRANSACTION; DROP FUNCTION IF EXISTS fsfn_Houses_TreeActualName(a_AOGUID VARCHAR(36),a_HOUSEGUID VARCHAR(36),a_MaskArray VARCHAR(2)[10]) CASCADE; CREATE OR REPLACE FUNCTION fsfn_Houses_TreeActualName( a_AOGUID VARCHAR(36), a_HOUSEGUID VARCHAR(36), a_MaskArray VARCHAR(2)[10] default '{TP,LM,LP,ST,HS,BY,BG}' ) RETURNS VARCHAR(1000) AS $BODY$ DECLARE c_HouseMaskArray CONSTANT VARCHAR(2)[3]:='{HS,BY,BG}'; c_HouseNoMask CONSTANT VARCHAR(2)[1] :='{HS}'; c_BodyNoMask CONSTANT VARCHAR(2)[1] :='{BY}'; c_BuildingNoMask CONSTANT VARCHAR(2)[1] :='{BG}'; c_HouseShortTypeName CONSTANT VARCHAR(10):='.'; c_BuildShortTypeName CONSTANT VARCHAR(10):='.'; c_StructShortTypeName CONSTANT VARCHAR(10):='.'; v_ENDDATE TIMESTAMP; v_HOUSENUM VARCHAR(10); v_BUILDNUM VARCHAR(10); v_STRUCNUM VARCHAR(10); v_TreeAddressObjectName VARCHAR(1000); v_Return_Error Integer :=0;
Erstellen der Funktion fstf_Houses_SearchByName
Kommentare zum Funktionsquellcode finden Sie hier .Funktionscode BEGIN TRANSACTION; DROP FUNCTION IF EXISTS fstf_Houses_SearchByName(a_HouseNum VARCHAR(20),a_BuildNum VARCHAR(10),a_StrucNum VARCHAR(10),a_FormalName VARCHAR(150),a_ShortName VARCHAR(20),a_ParentFormalName VARCHAR(150),a_ParentShortName VARCHAR(20), a_GrandParentFormalName VARCHAR(150),a_GrandParentShortName VARCHAR(20)); CREATE OR REPLACE FUNCTION fstf_Houses_SearchByName( a_HouseNum VARCHAR(20), a_BuildNum VARCHAR(10) default NULL, a_StrucNum VARCHAR(10) default NULL, a_FormalName VARCHAR(150) default NULL, a_ShortName VARCHAR(20) default NULL, a_ParentFormalName VARCHAR(150) default NULL, a_ParentShortName VARCHAR(20) default NULL, a_GrandParentFormalName VARCHAR(150) default NULL, a_GrandParentShortName VARCHAR(20) default NULL ) RETURNS TABLE (rtf_AOGUID VARCHAR(36),rtf_HOUSEGUID VARCHAR(36),rtf_AOLevel INTEGER,rtf_HousesFullName VARCHAR(1000),rtf_HouseNum VARCHAR(20),rtf_BuildNum VARCHAR(10),rtf_StrucNum VARCHAR(10),rtf_EndDate TIMESTAMP,rtf_ShortName VARCHAR(20),rtf_FormalName VARCHAR(150), rtf_CurrStatus INTEGER,rtf_ActStatus INTEGER, rtf_ParentShortName VARCHAR(20),rtf_ParentFormalName VARCHAR(150),rtf_GrandParentShortName VARCHAR(20),rtf_GrandParentFormalName VARCHAR(150)) AS $BODY$ DECLARE c_WildChar CONSTANT VARCHAR(2)='%'; c_BlankChar CONSTANT VARCHAR(2)=' '; v_HouseNumTemplate VARCHAR(150); v_BuildNumTemplate VARCHAR(150); v_StrucNumTemplate VARCHAR(150); v_FormalNameTemplate VARCHAR(150); v_ShortNameTemplate VARCHAR(20); v_ParentFormalNameTemplate VARCHAR(150); v_ParentShortNameTemplate VARCHAR(20); v_GrandParentFormalNameTemplate VARCHAR(150); v_GrandParentShortNameTemplate VARCHAR(20);
Erstellen einer FIAS-Haustabelle fias_Houses
Skriptcode BEGIN TRANSACTION; DROP TABLE IF EXISTS fias_Houses; DROP TABLE IF EXISTS fias_EstateStatus; DROP TABLE IF EXISTS fias_StructureStatus; CREATE TABLE IF NOT EXISTS fias_Houses( HOUSEID VARCHAR(36) NOT NULL, AOGUID VARCHAR(36) NULL, HOUSEGUID VARCHAR(36) NULL, HOUSENUM VARCHAR(10) NULL, BUILDNUM VARCHAR(10) NULL, STRUCNUM VARCHAR(10) NULL, POSTALCODE VARCHAR(6) NULL, OKATO VARCHAR(11) NULL, OKTMO VARCHAR(11) NULL, IFNSFL VARCHAR(4) NULL, TERRIFNSFL VARCHAR(4) NULL, IFNSUL VARCHAR(4) NULL, TERRIFNSUL VARCHAR(4) NULL, ESTSTATUS INTEGER NULL, STATSTATUS INTEGER NULL, STRSTATUS INTEGER NULL, STARTDATE TIMESTAMP NULL, ENDDATE TIMESTAMP NULL, UPDATEDATE TIMESTAMP NULL, NORMDOC VARCHAR(36) NULL, COUNTER INTEGER NULL, CADNUM VARCHAR(50) NULL, DIVTYPE INTEGER NULL, CONSTRAINT XPKfias_Houses PRIMARY KEY ( HOUSEID )) WITH (OIDS=False); CREATE INDEX XIE1fias_Houses ON fias_Houses(AOGUID); CREATE INDEX XIE2fias_Houses ON fias_Houses(HOUSEGUID); CREATE INDEX XIE3fias_Houses ON fias_Houses(AOGUID,HOUSEGUID); CREATE INDEX XIE4fias_Houses ON fias_Houses(HOUSENUM,BUILDNUM,STRUCNUM); CREATE INDEX XIE5fias_Houses ON fias_Houses(HOUSENUM); CREATE INDEX XIE6fias_Houses ON fias_Houses(BUILDNUM); CREATE INDEX XIE7fias_Houses ON fias_Houses(STRUCNUM); COMMENT ON TABLE fias_Houses IS 'HOUSE , .'; COMMENT ON COLUMN fias_Houses.HOUSEID IS ' '; COMMENT ON COLUMN fias_Houses.AOGUID IS ' (, , ..)'; COMMENT ON COLUMN fias_Houses.HOUSEGUID IS ' '; COMMENT ON COLUMN fias_Houses.HOUSENUM IS ' '; COMMENT ON COLUMN fias_Houses.BUILDNUM IS ' '; COMMENT ON COLUMN fias_Houses.STRUCNUM IS ' '; COMMENT ON COLUMN fias_Houses.POSTALCODE IS ' '; COMMENT ON COLUMN fias_Houses.IFNSFL IS ' '; COMMENT ON COLUMN fias_Houses.TERRIFNSFL IS ' '; COMMENT ON COLUMN fias_Houses.IFNSUL IS ' '; COMMENT ON COLUMN fias_Houses.TERRIFNSUL IS ' '; COMMENT ON COLUMN fias_Houses.OKATO IS ''; COMMENT ON COLUMN fias_Houses.OKTMO IS ''; COMMENT ON COLUMN fias_Houses.ESTSTATUS IS ' '; COMMENT ON COLUMN fias_Houses.STRSTATUS IS ' '; COMMENT ON COLUMN fias_Houses.STATSTATUS IS ' '; COMMENT ON COLUMN fias_Houses.STARTDATE IS ' '; COMMENT ON COLUMN fias_Houses.ENDDATE IS ' '; COMMENT ON COLUMN fias_Houses.UPDATEDATE IS ' () '; COMMENT ON COLUMN fias_Houses.NORMDOC IS ' '; COMMENT ON COLUMN fias_Houses.COUNTER IS ' 4'; COMMENT ON COLUMN fias_Houses.CADNUM IS ' '; COMMENT ON COLUMN fias_Houses.DIVTYPE IS ' : 0 â 1 â 2 â '; CREATE TABLE IF NOT EXISTS fias_EstateStatus( EstateStatusID INTEGER NOT NULL, EstateStatusName varchar(60) NULL, EstateStatusShortName varchar(20) NULL, CONSTRAINT XPKfias_EstateStatus PRIMARY KEY (EstateStatusID)) WITH (OIDS=False); COMMENT ON TABLE fias_EstateStatus IS ' () '; COMMENT ON COLUMN fias_EstateStatus.EstateStatusID IS ' . :0 â ,1 â ,2 â ,3 â '; COMMENT ON COLUMN fias_EstateStatus.EstateStatusName IS ''; COMMENT ON COLUMN fias_EstateStatus.EstateStatusShortName IS ' '; CREATE TABLE IF NOT EXISTS fias_StructureStatus( StructureStatusID INTEGER NOT NULL, StructureStatusName varchar(60) NULL, StructureStatusShortName varchar(20) NULL, CONSTRAINT XPKfias_StructureStatus PRIMARY KEY (StructureStatusID)) WITH (OIDS=False); COMMENT ON TABLE fias_StructureStatus IS ' () '; COMMENT ON COLUMN fias_StructureStatus.StructureStatusID IS ' . :0 â ,1 â ,2 â ,3 â '; COMMENT ON COLUMN fias_StructureStatus.StructureStatusName IS ''; COMMENT ON COLUMN fias_StructureStatus.StructureStatusShortName IS ' ';
Laden Sie FIAS Home Updates in die Tabelle fias_Houses herunter
Skript-Quellcode BEGIN TRANSACTION; do $$ BEGIN DROP TABLE IF EXISTS fias_DeletedHouses_temp; DROP TABLE IF EXISTS fias_Houses_temp; DROP TABLE IF EXISTS fias_EstateStatus_temp; DROP TABLE IF EXISTS fias_StructureStatus_temp; CREATE TABLE fias_Houses_temp AS SELECT * FROM fias_Houses LIMIT 1; DELETE FROM fias_Houses_temp; CREATE TABLE fias_DeletedHouses_temp AS SELECT * FROM fias_Houses LIMIT 1; DELETE FROM fias_DeletedHouses_temp; CREATE TABLE fias_EstateStatus_temp AS SELECT * FROM fias_EstateStatus LIMIT 1; DELETE FROM fias_EstateStatus_temp; CREATE TABLE fias_StructureStatus_temp AS SELECT * FROM fias_StructureStatus LIMIT 1; DELETE FROM fias_StructureStatus_temp; COPY fias_EstateStatus_temp(EstateStatusID,EstateStatusNAME,EstateStatusShortName) FROM 'W:\Projects\Enisey GIS\DB\SourceData\ESTSTAT_20180827.csv' WITH (FORMAT csv,DELIMITER ';', ENCODING 'UTF8'); UPDATE fias_EstateStatus s SET EstateStatusNAME=t.EstateStatusNAME, EstateStatusShortName=t.EstateStatusShortName FROM fias_EstateStatus ds INNER JOIN fias_EstateStatus_temp t ON ds.EstateStatusID=t.EstateStatusID WHERE ds.EstateStatusID=s.EstateStatusID; INSERT INTO fias_EstateStatus(EstateStatusID,EstateStatusNAME,EstateStatusShortName) SELECT EstateStatusID,EstateStatusNAME,EstateStatusShortName FROM fias_EstateStatus_temp t WHERE NOT EXISTS (SELECT * FROM fias_EstateStatus os WHERE t.EstateStatusID=os.EstateStatusID); COPY fias_StructureStatus_temp(StructureStatusID,StructureStatusNAME,StructureStatusShortName) FROM 'W:\Projects\Enisey GIS\DB\SourceData\STRSTAT_20180827.csv' WITH (FORMAT csv,DELIMITER ';', ENCODING 'UTF8'); UPDATE fias_StructureStatus s SET StructureStatusNAME=t.StructureStatusNAME, StructureStatusShortName=t.StructureStatusShortName FROM fias_StructureStatus ds INNER JOIN fias_StructureStatus_temp t ON ds.StructureStatusID=t.StructureStatusID WHERE ds.StructureStatusID=s.StructureStatusID; INSERT INTO fias_StructureStatus(StructureStatusID,StructureStatusNAME,StructureStatusShortName) SELECT StructureStatusID,StructureStatusNAME,StructureStatusShortName FROM fias_StructureStatus_temp t WHERE NOT EXISTS (SELECT * FROM fias_StructureStatus os WHERE t.StructureStatusID=os.StructureStatusID); COPY fias_Houses_temp(AOGUID,BUILDNUM,ENDDATE,ESTSTATUS,HOUSEGUID,HOUSEID,HOUSENUM,STATSTATUS,IFNSFL,IFNSUL,OKATO,OKTMO,POSTALCODE,STARTDATE,STRUCNUM,STRSTATUS,TERRIFNSFL,TERRIFNSUL,UPDATEDATE,NORMDOC,COUNTER,CADNUM,DIVTYPE) FROM 'W:\Projects\Enisey GIS\DB\SourceData\HOUSE24_20180827.csv' WITH (FORMAT csv,DELIMITER ';', ENCODING 'UTF8'); UPDATE fias_Houses h SET AOGUID=t.AOGUID, BUILDNUM=t.BUILDNUM, ENDDATE=t.ENDDATE, ESTSTATUS=t.ESTSTATUS, HOUSEGUID=t.HOUSEGUID, HOUSENUM=t.HOUSENUM, STATSTATUS=t.STATSTATUS, IFNSFL=t.IFNSFL, IFNSUL=t.IFNSUL, OKATO=t.OKATO, OKTMO=t.OKTMO, POSTALCODE=t.POSTALCODE, STARTDATE=t.STARTDATE, STRUCNUM=t.STRUCNUM, STRSTATUS=t.STRSTATUS, TERRIFNSFL=t.TERRIFNSFL, TERRIFNSUL=t.TERRIFNSUL, UPDATEDATE=t.UPDATEDATE, NORMDOC=t.NORMDOC, COUNTER=t.COUNTER, CADNUM=t.CADNUM, DIVTYPE=t.DIVTYPE FROM fias_Houses dh INNER JOIN fias_Houses_Temp t ON t.HOUSEID=dh.HOUSEID WHERE h.HOUSEID=dh.HOUSEID; DELETE FROM fias_Houses h WHERE EXISTS(SELECT 1 FROM fias_DeletedHouses_temp delh WHERE delh.HOUSEID=h.HOUSEID); INSERT INTO fias_Houses(AOGUID,BUILDNUM,ENDDATE,ESTSTATUS,HOUSEGUID,HOUSEID,HOUSENUM,STATSTATUS,IFNSFL,IFNSUL,OKATO,OKTMO,POSTALCODE,STARTDATE,STRUCNUM,STRSTATUS,TERRIFNSFL,TERRIFNSUL,UPDATEDATE,NORMDOC,COUNTER,CADNUM,DIVTYPE) SELECT AOGUID,BUILDNUM,ENDDATE,ESTSTATUS,HOUSEGUID,HOUSEID,HOUSENUM,STATSTATUS,IFNSFL,IFNSUL,OKATO,OKTMO,POSTALCODE,STARTDATE,STRUCNUM,STRSTATUS,TERRIFNSFL,TERRIFNSUL,UPDATEDATE,NORMDOC,COUNTER,CADNUM,DIVTYPE FROM fias_Houses_Temp t WHERE NOT EXISTS(SELECT * FROM fias_Houses h WHERE t.HOUSEID=h.HOUSEID); DROP TABLE IF EXISTS fias_DeletedHouses_temp; DROP TABLE IF EXISTS fias_Houses_temp; DROP TABLE IF EXISTS fias_EstateStatus_temp; DROP TABLE IF EXISTS fias_StructureStatus_temp; END; $$LANGUAGE plpgsql;