施設のフリーテキスト入力の住所をPostgreSQLの文字列置換で立ち向かう

検索プラットフォーム事業部エンジニアの相澤です。
普段はPostgreSQLで複数の旅行会社のデータをまとめるような処理を取り扱っています。

弊社の得意な分野はまさに旅行系の「複雑かつ膨大な」在庫・料金などのデータ処理なのですが、これを高速に扱えるのであれば、他の部分に目が行くのがエンジニアのサガ。

そこで、様々な会社から入稿される施設データの中で特に厄介なものである、「フリーテキスト入力」をなんとか綺麗にできないかと考えました。

前がたり

旅行会社が持つ情報というのは、「電話番号」「緯度経度」「郵便番号」「住所」「禁煙・喫煙／露天風呂／インターネット環境／WiFi etcの有無」「バリアフリー／幼児／ペットetcの対応状況」というものになっているのですが、電話番号・郵便番号・緯度経度は数字の全角半角の表記ゆれがある程度でデータ管理がしやすいのに対し、施設名・住所は大抵の場合、入力する人が入力欄に書き込んだ通りにデータ入稿されるため、表記ゆれや意図しないデータが入るなど非常に管理しにくいのです。

フリーテキスト入力なものでも「説明」「補足」「口コミ」といったデータは、 最低限の処理を施したらwebサイトに掲載できるのですが（なお、HTML制御文字が混ざっていたり、HTMLが入力されていたりする場合があります。口コミや宣伝文句に<strong>とか<big>とか混ぜないでください･･･。そういったものは取り除く必要があります）、住所に関しては違います。

別々の会社から入稿されたこのデータとあのデータは、同じ施設のデータなのか、はたまた同名の異なる施設のデータなのか。住所は施設（建物）の同一性を保証する重要なデータとなります（なお建物名は表記ゆれが大きく、A棟B棟,離れ別館などがあるので案外当てになりません）。

そこで今回は フリーテキストで入力された住所を、人が同じ住所か違う住所か判断できるレベルまでPostgreSQLの文字列置換を使って正規化することに取り組んでみたいと思います。

今回は住所に関する専門的な知識は使わず、一般的な技術の範囲内で取り組んでいきます。

やっていくぞ！

目標
与えられたデータを、同じ住所のものは同じ形に正規化する関数を作成します！

準備

テーブルlocationListを下記のように定義します。

論理名	物理名	型
フリーテキスト入力住所カラム	address_original	test
空の正規化住所カラム	address_normalized	text

以下のようにして置換を行い、正規化します。

CREATE OR REPLACE FUNCTION normalize(original text) RETURNS text AS $funcbody$
DECLARE
    result  text;
BEGIN
    result := original;
-- この辺に置換処理を書く
    RETURN result;
END;
$funcbody$ LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;

UPDATE locationList SET address_normalized = normalize(address_original);

step1 ：注意文言や制御文字の削除 (regexp_replace を用いた削除)

住所欄には、住所以外の文言が入力されている場合が多々あります。
例: 『(駐車場は裏にあります)』『※〇〇駅徒歩５分 』『〇〇市（旧：☓☓町）』
　そのほか電話番号、セールストークetc･･･

こういうもののほとんどはカッコや記号の後ろに書いてあるので、これを目印に消しましょう。

使う関数は正規表現を扱える regexp_replaceですね！以下のようになります。
丁寧にやるならカッコの対応をきちんと種類ごとにしてもいいですが、 現実的に入力されるカッコの種類は一定ではなく、シンタックスも必ずしも一致しません。 カッコは最短マッチで消した後、残っているものも消してしまいます。

-- カッコの中身の削除
    result := regexp_replace(
        result
        ,E'(\\(|（|\\[|「|【|『|〈|《).*?(\\)|）|\\]|」|】|』|〉|》)'
        ,E''
        ,'g'
    );
    result := regexp_replace(
        result
        ,E'(\\(|（|\\[|「|【|『|〈|《|\\)|）|\\]|」|】|』|〉|》)'
        ,E''
        ,'g'
    );
    -- 注意文言の削除
    result := regexp_replace(
        result
        ,E'(※|＊|◎|～|~|★|■|◆|●|☆|□|◇|○|●|(TEL)|℡|〒).*$'
        ,E''
        ,'g'
    );

HTML制御文字は&から始まり、;で終わりますが、入力システム側で大文字に変換されたりして、無効化されている場合もあります。大文字小文字両方に対応し、制御文字を取り除きます。

-- 制御文字の削除
    result := regexp_replace(
        result
        ,E'(&|＆).+?(;|；)'
        ,E''
        ,'g'
    );

step2：かな・アルファベット・数字表記ゆれ対応 (translateを用いた置換)

住所にはたくさんの"かな"が含まれますが、ひらがな・カタカナ・濁点・半濁点は意外と一致しませんので正規化します。translateという関数は、一対一対応で文字を置換してくれますので、これを使いましょう。

ちなみに濁点や半濁点は単体でも入力できますし、半角カナでは独立するので、気をつけて置換する必要があります。

-- 制御文字の削除
    result := regexp_replace(
        result
        ,E'(&|＆).+?(;|；)'
        ,E''
        ,'g'
    );
    -- 濁点半濁点対応
    result := translate(
        result
        ,'ゔヴがぎぐげごガギグゲゴざじずぜぞザジズゼゾだぢづでどダヂヅデドばびぶべぼバビブベボぱぴぷぺぽパピプペポﾟﾞ゛゜'
        ,'ううかきくけこかきくけこさしすせそさしすせそたちつてとたちつてとはひふへほはひふへほはひふへほはひふへほ'
    );
    -- 歴史的仮名遣い対応
    result := translate(
        result
        ,'ゐゑヰヱ'
        ,'いえいえ'
    );
    -- 小文字を大文字に
    result := translate(
        result
        ,'ぁぃぅぇぉｧｨｩｪｫァィゥェォヵヶっッｯゃゅょャュョｬｭｮ'
        ,'あいうえおあいうえおあいうえおかけつつつやゆよやゆよやゆよ'
    );
    -- 半角カナを全角カナに
    result := translate(
        result
        ,'ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝ'
        ,'あいうえおかきくけこさしすせそたちつてとなにぬねのはひふへほまみむめもやゆよらりるれろわおん'
    );
    -- 全角カナをひらがなに
    result := translate(
        result
        ,'アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲン'
        ,'あいうえおかきくけこさしすせそたちつてとなにぬねのはひふへほまみむめもやゆよらりるれろわおん'
    );

step3：スペース・ハイフンの置換(unicodeを用いた置換)

さて、基本的な正規化を終えるにはあと一歩ですがこれが面倒なのです。

スペース・ハイフンには実はものすごい種類があります（今回は対応しませんがチルダも厄介です）。エンジニアでもなければ全角ハイフン・半角ハイフンなどは区別しないのかもしれません。

基本的な記号は置換してしまいましょう。

• 全角スペース・半角スペース・タブ → 今回は 消します
• 全角ハイフン・半角ハイフンetc.. → 半角ハイフンに統一しましょう（全角ハイフン系の記号にすると漢数字のイチと見分けが付きにくので）

ここで E'(全角スペース|半角スペース|タブ)'のようなパターンの書き方もできますが、これだと一目で見て何をやっているのかよくわからなくなってしまいますね。
スペースはまだマシですが、ハイフンなどは何が対応できていて何に対応できていないのかがわからなくなってしまいます。

ここはUnicodeを使用して置換をコントロールします。具体的には以下のようにします。

--スペースの削除
    result := regexp_replace(
        result
        ,E'(\\u0020|\\u00A0|\\u0009)'
        ,E''
        ,'g'
    );
    -- ハイフンの統一
    result := regexp_replace(
        result
        ,E'(\\u002D|\\uFF0D|\\u2212|\\u2015|\\u2010|\\u2011|\\u2012|\\u2013|\\u2014|\\uFF70|\\u4E00)'
        ,E'\u002D'
        ,'g'
    );

ハイフンとして長音記号を扱うかどうかは、かなり難しいところです。
というのも、ディズニｰランドのように何故かハイフンと長音を混同した入力がみられるためです（制御文字などに対しても思うのですが、素朴な疑問としてどうやって打っているのでしょうか･･･）。私は長音記号はハイフンとしませんでした。

ちなみに、「一丁目3一7」のようにハイフンの代わりに漢数字のイチが入力される場合もあり、そちらも正規化を諦めました（普通に不正データでは･･･）。

step4：住所ゆれの対応（捕捉変数を使う）

住所にはいくつかの"どちらもあっている"パターンがあります。

1. 〇〇県(〇〇郡)〇〇町 の 郡はあってもなくても良いものです。
〇〇県 + (１文字以上) + 郡 + (１文字以上)市町村というパターンに関して"郡"を抜いてしまえば良さそうです。
100%の精度ではないですが、ある程度はカバーできそうです。
PostgreSQLの正規表現には(先読み|後読み)(肯定|否定)はありませんのでキャプチャー（捕捉変数）を用いて置換します。

-- 都道府県 + 郡 の無視
    result := regexp_replace(
        result
        ,E'^(北海道|青森県|岩手県|宮城県|秋田県|山形県|福島県|茨城県|栃木県|群馬県|埼玉県|千葉県|東京都|神奈川県|新潟県|富山県|石川県|福井県|山梨県|長野県|岐阜県|静岡県|愛知県|三重県|滋賀県|京都府|大阪府|兵庫県|奈良県|和歌山県|鳥取県|島根県|岡山県|広島県|山口県|徳島県|香川県|愛媛県|高知県|福岡県|佐賀県|長崎県|熊本県|大分県|宮崎県|鹿児島県|沖縄県)(.+?郡)(.+(市|町|村))'
        ,E'\\1\\3\\4'
    );

行政区画の中に"字"（あざ）、大字（おおあざ）が入ってくる場合があります。私の祖父の家もそういった行政区画だったのですが、住んでいる本人たちも正式にはどう書くべきか知らないようでした。
市町村名などに"字"が含まれる場合に誤って消してしまうかもしれませんが、それで混同してしまうような市町村はなさそうだったので、私はこれを無視します。

-- 字・大字は判断に使えないので無視する
    result := regexp_replace(
        result
        ,E'大?字'
        ,E''
        ,'g'
    );

また、京都には「〇〇通り」「〇〇上ル」「下る」「入る」といった地名があるようですが、この送り仮名はあったりなかったりひらがなだったりカタカナだったりします。送り仮名は消してしまいましょう。

-- 入る・上る・下るの[る]はあったりなかったりカタカナだったりするので消す
    result := regexp_replace(
        result
        ,E'(入|上|下)る'
        ,E'\\1'
        ,'g'
    );
    -- 通りの[り]はあったりなかったりカタカナだったりするので消す
    result := regexp_replace(
        result
        ,E'(通)り'
        ,E'\\1'
        ,'g'
    );

step5：番地以下の正規化（以下は新しい技術性はありません）

番地以下のカテゴリーには区切るハイフンだけではなく、丁・丁目・番・番地・番街・番町・号・ハイフンなどのバリエーションがあるので、これらをすべてハイフンで統一してしまいましょう。
余計なことはしないように、数字の後ろの場合にのみ置換をかけます。

result := regexp_replace(
        result
        ,E'(０|１|２|３|４|５|６|７|８|９|〇|一|二|三|四|五|六|七|八|九|十|百|千)(丁目?(の|\\u002D)?|番(地|町|街)?(の|\\u002D)?|の|号)'
        ,E'\\1\u002D'
        ,'g'
    );

この処理には問題があります。「四ノ宮(→変換されて「四の宮」)」のような地名は、巻き込まれて「四-宮」になってしまいます。
予め、漢数字 + ノ ＋ 数字以外("四ノ宮"など) は特別に扱うためにカタカナのノに戻します。

また、北海道では「条」が「丁目」のように使われています。
京都やその他の地域でこのようなことはないので、北海道の「条」だけ変換をかけると良さそうですね。

-- 番地の正規化
    -- 漢数字 + ノ ＋ 数字以外("四ノ宮"など) は特別に扱うためにカタカナに戻す
    result := regexp_replace(
        result
        ,E'(一|二|三|四|五|六|七|八|九|十|百|千)(の)([^０-９])'
        ,E'\\1ノ\\3'
        ,'g'
    );
    result := regexp_replace(
        result
        ,E'(０|１|２|３|４|５|６|７|８|９|〇|一|二|三|四|五|六|七|八|九|十|百|千)(丁目?(の|\\u002D)?|番(地|町|街)?(の|\\u002D)?|の|号)'
        ,E'\\1\u002D'
        ,'g'
    );
    -- 北海道は「条」をハイフンとして扱う
    result := regexp_replace(
        result
        ,E'^(北海道.*)(０|１|２|３|４|５|６|７|８|９|〇|一|二|三|四|五|六|七|八|九|十|百|千)(条)'
        ,E'\\1\\2\u002D'
        ,'g'
    );

step6：説明や建物名を除く

住所に混入された説明や建物名を完全に除くのは難しいです。できることがあるとすれば、「最初に登場する算用数字・ハイフン群の後ろは、説明か何かと判断する」という方法です。
ただし現実には「2条河原」のように地名も算用数字で入稿される場合があるので、それは少々大雑把すぎるやり方です。

-- 不要な説明や建物名を除く
    result := regexp_replace(
        result
        ,E'^(.*?)((０|１|２|３|４|５|６|７|８|９|\\u002D)+)(.*)$'
        ,E'\\1\\2'
    );
    result := regexp_replace(
        result
        ,E'\\u002D$'
        ,E''
        ,'g'
    );

結果

これらの処理を行うことで、私の扱っているデータからは句読点や記号は綺麗サッパリ消すことができました。
しかし、今回作った関数ではうまく扱えていないパターンもありますのでご紹介します。

• 建物の名前の扱い
  記号やそのほか様々なものが入ってきており、住所に建物の名前が入っているパターンでは、建物の名前をきれいにすることができませんでした。 今回は建物自体が同じかどうかを判断するものだったので、無視しましたが、 テナント等を判別する場合難しい課題になりそうです。
• 千葉県浦安なのに住所が「東京都浦安～」となっているもの
  千葉県の「東京」ディズニーラントだけではなく、千葉・埼玉にこのパターンが結構ありました（データ提供サイトのご都合かもしれません）。
  郵便番号などその他のデータをもとに上書きしてしまったほうが良いかもしれません。
• 旧字の統一
  旧字体が正式な住所の場合、新字と旧字が混ざってしまうパターンがあります。
  そういったパターンすべてを洗い出してtranslateすれば良いのですが、洗い出しができませんでした。
• 漢数字・ローマ数字
  漢数字やローマ数字を算用数字に置換することができませんでした。
  うまいやり方があるのでしょうか･･･
• 京都の住所の一部
  「 〇〇番地〇〇通り〇〇丁目 」のように、丁目以下にも細かい情報が入ってきて対応しきれていません。
• ハイフンと漢数字のイチと長音記号
  「星のリゾｰト」のような記載や「三丁目一2（ハイフンではなく漢数字のイチ）」のようなパターンへの対応ができませんでした。
• 住所の真ん中に説明がガンガン入ってくるパターン
• 顔文字
  取り除ききれない部分がありました。

上記に関しては私では解消しきれませんでしたが、住所や日本語の知識をつければ対応できるものもありそうです！

完成品

CREATE OR REPLACE FUNCTION normalize(original text) RETURNS text AS $funcbody$
DECLARE
    result  text;
BEGIN
    result := original;
    --スペースの削除
    result := regexp_replace(
        result
        ,E'(\\u0020|\\u00A0|\\u0009)'
        ,E''
        ,'g'
    );
    -- ハイフンの統一
    result := regexp_replace(
        result
        ,E'(\\u002D|\\uFF0D|\\u2212|\\u2015|\\u2010|\\u2011|\\u2012|\\u2013|\\u2014|\\uFF70|\\u4E00)'
        ,E'\u002D'
        ,'g'
    );
    -- カッコの中身の削除
    result := regexp_replace(
        result
        ,E'(\\(|（|\\[|「|【|『|〈|《).*?(\\)|）|\\]|」|】|』|〉|》)'
        ,E''
        ,'g'
    );
    result := regexp_replace(
        result
        ,E'(\\(|（|\\[|「|【|『|〈|《|\\)|）|\\]|」|】|』|〉|》)'
        ,E''
        ,'g'
    );
    -- 注意文言の削除
    result := regexp_replace(
        result
        ,E'(※|＊|◎|～|~|★|■|◆|●|☆|□|◇|○|●|(TEL)|℡|〒).*$'
        ,E''
        ,'g'
    );
    -- 制御文字の削除
    result := regexp_replace(
        result
        ,E'(&|＆).+?(;|；)'
        ,E''
        ,'g'
    );
    -- 濁点半濁点対応
    result := translate(
        result
        ,'ゔヴがぎぐげごガギグゲゴざじずぜぞザジズゼゾだぢづでどダヂヅデドばびぶべぼバビブベボぱぴぷぺぽパピプペポﾟﾞ゛゜'
        ,'ううかきくけこかきくけこさしすせそさしすせそたちつてとたちつてとはひふへほはひふへほはひふへほはひふへほ'
    );
    -- 歴史的仮名遣い対応
    result := translate(
        result
        ,'ゐゑヰヱ'
        ,'いえいえ'
    );
    -- 小文字を大文字に
    result := translate(
        result
        ,'ぁぃぅぇぉｧｨｩｪｫァィゥェォヵヶっッｯゃゅょャュョｬｭｮ'
        ,'あいうえおあいうえおあいうえおかけつつつやゆよやゆよやゆよ'
    );
    -- 半角カナを全角カナに
    result := translate(
        result
        ,'ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝ'
        ,'あいうえおかきくけこさしすせそたちつてとなにぬねのはひふへほまみむめもやゆよらりるれろわおん'
    );
    -- 全角カナをひらがなに
    result := translate(
        result
        ,'アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲン'
        ,'あいうえおかきくけこさしすせそたちつてとなにぬねのはひふへほまみむめもやゆよらりるれろわおん'
    );
    -- アルファベットの正規化
    result := translate(
        result
        ,'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
        ,'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ'
    );
    result := translate(
        result
        ,'abcdefghijklmnopqrstuvwxyz'
        ,'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ'
    );
    result := translate(
        result
        ,'ａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ'
        ,'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ'
    );
    result := translate(result
        ,'ΑΒΓΔΕΖΗΘΙΚΛΜΝΞΟΠΡΣΤΥΦΧΨΩ'
        ,'ＡＢＧＤＥＺＨＱＩＫＬＭＮＸＯＰＲＳＴＵＦＣＹＷ'
    );
    result := translate(result
        ,'αβγδεζηθικλμνξοπρστυφχψω'
        ,'ＡＢＧＤＥＺＨＱＩＫＬＭＮＸＯＰＲＳＴＵＦＣＹＷ'
    );
    -- 数字の正規化
    result := translate(result
        ,'0123456789ⅰⅠⅱⅡⅲⅢ'
        ,'０１２３４５６７８９１２１２３３'
    );
    -- 都道府県 + 郡 の無視
    result := regexp_replace(
        result
        ,E'^(北海道|青森県|岩手県|宮城県|秋田県|山形県|福島県|茨城県|栃木県|群馬県|埼玉県|千葉県|東京都|神奈川県|新潟県|富山県|石川県|福井県|山梨県|長野県|岐阜県|静岡県|愛知県|三重県|滋賀県|京都府|大阪府|兵庫県|奈良県|和歌山県|鳥取県|島根県|岡山県|広島県|山口県|徳島県|香川県|愛媛県|高知県|福岡県|佐賀県|長崎県|熊本県|大分県|宮崎県|鹿児島県|沖縄県)(.+?郡)(.+(市|町|村))'
        ,E'\\1\\3\\4'
    );
    -- 字・大字は判断に使えないので無視する
    result := regexp_replace(
        result
        ,E'大?字'
        ,E''
        ,'g'
    );
    -- 入る・上る・下るの[る]はあったりなかったりカタカナだったりするので消す
    result := regexp_replace(
        result
        ,E'(入|上|下)る'
        ,E'\\1'
        ,'g'
    );
    -- 通りの[り]はあったりなかったりカタカナだったりするので消す
    result := regexp_replace(
        result
        ,E'(通)り'
        ,E'\\1'
        ,'g'
    );
    -- 番地の正規化
    -- 漢数字 + ノ ＋ 数字以外("四ノ宮"など) は特別に扱うためにカタカナに戻す
    result := regexp_replace(
        result
        ,E'(一|二|三|四|五|六|七|八|九|十|百|千)(の)([^０-９])'
        ,E'\\1ノ\\3'
        ,'g'
    );
    result := regexp_replace(
        result
        ,E'(０|１|２|３|４|５|６|７|８|９|〇|一|二|三|四|五|六|七|八|九|十|百|千)(丁目?(の|\\u002D)?|番(地|町|街)?(の|\\u002D)?|の|号)'
        ,E'\\1\u002D'
        ,'g'
    );
    -- 北海道は「条」をハイフンとして扱う
    result := regexp_replace(
        result
        ,E'^(北海道.*)(０|１|２|３|４|５|６|７|８|９|〇|一|二|三|四|五|六|七|八|九|十|百|千)(条)'
        ,E'\\1\\2\u002D'
        ,'g'
    );
    -- 不要な説明や建物名を除く
    result := regexp_replace(
        result
        ,E'^(.*?)((０|１|２|３|４|５|６|７|８|９|\\u002D)+)(.*)$'
        ,E'\\1\\2'
    );
    result := regexp_replace(
        result
        ,E'\\u002D$'
        ,E''
        ,'g'
    );
    RETURN result;
END;
$funcbody$ LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;

最後に

フォルシアではデータクレンジングを専門にしているエンジニアもいますが、今回は私の勉強も兼ねていたので、自力で試行錯誤しました。

正規表現はパズルみたいな面白さもあり、仕様には知識だけではなく経験も必要なので、適切な課題を設定して取り組むのは面白いですね。