El artículo anterior que describe las direcciones y funciones de FIAS para trabajar con ellas en el entorno PostgreSQL despertó interés entre una pequeña parte de los lectores.
Por lo tanto, tiene sentido describir funciones similares en PL / pgSQL para trabajar con una lista de casas FIAS cargadas en una base de datos que ejecuta PostgreSQL.
La primera mitad del artículo proporciona comentarios sobre la implementación de funciones. En el segundo, los códigos fuente de funciones, así como los scripts para crear una tabla con registros internos de FIAS, así como cargar datos en esta tabla desde un archivo en formato CSV. Para aquellos lectores que solo están interesados en los textos de origen, sugerimos proceder inmediatamente al Apéndice.
Este artículo está estrechamente relacionado con los materiales de la serie de artículos "Direcciones FIAS en el entorno PostgreSQL" (
comienzo ,
continuación 1 ,
continuación 2 ,
finalización ).
Árbol genealógico en casa
Comencemos con un ejemplo.
Llamar a la función f stf_Houses_AddressObjectTree ('42301ab8-9ead-4f8e-8281-e64f2769a254') dará como resultado la siguiente lista de entradas.
Tabla 1. El resultado de la función.
En una inspección más cercana, puede observar que el identificador del elemento ( HOUSEGUID ) “d. 1, edif. 2, p. 26 ”, se recibieron seis registros como resultado:
- tres registros de padres con elementos formadores de direcciones: sobre la región, ciudad y calle;
- tres registros con las características del número de casa: número de casa, número de edificio y número de edificio.
La función tiene otro parámetro opcional: la fecha de vencimiento del registro ( EndDate ), con la que puede ver el pedigrí no solo del registro actual de la casa, sino también de registros ya obsoletos.
El texto completo de la función se proporciona en el Apéndice en la sección Creación de la función fstf_Houses_AddressObjectTree .
Desde el principio
Si sabe cómo está organizada la mesa de la casa FIAS, entonces esta sección puede omitirse.
Las casas FIAS ( CASAS ) son una lista secundaria para la lista de elementos generadores de direcciones de FIAS ( ADDROBJ ). Cada entrada de la lista de la casa se refiere a un elemento generador de direcciones FIAS por el valor del campo AOGUID . Para determinar en qué calle y en qué localidad se encuentra la casa, debe encontrar el registro correspondiente con el mismo identificador de lista ADDROBJ por el valor AOGUID del registro HOUSES .
A pesar de la simplicidad externa del mecanismo de interacción entre la lista de casas y la lista de elementos formadores de direcciones en su interacción, las características complican la implementación de funciones en las CASAS .
En primer lugar, cada registro de la lista de casas por el identificador AOGUID se refiere a un grupo de elementos formadores de direcciones, uno de los cuales es relevante.
En segundo lugar, hay varias entradas en la lista FIAS con el mismo conjunto de características de número de casa: número de casa, número de edificio, número de edificio.
En tercer lugar, el registro de la casa no siempre se hereda del registro de la calle del pueblo.
Pero, primero lo primero.
Para una mayor consideración del almacenamiento de información sobre casas en FIAS, es suficiente limitarnos a 4 tablas (archivos DBF):
- ADDROBJ - lista de elementos formadores de direcciones;
- CASAS - lista de casas;
- STRSTAT - un directorio de características estructurales;
- ESTSTAT - un directorio de signos de propiedad.
ADDROBJ se discutió en detalle en una publicación anterior, "Direcciones de FIAS en PostgreSQL", por lo que sus características se discutirán aquí exactamente tanto como sea necesario para describir las características de las casas.
Tabla 2. Historia de la casa “Territorio de Krasnoyarsk, distrito de Taimyr Dolgan-Nenetsky, Dudinka, ul. Dudinskaya, 1
Como se puede ver en la tabla, en contraste con los objetos que forman direcciones, los registros de la historia de la casa no tienen signos especiales de relevancia. El registro con la fecha de finalización más antigua del período, que es mayor que la actual, es relevante. Hasta ahora, los registros actuales de la casa están marcados con la fecha 06.06.2079. Todos los demás registros sobre la casa se consideran históricos, y las fechas de inicio y finalización caracterizan el período de relevancia de cada registro.
La lista de casas FIAS no contiene punteros a los registros anteriores y siguientes sobre la casa. Por lo tanto, el orden de los registros desde la profundidad real de la historia de la casa está determinado por la fecha de finalización decreciente y más allá de la fecha de inicio del período, respectivamente EndDate y StartDate .
SELECT * FROM fias_Houses h WHERE h.HOUSEGUID='2fd64ecd-4b4b-4fc8-bd15-4a4a2f310b21' ORDER BY h.ENDDATE DESC,h.STARTDATE DESC;
Lector atento mirando la Fig. 1, probablemente me hice la pregunta: ¿por qué se mencionan los libros de referencia de signos de estructura y propiedad? FIAS utiliza más de 10 de este tipo de directorios, entonces, ¿por qué se destacan estos dos?
La respuesta sorprenderá a muchos: desde el punto de vista de la "lógica FIAS", la dirección de la casa no está completamente identificada por la dirección, la casa, el edificio y los números de edificio. El término "lógica FIAS" se utilizó en la respuesta de un empleado del Servicio de Impuestos Federales a mi pregunta de por qué en la lista de casas en el Territorio de Krasnoyarsk hay más de 250 direcciones de casas emparejadas. La misma respuesta dice que la unicidad del registro es proporcionada por los valores AOGUID, HOUSENUM, BUILDNUM, STRUCNUM, STRSTATUS, ESTSTATUS.
En otras palabras, para encontrar un objeto no es suficiente conocer la localidad, calle, número de casa. También debes saber:
- "Posesión" es o "propiedad de la vivienda";
- el estado de este objeto está definido o no definido;
- etc.
Así es como se ve una muestra de la lista general de casas FIAS con direcciones duplicadas.
El hecho de que diferentes objetos tengan la misma dirección no es sorprendente. El edificio y la tierra debajo de él; casa, garaje, casa de baños para un propietario. Todos tienen la misma dirección. Pero FIAS es un registro de dirección, es decir lista de direcciones. Por lo tanto, es natural esperar que las direcciones sean únicas en él, y no edificios, estructuras, estructuras.
Es decir La lista de casas FIAS de la lista de direcciones de casas comenzó a desarrollarse hacia la lista de edificios de tierra. Y los usuarios de FIAS deben considerar esto.
Todos pueden verificar la presencia de casas con direcciones duplicadas ejecutando una instrucción SELECT similar a la siguiente. Al mismo tiempo, la función fsfn_Houses_TreeActualName no se puede usar, porque se usa solo para reducir el número de columnas en el resultado. No es necesario utilizar los directorios fias_StructureStatus (análogo de STRSTAT) y fias_EstateStatus (análogo a ESTSTAT), como El efecto marcado también se puede rastrear en los códigos de signos de estructura y posesión.
código fuente del operador 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;
Y finalmente, otra característica de la lista de inicio de FIAS. Cada registro interno de esta lista contiene un enlace a un elemento formador de direcciones, cuya lista es una jerarquía de dichos elementos. En cada nivel de la jerarquía hay elementos formadores de direcciones que pertenecen a diferentes tipos. Entonces, el elemento raíz es la región (territorio de Krasnoyarsk en nuestro caso), en el siguiente nivel se encuentra un okrug autónomo, región o ciudad de subordinación regional. Y así sucesivamente. (Para más detalles, vea "Direcciones FIAS en PostgreSQL").
Formalmente, un registro interno le permite hacer referencia a un elemento de la jerarquía en cualquier nivel. Afortunadamente, no había casas que se refirieran a un distrito o región entre los datos del Territorio de Krasnoyarsk. Sin embargo, no todas las casas se refieren a la calle del pueblo:
- El 98% de los hogares FIAS están vinculados a calles en áreas pobladas;
- 1.2% de casas - con calles en asociaciones de jardinería;
- 0.3% de las casas son con asentamientos;
- 0.5% de casas con otros elementos direccionables.
Fig. 2)Propagación de domicilios por propietarios (FIAS vs mapa)
Aquí se describe un problema que conduce a una interpretación ambigua del árbol genealógico. (Igor Leonidovich Timoshchenkov, especialista en SIG, Aigeo LLC, Krasnoyarsk, me llamó la atención sobre este problema).
Lo anterior muestra cómo varios registros contienen la misma dirección en casa. Lo que puede explicarse por el deseo de la inspección fiscal de mantener no solo un registro de una casa privada, sino también de los edificios circundantes: un garaje, un granero, etc. Pero hay ejemplos inversos cuando varios edificios (fias_Houses) corresponden a un edificio (casa) con diferentes números para esta casa.
Echa un vistazo a esta foto. A la izquierda hay una captura de pantalla con un mapa del pueblo donde se encuentran las casas para dos propietarios. Estas son casas comunes de un piso con dos entradas. Una familia vive a la derecha y otra a la izquierda. Todavía se pueden imaginar como casas de dos apartamentos.
Ahora mira la mesa de la derecha. En él, casi todas las casas con dos propietarios corresponden a 3 entradas. Es decir La tabla de casas FIAS muestra tanto la dirección de la casa individual ("d. 1") como las direcciones de las partes de la casa ("d. 1/1", "d. 1/2") pertenecientes a un propietario.
Como funciona
La función fstf_Houses_AddressObjectTree tiene dos versiones: con cuatro o con dos parámetros. En la versión de la función con dos parámetros, se transmiten el identificador de la casa ( HouseGUID ) y la fecha de vencimiento del registro (EndDate ). Una versión con cuatro parámetros requiere además un identificador para el elemento generador de dirección ( AOGUID ) y el estado actual ( CurrStatus ).
código fuente del operador 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;
Una función con menos parámetros calcula los valores de los parámetros faltantes y llama a una función con una gran cantidad de parámetros. Para hacer esto, el identificador del elemento que forma la dirección simplemente se recupera del campo correspondiente de la tabla de la casa (f ias_Houses ). Y el valor del estado actual ( CurrStatus ) se calcula de acuerdo con las siguientes reglas:
- si ninguno de los registros históricos del elemento formador de direcciones contiene 0 en el campo CurrStatus, entonces a la variable v_CurrStatus se le asigna el valor de campo máximo para este elemento formador de direcciones;
- de lo contrario, a esta variable se le asigna el valor 0.
Una función con una gran cantidad de parámetros primero llama a la función fstf_AddressObjects_AddressObjectTree , que devuelve los elementos que forman la dirección principal para la casa. Puede leer más sobre la función fstf_AddressObjects_AddressObjectTree en la sección Pedigree del elemento de formación de direcciones del documento Direcciones FIAS en PostgreSQL
.
Luego, las entradas sobre los elementos formadores de direcciones se complementan con entradas sobre los números de la casa, el edificio, la estructura (ver Tabla 1), que se crean para cada campo no vacío sobre el número de la casa, el edificio y la estructura.
Para que todos los registros de salida tengan la misma estructura y no sin cierta cuota de rapidez, los valores del nivel de código de campos ( AOLevel ), el estado actual ( CurrStatus ) y el estado de relevancia ( ActStatus ) se crean artificialmente en el cuerpo de la función.
El código para el nivel de la casa (edificio, estructura) siempre se establece en 8, consulte el libro de referencia "Niveles de objetos direccionables" de la información de FIAS en el documento Composición de información ).
El estado de relevancia se establece en 1 si la fecha de vencimiento del registro ( EndDate ) es 06.06.2079 y 0 en caso contrario.
Los valores del campo CurrStatus son más complicados. Usando sus valores, se resuelven dos tareas simultáneamente: se establece el identificador de cada versión del registro sobre el elemento que forma la dirección y se asigna el signo de la relevancia del registro. Por lo tanto, el último registro actual sobre un elemento contiene un valor de 0 en este campo, y todos los registros históricos están numerados en el orden de aparición: "1" es el registro más antiguo, seguido en el tiempo: "2", etc. El orden de asignación de valores al campo CurrStatus se describe con más detalle en la publicación Direcciones FIAS en PostgreSQL .
Encabezado 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;
Dirección completa de la casa
La idea principal de la función fsfn_Houses_TreeActualName es devolver el número de casa conectado en una línea junto con los nombres de todos sus antepasados: elementos formadores de direcciones.
Por ejemplo, deje que la función del árbol genealógico (fstf_Houses_AddressObjectTree) devuelva la siguiente lista de valores.
Tabla 4. El resultado de la función fstf_Houses_AddressObjectTree ('0c0d670f-097c-4e1d-bcac-9e9b22fb1b99')
Entonces fsfn_Houses_TreeActualName ('0c0d670f-097c-4e1d-bcac-9e9b22fb1b99') debería devolver: “ Sr. Krasnoyarsk, 34A St. Lazo, bld. 6, p. 17 ".
La función fsfn_Houses_TreeActualName se puede simplificar como una función agregada STRING_AGG sobre el resultado de una función que devuelve un árbol genealógico en el hogar.
La función en cuestión tiene otro parámetro opcional: una matriz de máscaras ( a_MaskArray ), con la que puede incluir en el resultado no todos los nombres de elementos, sino solo aquellos que son necesarios.
Tabla 5. Lista de máscaras de funciones.
versión de texto de la tabla| Valor | Nota |
|---|
| {HS} | Máscara - número de casa |
| {POR} | Máscara - número de caso |
| {BG} | Máscara: número de edificio |
| {ST} | Máscara - calle |
| {ZC} | Máscara - Código postal |
| {DT} | Máscara - área urbana |
| {LP} | Máscara - ciudad subordinada |
| {LM} | Máscara: el asentamiento principal |
| {TP} | Máscara: región del sujeto de la federación |
| {TM} | Máscara: sujeto de la federación (región) |
| {CY} | Máscara - País |
Consulte también la sección "
Nombre completo del elemento formador de direcciones" de la publicación "Direcciones FIAS en PostgreSQL ".
El texto de la función se proporciona en la sección Aplicación "
Creación de la función fsfn_Houses_TreeActualName ".
FIAS Home Search
La función fstf_Houses_SearchByName está diseñada para buscar las direcciones de las casas FIAS por sus números y los nombres de los elementos formadores de direcciones. Además, la búsqueda puede llevarse a cabo no solo por el nombre y el tipo del elemento actual, sino también por los nombres y tipos de uno o dos de sus antepasados más cercanos.
Veamos algunos ejemplos. Y para empezar, encontraremos todas las casas con el número "220".
Tabla 6. Resultado de la función fstf_Houses_SearchByName ('220')
A diferencia de la función de búsqueda de elementos formadores de direcciones ( fstf_AddressObjects_SearchByName ), el resultado de esta función no contiene el efecto de "nadar" a través de los niveles de elementos formadores de direcciones. El primer parámetro de la función siempre contiene el patrón de búsqueda para el número de casa, el segundo, el número de edificio, el tercer número de edificio.
Ahora cambia la solicitud. Encontramos todas las casas de elementos formadores de direcciones, cuyo número contiene el número "1", y la palabra "Krasnoyarsk" aparece en los nombres.
Tabla 7. El resultado de la función fstf_Houses_SearchByName ('1', NULL, NULL, 'Krasnoyarsk')
El propósito de los parámetros restantes coincide exactamente con el propósito de los parámetros de la función de búsqueda de los elementos generadores de direcciones (fstf_AddressObjects_SearchByName).
El texto de la función se proporciona en la sección Aplicación " Creación de la función fstf_Houses_SearchByName "
.
Como funciona
La implementación de fstf_Houses_SearchByName es similar en muchos aspectos a la implementación de la función de búsqueda de elementos generadores de direcciones (fstf_AddressObjects_SearchByName) . La principal diferencia es que la búsqueda se realiza en dos tablas relacionadas, fias_Houses y fias_AddressObjects .
La función tiene 9 argumentos. Los tres primeros son los números de casa ( a_HouseNum ), el edificio (a_BuildNum ) y el edificio ( a_StrucNum ). Los 6 restantes ( a_FormalName , a_ShortName , a_ParentFormalName , a_ParentShortName , a_GrandParentFormalName , a_GrandParentShortName ) coinciden completamente con los parámetros de la función.
Si establece solo el valor del parámetro "número de casa", la función devolverá todas las direcciones en el número de casa que la secuencia especificada encuentra con un símbolo. Si pasa NULL o una cadena vacía ("") como número de casa, se devolverán las direcciones de todas las casas cuyos elementos de dirección se especifiquen mediante un conjunto de otros parámetros.
Epílogo
Esta sección contiene recomendaciones sobre cómo cargar la lista de casas FIAS en la tabla fias_Houses .
La carga de datos en una tabla de casas se realiza de la misma manera que la carga de datos en una tabla de elementos formadores de direcciones . Solo el archivo fuente será HOUSE99.DBF , no ADDROB99.DBF . Aquí 99 es el número de la región (República, región, territorio). Por ejemplo, para el territorio de Krasnoyarsk, el archivo fuente es el archivo HOUSE24.DBF .
Primero, el siguiente archivo con la actualización se descarga desde la página Actualizaciones de FIAS. El archivo HOUSE99.DBF se extrae de él .
.
Luego, el archivo HOUSE99.DBF se convierte al formato CSV y ya convertido, se carga mediante la instrucción COPY en la tabla temporal fias_Houses_Temp .
Y finalmente, los datos temporales se utilizan para actualizar la tabla principal, es decir. inexistente en fias_Houses se agregan, y los existentes se reemplazan.
En la sección " Descarga de actualizaciones de FIAS House en la tabla fias_Houses " se ofrece un ejemplo de un script para actualizar una tabla de casas.
App
Crear la función fstf_Houses_AddressObjectTree
Los comentarios sobre el código fuente de la función se pueden encontrar aquí .
código de función 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;
Crear la función fsfn_Houses_TreeActualName
Los comentarios sobre el código fuente de la función se pueden encontrar aquí .
código de función 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;
Crear la función fstf_Houses_SearchByName
Los comentarios sobre el código fuente de la función se pueden encontrar aquí .código de función 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);
Crear una tabla de la casa FIAS fias_Houses
código 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 ' ';
Descargue las actualizaciones de FIAS Home en la tabla fias_Houses
código fuente del 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;