How to convert self-defined types, e.g. geometry, to and from String?

Question

There is geometry type column in database like Postgis or h2gis(I am using it). In the console provided by database, I can create a geometry value with select ST_GeomFromText('POINT(12.3 12)', 4326). Or select a column with geometry type simply by select * from geom.

However I don't know how to insert a geometry value (a string actually) into a table or the opposite direction conversion. There are also several miscellaneous question below.

Here is the table definition in slick:

class TableSimple(tag:Tag) extends Table[ (Double,String,String) ](tag,"tb_simple"){

  def col_double = column[Double]("col_double",O.NotNull)
  def col_str = column[String]("col_str",O.NotNull)
  def geom = column[String]("geom",O.DBType("Geometry"))
  def * = (col_double,col_str,geom)
}

1. About select

The most simple one:

 sql" select col_double,col_str, geom from tb_simple ".as[(Double,String,String)]

won't work unless casting geom to string explicitly like:

 sql" select col_double,col_str, cast( geom as varchar) from tb_simple ".as[(Double,String,String)]

The first sql throws the error java.lang.ClassNotFoundException: com.vividsolutions.jts.io.ParseException

Q1: How does slick know com.vividsolutions.jts.io.ParseException (it is lib used by h2gis)? Is it an error on the server side or client side(slick side)?

Q2: How to convert/treat column geom as string without writing too much code(e.g. create a new column type in slick)?

2. About insert

First of all the following sql works

StaticQuery.updateNA("""  insert into tb_simple values(11,'abcd',ST_GeomFromText('POINT(5.300000 1.100000)', 4326))  """).execute

I hope code like TableQuery[TableSimple] += (10.3,"hello","ST_GeomFromText('POINT(0.300000 1.100000)'") would work but it doesn't. It shouldn't because slick translate it to

insert into tb_simple values(11,'abcd','ST_GeomFromText(''POINT(5.300000 1.100000)'', 4326)')

Notice the function ST_GeomFromText become a part of string, that's why it doesn't work.

Q3: Can I implant a string directly for a column instead of wrapped with '' in slick?

I hope I can insert a row as easy as TableQuery[TableSimple] += (10.3,"hello","ST_GeomFromText('POINT(0.300000 1.100000)'") or similar code.

Q4 What's the most convenient way in Slick to implement bidirectional conversion to and from String for a geometry or other self-defined column in the database?

Christophe Vanfleteren Christophe Vanfleteren · Accepted Answer · 2015-01-05T12:26:20

Answering you main question: Slick-pg offers mapping of geometry types in the db to actual geometry types in your model.

It works for Postgis, but maybe it can also work with H2Gis.

You can find slick-pg at https://github.com/tminglei/slick-pg

How to convert self-defined types, e.g. geometry, to and from String?

1 Answers