Squeryl geo queries with a postgres backend?

Question

How can I perform geo queries using Squeryl with a postgres backend? The sort of queries I want to run are "return all users within x kilometres", etc.

If geo queries aren't supported directly/through a plugin, how can I run raw SQL queries? I saw one gist and it looked complicated.

Update Specifically I want to run the following query:

SELECT events.id, events.name FROM events 
WHERE earth_box( {current_user_lat}, {current_user_lng}, 
  {radius_in_metres}) @> ll_to_earth(events.lat, events.lng);

This is taken from http://johanndutoit.net/searching-in-a-radius-using-postgres/

Squeryl is "A Scala ORM and DSL for talking with Databases". Which exact SQL query do you want to transform into a squeryl statement? What I want to give a hint about: if databases aren't able to solve your problem, then squeryl does not solve them, too. — VasiliNovikov, Sep 07 '15 at 13:22
@VasyaNovikov I've updated my question with a specific query that does I want to execute. — jbrown, Sep 07 '15 at 15:07
Then it seems like your question is about using custom functions of the DB. I never used this, but it seems like squeryl has a paragraph about that: http://squeryl.org/custom-functions.html Maybe it'll help. If not, maybe you should rise a separate question on SO (people may have skipped the question not understanding the "geo" goal). — VasiliNovikov, Sep 07 '15 at 18:43

score 0 · Answer 1 · answered Sep 15 '15 at 13:05

This object should solve your problem.

object object RawSql {

  def q(query: String, args: Any*) =
    new RawTupleQuery(query, args)

  class RawTupleQuery(query: String, args: Seq[Any]) {

    private def prep = {

      val s = Session.currentSession

      val st = s.connection.prepareStatement(query)

      def unwrap(o: Any) = o match {
    case None => null
    case Some(ob) => ob.asInstanceOf[AnyRef]
    case null => null
    case a@AnyRef => a
    case a@_ => a
      }

      for(z <- args.zipWithIndex) {
    st.setObject(z._2 + 1, unwrap(z._1))
      }

      st
    }

    def toSeq[A1]()(implicit f1 : TypedExpressionFactory[A1,_]) = {

      val st = prep
      val rs = st.executeQuery

      try {

    val ab = new ArrayBuffer[A1]

    val m1 = f1.thisMapper.asInstanceOf[PrimitiveJdbcMapper[A1]]

    while(rs.next)
      ab.append(m1.convertFromJdbc(m1.extractNativeJdbcValue(rs, 1)))

    ab
      }
      finally {
    rs.close()
    st.close()
      }
    }

    def toTupleSeq[A1,A2]()(implicit f1 : TypedExpressionFactory[A1,_], f2 : TypedExpressionFactory[A2,_]) = {

      val st = prep
      val rs = st.executeQuery

      try {

    val ab = new ArrayBuffer[(A1,A2)]

    val m1 = f1.thisMapper.asInstanceOf[PrimitiveJdbcMapper[A1]]
    val m2 = f2.thisMapper.asInstanceOf[PrimitiveJdbcMapper[A2]]

    while(rs.next)
      ab.append(
        (m1.convertFromJdbc(m1.extractNativeJdbcValue(rs, 1)),
          m2.convertFromJdbc(m2.extractNativeJdbcValue(rs, 2))))
    ab
      }
      finally {
    rs.close()
    st.close()
      }
    }
  }
}

I got from this gist:

https://gist.github.com/max-l/9250053

I haven't tested this. I think I'll use doobie instead since it bundles support for postgis. — jbrown, Sep 18 '15 at 15:41

Squeryl geo queries with a postgres backend?

1 Answers1