PostgreSQLを実行しているデータベースにロードされたFIAS要素。 ここに、 最初と2番目の部分へのリンクがあります。
記事全体は4つのパートで構成されています。 この記事の前半では、関数の実装に関するコメントを提供しています。 第二に-関数のソースコード。 ソーステキストのみに関心がある読者のために、 付録に直接アクセスすることを提案します。
住所要素検索
fstf_AddressObjects_SearchByName関数は、名前でFIASアドレス生成要素を検索するように設計されています。 さらに、現在の要素の名前とタイプだけでなく、最も近い先祖の1つまたは2つの名前とタイプによっても検索を実行できます。
いくつかの例を見てみましょう。 そもそも、名前に「マッシュルーム」という単語が含まれるすべての住所形成要素を見つけます。
表8.関数fstf_AddressObjects_SearchByName（ 'Mushroom'）の結果
| AOGUID | アオレベル | 完全な住所 | ショートネーム | 正式名称 | Currstatus | ActStatus |
|---|---|---|---|---|---|---|
| 15faf08c-78b6-4b92-8a56-2ff70f2c4cab | 6 | アチンスキー地区、pきのこ | n | きのこ | 0 | 1 |
| f1772172-4dd1-449d-b2d2-ab96883d8871 | 7 | キノコごとのケジンスキー地区、コディンスク氏 | あたり | きのこ | 0 | 1 |
| 146cbcb5-4ad9-4578-916f-80ebd5c2b846 | 7 | エメリャノフスク地区、エリート、マッシュルームあたり | あたり | きのこ | 0 | 1 |
| a8ee8caf-fd5f-489c-92d9-f560e3f93c8b | 7 | スホブジンスキー地区、d。シェスタコヴォ、マッシュルームあたり | あたり | きのこ | 0 | 1 |
| 84f4baa8-1db2-471d-967d-20d489bca68e | 7 | きのこあたり、チュギンチャトのあるクラギンスキー地区 | あたり | きのこ | 0 | 1 |
| 1f2b7975-ce05-4627-bd13-d8d6228accd7 | 7 | ソルスク氏、マッシュルームあたり | あたり | きのこ | 0 | 1 |
フルネームを作成する機能の利点の視覚的な証拠を除いて、結果には予期しないものはありません。
リクエストを変更します。 「マッシュルーム」という単語が出現する最も近い先祖の名前のすべての住所形成要素を検索します。
表9. fstf_AddressObjects_SearchByName関数の結果
（NULL、NULL、「マッシュルーム」）
| AOGUID | アオレベル | 完全な住所 | ショートネーム | 正式名称 | Currstatus | ActStatus | 親の短縮名 | 親の正式名 |
|---|---|---|---|---|---|---|---|---|
| 45064ade-a0a7-4258-88c8-baa57094aa2d | 7 | アチンスキー地区、p。Mushroom、st。Railway | st | 鉄道 | 0 | 1 | n | きのこ |
| ba4ec53c-50b7-4325-866a-81f97a38214c | 7 | アチンスキー地区、p。Mushroom、Zapadnaya st。 | st | 西部 | 0 | 1 | n | きのこ |
| d6e9e0cc-e944-4deb-a09c-c545af691836 | 7 | アチンスキー地区、p。Mushroom、St。North | st | 北 | 0 | 1 | n | きのこ |
| 5ae71e68-5477-446b-b878-0a9c9bf3bdcd | 7 | アチンスキー地区、p。キノコ、セントサウス | st | 南 | 0 | 1 | n | きのこ |
このクエリの結果は、 アドレス形成要素の名前には「マッシュルーム」という単語はありませんが、先祖の名前に含まれています。
そして最後に、「アチンスキー」という単語が存在し、彼の孫の名前に「Oz_rn」という単語の一部となる前駆体の名前による検索を考えてみましょう。 ここでは、特殊文字が使用されます-下線文字「_」。 この記号は、単一の記号がその場所にあることを示します。 ここでは、「Lake」または「Lake」という名前の要素だけでなく、「Lake」または「Lake」の要素も検索するために適用されます。
表9. fstf_AddressObjects_SearchByName関数の結果（NULL、NULL、 'Mushroom'）
| AOGUID | アオレベル | 完全な住所 | ショートネーム | 正式名称 | Currstatus | ActStatus | 親の短縮名 | 親の正式名 | グランドペアレントショートネーム | 祖父母の正式名称 |
|---|---|---|---|---|---|---|---|---|---|---|
| 715eef9d-48f6-4322-bcaa-9d239e89b7e4 | 7 | Achinsky地区、dバラバノフカ、レーンオゼルニー | あたり | 湖 | 0 | 1 | d | ドラム | 地区 | アチンスキー |
| 05c7b2ad-e405-4c8b-9503-6761971e858e | 7 | Achinsky地区、d。Ilyinka、Ozernaya st。 | st | 湖 | 0 | 1 | d | イリンカ | 地区 | アチンスキー |
| bdfcd515-1851-4caf-83ba-12ee79f9f6a7 | 7 | カザチンスキー地区、ドゥドフカ、オゼルナヤ通り | st | 湖 | 0 | 1 | と | ドゥドフカ | 地区 | カザチンスキー |
リクエストの結果、クラスノヤルスク地域のアチンスキー地区とカザチンスキー地区の3つの集落でオゼルナヤ通りとオゼルニ車線が見つかりました。 関数のテキストは、 アプリケーションセクションの「fstf_AddressObjects_SearchByName関数の作成」に記載されています 。
仕組み
値が最初の2つの引数（アドレス生成要素の名前（a_FormalName）とタイプ（a_ShortName））のみに割り当てられている場合、検索はfias_AddressObjectsテーブルのすべてのレコードで実行されます。 以前は、転送されたパラメーターの値は大文字に変換され、スペースは記号「％」に置き換えられました。 同じシンボルが左右の値を囲んでいます。 この方法で変換された値は、LIKE操作の一部として検索クエリで使用されます。 そのような要求の例を図に示します。 4。
図 4.アドレス形成要素の簡単な検索。
CurrStatusの値を選択する条件については、「アドレス形成要素の血統」「仕組み」のセクションの記事の最初の部分で詳しく説明しました。
親アドレス生成要素の名前とタイプで検索するには、2つの引数の少なくとも1つに値を割り当てる必要があります：3番目（a_ParentFormalName）、または4番目（a_ParentShortName）。 この場合、検索は、すべてのレコードfias_AddressObjectsとpfa.AOGUID = cfa.ParentGUIDに基づく親アドレス生成要素のレコードを結合（INNER JOIN）することによって取得されたテーブルのすべてのレコードで実行されます。
そのような要求の例を図に示します。 5。
図 5.親アドレス生成要素の名前とタイプで検索します。
入力パラメーター値の前処理は、単純な検索の場合と同じルールに従って実行されます。 前駆体アドレス形成要素の名前とタイプで検索するには、2つの引数の少なくとも1つに値を割り当てる必要があります：5番目（a_GrandParentFormalName）または6番目（a_GrandParentShortName）。 この場合、すべてのfias_AddressObjectsレコードを親および祖父母の住所形成要素のレコードと二重結合（INNER JOIN）して取得したテーブルのすべてのレコードで検索が実行されます。 そのような要求の例を図に示します。 6。
図 6.前駆体アドレス形成要素の名前とタイプで検索します。
入力パラメーター値の前処理は、単純な検索の場合と同じルールに従って実行されます。
付録
fstf_AddressObjects_SearchByName関数の作成
関数ソースコード
BEGIN TRANSACTION; DROP FUNCTION IF EXISTS fstf_AddressObjects_SearchByName( 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_AddressObjects_SearchByName( a_FormalName VARCHAR(150), /* */ /* */ 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_AOLevel INTEGER, rtf_AddressObjectsFullName VARCHAR(1000), rtf_ShortName VARCHAR(20), rtf_FormalName VARCHAR(150), rtf_CurrStatus 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_FormalNameTemplate VARCHAR(150); /* */ /* */ v_ShortNameTemplate VARCHAR(20); /* */ /* */ v_ParentFormalNameTemplate VARCHAR(150); /* */ /* */ v_ParentShortNameTemplate VARCHAR(20); /* */ /* */ v_GrandParentFormalNameTemplate VARCHAR(150); /* */ /* */ v_GrandParentShortNameTemplate VARCHAR(20); /* */ /* */ --************************************************************ --************************************************************ BEGIN v_ShortNameTemplate:=UPPER(COALESCE(c_WildChar|| REPLACE(TRIM(a_ShortName),c_BlankChar,c_WildChar)|| c_WildChar,c_WildChar)); v_FormalNameTemplate:=UPPER(c_WildChar|| REPLACE(TRIM(a_FormalName),c_BlankChar,c_WildChar)|| c_WildChar); IF a_ParentFormalName IS NULL AND a_ParentShortName IS NULL AND a_GrandParentFormalName IS NULL AND a_GrandParentShortName IS NULL THEN RETURN QUERY SELECT cfa.AOGUID,cfa.AOLevel, fsfn_AddressObjects_TreeActualName(cfa.AOGUID), cfa.ShortName,cfa.FORMALNAME, cfa.currstatus,NULL::VARCHAR,NULL::VARCHAR, NULL::VARCHAR,NULL::VARCHAR FROM fias_AddressObjects cfa WHERE cfa.currstatus= CASE WHEN 0 < ALL(SELECT iao.currstatus FROM fias_AddressObjects iao WHERE cfa.aoguid = iao.aoguid) THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao WHERE cfa.aoguid = iao.aoguid) ELSE 0 END AND UPPER(cfa.FORMALNAME) LIKE v_FormalNameTemplate AND UPPER(cfa.ShortName) LIKE v_ShortNameTemplate ORDER BY cfa.AOLevel,cfa.ShortName,cfa.FORMALNAME; ELSIF a_ParentFormalName IS NOT NULL AND a_GrandParentFormalName IS NULL AND a_GrandParentShortName IS NULL THEN v_ParentShortNameTemplate:=UPPER(COALESCE(c_WildChar|| REPLACE(TRIM(a_ParentShortName),c_BlankChar,c_WildChar)|| c_WildChar,c_WildChar)); v_ParentFormalNameTemplate:=UPPER(c_WildChar|| REPLACE(TRIM(a_ParentFormalName),c_BlankChar,c_WildChar)|| c_WildChar); v_FormalNameTemplate:=COALESCE(v_FormalNameTemplate,c_WildChar); RETURN QUERY SELECT cfa.AOGUID,cfa.AOLevel,fsfn_AddressObjects_TreeActualName(cfa.AOGUID), cfa.ShortName,cfa.FORMALNAME,cfa.currstatus, pfa.ShortName,pfa.FORMALNAME, NULL::VARCHAR,NULL::VARCHAR FROM fias_AddressObjects pfa INNER JOIN fias_AddressObjects cfa ON pfa.AOGUID=cfa.ParentGUID WHERE cfa.currstatus=CASE WHEN 0 < ALL (SELECT iao.currstatus FROM fias_AddressObjects iao WHERE cfa.aoguid = iao.aoguid) THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao WHERE cfa.aoguid = iao.aoguid) ELSE 0 END AND pfa.currstatus=CASE WHEN 0 < ALL(SELECT iao.currstatus FROM fias_AddressObjects iao WHERE pfa.aoguid = iao.aoguid) THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao WHERE pfa.aoguid = iao.aoguid) ELSE 0 END AND UPPER(pfa.FORMALNAME) LIKE v_ParentFormalNameTemplate AND UPPER(pfa.ShortName) LIKE v_ParentShortNameTemplate AND UPPER(cfa.FORMALNAME) LIKE v_FormalNameTemplate AND UPPER(cfa.ShortName) LIKE v_ShortNameTemplate ORDER BY pfa.ShortName,pfa.FORMALNAME, cfa.AOLevel,cfa.ShortName,cfa.FORMALNAME; ELSE v_GrandParentShortNameTemplate:=UPPER(COALESCE(c_WildChar|| REPLACE(TRIM(a_GrandParentShortName),c_BlankChar,c_WildChar)|| c_WildChar,c_WildChar)); v_GrandParentFormalNameTemplate:=UPPER(c_WildChar|| REPLACE(TRIM(a_GrandParentFormalName),c_BlankChar,c_WildChar)|| c_WildChar); v_ParentShortNameTemplate:=COALESCE(UPPER(COALESCE(c_WildChar|| REPLACE(TRIM(a_ParentShortName),c_BlankChar,c_WildChar)|| c_WildChar,c_WildChar)),c_WildChar); v_ParentFormalNameTemplate:=COALESCE(UPPER(c_WildChar|| REPLACE(TRIM(a_ParentFormalName),c_BlankChar,c_WildChar)|| c_WildChar),c_WildChar); v_FormalNameTemplate:=COALESCE(v_FormalNameTemplate,c_WildChar); RETURN QUERY SELECT cfa.AOGUID,cfa.AOLevel,fsfn_AddressObjects_TreeActualName(cfa.AOGUID), cfa.ShortName,cfa.FORMALNAME, cfa.currstatus,pfa.ShortName,pfa.FORMALNAME, gpfa.ShortName,gpfa.FORMALNAME FROM fias_AddressObjects gpfa INNER JOIN fias_AddressObjects pfa ON gpfa.AOGUID=pfa.ParentGUID INNER JOIN fias_AddressObjects cfa ON pfa.AOGUID=cfa.ParentGUID WHERE cfa.currstatus=CASE WHEN 0 < ALL(SELECT iao.currstatus FROM fias_AddressObjects iao WHERE cfa.aoguid = iao.aoguid) THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao WHERE cfa.aoguid = iao.aoguid) ELSE 0 END AND pfa.currstatus=CASE WHEN 0 < ALL(SELECT iao.currstatus FROM fias_AddressObjects iao WHERE pfa.aoguid = iao.aoguid) THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao WHERE pfa.aoguid = iao.aoguid) ELSE 0 END AND gpfa.currstatus=CASE WHEN 0 < ALL(SELECT iao.currstatus FROM fias_AddressObjects iao WHERE gpfa.aoguid = iao.aoguid) THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao WHERE gpfa.aoguid = iao.aoguid) ELSE 0 END AND UPPER(gpfa.FORMALNAME) LIKE v_GrandParentFormalNameTemplate AND UPPER(gpfa.ShortName) LIKE v_GrandParentShortNameTemplate AND UPPER(pfa.FORMALNAME) LIKE v_ParentFormalNameTemplate AND UPPER(pfa.ShortName) LIKE v_ParentShortNameTemplate AND UPPER(cfa.FORMALNAME) LIKE v_FormalNameTemplate AND UPPER(cfa.ShortName) LIKE v_ShortNameTemplate ORDER BY gpfa.ShortName,gpfa.FORMALNAME, pfa.ShortName,pfa.FORMALNAME, cfa.AOLevel,cfa.ShortName,cfa.FORMALNAME; END IF; END; $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION fstf_AddressObjects_SearchByName( a_FormalName VARCHAR(150),a_ShortName VARCHAR(20), a_ParentFormalName VARCHAR(150),a_ParentShortName VARCHAR(20), a_GrandParentFormalName VARCHAR(150),a_GrandParentShortName VARCHAR(20)) IS ' '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; --SELECT * FROM fstf_AddressObjects_SearchByName('',''); --SELECT * FROM fstf_AddressObjects_SearchByName(''); --SELECT * FROM fstf_AddressObjects_SearchByName('',NULL,''); --SELECT * FROM fstf_AddressObjects_SearchByName(NULL,NULL,''); --SELECT * FROM fstf_AddressObjects_SearchByName('_','',NULL); SELECT * FROM fstf_AddressObjects_SearchByName('_','','',NULL,'');
ご清聴ありがとうございました！