0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@ausssxi0(開発者 MotoHub)

全国4万件のバイク駐車場を9,000駅に紐付けた話 — 座標計算とデータクリーニングの泥臭い戦い

0
Posted at

はじめに

個人開発のバイクポータルサイト MotoHub では、全国約4万件のバイク駐車場データを掲載しています。駅別の駐車場ランディングページを作るために、40,569件の駐車場を9,032駅に紐付ける作業を行いました。

最終的に22,864件（56.4%）の紐付けに成功。都市部の主要駅周辺はほぼ100%カバーできています。

image.png

なぜ駅と紐付けるのか

「渋谷 バイク 駐輪場」「新宿駅 バイク 駐車場」といった駅名ベースの検索に対応するためです。地域検索のランディングページとして、駅ごとに周辺駐車場をまとめたページを生成します。

データ構造

駐車場テーブル（bike_parkings）

CREATE TABLE bike_parkings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    address TEXT,
    prefecture VARCHAR(20),
    city VARCHAR(100),         -- ← ここに問題あり
    latitude DECIMAL(10,7),
    longitude DECIMAL(10,7),
    station_id BIGINT UNSIGNED, -- 最寄り駅
    notes TEXT,                 -- 「最寄り駅: 渋谷駅」等
    price_per_hour INT,
    price_per_month INT,
    -- ...
);

駅テーブル（stations）

id, name, slug, prefecture, latitude, longitude, is_major

全国9,032駅のデータを投入済み。主要30駅には is_major = true を設定。

紐付けアルゴリズム

Step 1: notes（メモ欄）からの駅名抽出

駐車場データのnotesに「最寄り駅: 渋谷駅」等の記載がある場合、正規表現で駅名を抽出してマッチング。

// notesから駅名を抽出
preg_match('/最寄り駅[:：]?\s*(.+?)駅/', $parking->notes, $matches);
if ($matches) {
    $stationName = $matches[1];
    $station = Station::where('name', $stationName)
        ->where('prefecture', $parking->prefecture)
        ->first();
}

Step 2: 座標距離計算（ハーバーサイン公式）

notesで見つからない場合は、座標から最寄り駅を計算します。半径500m以内の最も近い駅を紐付け。

image.png

// ハーバーサイン公式でSQLクエリ
$nearbyStation = Station::selectRaw("
    *,
    (6371000 * ACOS(
        COS(RADIANS(?)) * COS(RADIANS(latitude)) *
        COS(RADIANS(longitude) - RADIANS(?)) +
        SIN(RADIANS(?)) * SIN(RADIANS(latitude))
    )) AS distance
", [$parking->latitude, $parking->longitude, $parking->latitude])
->having('distance', '<=', 500) // 500m以内
->orderBy('distance')
->first();

Step 3: 一括処理（OOM対策）

4万件を一度に処理するとメモリが足りないので、chunk処理にしています。

BikeParking::whereNull('station_id')
    ->chunk(500, function ($parkings) {
        foreach ($parkings as $parking) {
            $station = $this->findNearestStation($parking);
            if ($station) {
                $parking->update(['station_id' => $station->id]);
            }
        }
    });

cityカラムのデータ品質問題

紐付け以前に、city カラムのデータ品質が壊滅的でした。

問題1: Unicode安全でないtrim

PHPの ltrim() がマルチバイト文字のバイトを破壊していました。

// ❌ ltrimがUTF-8のバイトを削る
ltrim('蒲郡市', '蒲') // → 文字化け（0xE8を削ってしまう）

// ✅ mb_ltrimを使う（PHP 8.3以前はpolyfill）
function mbLtrim(string $str, string $chars): string {
    return preg_replace('/^[' . preg_quote($chars, '/') . ']+/u', '', $str);
}

問題2: 都道府県名の混入

-- 期待値
prefecture = '神奈川県', city = '相模原市'

-- 実際のデータ
prefecture = '神奈川県', city = '神奈川県相模原市'

問題3: 政令指定都市の区欠落

-- 期待値
city = '横浜市西区'

-- 実際のデータ
city = '横浜市'  -- 区がない

問題4: 重複文字・不正文字

city = '大阪市大阪市北区'  -- 市名が重複
city = '渋谷区渋谷区'      -- 区名が重複

データクリーニング結果

修正スクリプト: FixParkingCities

5段階の修復パイプラインを実装しました。

class FixParkingCities extends Command
{
    private function repairBadCity(string $city, string $prefecture): string
    {
        // Stage 1: NFKC正規化
        $city = Normalizer::normalize($city, Normalizer::FORM_KC);

        // Stage 2: 都道府県名除去
        $city = str_replace($prefecture, '', $city);

        // Stage 3: 重複文字除去
        $city = preg_replace('/(.{2,})\1+/u', '$1', $city);

        // Stage 4: 政令指定都市の区補完
        if ($this->isSeirei($city) && !preg_match('/区$/u', $city)) {
            $city = $this->inferWardFromNearestNeighbor($city, $prefecture);
        }

        // Stage 5: isCleanCity()で最終チェック
        return $city;
    }
}

座標最近傍での区補完

政令指定都市で区が欠けている場合、同じ市内で座標が最も近い駐車場の区を参照して補完します。

private function inferWardFromNearestNeighbor(string $city, string $pref): string
{
    // 同じ市名で区が入っている最寄りレコードを検索
    $neighbor = BikeParking::where('prefecture', $pref)
        ->where('city', 'LIKE', $city . '%区')
        ->orderByRaw("ST_Distance_Sphere(
            POINT(longitude, latitude),
            POINT(?, ?)
        )", [$this->currentLng, $this->currentLat])
        ->first();

    return $neighbor ? $neighbor->city : $city;
}

修正件数

処理 件数
parking:fix-cities 14,653件
fix_production_cities（本番修正） 841件
追加修正 数百件
政令指定都市の区欠落→座標補完 23件
station:fix-cities 1,218件

駅別ランディングページ

紐付けが完了したら、駅ごとのLPを自動生成します。

URL構造

/parking/station              -- 駅一覧
/parking/station/tokyo        -- 主要駅トップ
/parking/station/shinjuku     -- 新宿駅の駐車場
/parking/station/shibuya      -- 渋谷駅の駐車場

image.png

ページの内容

  • 駅周辺の駐車場一覧（距離順）
  • Leaflet地図にマーカー表示
  • 料金相場（時間/日額/月極の平均）
  • FAQ + JSON-LD
  • 周辺の他の駅へのリンク

エリアページ

image.png

SEO効果

318ページの地域LP（47都道府県 + 271市区町村）を自動生成。サイトマップに登録してGSCに送信しました。

GSCサイトマップ

紐付け結果

指標 値
総駐車場数 40,569件
紐付け済み 22,864件
紐付け率 56.4%
対象駅数 9,032駅
主要30駅のカバー率 ほぼ100%

56%は低く見えますが、紐付けできなかった17,705件の大半は駅から500m以上離れた郊外の駐車場です。駅別LPの目的は「駅近駐車場を見つけたいユーザー向け」なので、駅から遠い駐車場が紐付かないのはむしろ正しい動作です。

今後の改善

  • 半径拡大（500m → 1km）: 地方駅のカバレッジ向上
  • notes活用の強化: 「○○駅から徒歩X分」パターンの抽出
  • ランドマーク紐付け: 駅以外の主要スポット（大学、商業施設等）

まとめ

  • GISデータの紐付けは座標計算だけでは不十分。テキスト解析（notes）との組み合わせが必要
  • データ品質問題（Unicode破壊、重複文字、区欠落）は地味だが避けて通れない
  • PHPの ltrim() はマルチバイト文字に対して安全ではない
  • 政令指定都市の区補完には座標最近傍が有効
  • chunk処理でOOMを防ぎつつ、4万件を安定して処理
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?