[postgis-users] Creating lines from a geometry collection

[Birgit Laggner]

Hi David,

I would think working with a subselect would solve your problem:

SELECT
     generate_series(1,ST_NumGeometries(geom)) as gid,
     geom
FROM (
     SELECT
         ST_GeomFromEWKB((ST_Dump(ST_Split(g.geom, blade.geom))).geom) 
As geom,
     FROM
         points as blade,
         lines as g
     WHERE
         ST_Intersects(g.geom, blade.geom)
     ) as sel;

Hope that helps,

Birgit.



Am 25.02.2012 22:01, schrieb David Quinn:
> After re-reading the documentation I realized I missed an example, so 
> I figured out what I need to do (in part):
>
> SELECT
>     ST_GeomFromEWKB((ST_Dump(ST_Split(g.geom, blade.geom))).geom) As geom
> FROM
>     points as blade,
>     lines as g
> WHERE
>     ST_Intersects(g.geom, blade.geom)
>
> This works fine, but I also want to include a column that is an 
> identifier. I've tried doing the following:
>
> SELECT
>     ST_GeomFromEWKB((ST_Dump(ST_Split(g.geom, blade.geom))).geom) As geom,
>     generate_series(1,ST_NumGeometries((ST_Split(g.geom, 
> blade.geom)))) as gid
> FROM
>     points as blade,
>     lines as g
> WHERE
>     ST_Intersects(g.geom, blade.geom)
>
> While this does generate a series, it starts counting for each line 
> that it splits so it is 1,2,3,1,2,1,2,3,4. How can I have a sequential 
> GID (or get the total geometry count)?
>
> -David
>
> On Sat, Feb 25, 2012 at 2:54 PM, David Quinn <daithiquinn at gmail.com 
> <mailto:daithiquinn at gmail.com>> wrote:
>
>     Hello,
>
>     I'm trying to split up a line using points into several smaller
>     lines. I'm using ST_Split but I don't understand how to combine
>     the returned values into a line. My code is as follows:
>
>     SELECT
>        ST_Dump(ST_Split(g.geom, blade.geom)) AS geom
>     FROM
>         points as blade,
>         lines as g
>     WHERE
>         ST_Intersects(g.geom, blade.geom)
>
>     I've tried using a few different geometry constructors such as
>     ST_LineFromMultiPoint() and ST_GeomFromEWKB() to convert what
>     ST_Dump() returns to create lines but my syntax/approach is not
>     correct. What is the correct approach?
>
>     Thanks,
>     David
>
>
>
>
> _______________________________________________
> 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/20120227/58041d56/attachment.html>