CBcloud テックブログはじめます

概要

• はじめます記事です
• よろしくね

CBcloudとは

物流 x テクノロジーをかけ合わせた、次世代の物流インフラを作る会社です！

物流業界には小口配送増加・人材不足・業務の非効率・評価制度など様々な課題を抱えています。わたしたちの提供するあたらしいカタチが、これらの課題を解決し、新たなサービスの誕生や、事業の拡大に寄与できると信じています。
わたしたちはこれまでの物流業界での経験・ノウハウ、先端のテクノロジー、そして常にお客様の視点をもって、「運ぶ」「届ける」のカタチを変革し、物流に関わるすべてのスタンダードを再構築していきます。また、荷主様とお客様つなぐドライバー様の仕事が正しく評価され、ただモノを運ぶだけではない、その先のキャリアに繋がる土台づくりのパートナーとなり、物流業界に関わる人々の未来を創り続けていきます。

主に、Web開発（Rails、Vue、Nuxt）とモバイル開発（Flutter、Swift、Kotlin）をメインに、BtoC、BtoB向けにプロダクト展開している会社になります。

というわけでQiitaでやっていくぞ！！！

なぜテックブログをはじめるのか

実ははじめてました。 まずはこちらを御覧ください。

去年Zennで進めていましたが、書いてくれる人が書くというような進みでありました。

今期からは、CBcloudのメンバーが書いていく方針を用意したのと、色々なやり取りの後に今期はQiitaに書いていくぞ、ということになりました。

というわけでQiitaでやっていくぞ！！！

どんな技術ネタを投稿したいのか

よくカジュアル面談等で聞かれるのが

• 物流をメインに社会課題へコミットしてそうだが、具体どんなことをやっているのかわからない
• どういう技術を使って、どういう仕事をしているのか
• どういう人が働いているのか

主にこの3つが多いので、それも含めてQiitaで書いていけたらなーと思っています！

ちなみに最近TV CMも出しました！（沖縄限定）

また、露出が少ないとは言われていますが、一応多数インタビューされてたりしています！！！

ので、せっかくなので紹介です。

よかったらみてくれ！！

というわけでQiita(ry

最後に

まとめません。

ここからは技術の時間だーーーーーーーーーーーーーーーーーーーーー！！！

僕がいるチームは、プロダクトオペレーションズというチームです。主にパートナーサクセス本部が担当している運行サポートする人たちをエンパワーするためのプロダクトを開発しています。

弊社モバイルアプリでは、運行中のステータスを管理するためにGPS情報を取得していますが、その情報を用いて、色々ガチャガチャすることが多いので、ある程度GPSの扱いを理解する必要があります。

Google Map APIとMySQLを駆使して、高速に検索したり、データを取得したりすることが多いです。

というわけで入門しましょう！

MySQLの空間データ型について

GPS情報と聞くと、緯度経度を思い浮かべるでしょう。これらのデータは「空間データ」と呼ばれます。

MySQLでこれらのデータを保存する際、単純に考えると小数点表記なので、float型やdouble型で定義すればよいと考えるかもしれません。
しかし、その方法では、GPS情報を活用するための距離計算などをアプリケーション側で実装する必要があります。

例えば、空間データの仕様では「ある地点から指定距離内にある全てのレストランを検索する」ということができます。

しかし、float型などで保存した場合、地点間の距離を求めようとするとアプリケーション側でその計算を行うためには、一度データをすべて取得しなければならず、非常に非効率です。

そこで、MySQLには「空間データを扱える型」が用意されています。これにより、距離計算などの空間的な操作をデータベース側で効率的に処理することができます。

詳しくは公式ドキュメントをご参照ください。(丸投げ)

検証するために今回はPointを利用してみます。

検証環境を用意する

Dockerで用意しましょう。

ChatGPTにMySQL8とJupyterの環境を用意してくれと依頼してできたのがこれです。

version: '3.8'

services:
  mysql:
    image: mysql:8.0
    container_name: mysql
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: testdb
      MYSQL_USER: testuser
      MYSQL_PASSWORD: testpassword
    ports:
      - "3306:3306"
    volumes:
      - mysql-data:/var/lib/mysql
      - ./my.cnf:/etc/mysql/conf.d/my.cnf  # カスタム設定ファイルをマウント

  jupyter:
    image: jupyter/base-notebook:python-3.11
    container_name: jupyter
    environment:
      JUPYTER_ENABLE_LAB: "yes"
    ports:
      - "8888:8888"
    volumes:
      - jupyter-data:/home/jovyan
      - ./notebooks:/home/jovyan/work
    command: start-notebook.sh --NotebookApp.token=''

volumes:
  mysql-data:
  jupyter-data:

$ touch my.conf
$ vim my.conf

データをINSERTするときにメモリエラーで実行できなくなったので、先んじてここでmy.confを作っておいてください。

[mysqld]
max_allowed_packet=256M

ここから下記コマンドで起動します。ここまでで50秒です。すごい時代だ。

$ docker compose up

Kaggleのデータをダウンロードしてテーブルへ保存する

このデータをお借りします。

Jupyterを起動したら、セル内でガチャガチャします。

!pip install mysql-connector-python pandas

import mysql.connector
import pandas as pd
from sqlalchemy import create_engine, text


# MySQLへの接続
host = "mysql"
user = "testuser"
password = "testpassword"
database = "testdb"

conn = mysql.connector.connect(
    host=host,
    user=user,
    password=password,
    database=database
)

cursor = conn.cursor()
cursor.execute("SELECT DATABASE();")
database_name = cursor.fetchone()
print(f"Connected to database: {database_name[0]}")

engine = create_engine(f'mysql+mysqlconnector://{user}:{password}@{host}/{database}')

df = pd.read_csv('./work/transjakarta_gps.csv')
# positionカラムを作成するためのカラムを追加
df['position'] = df.apply(lambda row: f"POINT({row['longitude']} {row['latitude']})", axis=1)

# 一時テーブルにデータフレームを保存
df.to_sql('gps_data_temp', con=engine, if_exists='replace', index=False)

# テーブル作成
create_query = '''CREATE TABLE IF NOT EXISTS gps_data (
    bus_code VARCHAR(255),
    trip_id VARCHAR(255),
    gps_datetime DATETIME,
    location VARCHAR(255),
    dtd FLOAT,
    corridor VARCHAR(255),
    position POINT NOT NULL,
    speed FLOAT,
    course FLOAT,
    color VARCHAR(255),
    SPATIAL INDEX(position)
);
'''

with engine.connect() as connection:
    connection.execute(text(create_query))


# 一時テーブルからPOINT型にしてコピーする
# PandasではST_GeomFromTextを指定することができないため
insert_query = '''
INSERT INTO gps_data (bus_code, trip_id, gps_datetime, location, dtd, corridor, position, speed, course, color)
SELECT 
    bus_code, trip_id, gps_datetime, location, dtd, corridor, ST_GeomFromText(position), speed, course, color
FROM gps_data_temp;
'''


with engine.connect() as connection:
    connection.execute(text(insert_query))


# 一時テーブルを削除
with engine.connect() as connection:
    connection.execute(text('DROP TABLE gps_data_temp;'))


cursor.close()
conn.close()

これで準備おｋです！

使ってみる

そもそもこのデータは、地図でみてみるとどこなんでしょうか。Google Mapで確認してみます。

ひとつ適当な値取り出して、Google Mapで検索してみると、、、なるほど？

なるほど

この位置から8km先にある、この施設の位置情報（-6.2166646, 106.8700429）をもって、検証してみます。

ST_Distance_Sphere関数を使って、距離で絞り込んでみます。

query = '''SELECT * FROM gps_data
WHERE ST_Distance_Sphere(position, ST_GeomFromText('POINT(106.8700429 -6.2166646)')) <= {};
'''.format(1000 * 8)  # 1000m * n

df2 = pd.read_sql(query,con = engine)
df2.head()

取れました。簡単！

理解のためにここらで説明を書いていきます。

ST_GeomFromText

ST_GeomFromTextは、Well-Known Text (WKT) 形式の文字列から空間データを生成する関数です。

WKTについて

ST_Distance_Sphere

地球を「半径6,370,986mの真球」と見立てて距離（m）を計算する関数です。

ST_Distance_Sphere(POINT型カラム名, ST_GeomFromText(緯度経度))

これで、距離が計算されるので、10km以上〜のように条件で絞り込むことができるようになります。

ただ、これだけだとインデックスが使えないそうなので、ひと手間必要とのこと。

ST_X, ST_Y

POINT型のデータはそのまま取得すると、今回Pythonでやっていますが、上の結果にもありますが、 b'\x00\x00\x00\x00\x01\x01\x00\x00\x00\xc8a0\x...な感じのバイナリデータで取得されます。

これを変換してくれる関数が、ST_X, ST_Yです。

query = '''SELECT ST_X(position) AS Longitude, ST_Y(position) AS Latitude FROM gps_data
WHERE ST_Distance_Sphere(position, ST_GeomFromText('POINT(106.8700429 -6.2166646)')) <= {};
'''.format(1000 * 8)  # 1000m * n

df2 = pd.read_sql(query,con = engine)
df2.head()

よしっ！

雑感

軽い感じで入門できましたね！！雑な記事ですまんな

こんな感じでいいのか正直わからないですが、（少なくとも僕は）こんなノリで書いていこうと思います

今後ともゆたしくうにげーさびら〜〜〜（沖縄方言で「よろしくお願いします」の意味）