[postgis-users] Merge Lines

[Kevin Neufeld]

Interesting problem.

You can't do the typical approach:
   - duplicate the linework
   - union / linemerge the linework so it's properly noded
   - spatially transfer the attributes back to your cleaned geometries.
Because lines would merge possibly along lines with different attributes.

I think you hit on the best way to tackle this:
   - convert all your lines to two points lines, keeping the attributes on every segment
       (you could do this with a simple query using generate_series)
   - group by the segments (where the geometries are the same)
   - group by your attributes and linemerge the segments into linestrings.

Be cautious with the group by geometry bit.  A GROUP BY in postgres uses the natural ordering of the datatype ... in the 
case of geometries, the = operator compares the bounding boxes of two geometries, not the geometry themselves.  So two 
segments can have the same bounding box, but be crossing, perpendicular to each other.

If you want to group by a geometry, I found that grouping by the encoded hex string of the geometry works well.

Cheers,
Kevin


Stefan Wagner wrote:
> Hi,
> 
> I'm sorry, if my question was answered already, but i couldn't find a
> similar post.
> 
> I'm using mapnik with PostGIS to render a map of Austria's capital Vienna.
> Everything worked fine so far, but now i want to add the public transport
> lines. Would be no problem, these are simple LINESTRINGs, but as on some
> streets, different lines come together respectively are congruent, this
> sometimes looks awful, because the lines are drawn on top of each other and
> the captions are not placed well because of different starting points of the
> lines.
> 
> So what i would need is: only have one line, if there come together several
> lines (so merge different lines to one, on parts where they are congruent)
> and have the type and name put together like with group_concat (e.g. line 40
> and 41 share the same line, have a field with "40,41" or "bus,tram,tram" if
> there are 1 bus- and 2 tramlines).
> I tried to group by the geom Column, and use group_concat (rather a
> equivalent to group_concat found somewhere on the net) to put together the
> captions. This sometimes worked, but in most cases it didn't (would be to
> easy, right? :-) )
> 
> So do you see an solution for this, or do i have to split all linestrings
> into simple 2point lines and group by that?
> 
> TIA.
> 
> Greetings from Austria,
> Stefan Wagner