[postgis-users] Creating lines from a geometry collection

[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> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120225/ed74a97b/attachment.html>