SquerylでPostgreSQLのgeometry関連関数を使う

以前別ブログで、Squerylで最初からは用意されていない集計関数（median）を実装する、というエントリを書いた。今回はそれと似た内容で、PostgreSQLの位置情報の関数をSquerylで使えるようにしてみる。

Squeryl 0.9.5-6

距離を調べるSQL

今回使いたいのは、2点間の距離を調べる <-> という関数。例えば、以下のクエリーのように、ある位置(36.726637,139.526557)から3000m以内にある点のうち、距離が近い順に30件取得したいとする。これをSquerylを使って実現したい。

SELECT p.*, (p.latlng <-> POINT(36.726637,139.526557)) * 111000 as l
FROM place p
WHERE (p.latlng <-> POINT(36.726637,139.526557)) < 3000.0 / 111000.0
ORDER BY p.latlng <-> POINT(36.726637,139.526557)
LIMIT 30

Squerylでの定義

先に結論から書いておく。以下のように定義した。もっといい方法があるかもしれない。特に、StringExpression を受け取る所がイマイチな気がする。

  def distance(col1: StringExpression[String], col2: StringExpression[String])
  = new FunctionNode[Double]("distance", None, Seq(col1, col2)) with NumericalExpression[Double] {
    override def doWrite(sw: StatementWriter) = {
      col1.write(sw)
      sw.write(" <-> ")
      col2.write(sw)
    }
  }

使い方

上で定義したdistance関数をどのように使うかについて、以下に述べる。

point型を含むテーブルに対応したクラスの定義

クラス定義は以下の通り。ポイントはPostgreSQLのpoint型のカラム(latlng)はString型のフィールドで値を受け取り、それを自作のPointクラス（後述）に渡して使用する。

class Place (
  val id: Int = 0,

  @Column("latlng")
  val rawLatLng: String
) {
  def this() = {
    this(0, "")
  }

  def this(id: Int, latlng: Point) = {
    this(id, latlng.toString)
  }

  def latlng: Point = {
    Point(rawLatLng)
  }
}

クエリーの組み立て

先ほど作った”distance”関数を使って、以下のように書ける。ポイントは、distance関数にはStringを渡すこと。

    from(places)(p =>
        select(p)
        where(distance(p.rawLatLng, Point(lat, lng).toString) < (3000.0 / 111000.0))
        orderBy(distance(p.rawLatLng, Point(lat, lng).toString))

Pointクラス？

上のほうで出た、Pointクラスは以下のように定義した。

case class Point (
  val lat: Double,
  val lng: Double
) {
  override def toString = {
    "(%f,%f)".format(lat, lng)
  }
}

object Point {
  def apply(s: String): Point = {
    parseStr(s)
  }

  private def parseStr(pointStr: String): Point = {
    // 省略
  }
}

少しSquerylのソースも見てみる

distanceメソッドは、SquerylのFunctionNodeクラスを使用しているが、FunctionNodeクラスのコンストラクタのシグニチャーは以下の通り。これと先ほどのdistance関数を見比べてみれば意味が分かりやすいかも。

class FunctionNode[A](val name: String, _mapper : Option[OutMapper[A]], val args: Iterable[ExpressionNode]) extends ExpressionNode {

また、distance関数はNumericalExpression traitをmix-inしている。それにより、結果がdouble型で返される。

まとめ

Squerylが直接サポートしていないRDBMSの関数（今回は<->）でも、自分で少しコードを書けばSquerylで使えるようになる。

この辺のドキュメントはあまりないので、ソースを見るのが一番早いかも。

メモ：このスライド（？）も少し参考した。

Share on Facebook