Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
1
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

Organization

Postgisを使って緯度経度から2点間の距離を計算した

背景

インターン先のサービス提携先の各店舗の商圏内にいるユーザーIDとその店舗を算出して個別のユーザーアプローチを取ることになったので、やってみました。
最初はmetabaseからユーザーと店舗の位置情報テーブルを個別に二つcsvファイルで用意してpythonで読み込んで計算したんですが、postgresqlを採用してたのでpostgisを使ってmetabase内で計算することにしました。
metabaseは社内で利用している無料のBIツールです。
pythonで計算した時の参考記事はこちら

やりたいこと

各ユーザーと1km以内にある店舗の組み合わせを全て出したい

データ

・faclility_id,lat,longの3列*店舗数のfacilitiesテーブル
・user_id,lat,longの3列*ユーザー数のuserテーブル

コード

distance
SELECT
    dist.user_id
    dist.facility_id
    dist.distance
FROM
    (SELECT
        users.user_id AS user_id,
        facilities.facility_id AS facility_id,
        st_distance_sphere(st_makepoint(users.long, users.lat),
                           st_makepoint(facilities.long, facilities.lat)) AS distance,
    FROM users
    CROSS JOIN facilities) AS dist
WHERE dist.distance < 1000;

CROSSJOINでユーザーレコードと店舗レコードの全組み合わせのレコードを作り、その全部に対してpostgisのst_distance_sphere関数を使うことで全組み合わせの距離を算出しています。

やってみて

この関数では完全な球形に対して計算しているので回転楕円体である地球により近似するためにはst_distance_spheroid関数を使うとより精密になりますが、今回の計算で算出する距離は長くとも100km程度で、欲しいのは1km以内かどうかなのでこれで十分かと思います。

ちなみに、pythonでやってみた時との誤差は数m程度だったので、この誤差が上記の厳密さに起因しているのかと思いました。

参考にした記事
postgresql-PostGisの距離計算

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
1
Help us understand the problem. What are the problem?