log_pass_tsubame.geom（乗降地点）から最も近い stop_tsubame.geom（停留所）を1件だけ割り当て、その停留所ごとの乗車回数（on_off=1）と降車回数（on_off=0）を集計するSQL

ebata

11時間前

PostGISのgeomを使うと、なかなか便利

tsubame_db=# select * from stop_tsubame;
 id | objectid | stop_num |           name           |  geom_lon   |  geom_lat   |                        geom
----+----------+----------+--------------------------+-------------+-------------+----------------------------------------------------
  1 |        1 |        1 | 1．平高山                | 139.5683772 | 35.60588564 | 0101000020E610000058B9612530726140407520A98DCD4140
  2 |        2 |        2 | 2．高山団地上            | 139.5719216 | 35.60374933 | 0101000020E610000008FC862E4D7261407CAA75A847CD4140
  3 |        3 |        3 | 3．高山住宅              | 139.5727103 | 35.60306519 | 0101000020E6100000A3128DA453726140F9337A3D31CD4140
  4 |        4 |        4 | 4．ラヴィーレ前          | 139.5743602 | 35.60227363 | 0101000020E6100000F863A42861726140EE0B644D17CD4140
  5 |        5 |        5 | 5．センチュリータウン    | 139.5752315 |    35.60174 | 0101000020E61000002104E44B68726140F758FAD005CD4140
  6 |        6 |        6 | 6．たいらなか公園        | 139.5739583 | 35.60125333 | 0101000020E610000091F8CBDD5D726140057B7EDEF5CC4140
  7 |        7 |        7 | 7．ENEOS平SS             | 139.5744435 | 35.59918301 | 0101000020E6100000CBBC55D76172614069226407B2CC4140
  8 |        8 |        8 | 8．JA・向ヶ丘出張所      | 139.5769224 | 35.59937236 | 0101000020E61000008E0AF72576726140C529C63BB8CC4140
  9 |        9 |        9 | 9．小林外科胃腸科        | 139.5823024 | 35.60155191 | 0101000020E61000003C8CA438A2726140EE252AA7FFCC4140
 10 |       10 |       10 | 10．サンドラッグ前       | 139.5774515 | 35.59948257 | 0101000020E6100000D670917B7A726140055648D8BBCC4140
 11 |       11 |       11 | 11．マルエツ平二丁目店   | 139.5735948 | 35.59891768 | 0101000020E6100000FB647BE35A726140504CA455A9CC4140
 12 |       12 |       12 | 12．相鉄ローゼンたいら店 | 139.5698639 | 35.60027754 | 0101000020E610000079B537533C7261406369F9E4D5CC4140
 13 |       13 |       13 | 13．街ノ停留所           | 139.5691983 | 35.60159248 | 0101000020E61000000B6E5ADF367261404D8F7DFB00CD4140
(13 rows)


tsubame_db=# select * from log_pass_tsubame;

objectid | vehicle_id | psg_id | bus_num | on_off | act_type |      ride_time      |    created_time     |                        geom                        |      geom_lon      |      geom_lat
----------+------------+--------+---------+--------+----------+---------------------+---------------------+----------------------------------------------------+--------------------+--------------------
    56006 | tsubame001 |    999 |       3 |      1 | user     | 2025-09-24 11:06:31 | 2025-09-24 11:07:23 | 0101000020E610000044C41737FF7161407830404750CB4140 | 139.56240420000006 | 35.588387400000045
    56007 | tsubame001 |    999 |       3 |      0 | user     | 2025-09-24 11:07:47 | 2025-09-24 11:08:22 | 0101000020E6100000288B5649FF71614028C10AA650CB4140 | 139.56241290000003 |  35.58839870000003
    56406 | tsubame001 |    999 |      -1 |      1 | user     | 2025-09-29 09:31:41 | 2025-09-29 09:32:19 | 0101000020E6100000AC9E16CD54726140A076E73F5ACD4140 | 139.57285170000011 |  35.60431670000003
    56407 | tsubame001 |    999 |      -1 |      0 | user     | 2025-09-29 09:31:57 | 2025-09-29 09:32:19 | 0101000020E6100000B08CFCB054726140705DED725BCD4140 | 139.57283830000006 |  35.60435330000007
    56408 | tsubame001 |      1 |       1 |      1 | user     | 2025-09-29 09:59:59 | 2025-09-29 10:00:44 | 0101000020E61000003448B0273072614048F0AE6994CD4140 | 139.56837830000006 | 35.606091700000036
    56409 | tsubame001 |      2 |       1 |      1 | user     | 2025-09-29 10:00:11 | 2025-09-29 10:00:45 | 0101000020E61000001CEFD819307261407813382394CD4140 | 139.56837170000006 |  35.60608330000008
    56410 | tsubame001 |      1 |       1 |      0 | user     | 2025-09-29 10:05:20 | 2025-09-29 10:05:44 | 0101000020E6100000F402ECA353726140602A6F4738CD4140 | 139.57271000000003 |  35.60328000000004
    56411 | tsubame001 |      2 |       1 |      0 | user     | 2025-09-29 10:05:22 | 2025-09-29 10:05:44 | 0101000020E61000006C4DF38E53726140589B49E537CD4140 | 139.57270000000005 | 35.603268300000025
    56412 | tsubame001 |    514 |       2 |      0 | user     | 2025-09-29 10:43:52 | 2025-09-29 10:44:44 | 0101000020E61000009844ACB43B72614010BAAC78D9CC4140 | 139.56978830000003 |  35.60038670000006

log_pass_tsubame.geom（乗降地点）から最も近い stop_tsubame.geom（停留所）を1件だけ割り当て、その停留所ごとの 乗車回数（on_off=1） と 降車回数（on_off=0） を集計するSQLです（PostGIS前提）。

WITH counts AS (
  SELECT
    s.stop_num,
    SUM(CASE WHEN l.on_off = 1 THEN 1 ELSE 0 END) AS board,
    SUM(CASE WHEN l.on_off = 0 THEN 1 ELSE 0 END) AS alight
  FROM public.log_pass_tsubame AS l
  JOIN LATERAL (
    SELECT stop_num
    FROM public.stop_tsubame
    ORDER BY l.geom <-> public.stop_tsubame.geom
    LIMIT 1
  ) AS s ON TRUE
  GROUP BY s.stop_num
)
SELECT
  -- "1．平高山" → "1. 平高山"
  regexp_replace(st.name, '^([0-9]+)[．\.]\s*', '\1. ') AS name,
  st.geom_lat AS lat,
  st.geom_lon AS lng,
  COALESCE(c.board, 0)  AS board,
  COALESCE(c.alight, 0) AS alight
FROM public.stop_tsubame AS st
LEFT JOIN counts AS c
  ON c.stop_num = st.stop_num
WHERE c.stop_num IS NOT NULL     -- 割当実績のある停留所のみ
ORDER BY st.stop_num;

その結果は以下の通り

name           |     lat     |     lng     | board | alight
--------------------------+-------------+-------------+-------+--------
 1. 平高山                | 35.60588564 | 139.5683772 |    39 |     44
 2. 高山団地上            | 35.60374933 | 139.5719216 |     9 |     39
 3. 高山住宅              | 35.60306519 | 139.5727103 |    54 |     91
 4. ラヴィーレ前          | 35.60227363 | 139.5743602 |     3 |      9
 5. センチュリータウン    |    35.60174 | 139.5752315 |     2 |      5
 6. たいらなか公園        | 35.60125333 | 139.5739583 |     0 |      1
 7. ENEOS平SS             | 35.59918301 | 139.5744435 |     3 |     15
 8. JA・向ヶ丘出張所      | 35.59937236 | 139.5769224 |     4 |     34
 9. 小林外科胃腸科        | 35.60155191 | 139.5823024 |     7 |     21
 10. サンドラッグ前       | 35.59948257 | 139.5774515 |    11 |      5
 11. マルエツ平二丁目店   | 35.59891768 | 139.5735948 |    13 |      2
 12. 相鉄ローゼンたいら店 | 35.60027754 | 139.5698639 |   163 |     42
(12 rows)