Please add support for postgis' topology and TopoGeometry types

[icassina]

I know is very hard (i'm trying right now but I don't know slick well enough yet), but it would be quite nice to have topology support integrated into slick-pg.

Basically, if you want to have a topology with, let's say, points and lines with attributes you would proceed like this:

1) create the topology

% topology.CreateTopology(topologyname, srid, tolerance, hasz)
% see: http://postgis.net/docs/CreateTopology.html

SELECT topology.CreateTopology('my_topology_name', 21781, 0, false);

It will create a new schema named "my_topology_name" and with four tables in it:

• edges
• nodes
• faces
• relations

It will also store the metadata into topology.topology.

2) create your edges and nodes tables

CREATE TABLE public.my_edges(id SERIAL PRIMARY KEY, edge_attr1 VARCHAR(32) NOT NULL);
CREATE TABLE public.my_nodes(id SERIAL PRIMARY KEY, node_attr1 VARCHAR(64) NOT NULL);

3) add TopoGeometry columns to these tables

% see: http://postgis.net/docs/AddTopoGeometryColumn.html

SELECT topology.AddTopoGeometryColumn('my_topology_name', 'public', 'my_edges', 'line', 'LINE');
SELECT topology.AddTopoGeometryColumn('my_topology_name', 'public', 'my_nodes', 'point', 'POINT');

It's a special kind of column, that, on insert, will add geometries to the topology (splitting and snapping as needed), and on retrival, will get geometries from the topology.

The result of the select is the layer_id to use with topology.toTopoGeom() (next section). Let's say that my_edges.line has a layer_id of 1 and my_nodes.point has a layer_id of 2.

Note: on retrival, the type of the geometry will be MultiGeometry (i.e. LineString -> MultiLineString, Point -> MultiPoint) since splitting might have occurred!!

4) insert features

% see: http://postgis.net/docs/toTopoGeom.html

INSERT INTO public.my_edges(1, topology.toTopoGeom(your_LineString_GeomHere, 1), 'myneatattribute');
INSERT INTO public.my_nodes(1, topology.toTopoGeom(your_Point_GeomHere, 2), 'yay another attr!');

5) retrieve features

SELECT * FROM public.my_edges;
SELECT * FROM public.my_nodes;

Note: the TopoGeometry column will be automatically casted to a Geometry.

For my_edges you'll get the id, a MultiLineString and a varchar.

For my_nodes you'll get the id, a MultiPoint and a varchar.

[tminglei]

Hi @icassina I'm not familiar with postgis' topology, need some time to learn it.

BTW, to use it in Scala project, which Java/Scala type is a good candidate for type mapper?

[icassina]

That is the major problem, in my opinion.

TopoGeometry is a special type, containing information about the topology and layer to look for, and the features.. i'm not very familiar myself. However, on retrieval, it's transparently casted to the corresponding (multi) geometry.

The mechanism would be like this (it's complicated, that's why it takes a slick "master" to tackle it):

• you declare a table with "Topology" (a trait mix-in?).
• columns with TopoGeometry[G <: Geometry] would be treated in a special way: * on table creation: for each TopoGeometry column, create a layer (this is shown at point 3, with topology.AddTopoGeometryColumn) and store the layer_id somewher * on insert: wrap the geometry to be insered with topology.toTopoGeom(the_geometry, layerid) \ on select: the engine is able to cast back the column to a geometry. Due to the possibility of split when inserting other geometries to the topology, the geometry that is given back is Multi For this reason, I think it's better to just work with Multi geometries from the start.

example:

case class MyFeat(id: Long, line: MultiLineString, attr1: String)

class MyFeatTable(tag: Tag) extends Table[MyFeat](tag, Some("public"), "feats") with Topology {
  /* the Topology mix-in should do steps 1), 2) and 3) on table creation */
  def id = column[Long]("id", O.AutoInc, O.PrimaryKey)
  def attr1 = column[String]("attr1")
  def lines = column[TopoGeom[MultiLineString]]("lines") // using columns of topoGeom
  /* maybe is easier with: 
  def lines = topoGeomColumn[MultiLineString]("lines")
  */

  def * = (id, lines, attr1) <> (
    /* (select) here maybe the query should do an explicit cast with '::geometry' */,
    /* (insert) here the query should wrap (lines) into that toTopoGeom() postgis function */
  )
}

[aloiscochard]

@cvogt @szeiger hey guys, what do you think of the approach @icassina suggest? do you guys know some client who actually had to deal with such stuff? ty!

[icassina]

mmm you should add this to the MyFeatTable definition (to comply with the hypotetic Topology trait):

{
  …
 val topology_name = "my_feats_topology"
}

[szeiger]

I haven't run into this myself yet. How closely are those topology types related to relational tables? From what I see here in the original sketch, it looks more like a special feature, so maybe it should be implemented with a different API, likes sequences?

[tminglei]

Hi @icassina sorry for my slow response!

I'm busy on company's project these days, and will still be busy in next several weeks.

I did some learning and trying about postgis topology, and here's some problems and results:

• I didn't find existing java/scala types for TopoGeometry/TopoElement/TopoElementArray, so maybe we need create ours
• TopoGeometry is a composite type, and its output is a four tuple, like (1,1,4,1), but many functions required topology parameter is the topology name, so we need get topology name by topology_id when we want to do some topology operations based on a topogeometry
• postgis topology provided GetTopologyName to help us to get topology name, but there still is an associated problem: currently slick-pg built-in composite type support is NOT powerful enough to allow us to construct a COLUMN by accessing a composite type COLUMN's field, so using slick/slick-pg I can't express GetTopologyName((my_topo_geom).topology_id)
• slick-pg doesn't support row/record type currently, so I can't implement getfaceedges_returntype, and some functions' implementaton, like ST_GetFaceEdges/GetRingEdges/GetNodeEdges, will be impacted.

I'll keep learning and trying.

[icassina]

• Indeed, there is no such type. Probably create them is due.
• the table topology.topology contains that information. GetTopologyName probably just SELECT on that table.
• I see. I'm not experienced enough (but trying to learn!) to help with that, I'm afraid.
• personally I don't need to work on faces, yet, this can be implemented at a later time with little impact on overall functionality, I think.

Thank you very much. slick-pg would be the first library implementing seamless access to postgis topology!

[icassina]

@szeiger each topology is stored in their own schema. This is done by using special postgis functions, as described above.

The primary benefit of using topologies is that whenever you add a new geometry element to it, it will:

• snap to existing geometries
• split existing geometries, as needed. Thus enabling a much easier "editing" of the topology.

It also allows to:

• simplify a topology
• retrieve the whole topology as a TopoJSON string

This implies also that when you add a geometry to a topology using the above method (toTopoGeom), and that geometry is on (or snapped on) an existing LineString (for example), then, when retrieving that LineString (which has some attributes associated, see above) you will retrieve a MultiLineString instead, as it was splitted in the process. Both parts of the MultiLineString will still have the original attributes associated. Even lines that weren't splitted will be retrieved as MultiLineString, but with only a single part.

The author of these postgis functionalities told me about the undocumented valid values of the feature_type argument of the topology.AddTopoGeometryColumn function (the values used internally), which tackle a bit better their nature. Behaviour does not change but it allow us to think about the nature of what we're working with:

• instead of POINT, use puntal (i.e. 0-dimension geometries: Point in, MultiPoint out)
• instead of LINE, use lineal (i.e. 1-dimension geometries: LineString in, MultiLineString out)
• instead of POLYGON use areal (i.e. 2-dimension geometries: Polygon in, MultiPolygon out)

reference: https://github.com/postgis/postgis/blob/svn-trunk/topology/sql/topogeometry/totopogeom.sql.in

postgis also supports mixed type, for hierarchical layers, but I've not yet experimented with it. Taking this into account it would be more like a tree than a linear sequence (but Tree is a collection and share the API anyway).

[tminglei]

Hi @icassina just a little update. I already started working on this, but haven't any code changes submitted yet.