上一篇文章介绍了FIAS地址和在PostgreSQL环境中使用它们的功能，引起了一小部分读者的兴趣。
因此，在PL / pgSQL中描述类似的功能以处理加载到运行PostgreSQL的数据库中的FIAS房屋列表是有意义的。
本文的前半部分提供了有关功能实现的评论。 在第二篇中，介绍了功能的源代码以及用于创建带有FIAS内部记录的表的脚本，以及用于将数据从CSV格式的文件加载到该表中的脚本。 对于只对源文本感兴趣的读者，我们建议立即继续阅读附录。
本文与“ PostgreSQL环境中的FIAS地址”系列文章的材料（
开始 ，
继续1 ，
继续2 ，
结束 ）密切相关。
家里的家谱
让我们从一个例子开始。
调用f函数stf_Houses_AddressObjectTree （'42301ab8-9ead-4f8e-8281-e64f2769a254'）将产生以下条目列表。
表1.函数的结果。
在仔细检查时，您可能会注意到元素标识符（ HOUSEGUID ）“ d。 1，建筑物。 2，第26页，因此收到了六条记录：
- 三个具有地址形成元素的父记录：关于区域，城市和街道的信息；
- 三个具有门牌特征的记录：门牌号，门牌号和门牌号。
该函数还有另一个可选参数-记录的到期日期（ EndDate ），通过该参数，您不仅可以查看关于房屋的当前记录的谱系，还可以查看已经过时的记录的谱系。
该函数的全文在创建函数fstf_Houses_AddressObjectTree的附录中给出 。
从一开始
如果您知道FIAS房屋表的布置方式，则可以跳过此部分。
FIAS房屋（ HOUSES ）是FIAS（ ADDROBJ ）的地址生成元素列表的子列表。 每个房屋列表条目均通过AOGUID字段的值引用FIAS地址生成元素。 为了确定房屋位于哪条街道和哪个位置，您需要通过HOUSES记录的AOGUID值找到具有相同列表标识符ADDROBJ的对应记录。
尽管房屋清单和其交互中的地址形成元素清单之间的相互作用机制在表面上很简单，但功能使房屋功能的实现复杂化。
首先，标识符AOGUID记录的房屋清单中的每个记录都涉及一组地址形成元素，其中一个是相关的。
其次，FIAS列表中有几个条目具有相同的门牌特征集：门牌号，门牌号，门牌号。
第三，房屋的记录并不总是从村庄街道的记录中继承的。
但是，首先是第一件事。
为了进一步考虑FIAS中房屋信息的存储，将自己限制为4个表（DBF文件）就足够了：
- ADDROBJ-地址形成元素列表；
- 房屋 - 房屋清单；
- STRSTAT-结构特征目录；
- ESTSTAT-所有权标志目录。
在以前的出版物“ PostgreSQL中的FIAS地址”中详细讨论了ADDROBJ ，因此这里将充分描述它的功能以描述房屋的特征。
表2。房屋的历史“克拉斯诺亚尔斯克地区，泰米尔米尔Dolgan-Nenetsky区，Dudinka，ul。 杜丁斯卡亚1
从表中可以看出，与地址形成对象相反，房屋历史记录没有特殊的相关标志。 该期间的最早结束日期大于当前日期的记录是相关的。 到目前为止，当前房屋记录的日期标记为06.06.2079。 关于房屋的所有其他记录均被视为历史记录，并且开始日期和结束日期描述了每个记录的相关时间段。
FIAS房屋列表不包含指向房屋的上一个和下一个记录的指针。 因此，从实际开始到房屋历史的记录顺序由递减的结束日期确定，超出该日期的开始日期分别为EndDate和StartDate 。
SELECT * FROM fias_Houses h WHERE h.HOUSEGUID='2fd64ecd-4b4b-4fc8-bd15-4a4a2f310b21' ORDER BY h.ENDDATE DESC,h.STARTDATE DESC;
细心的读者看图。 1，我可能会问自己一个问题：为什么提到结构和所有权标志的参考书？ FIAS使用了超过10种此类目录，那么为什么要突出显示这两个目录？
答案将使很多人感到惊讶-从“ FIAS逻辑”的角度来看，房屋的地址不能完全由街道地址，房屋，建筑物和建筑物编号完全识别。 联邦税务局的一名雇员回答了我的问题，为什么在克拉斯诺亚尔斯克地区的房屋清单中有超过250个成对的房屋地址，因此使用了“ FIAS逻辑”一词。 相同的答案表示，记录的唯一性由值AOGUID，HOUSENUM，BUILDNUM，STRUCNUM，STRSTATUS，ESTSTATUS提供。
换句话说，要找到一个对象，仅仅知道位置，街道，门牌号码是不够的。 您还必须知道：
- “财产”是或“房屋所有权”；
- 该对象的状态已定义或未定义；
- 等
这就是FIAS房屋总列表中地址重复的样本的样子。
不同对象具有相同地址的事实不足为奇。 建筑物及其下面的土地； 一位业主的房屋，车库，浴室。 他们都有相同的地址。 但是FIAS是地址寄存器，即 地址列表。 因此，很自然地期望地址在其中是唯一的，而不是建筑物，结构，结构。
即 房屋地址列表中的FIAS房屋列表开始向地面建筑物列表发展。 FIAS用户需要考虑这一点。
每个人都可以通过执行类似于以下内容的SELECT语句来检查是否存在地址重复的房屋。 同时，无法使用函数fsfn_Houses_TreeActualName，因为 它仅用于减少结果中的列数。 不必使用fias_StructureStatus（STRSTAT的类似物）和fias_EstateStatus（类似于ESTSTAT）目录，例如 明显的效果也可以追溯到结构和占有标志的代码上。
操作员源代码 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;
最后，FIAS主页列表的另一个功能。 该列表的每个房屋记录均包含指向地址形成元素的链接，该地址形成元素的列表是此类元素的层次结构。 在层次结构的每个级别上，都有属于不同类型的地址形成元素。 因此，根本要素是地区（在我们的例子中为克拉斯诺亚尔斯克地区），在下一级是自治的直辖市，地区或地区从属城市。 依此类推。 （有关详细信息，请参见“ PostgreSQL中的FIAS地址”）。
正式地，房屋记录允许您在任何级别引用层次结构的元素。 幸运的是，在克拉斯诺亚尔斯克地区的数据中，没有房屋提到一个地区或地区。 然而，并非所有房屋都指村庄的街道：
- FIAS 98％的房屋与人口稠密地区的街道相连；
- 1.2％的房屋-在园艺协会中有街道；
- 0.3％的房屋属于定居点；
- 有其他可寻址元素的房屋的0.5％。
图 2。业主传播房屋地址（FIAS与地图）
这里描述的一个问题导致对家谱的模棱两可的解释。 （克拉斯诺亚尔斯克Aigeo LLC的GIS专家Igor Leonidovich Timoshchenkov提请我注意这个问题。）
上面显示了几个记录如何在家里包含相同的地址。 税务监察机构不仅要保留私人房屋的记录，而且还要保留周围建筑物的记录：车库，谷仓等，这可以解释。 但是，当多个建筑物（fias_Houses）对应于一栋具有不同编号的建筑物（house）时，有相反的例子。
看看这张照片。 左侧是屏幕快照，其中包含两个所有者的房屋所在村庄的地图。 这些是带有两个入口的普通一层房屋。 一个家庭住在右边，另一个家庭住在左边。 仍然可以将它们想象成来自两个公寓的房屋。
现在看右边的表。 在其中，几乎每个有两个所有者的房子都对应3个条目。 即 FIAS房屋表显示单个房屋的地址（“ d。1”）和属于一个所有者的房屋部分的地址（“ d。1/1”，“ d。1/2”）。
如何运作
fstf_Houses_AddressObjectTree函数具有两个版本：具有四个或具有两个参数。 在具有两个参数的函数版本中，将传输房屋标识符（ HouseGUID ）和记录的到期日期（EndDate ）。 具有四个参数的版本还需要用于地址生成元素（ AOGUID ）和当前状态（ CurrStatus ）的标识符 。
操作员源代码 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;
参数较少的函数将计算缺少的参数的值，然后调用具有大量参数的函数。 为此，只需从房屋表的相应字段（f ias_Houses ）中检索地址形成元素的标识符。 并且根据以下规则计算当前状态的值（ CurrStatus ）：
- 如果地址形成元素的历史记录在CurrStatus字段中都不包含0，则为v_CurrStatus变量分配此地址形成元素的最大字段值；
- 否则，将为该变量分配值0。
具有大量参数的函数首先调用函数fstf_AddressObjects_AddressObjectTree ，该函数返回房屋的父地址形成元素。 您可以在PostgreSQL的FIAS地址的地址形成元素的谱系部分中了解有关fstf_AddressObjects_AddressObjectTree函数的更多信息
。
然后，有关地址形成元素的条目由有关房屋，建筑物，结构编号的条目补充（请参见表1），这些条目是针对有关房屋，建筑物和结构编号的每个非空字段创建的。
为了使所有输出记录具有相同的结构，并且没有某种程度的泛滥，在功能主体中人工创建了字段代码级别（ AOLevel ），当前状态（ CurrStatus ）和关联状态（ ActStatus ）的值。
房屋（建筑物，构筑物）级别的代码始终设置为8，请参阅《 FIAS信息构成文档 》中的参考书“可寻址对象的级别”。
如果记录（ EndDate ）的到期日期是06.06.2079 ，则关联状态设置为1，否则设置为0。
CurrStatus字段值更加复杂。 使用其值，可以同时解决两个任务：设置有关地址形成元素的记录的每个版本的标识符，并分配记录相关性的符号。 因此，有关元素的最后一条当前记录在该字段中的值为0 ，并且所有历史记录均按出现顺序编号-“ 1”是最早的记录，在时间上紧随其后-“ 2”，依此类推。 将值分配给CurrStatus字段的顺序在PostgreSQL的FIAS地址中有更详细的描述。
扰流板方向 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;
满屋地址
fsfn_Houses_TreeActualName函数的主要思想是返回在一行中连接的房屋编号及其所有祖先的名称-地址形成元素。
例如，让族树函数（fstf_Houses_AddressObjectTree）返回以下值列表。
表4.函数fstf_Houses_AddressObjectTree（'0c0d670f-097c-4e1d-bcac-9e9b22fb1b99'）的结果
然后fsfn_Houses_TreeActualName （'0c0d670f-097c-4e1d-bcac-9e9b22fb1b99'）应该返回：“ Krasnoyarsk先生，位于 Bld St. Lazo，34A。 6，p。17 ”。
可以将函数fsfn_Houses_TreeActualName简化为集合函数STRING_AGG ，而不是将函数返回家中的树的结果。
有问题的函数还有另一个可选参数-掩码数组（ a_MaskArray ），您可以在掩码中包含所有元素名称，而不是所有元素名称。
表5.功能掩码列表。
表格的文字版本| 价值 | 注意事项 |
|---|
| {HS} | 面具-门牌号码 |
| {BY} | 口罩-盒号 |
| {BG} | 面罩-门牌号 |
| {ST} | 面具-街道 |
| {ZC} | 面具-邮编 |
| {DT} | 面具-市区 |
| {LP} | 面具-下属镇 |
| {LM} | 口罩-主要解决方案 |
| {TP} | 遮罩-联盟主题的区域 |
| {TM} | 遮罩-联盟的主题（区域） |
| {CY} | 面具-国家 |
另请参见
出版物“ PostgreSQL中的FIAS地址”的“地址形成元素的全名 ”部分。
函数文本在“应用程序”部分“
创建fsfn_Houses_TreeActualName函数 ”中给出。
FIAS主页搜索
fstf_Houses_SearchByName函数用于按编号和地址形成元素的名称搜索FIAS房屋的地址。 而且，不仅可以通过当前元素的名称和类型来进行搜索，还可以通过其最接近祖先中的一两个的名称和类型来进行搜索。
让我们看几个例子。 首先，我们将找到所有编号为“ 220”的房屋。
表6.函数fstf_Houses_SearchByName（'220'）的结果
与搜索地址形成元素（ fstf_AddressObjects_SearchByName ）的功能不同，此函数的结果不包含在地址形成元素的各个级别进行“游动”的效果。 该函数的第一个参数始终包含房屋编号的搜索模式，第二个-建筑物编号，第三个建筑物编号。
现在更改请求。 我们发现所有地址形成元素的房子，其地址包含数字“ 1”，并且名称中出现单词“ Krasnoyarsk”。
表7.函数fstf_Houses_SearchByName的结果（“ 1”，NULL，NULL，“ Krasnoyarsk”）
其余参数的用途与地址生成元素（fstf_AddressObjects_SearchByName）的搜索功能的参数用途完全一致。
该函数的文本在应用程序部分“ 创建fstf_Houses_SearchByName函数 ”中给出
。
如何运作
fstf_Houses_SearchByName的实现在许多方面类似于地址生成元素（fstf_AddressObjects_SearchByName）的搜索功能的实现。 主要区别在于搜索是在两个相关的表fias_Houses和fias_AddressObjects中执行的 。
该函数有9个参数。 其中的前三个是门牌号 （ a_HouseNum ），建筑物（a_BuildNum ）和建筑物（ a_StrucNum ）。 其余6个（ a_FormalName ， a_ShortName ， a_ParentFormalName ， a_ParentShortName ， a_GrandParentFormalName ， a_GrandParentShortName ）与函数参数完全一致。
如果仅设置“门牌号”参数的值，则该函数将返回门牌号中指定序列与符号相遇的所有地址。 如果传递NULL或空字符串（“”）作为门牌号，则将返回其地址元素由一组其他参数指定的所有房子的地址。
结语
本节包含有关如何将FIAS房屋列表加载到fias_Houses表中的建议。
将数据加载到房屋表中的方式与将数据加载到地址形成元素表中的方式几乎相同。 仅源文件将是HOUSE99.DBF ，而不是ADDROB99.DBF 。 这里的99是地区编号（共和国，州，地区）。 例如，对于克拉斯诺亚尔斯克地区，源文件是HOUSE24.DBF文件。
首先，从FIAS 更新页面下载具有更新的下一个存档。 HOUSE99.DBF文件是从中提取的。
。
然后将HOUSE99.DBF文件转换为CSV格式并已转换，然后通过COPY语句将其加载到临时表fias_Houses_Temp中 。
最后，临时数据用于更新主表，即 添加了fias_Houses中不存在的内容，并替换了现有的内容。
“将FIAS房屋更新下载到fias_Houses表 ”部分中提供了用于更新房屋表的脚本示例。
应用程式
创建fstf_Houses_AddressObjectTree函数
有关函数源代码的注释，请参见此处 。
功能码 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;
创建fsfn_Houses_TreeActualName函数
有关函数源代码的注释，请参见此处 。
功能码 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;
创建fstf_Houses_SearchByName函数
有关函数源代码的注释，请参见此处。功能码 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);
创建FIAS房屋表fias_Houses
脚本代码 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 ' ';
将FIAS主页更新下载到fias_Houses表
脚本源代码 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;