[postgis-users] multiline to linestring

Wed Feb 6 13:57:41 PST 2008

Gustavo,

It sounds like the dump command may work for you.

However it seems to me that you still may be trying to tackle a  
slightly different issue:

how to convert your multilinestrings into single linestrings (each on  
a row) and merge them in such a way to be collected along with all  
other contiguous linestrings that share end/start points.

A moment ago in IRC bitner kindly helped me work out these steps that  
may be helpful for you:

--Create testing multipolygon data with two linestrings that share  
endpoints and another that is free floating
drop table if exists lines;
create table lines (id serial,the_geom geometry);
INSERT into lines (the_geom) values (geomfromtext('MULTILINESTRING 
((4.8938679245283 46.3443396226415,33.313679245283  
73.7028301886792,49.5872641509434 80.0707547169811),(49.5872641509434  
80.0707547169811,90.9583136792453 89.2813679245283,54.3363797169811  
103.03608490566,29.0620872641509 103.293985849057,12.2125589622641  
107.1625,11.1624233831055 106.590266209164,10.4214805264064  
107.529010012923,11.2220067193803 108.417488521584,12.2326314413493  
107.778056025276),(37.506947728971 97.6302534161404,12.1756482134062  
82.9892270906306))',-1));

--Disaggregate multilines into linestrings, one per row
drop table if exists single_lines;
SELECT generate_series(1,(Select ST_NumGeometries(the_geom))) as id,
ST_GeometryN(the_geom, generate_series(1, ST_NumGeometries 
(the_geom))) AS the_geom
into single_lines FROM lines;

--Collect and merge all linestrings
drop table if exists bitner_collected;
SELECT st_linemerge(Collect(the_geom)) as the_geom
into bitner_collected from single_lines;

--Disagregate the resulting multilinestring resulting in continguous  
linestrings, again one per row
drop table if exists single_lines_again;
SELECT generate_series(1,(Select ST_NumGeometries(the_geom))) as id,  
ST_GeometryN(the_geom, generate_series(1, ST_NumGeometries 
(the_geom))) AS the_geom
into single_lines_again FROM bitner_collected;

Cheers,

Dane

ps. please anyone let me know if the text formatting is helpful or  
just annoying.

On Feb 6, 2008, at 9:30 AM, Kevin Neufeld wrote:

> Hi Gus,
> Glad to help!
> Actually, it is documented, (http://postgis.refractions.net/docs/ 
> ch06.html#id2704158) though I agree it definitely could be clearer,  
> i.e. with some examples.
> Here are some more links/examples to PostgreSQL's set returning  
> functions...
> http://www.postgresql.org/docs/8.2/static/xfunc-sql.html
> http://www.postgresql.org/docs/8.2/static/functions-srf.html
>
> Hope this helps,
> -- Kevin
>
> Gustavo Ces wrote:
>> thanks Kevin!
>>
>>    Now i understand which was the problem!
>>    And you run faster, how to extract geometry from geometry_dump  
>> type (?) was the next question! :)
>>    It´s not documentated, so i supose it´s a composite type and  
>> you know how to access it after search that type in database, don 
>> ´t you?
>>
>>
>>
>> Gus
>> ----- Original Message ----- From: "Kevin Neufeld"  
>> <kneufeld at refractions.net>
>> To: "PostGIS Users Discussion" <postgis- 
>> users at postgis.refractions.net>
>> Sent: Wednesday, February 06, 2008 5:22 PM
>> Subject: Re: [postgis-users] multiline to linestring
>>
>>
>>> Hi Gus,
>>>
>>> As noted from your error, ST_Dump() is a set returning function,  
>>> ie. you need to perform something like 'select * from dump(...)'
>>>
>>> Try this:
>>> select geom from dump(
>>>  (select linemerge(collect(the_geom)) from lines)
>>> );
>>>
>>> Cheers,
>>> -- Kevin
>>>
>>> Gustavo Ces wrote:
>>>> Hi all,
>>>>  i´ve got a problem with ST_Dump() ( well, actually this one  
>>>> doesn´t exist )
>>>> and dump() function. I´m working with
>>>>  "PostgreSQL 8.2.3 on i686-pc-mingw32, compiled by GCC gcc.exe  
>>>> (GCC) 3.4.2 (mingw-special)"
>>>> "POSTGIS="1.2.1" GEOS="2.2.3-CAPI-1.1.1" PROJ="Rel. 4.5.0, 22  
>>>> Oct 2006" USE_STATS"
>>>>  I want to convert linestrings from multilinestrings, with  
>>>> linemerge, to obtain united linestrings in overlapping points
>>>> with this sentence:
>>>>  select dump(linemerge(collect(the_geom))) from lines
>>>>  but i obtain:
>>>>  ERROR: set-valued function called in context that cannot accept  
>>>> a set
>>>> Estado SQL:0A000
>>>> The result of:
>>>>  select astext(LineMerge(Collect(the_geom))) from lines
>>>>  is:
>>>>
>>>> "MULTILINESTRING((4.8938679245283  
>>>> 46.3443396226415,33.313679245283  
>>>> 73.7028301886792,49.5872641509434  
>>>> 80.0707547169811,90.9583136792453  
>>>> 89.2813679245283,54.3363797169811  
>>>> 103.03608490566,29.0620872641509  
>>>> 103.293985849057,12.2125589622641 107.1625,11.1624233831055  
>>>> 106.590266209164,10.4214805264064  
>>>> 107.529010012923,11.2220067193803  
>>>> 108.417488521584,12.2326314413493 107.778056025276), 
>>>> (37.506947728971 97.6302534161404,12.1756482134062  
>>>> 82.9892270906306))"
>>>>  with numgeometries=2, the logic result with my original data.
>>>> How can i extract those linestrings separately?
>>>>  Gus
>>>> ------------------------------------------------------------------- 
>>>> -----
>>>>
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at postgis.refractions.net
>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080206/e0156878/attachment.html>