[postgis-users] union/merge huge river dataset into single lines group by name

[Kevin Neufeld]

The below queries should group together all rivers with the same name into a MULTILINESTRING geometry.  Since you are 
using LineMerge, degree 2 nodes, are removed from every collection.  The subsequent query will expand the collections 
back to individual linestrings so that disjoint lines (or lines that meet at confluences greater than 2) remain intact. 
  This is assuming of course that your dataset is properly noded.  If you have cases where rivers cross or where they 
intersect at vertices other than their endpoints and you want to treat these as one feature, use ST_Union instead of 
ST_LineMerge.

-- creates MULTILINESTRING per river name
CREATE TABLE merged_rivers AS
SELECT river_name, ST_Multi(ST_LineMerge(ST_Collect(the_geom))) AS geom
FROM my_river_table
GROUP BY river_name

-- explodes the MULTILINESTRING into separate LINESTRINGs
SELECT river_name, (ST_Dump(geom)).geom
FROM merged_rivers;

Hope that helps,
Kevin

karsten vennemann wrote:
> PostGIS users,
>  
> I was researching how to effectively merge river segments in a really 
> huge river dataset that have the same name and touch each other (share a 
> coordinate). The resulting layers should be used for labeling purposes. 
> I found that I could use something along the lines of 
> ST_LineMerge(ST_Collect(the_geom)) but would anybody have an example how 
> a merge operation would look to merge by river name and only if the 
> segments touch ?
>  
> Cheers
> Karsten
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users