Create a location database with Slick3 and POSTGRESQL

The goals are:

1. Can insert / update / delete data with location point.
2. Can query by location distance.

The overall steps are:

1. Install the postgis extension to postgresql
2. Create a table with GEOGRAPHY column
3. Create a Point case class
4. Tell slick to use Point class for a GEOGRAPHY column
5. Add the geometric / geographic functions to slick to perform various queries.

The first 3 steps are trivial, I’ll start with step #4 and #5. For the whole picture, please check this github repository to find out.

Tell slick to use Point class for a GEOGRAPHY column

Slick allows different ways to extend it, please refer to the official document for details. What we’re trying to do here, is using custom scalar types in queries. However, contrary to what’s documented by using a MappedColumnType, we have to go a step further.

MappedColumnType is great, if you simply want to map a new type to an already supported type. Mapping java8 time classes to the already supported java.sql.Timestampand java.sql.Timeis a good example of such use cases.

The Point case class or other PostGis data structure, however, can’t be mapped to existing data type. So we have to implement our own ColumnType.

The steps are as following:

• Add postgis-jdbc dependency to project
• Implement the ColumnType by subclassing DriverJdbcType[Point].

The implementation of our new PointDriverJdbcType is straight forward.

class PointDriverJdbcType extends DriverJdbcType[Point] with GeoTypedType {
  override def sqlType: Int = java.sql.Types.OTHER

  override def setValue(v: Point, p: PreparedStatement, idx: Int): Unit = {
    val g = new PGgeometry(new org.postgis.Point(v.lon.doubleValue(), v.lat.doubleValue()))
    p.setObject(idx, g)
  }

  override def getValue(r: ResultSet, idx: Int): Point = {
    r.getObject(idx) match {
      case g: PGgeometry if g.getGeoType == Geometry.POINT =>
        val p = g.getGeometry.asInstanceOf[org.postgis.Point]
        Point(p.x, p.y)
      case null =>
        null
      case x =>
        throw new SlickException(s"Database geometry data is not a point, it was: $x")
    }
  }

  override def updateValue(v: Point, r: ResultSet, idx: Int): Unit = {
    val g = new PGgeometry(new org.postgis.Point(v.lon.doubleValue(), v.lat.doubleValue()))
    r.updateObject(idx, g)
  }

  override def hasLiteralForm: Boolean = false
}

I opted for a similar code construct as slick to create a GeoJdbcTypesComponenttrait that extends JdbcTypesComponent.

trait GeoJdbcTypesComponent extends JdbcTypesComponent {
  self: JdbcProfile =>
  class PointDriverJdbcType extends DriverJdbcType[Point] {
    ...
  }

  private val _geoPointJdbcType = new PointDriverJdbcType

  trait GeoJdbcTypes {
    val geoPointJdbcType = _geoPointJdbcType
  }

  trait GeoImplicitColumnTypes {
    implicit val geoPointJdbcType = _geoPointJdbcType
  }
}

This trait will be later used to mixin into a newPostgisProfilewhich becomes the profile to import when postgis data / queries are relevant.

trait PostgisProfile extends PostgresProfile with GeoJdbcTypesComponent {
  override val columnTypes: JdbcTypes with GeoJdbcTypes = new JdbcTypes with GeoJdbcTypes

  override val api = new API with GeoImplicitColumnTypes
}

object PostgisProfile extends PostgisProfile

Interested in Slick 3.3.0 ?

Register PGgeometry to postgresql jdbc driver

After running our simple test cases. ClassCastException was thrown, I then realized Postgresql jdbc driver didn’t recognize the postgis PGgeometry type. Since this is a vendor specific data type, we’ll have to look for vendor specific solution. It turns out we can register datatype to PGConnection via the addDataType() call.

It’s a bit difficult to call addDataType with Slick. You usually have no access to raw connection. The connection you’ve got is a proxied connection by the underlying connection pool (currently hikariCp as a default). I’ve tried to fiddle with JdbcBackend.createSession, but realized I should really work with the DataSource where connections are being created.

class GeoDatabaseUrlDataSource extends DatabaseUrlDataSource {
  override def getConnection(): Connection = {
    val c = super.getConnection()
    val cls = Class.forName("org.postgis.PGgeometry").asInstanceOf[Class[PGgeometry]]
    val pgconn = c.asInstanceOf[PGConnection]
    pgconn.addDataType("geography", cls)
    logger.trace(s"Added geography data type to connection: $c")
    c
  }
}

A simple implementation that extends DatabaseUrlDataSource with the override getConnection method should lift you through. Simply tell slick to use it as the datasource and we’re done.

slick {
  dbs {
    default {
      driver = "slick.jdbc.PostgresProfile$"
      db.dataSourceClass = "models.jdbc.GeoDatabaseUrlDataSource"
      db.properties.url = "postgres://test:test@192.168.56.98/test"
      db.properties.driver = "org.postgresql.Driver"
    }
  }
}

I had no success following this document on PostGIS web site (with POSTGRESQL 10, PostGIS 2.4). After some tracing, I realize Postgresql is reporting “geography” data type, but we’ve registered “geometry”. Thing was not improved if I changed the column type to geometry. This is why I registered “geography” not “geometry” in the above code snippet.

By now you should be able to perform basic CRUD operation.

Add ST_Distance function to slick

As mentioned in the beginning, one of the goal of this small project is to be able to query locations by distance to another point. Usually we’d want to find out the closest cafe to where you’re, and PostGis provids a ST_Distance method. Together with an Order By clause, this can be achieved with one SQL. In the term of Slick, with one sortBy.

Slick has a very complex type system, most of the traits or classes have multiple generic parameter types. We could easily got lost if not followed closely. But this type system can also be a good thing. The scala compiler is here to help, usually the code that compiles is basically functional. Tracing the implicit conversion is still a pain though…

Any way. To add functions to our geography type, we should start by mimicing existing ExtensionMethods. In the ExtensionMethods we can see NumericColumnExtensionMethods and StringColumnExtensionMethods. The 2 actually are slightly different in mind. Because the nature of Numeric data, NumericColumnExtensionMethods aims to provide a foundation for all numeric type, which is why we see BaseNumericColumnExtensionMethods and OptionNumericColumnExtensionMethods. StringColumnExtensionMethods on the other hand is very straight forward, it serves the String and the String only. For our purpose, we’ll take the String approach as the below.

final class PointColumnExtensionMethods[P1](val c: Rep[P1]) extends AnyVal with ExtensionMethods[Point, P1] {
  protected[this] implicit def b1Type = implicitly[TypedType[Point]]

  def distance[R](s: Point)(implicit om: o#to[Double, R]) =
    om.column(Operators.distance, n, LiteralNode(GeoTypes.pointType, s))

}

object PointColumnExtensionMethods {
  object Operators {
    val distance = new JdbcFunction("ST_Distance")
  }

  trait PointColumnExtensionMethodConversions {
    implicit def pointColumnExtensionMethods[B1](c: Rep[Point]): PointColumnExtensionMethods[Point] = new PointColumnExtensionMethods[Point](c)
    implicit def pointOptionColumnExtensionMethods[B1](c: Rep[Option[Point]])(implicit tm: BaseTypedType[B1]): PointColumnExtensionMethods[Option[Point]] = new PointColumnExtensionMethods[Option[Point]](c)
  }
}

We’ve seen the implicitly requested of TypedType[Point]. We need this TypedType to convert from Type to JdbcType.

class PointScalaType[T](implicit val classTag: ClassTag[T]) extends ScalaType[T] with BaseTypedType[T] {
  override def nullable: Boolean = true

  override def ordered: Boolean = false

  override def scalaOrderingFor(ord: ast.Ordering) = new Ordering[T] {
    override def compare(x: T, y: T): Int = 0
  }
}

object GeoTypes {
  implicit val pointType = new PointScalaType[Point]()
}

We don’t need to care about the ordering of Point. As a matter of fact, I’m not sure what the right order would be for Points. So we’ll always return 0 for Ordering.

This new PointColumnExtensionMethodConversions must be mixin with our existing PostgisProfile.

trait PostgisProfile extends PostgresProfile with GeoJdbcTypesComponent {
  override val columnTypes: JdbcTypes with GeoJdbcTypes = new JdbcTypes with GeoJdbcTypes

  override val api = new API with GeoImplicitColumnTypes with PointColumnExtensionMethodConversions
}

To use our new distance function, the below DAO codes can be used.

import models.jdbc.PostgisProfile.api._
val q = locationQuery
  .sortBy(_.location.distance(Point(0.1, 0.1)))
  .map(_.model)

This is probably what you need to map PostGIS Point to your slick project, a location database can be built from here. Although we’re still missing most of the interesting features provided by PostGIS, but it should serve as the basis of our new adventure. Also, you can check the source codes in the repository below.

sohoffice/slick-ext

Did you learn something new? If so please:

↓ clap 👏 button below️ so more people can see this