How can I control the Geometry type before an update

Question

I have a rails method that merge multilinestring into linestring.

Sometimes, I have a mistake in the datas and get this error message :

Geometry type (MultiLineString) does not match column type (LineString).

How can I control it and prevent errors ? Can I do it, directly in the sql query ?

def update_path(trace)
  trace.update(path: merge_tracks(trace))
end

def merge_tracks(trace)
  tracks = <<-SQL
    SELECT ST_AsText(St_linemerge(ST_Collect(tr.path))) as geojson
      FROM tracks tr
      INNER JOIN trace_tracks ON tr.id = trace_tracks.track_id
      WHERE trace_tracks.trace_id = #{trace.id}
  SQL
  ActiveRecord::Base.connection.execute(tracks).getvalue(0,0)
end

Answer 1

It would be helpful if you could include an example of the LineStrings that are returning a MultiLineString. But it's probably because the LineStrings don't share a common point, and therefore can't be merged.

If you're OK with throwing those rows out as opposed to fixing them, then this should work:

SELECT ST_AsGeoJson(St_linemerge(ST_Collect(tr.path))) as geojson
      FROM tracks tr
      INNER JOIN trace_tracks ON tr.id = trace_tracks.track_id
      WHERE trace_tracks.trace_id = #{trace.id}
      AND ST_GeometryType(St_linemerge(ST_Collect(tr.path))) = 'ST_Linestring';

If you want to keep them and are OK returning more than one row, then you can do:

SELECT ST_AsGeoJson(geom) as geojson
   FROM
   (SELECT (ST_Dump(ST_LineMerge(ST_Collect(tr.path)))).*
      FROM tracks tr
      INNER JOIN trace_tracks ON tr.id = trace_tracks.track_id
      WHERE trace_tracks.trace_id = #{trace.id}
    ) g;

If you want to forcibly create a line out of the multiline's points (i.e. connect the two disjoint lines together), with possibly unexpected results, you can do this (USE AT YOUR OWN RISK):

SELECT ST_AsGeoJson(ST_MakeLine(geom)) as geojson
   FROM
   (SELECT (ST_DumpPoints(tr.path)).*
      FROM tracks tr
      INNER JOIN trace_tracks ON tr.id = trace_tracks.track_id
      WHERE trace_tracks.trace_id = #{trace.id}
    ) g;

That simply connects the end of one line to the beginning of the following one in whatever order the lines are stored.