O artigo anterior, que descreve os endereços e funções do FIAS para trabalhar com eles no ambiente PostgreSQL, despertou interesse entre uma pequena parte dos leitores.
Portanto, faz sentido descrever funções semelhantes no PL / pgSQL para trabalhar com uma lista de casas FIAS carregadas em um banco de dados executando o PostgreSQL.
A primeira metade do artigo fornece comentários sobre a implementação de funções. No segundo, códigos-fonte de funções, bem como scripts para criar uma tabela com registros internos do FIAS, além de carregar dados nessa tabela a partir de um arquivo no formato CSV. Para os leitores que estão interessados apenas nos textos originais, sugerimos seguir imediatamente para o Apêndice.
Este artigo está intimamente relacionado aos materiais da série de artigos “Endereços FIAS no ambiente PostgreSQL” (
início ,
continuação 1 ,
continuação 2 ,
término ).
Árvore genealógica em casa
Vamos começar com um exemplo.
Chamar a função f stf_Houses_AddressObjectTree ('42301ab8-9ead-4f8e-8281-e64f2769a254') resultará na seguinte lista de entradas.
Tabela 1. O resultado da função.
Em uma inspeção mais detalhada, você pode perceber que o identificador do elemento ( HOUSEGUID ) “d. 1, prédio. 2, p. 26 ”, seis registros foram recebidos como resultado:
- três registros pai com elementos de formação de endereço: sobre a região, cidade e rua;
- três registros com as características do número da casa: número da casa, número da construção e número da construção.
A função possui outro parâmetro opcional - a data de validade do registro ( EndDate ), com a qual é possível visualizar o pedigree não apenas do registro atual da casa, mas também dos registros já desatualizados.
O texto completo da função é fornecido no apêndice na seção Criando a função fstf_Houses_AddressObjectTree .
Desde o começo
Se você souber como a tabela da casa FIAS é organizada, esta seção poderá ser ignorada.
As casas FIAS ( CASAS ) são uma lista filho da lista de elementos geradores de endereço do FIAS ( ADDROBJ ). Cada entrada da lista de casas refere-se a um elemento gerador de endereço FIAS pelo valor do campo AOGUID . Para determinar em qual rua e em qual localidade a casa está localizada, você precisa encontrar o registro correspondente com o mesmo identificador de lista ADDROBJ pelo valor AOGUID do registro HOUSES .
Apesar da simplicidade externa do mecanismo de interação entre a lista de casas e a lista de elementos formadores de endereços em sua interação, recursos complicam a implementação de funções em CASAS .
Em primeiro lugar, cada registro da lista de casas pelo identificador AOGUID refere-se a um grupo de elementos formadores de endereço, um dos quais é relevante.
Em segundo lugar, existem várias entradas na lista FIAS com o mesmo conjunto de características de número da casa: número da casa, número da construção, número da construção.
Em terceiro lugar, o registro da casa nem sempre é herdado do registro da rua da vila.
Mas, primeiro as primeiras coisas.
Para uma análise mais aprofundada do armazenamento de informações sobre casas no FIAS, basta limitar-nos a 4 tabelas (arquivos DBF):
- ADDROBJ - lista de elementos formadores de endereço;
- CASAS - lista de casas;
- STRSTAT - um diretório de recursos estruturais;
- ESTSTAT - um diretório de sinais de propriedade.
O ADDROBJ foi discutido em detalhes na publicação anterior, “Endereços FIAS no PostgreSQL”, para que seus recursos sejam discutidos aqui exatamente o necessário para descrever as características das casas.
Tabela 2. História da casa “Território de Krasnoyarsk, distrito de Taimyr Dolgan-Nenetsky, Dudinka, ul. Dudinskaya, 1
Como pode ser visto na tabela, em contraste com os objetos de formação de endereço, os registros da história da casa não têm sinais especiais de relevância. O registro com a data final mais antiga do período, que é maior que a atual, é relevante. Até o momento, os registros atuais da casa estão marcados com a data 06.06.2079. Todos os outros registros da casa são considerados históricos, e as datas de início e término caracterizam o período de relevância de cada registro.
A lista de casas do FIAS não contém ponteiros para os registros anteriores e seguintes sobre a casa. Portanto, a ordem dos registros do histórico real da casa é determinada pela data final decrescente e além dela, a data de início do período, respectivamente EndDate e StartDate .
SELECT * FROM fias_Houses h WHERE h.HOUSEGUID='2fd64ecd-4b4b-4fc8-bd15-4a4a2f310b21' ORDER BY h.ENDDATE DESC,h.STARTDATE DESC;
Leitor atento olhando para a Fig. 1, eu provavelmente me perguntei: por que os livros de referência de sinais de estrutura e propriedade são mencionados? O FIAS usa mais de 10 desses tipos de diretórios, então por que esses dois são destacados?
A resposta surpreenderá muitos - do ponto de vista da “lógica FIAS”, o endereço da casa não é totalmente identificado pelo endereço da rua, casa, prédio e número do prédio. O termo "lógica FIAS" foi usado na resposta de um funcionário do Serviço Federal de Tributação à minha pergunta: por que na lista de casas no Território de Krasnoyarsk existem mais de 250 endereços de casas emparelhados? A mesma resposta disse que a exclusividade do registro é fornecida pelos valores AOGUID, HOUSENUM, BUILDNUM, STRUCNUM, STRSTATUS, ESTSTATUS.
Em outras palavras, para encontrar um objeto, não basta saber a localidade, rua, número da casa. Você também deve saber:
- "Posse" é ou "propriedade de casa";
- o status deste objeto está definido ou não;
- etc.
É assim que uma amostra da lista geral de casas FIAS com endereços duplicados se parece.
O fato de objetos diferentes terem o mesmo endereço não é surpreendente. O edifício e a terra sob ele; casa, garagem, casa de banho para um proprietário. Todos eles têm o mesmo endereço. Mas o FIAS é um registro de endereço, ou seja, lista de endereços. Portanto, é natural esperar que os endereços sejam únicos, e não edifícios, estruturas, estruturas.
I.e. a lista de casas do FIAS a partir da lista de endereços de casas começou a se desenvolver em direção à lista de edifícios no térreo. E os usuários do FIAS precisam considerar isso.
Todos podem verificar a presença de casas com endereços duplicados executando uma instrução SELECT semelhante à seguinte. Ao mesmo tempo, a função fsfn_Houses_TreeActualName não pode ser usada, porque é usado apenas para reduzir o número de colunas no resultado. Não é necessário usar os diretórios fias_StructureStatus (análogo ao STRSTAT) e fias_EstateStatus (análogo ao ESTSTAT), como o efeito marcado também pode ser rastreado nos códigos de sinais de estrutura e posse.
código fonte do 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;
E, finalmente, outro recurso da lista inicial do FIAS. Cada registro interno desta lista contém um link para um elemento de formação de endereço, cuja lista é uma hierarquia de tais elementos. Em cada nível da hierarquia existem elementos de formação de endereço pertencentes a diferentes tipos. Portanto, o elemento raiz é a região (território de Krasnoyarsk, no nosso caso); no próximo nível, é um okrug autônomo, região ou cidade de subordinação regional. E assim por diante (Para detalhes, consulte "Endereços FIAS no PostgreSQL").
Formalmente, um registro de empresa permite que você se refira a um elemento da hierarquia em qualquer nível. Felizmente, não havia casas referentes a um distrito ou região entre os dados do Território de Krasnoyarsk. No entanto, nem todas as casas se referem à rua da vila:
- 98% das casas do FIAS estão ligadas a ruas em áreas povoadas;
- 1,2% das casas - com ruas em associações de jardinagem;
- 0,3% das casas são de assentamentos;
- 0,5% das casas com outros elementos endereçáveis.
Fig. 2)Propagação de endereços de casas pelos proprietários (FIAS vs mapa)
Aqui é descrito um problema que leva a uma interpretação ambígua da árvore genealógica. (Igor Leonidovich Timoshchenkov, especialista em GIS, Aigeo LLC, Krasnoyarsk, chamou minha atenção para esse problema.)
O exemplo acima mostra como vários registros contêm o mesmo endereço em casa. O que pode ser explicado pelo desejo da fiscalização de manter não apenas um registro de uma casa particular, mas também dos edifícios circundantes: uma garagem, um celeiro, etc. Mas há exemplos inversos quando vários prédios (fias_Houses) correspondem a um prédio (casa) com números diferentes para esta casa.
Dê uma olhada nesta foto. À esquerda, há uma captura de tela com um mapa da vila onde estão localizadas as casas para dois proprietários. Estas são casas comuns de um andar com duas entradas. Uma família vive à direita e outra à esquerda. Eles ainda podem ser imaginados como casas de dois apartamentos.
Agora olhe para a mesa à direita. Nele, quase todas as casas com dois proprietários correspondem a 3 entradas. I.e. A tabela de casas do FIAS mostra o endereço da casa individual (“d. 1”) e os endereços das partes da casa (“d. 1/1”, “d. 1/2”) pertencentes a um proprietário.
Como isso funciona
A função fstf_Houses_AddressObjectTree possui duas versões: com quatro ou com dois parâmetros. Na versão da função com dois parâmetros, o identificador da casa ( HouseGUID ) e a data de validade do registro (EndDate ) são transmitidos. Uma versão com quatro parâmetros requer adicionalmente um identificador para o elemento gerador de endereço ( AOGUID ) e o status atual ( CurrStatus ).
código fonte do 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;
Uma função com menos parâmetros calcula os valores dos parâmetros ausentes e chama uma função com um grande número de parâmetros. Para fazer isso, o identificador do elemento de formação de endereço é simplesmente recuperado do campo correspondente da tabela da casa (f ias_Houses ). E o valor do status atual ( CurrStatus ) é calculado de acordo com as seguintes regras:
- se nenhum dos registros de histórico do elemento de formação de endereço contiver 0 no campo CurrStatus, a variável v_CurrStatus receberá o valor máximo do campo para esse elemento de formação de endereço;
- caso contrário, essa variável recebe o valor 0.
Uma função com um grande número de parâmetros chama primeiro a função fstf_AddressObjects_AddressObjectTree , que retorna os elementos de formação de endereço pai para a casa. Você pode ler mais sobre a função fstf_AddressObjects_AddressObjectTree na seção Pedigree do elemento de formação de endereço do documento FIAS Addresses no PostgreSQL
.
Em seguida, as entradas sobre os elementos de formação de endereço são complementadas pelas entradas sobre os números da casa, prédio, estrutura (consulte a Tabela 1), criados para cada campo não vazio sobre o número da casa, prédio e estrutura.
Para que todos os registros de saída tenham a mesma estrutura e não sem um compartilhamento de flexibilidade, os valores do nível de código dos campos ( AOLevel ), status atual ( CurrStatus ) e status de relevância ( ActStatus ) são criados artificialmente no corpo da função.
O código para o nível da casa (edifício, estrutura) é sempre definido como 8; consulte o livro de referência "Níveis de objetos endereçáveis" do documento Informações do FIAS sobre a composição da informação ).
O status de relevância é definido como 1 se a data de validade do registro ( EndDate ) for 06.06.2079 e 0 em caso contrário.
Os valores do campo CurrStatus são mais complicados. Usando seus valores, duas tarefas são resolvidas simultaneamente: o identificador de cada versão do registro sobre o elemento de formação de endereço é definido e o sinal da relevância do registro é atribuído. Portanto, o último registro atual sobre um elemento contém um valor 0 neste campo e todos os registros históricos são numerados na ordem em que aparecem - “1” é o registro mais antigo que segue a tempo - “2” etc. A ordem de atribuição de valores ao campo CurrStatus é descrita em mais detalhes na publicação FIAS Addresses no PostgreSQL .
Título 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;
Endereço completo da casa
A idéia principal da função fsfn_Houses_TreeActualName é retornar o número da casa conectado em uma linha, juntamente com os nomes de todos os seus ancestrais - elementos de formação de endereço.
Por exemplo, deixe a função da árvore genealógica (fstf_Houses_AddressObjectTree) retornar a seguinte lista de valores.
Tabela 4. O resultado da função fstf_Houses_AddressObjectTree ('0c0d670f-097c-4e1d-bcac-9e9b22fb1b99')
Então fsfn_Houses_TreeActualName ('0c0d670f-097c-4e1d-bcac-9e9b22fb1b99') deve retornar: “ Sr. Krasnoyarsk, 34A St. Lazo, bld. 6, p. 17 ”.
A função fsfn_Houses_TreeActualName pode ser simplificada como uma função agregada STRING_AGG sobre o resultado de uma função que retorna uma árvore genealógica em casa.
A função em questão possui outro parâmetro opcional - uma matriz de máscaras ( a_MaskArray ), com a qual você pode incluir no resultado nem todos os nomes de elementos, mas apenas aqueles necessários.
Tabela 5. Lista de máscaras de função.
versão em texto da tabela| Valor | Nota |
|---|
| {HS} | Máscara - número da casa |
| {BY} | Máscara - número do caso |
| {BG} | Máscara - número do edifício |
| {ST} | Máscara - rua |
| {ZC} | Mask - Código Postal |
| {DT} | Máscara - área urbana |
| {LP} | Máscara - cidade subordinada |
| {LM} | Máscara - o assentamento principal |
| {TP} | Máscara - região do sujeito da federação |
| {TM} | Máscara - sujeito da federação (região) |
| {CY} | Máscara - País |
Veja também a seção “
Nome completo do elemento formador de endereço” da publicação “Endereços FIAS no PostgreSQL ”.
O texto da função é fornecido na seção Aplicativo "
Criando a função fsfn_Houses_TreeActualName ".
FIAS Home Search
A função fstf_Houses_SearchByName destina - se a procurar os endereços das casas FIAS por seus números e nomes dos elementos formadores de endereço. Além disso, a pesquisa pode ser realizada não apenas pelo nome e tipo do elemento atual, mas também pelos nomes e tipos de um ou dois de seus ancestrais mais próximos.
Vejamos alguns exemplos. E, para começar, encontraremos todas as casas com o número "220".
Tabela 6. Resultado da função fstf_Houses_SearchByName ('220')
Diferentemente da função de procurar elementos formadores de endereço ( fstf_AddressObjects_SearchByName ), o resultado dessa função não contém o efeito de "nadar" pelos níveis de elementos formadores de endereço. O primeiro parâmetro da função sempre contém o padrão de pesquisa para o número da casa, o segundo - o número da construção, o terceiro número da construção.
Agora mude a solicitação. Encontramos todas as casas de elementos formadores de endereços, cujo número contém o número "1" e a palavra "Krasnoyarsk" aparece nos nomes.
Tabela 7. O resultado da função fstf_Houses_SearchByName ('1', NULL, NULL, 'Krasnoyarsk')
A finalidade dos parâmetros restantes coincide exatamente com a finalidade dos parâmetros da função de pesquisa dos elementos geradores de endereço (fstf_AddressObjects_SearchByName).
O texto da função é fornecido na seção Aplicativo " Criando a função fstf_Houses_SearchByName "
.
Como isso funciona
A implementação de fstf_Houses_SearchByName é, em muitos aspectos, semelhante à implementação da função de pesquisa de elementos geradores de endereço (fstf_AddressObjects_SearchByName) . A principal diferença é que a pesquisa é realizada em duas tabelas relacionadas, fias_Houses e fias_AddressObjects .
A função possui 9 argumentos. Os três primeiros são os números da casa ( a_HouseNum ), o prédio (a_BuildNum ) e o prédio ( a_StrucNum ). Os 6 restantes ( a_FormalName , a_ShortName , a_ParentFormalName , a_ParentShortName , a_GrandParentFormalName , a_GrandParentShortName ) coincidem completamente com os parâmetros da função.
Se você definir apenas o valor do parâmetro “número da casa”, a função retornará todos os endereços no número da casa que a sequência especificada encontrar com um símbolo. Se você passar NULL ou uma string vazia ("") como o número da casa, serão retornados os endereços de todas as casas cujos elementos de endereço são especificados por um conjunto de outros parâmetros.
Epílogo
Esta seção contém recomendações sobre como carregar a lista de casas FIAS na tabela fias_Houses .
O carregamento de dados em uma tabela de casas é realizado da mesma maneira que o carregamento de dados em uma tabela de elementos de formação de endereço . Somente o arquivo de origem será HOUSE99.DBF , não ADDROB99.DBF . Aqui 99 é o número da região (república, oblast, território). Por exemplo, para o território de Krasnoyarsk, o arquivo de origem é o arquivo HOUSE24.DBF .
Primeiro, o próximo arquivo com a atualização é baixado da página Atualizações do FIAS. O arquivo HOUSE99.DBF é extraído dele .
.
Em seguida, o arquivo HOUSE99.DBF é convertido para o formato CSV e já convertido. Ele é carregado pela instrução COPY na tabela temporária fias_Houses_Temp .
E, finalmente, os dados temporários são usados para atualizar a tabela principal, ou seja, inexistentes em fias_Houses são adicionados e os existentes são substituídos.
Um exemplo de script para atualizar uma tabela de casas é fornecido na seção “ Download de atualizações de casas do FIAS na tabela fias_Houses ”.
App
Criando a função fstf_Houses_AddressObjectTree
Comentários sobre o código fonte da função podem ser encontrados aqui .
código de função 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;
Criando a função fsfn_Houses_TreeActualName
Comentários sobre o código fonte da função podem ser encontrados aqui .
código de função 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;
Criando a função fstf_Houses_SearchByName
Comentários sobre o código fonte da função podem ser encontrados aqui .código de função 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);
Criando uma tabela de casas 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 ' ';
Faça o download das atualizações da página inicial do FIAS na tabela fias_Houses
código fonte do 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;