L'article précédent décrivant les adresses FIAS et les fonctions permettant de les utiliser dans l'environnement PostgreSQL a suscité l'intérêt d'une petite partie des lecteurs.
Par conséquent, il est logique de décrire des fonctions similaires en PL / pgSQL pour travailler avec une liste de maisons FIAS chargées dans une base de données exécutant PostgreSQL.
La première moitié de l'article fournit des commentaires sur la mise en œuvre des fonctions. Dans le second, les codes sources des fonctions, ainsi que les scripts pour créer une table avec les enregistrements maison FIAS, ainsi que le chargement des données dans cette table à partir d'un fichier au format CSV. Pour les lecteurs qui ne sont intéressés que par les textes sources, nous suggérons de passer immédiatement à l'annexe.
Cet article est étroitement lié aux matériaux de la série d'articles «Adresses FIAS dans l'environnement PostgreSQL» (
début ,
suite 1 ,
suite 2 ,
fin ).
Arbre généalogique à la maison
Commençons par un exemple.
L'appel de la fonction f stf_Houses_AddressObjectTree ('42301ab8-9ead-4f8e-8281-e64f2769a254') entraînera la liste d'entrées suivante.
Tableau 1. Le résultat de la fonction.
En y regardant de plus près, vous remarquerez peut-être que l'identifiant de l'élément ( HOUSEGUID ) “d. 1, bldg. 2, p. 26 ”, six enregistrements ont ainsi été reçus:
- trois enregistrements de parents avec des éléments de formation d'adresse: sur la région, la ville et la rue;
- trois enregistrements avec les caractéristiques du numéro de maison: numéro de maison, numéro de bâtiment et numéro de bâtiment.
La fonction a un autre paramètre facultatif - la date d'expiration de l'enregistrement ( EndDate ), avec laquelle vous pouvez afficher le pedigree non seulement de l'enregistrement actuel de la maison, mais également des enregistrements déjà obsolètes.
Le texte complet de la fonction est donné dans l'annexe dans la section Création de la fonction fstf_Houses_AddressObjectTree .
Dès le début
Si vous savez comment la table maison FIAS est organisée, cette section peut être ignorée.
Les maisons FIAS ( HOUSES ) sont une liste enfant pour la liste des éléments générateurs d'adresses de FIAS ( ADDROBJ ). Chaque entrée de liste de maisons fait référence à un élément générateur d'adresse FIAS par la valeur du champ AOGUID . Afin de déterminer sur quelle rue et dans quelle localité la maison est située, vous devez trouver l'enregistrement correspondant avec le même identifiant de liste ADDROBJ par la valeur AOGUID de l'enregistrement HOUSES .
Malgré la simplicité extérieure du mécanisme d'interaction entre la liste des maisons et la liste des éléments de formation d'adresse dans leur interaction, des caractéristiques compliquent la mise en œuvre des fonctions sur les MAISONS .
Premièrement, chaque enregistrement de la liste des maisons par l'identifiant AOGUID fait référence à un groupe d'éléments de formation d'adresse, dont l'un est pertinent.
Deuxièmement, il y a plusieurs entrées dans la liste FIAS avec le même ensemble de caractéristiques de numéro de maison: numéro de maison, numéro de bâtiment, numéro de bâtiment.
Troisièmement, le dossier de la maison n'est pas toujours hérité du dossier de la rue du village.
Mais, tout d'abord.
Pour approfondir le stockage des informations sur les maisons dans la FIAS, il suffit de se limiter à 4 tableaux (fichiers DBF):
- ADDROBJ - liste des éléments de formation d'adresse;
- MAISONS - liste des maisons;
- STRSTAT - un répertoire des caractéristiques structurelles;
- ESTSTAT - un répertoire des signes de propriété.
ADDROBJ a été discuté en détail dans la publication précédente, «Adresses FIAS dans PostgreSQL», de sorte que ses fonctionnalités seront discutées ici exactement autant que nécessaire pour décrire les caractéristiques des maisons.
Tableau 2. Histoire de la maison «Territoire de Krasnoïarsk, district de Taimyr Dolgan-Nenetsky, Dudinka, ul. Dudinskaya, 1
Comme le montre le tableau, contrairement aux objets formant des adresses, les enregistrements de l'histoire de la maison n'ont pas de signes particuliers de pertinence. L'enregistrement avec la date de fin de période la plus ancienne, supérieure à l'actuelle, est pertinent. Jusqu'à présent, les enregistrements actuels de la maison portent la date 06.06.2079. Tous les autres enregistrements concernant la maison sont considérés comme historiques, et les dates de début et de fin caractérisent la période de pertinence de chaque enregistrement.
La liste des maisons FIAS ne contient pas de pointeurs vers les enregistrements précédents et suivants concernant la maison. Par conséquent, l'ordre des enregistrements de la profondeur réelle dans l'historique de la maison est déterminé par la date de fin décroissante et au-delà la date de début de la période, respectivement EndDate et StartDate .
SELECT * FROM fias_Houses h WHERE h.HOUSEGUID='2fd64ecd-4b4b-4fc8-bd15-4a4a2f310b21' ORDER BY h.ENDDATE DESC,h.STARTDATE DESC;
Lecteur attentif en regardant la Fig. 1, je me suis probablement posé la question: pourquoi mentionne-t-on des ouvrages de référence de signes de structure et de propriété? FIAS utilise plus de 10 de ces types d'annuaires, alors pourquoi ces deux répertoires sont-ils mis en évidence?
La réponse en surprendra beaucoup - du point de vue de la «logique FIAS», l'adresse de la maison n'est pas entièrement identifiée par l'adresse, la maison, le bâtiment et les numéros de bâtiment. Le terme «logique FIAS» a été utilisé dans la réponse d'un employé du Service fédéral des impôts à ma question: pourquoi dans la liste des maisons dans le territoire de Krasnoïarsk, il y a plus de 250 adresses de maisons jumelées. La même réponse indique que l'unicité de l'enregistrement est fournie par les valeurs AOGUID, HOUSENUM, BUILDNUM, STRUCNUM, STRSTATUS, ESTSTATUS.
En d'autres termes, pour trouver un objet, il ne suffit pas de connaître la localité, la rue, le numéro de la maison. Vous devez également savoir:
- «Possession» est ou «propriété du logement»;
- le statut de cet objet est défini ou non défini;
- etc.
Voici à quoi ressemble un échantillon de la liste générale des maisons FIAS avec des adresses en double.
Le fait que différents objets aient la même adresse n'est pas surprenant. Le bâtiment et le terrain en dessous; maison, garage, bains pour un propriétaire. Ils ont tous la même adresse. Mais FIAS est un registre d'adresses, c'est-à-dire liste d'adresses. Par conséquent, il est naturel de s'attendre à ce que les adresses y soient uniques, et non les bâtiments, les structures, les structures.
C'est-à-dire la liste des maisons FIAS à partir de la liste des adresses des maisons a commencé à évoluer vers la liste des bâtiments au sol. Et les utilisateurs de FIAS doivent en tenir compte.
Tout le monde peut vérifier la présence de maisons avec des adresses en double en exécutant une instruction SELECT similaire à la suivante. Dans le même temps, la fonction fsfn_Houses_TreeActualName ne peut pas être utilisée, car il est utilisé uniquement pour réduire le nombre de colonnes dans le résultat. Il n'est pas nécessaire d'utiliser les répertoires fias_StructureStatus (analogique de STRSTAT) et fias_EstateStatus (analogue à ESTSTAT), comme l'effet marqué peut également être retracé sur les codes des signes de structure et de possession.
code source de l'opérateur 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;
Et enfin, une autre caractéristique de la liste d'accueil FIAS. Chaque enregistrement de maison de cette liste contient un lien vers un élément de formation d'adresse, dont la liste est une hiérarchie de ces éléments. À chaque niveau de la hiérarchie se trouvent des éléments de formation d'adresse appartenant à différents types. L'élément racine est donc la région (le territoire de Krasnoïarsk dans notre cas), au niveau suivant un okrug autonome, une région ou une ville de subordination régionale. Et ainsi de suite. (Pour plus de détails, voir "Adresses FIAS dans PostgreSQL").
Formellement, un enregistrement de maison vous permet de faire référence à un élément de la hiérarchie à n'importe quel niveau. Heureusement, aucune maison ne faisait référence à un district ou à une région parmi les données du territoire de Krasnoïarsk. Néanmoins, toutes les maisons ne se réfèrent pas à la rue du village:
- 98% des logements FIAS sont reliés aux rues des zones habitées;
- 1,2% des maisons - avec des rues dans les associations de jardinage;
- 0,3% des maisons sont des habitations;
- 0,5% des maisons avec d'autres éléments adressables.
Fig. 2.Propagation des adresses des maisons par les propriétaires (FIAS vs plan)
Un problème est décrit ici qui conduit à une interprétation ambiguë de l'arbre généalogique. (Igor Leonidovich Timoshchenkov, spécialiste des SIG, Aigeo LLC, Krasnoyarsk, a attiré mon attention sur ce problème.)
Ce qui précède montre comment plusieurs enregistrements contiennent la même adresse à la maison. Ce qui peut s'expliquer par la volonté de l'inspection fiscale de tenir non seulement un registre d'une maison particulière, mais aussi des bâtiments environnants: un garage, une grange, etc. Mais il existe des exemples inversés lorsque plusieurs bâtiments (fias_Houses) correspondent à un bâtiment (maison) avec des numéros différents pour cette maison.
Jetez un oeil à cette photo. Sur la gauche, il y a une capture d'écran avec une carte du village où se trouvent les maisons pour deux propriétaires. Ce sont des maisons ordinaires d'un étage avec deux entrées. Une famille vit à droite et une autre à gauche. Ils peuvent encore être imaginés comme des maisons de deux appartements.
Regardez maintenant le tableau à droite. Dans ce document, presque chaque maison avec deux propriétaires correspond à 3 entrées. C'est-à-dire Le tableau des maisons FIAS indique à la fois l'adresse de la maison individuelle («d. 1») et les adresses des parties de la maison («d. 1/1», «d. 1/2») appartenant à un propriétaire.
Comment ça marche
La fonction fstf_Houses_AddressObjectTree a deux versions: avec quatre ou avec deux paramètres. Dans la version de la fonction à deux paramètres, l'identifiant de la maison ( HouseGUID ) et la date d'expiration de l'enregistrement (EndDate ) sont transmis. Une version avec quatre paramètres nécessite en outre un identifiant pour l'élément générateur d'adresse ( AOGUID ) et l'état actuel ( CurrStatus ).
code source de l'opérateur 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;
Une fonction avec moins de paramètres calcule les valeurs des paramètres manquants et appelle une fonction avec un grand nombre de paramètres. Pour ce faire, l'identifiant de l'élément de formation d'adresse est simplement récupéré dans le champ correspondant de la table maison (f ias_Houses ). Et la valeur de l'état actuel ( CurrStatus ) est calculée selon les règles suivantes:
- si aucun des enregistrements d'historique de l'élément de formation d'adresse ne contient 0 dans le champ CurrStatus, la variable v_CurrStatus se voit attribuer la valeur de champ maximale pour cet élément de formation d'adresse;
- sinon, cette variable se voit attribuer la valeur 0.
Une fonction avec un grand nombre de paramètres appelle d'abord la fonction fstf_AddressObjects_AddressObjectTree , qui renvoie les éléments de formation d'adresse parents pour la maison. Vous pouvez en savoir plus sur la fonction fstf_AddressObjects_AddressObjectTree dans la section Pedigree de l' élément de formation d'adresse du document Adresses FIAS dans PostgreSQL
.
Ensuite, les entrées sur les éléments de formation d'adresse sont complétées par des entrées sur les numéros de la maison, du bâtiment, de la structure (voir tableau 1), qui sont créées pour chaque champ non vide sur le numéro de la maison, du bâtiment et de la structure.
Pour que tous les enregistrements de sortie aient la même structure et non sans une certaine part de caractère, les valeurs du niveau de code des champs ( AOLevel ), de l'état actuel ( CurrStatus ) et de l'état de pertinence ( ActStatus ) sont créées artificiellement dans le corps de la fonction.
Le code du niveau de la maison (bâtiment, structure) est toujours fixé à 8, voir le livre de référence «Niveaux d'objets adressables» du document FIAS Informations sur la composition des informations ).
Le statut de pertinence est défini sur 1 si la date d'expiration de l'enregistrement ( EndDate ) est 06.06.2079 et 0 sinon.
Les valeurs des champs CurrStatus sont plus compliquées. En utilisant ses valeurs, deux tâches sont résolues simultanément: l'identifiant de chaque version de l'enregistrement concernant l'élément de formation d'adresse est défini et le signe de la pertinence de l'enregistrement est attribué. Par conséquent, le dernier enregistrement actuel concernant un élément contient une valeur de 0 dans ce champ, et tous les enregistrements historiques sont numérotés dans l'ordre d'apparition - "1" est le premier enregistrement, le suivant dans le temps - "2", etc. L'ordre d'affectation des valeurs au champ CurrStatus est décrit plus en détail dans la publication Adresses FIAS dans PostgreSQL .
En-tête de spoiler 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;
Adresse complète du domicile
L'idée principale de la fonction fsfn_Houses_TreeActualName est de renvoyer le numéro de maison connecté en une seule ligne avec les noms de tous ses ancêtres - les éléments de formation d'adresse.
Par exemple, laissez la fonction d'arbre généalogique (fstf_Houses_AddressObjectTree) retourner la liste de valeurs suivante.
Tableau 4. Le résultat de la fonction fstf_Houses_AddressObjectTree ('0c0d670f-097c-4e1d-bcac-9e9b22fb1b99')
Ensuite, fsfn_Houses_TreeActualName ('0c0d670f-097c-4e1d-bcac-9e9b22fb1b99') devrait retourner: “ M. Krasnoyarsk, 34A St. Lazo, bld. 6, p. 17 ”.
La fonction fsfn_Houses_TreeActualName peut être simplifiée en tant que fonction d'agrégation STRING_AGG sur le résultat d'une fonction qui renvoie un arbre généalogique à la maison.
La fonction en question a un autre paramètre facultatif - un tableau de masques ( a_MaskArray ), avec lequel vous pouvez inclure dans le résultat non pas tous les noms d'éléments, mais seulement ceux qui sont nécessaires.
Tableau 5. Liste des masques de fonction.
version texte du tableau| Valeur | Remarque |
|---|
| {HS} | Masque - numéro de maison |
| {BY} | Masque - numéro de boîtier |
| {BG} | Masque - numéro de bâtiment |
| {ST} | Masque - rue |
| {ZC} | Masque - Code postal |
| {DT} | Masque - zone urbaine |
| {LP} | Masque - ville subordonnée |
| {LM} | Mask - la principale colonie |
| {TP} | Masque - région du sujet de la fédération |
| {TM} | Masque - sujet de la fédération (région) |
| {CY} | Masque - Pays |
Voir également la section «
Nom complet de l'élément de formation d'adresse» de la publication «Adresses FIAS dans PostgreSQL ».
Le texte de la fonction est donné dans la section Application «
Création de la fonction fsfn_Houses_TreeActualName ».
Accueil FIAS Recherche
La fonction fstf_Houses_SearchByName est destinée à rechercher les adresses des maisons FIAS par leurs numéros et les noms des éléments de formation d'adresse. De plus, la recherche peut être effectuée non seulement par le nom et le type de l'élément courant, mais également par les noms et types d'un ou deux de ses ancêtres les plus proches.
Regardons quelques exemples. Et pour commencer, nous trouverons toutes les maisons avec le numéro "220".
Tableau 6. Résultat de la fonction fstf_Houses_SearchByName ('220')
Contrairement à la fonction de recherche d'éléments de formation d'adresse ( fstf_AddressObjects_SearchByName ), le résultat de cette fonction ne contient pas l'effet de "nage" à travers les niveaux d'éléments de formation d'adresse. Le premier paramètre de la fonction contient toujours le modèle de recherche pour le numéro de la maison, le second - le numéro du bâtiment, le troisième numéro du bâtiment.
Modifiez maintenant la demande. Nous trouvons toutes les maisons d'éléments formant des adresses, dont le nombre contient le nombre «1», et le mot «Krasnoyarsk» apparaît dans les noms.
Tableau 7. Le résultat de la fonction fstf_Houses_SearchByName ('1', NULL, NULL, 'Krasnoyarsk')
La finalité des paramètres restants coïncide exactement avec la finalité des paramètres de la fonction de recherche des éléments générateurs d'adresses (fstf_AddressObjects_SearchByName).
Le texte de la fonction est donné dans la section Application « Création de la fonction fstf_Houses_SearchByName »
.
Comment ça marche
L'implémentation de fstf_Houses_SearchByName est à bien des égards similaire à l'implémentation de la fonction de recherche d'éléments générateurs d'adresses (fstf_AddressObjects_SearchByName) . La principale différence est que la recherche est effectuée dans deux tables associées, fias_Houses et fias_AddressObjects .
La fonction a 9 arguments. Les trois premiers d'entre eux sont les numéros de maison ( a_HouseNum ), le bâtiment (a_BuildNum ) et le bâtiment ( a_StrucNum ). Les 6 autres ( a_FormalName , a_ShortName , a_ParentFormalName , a_ParentShortName , a_GrandParentFormalName , a_GrandParentShortName ) coïncident complètement avec les paramètres de la fonction.
Si vous définissez uniquement la valeur du paramètre «numéro de maison», la fonction renvoie toutes les adresses du numéro de maison que la séquence spécifiée rencontre avec un symbole. Si vous passez NULL ou une chaîne vide ("") comme numéro de maison, les adresses de toutes les maisons dont les éléments d'adresse sont spécifiés par un ensemble d'autres paramètres seront retournées.
Épilogue
Cette section contient des recommandations sur la façon de charger la liste des maisons FIAS dans la table fias_Houses .
Le chargement de données dans une table de maisons s'effectue de la même manière que le chargement de données dans une table d'éléments de formation d'adresse . Seul le fichier source sera HOUSE99.DBF , pas ADDROB99.DBF . Voici 99 le numéro de la région (République, oblast, territoire). Par exemple, pour le territoire de Krasnoïarsk, le fichier source est le fichier HOUSE24.DBF .
Tout d'abord, l'archive suivante avec la mise à jour est téléchargée à partir de la page Mises à jour FIAS. Le fichier HOUSE99.DBF en est extrait .
.
Le fichier HOUSE99.DBF est ensuite converti au format CSV et déjà converti, il est chargé par l'instruction COPY dans la table temporaire fias_Houses_Temp .
Et enfin, les données temporaires sont utilisées pour mettre à jour la table principale, c'est-à-dire inexistantes dans fias_Houses sont ajoutées et celles existantes sont remplacées.
Un exemple de script pour mettre à jour une table de maisons est donné dans la section « Téléchargement des mises à jour de FIAS House vers la table fias_Houses ».
App
Création de la fonction fstf_Houses_AddressObjectTree
Vous trouverez des commentaires sur le code source de la fonction ici .
code de fonction 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;
Création de la fonction fsfn_Houses_TreeActualName
Vous trouverez des commentaires sur le code source de la fonction ici .
code de fonction 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;
Création de la fonction fstf_Houses_SearchByName
Vous trouverez des commentaires sur le code source de la fonction ici .code de fonction 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);
Création d'une table maison FIAS fias_Houses
code de script 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 ' ';
Télécharger les mises à jour de FIAS Home dans le tableau fias_Houses
code source du script 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;