[postgis-users] Find individual lengths of a line between vertices

[Kevin Neufeld]

ST_Dump is useful for extracting individual geometries from a 
collection, like LINESTRINGs from a MULTILINESTRING.  There is a 
function slated for future development called ST_DumpPoints which will 
do what you are after, but that'll be sometime with version 1.4.1 or 
something.

For now, you can do what you want with generate_series.
ie.

SELECT
  ST_AsText(ST_MakeLine(p1, p2)),
  ST_Length(ST_MakeLine(p1, p2))
FROM (
  SELECT
    ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) AS p1,
    ST_PointN(geom, generate_series(2, ST_NPoints(geom))) AS p2
  FROM
   (SELECT ST_AsText('
      LINESTRING(
          0 0,
          0 5,
          10 5,
          10 2,
          2 2)'::geometry) AS geom
    ) AS a
  ) AS b;

       st_astext       | st_length
-----------------------+-----------
 LINESTRING(0 0,0 5)   |         5
 LINESTRING(0 5,10 5)  |        10
 LINESTRING(10 5,10 2) |         3
 LINESTRING(10 2,2 2)  |         8
(4 rows)

Cheers,
Kevin


benton101 wrote:
> Hi,
>
> I have a line of Geo Type LINESTRING. It contains one vertices somewhere
> along its total length with a start and end point. I want to find the length
> of each line between the end points and the vertices. I have attempted to
> use ST_Dump to do this. I was hoping that it would create two records, but
> it seems that ST_Dump see's it as one geometry. Is this true and if so is
> there another function I could use to find out the length of each vertices?
>
> This is the sql I have formed, the table has some lines that overlap /
> intersect each other ...
>
> SELECT get_results.the_geom AS the_geom
>   FROM (SELECT (ST_Dump(dump_results.the_geom)).geom AS the_geom
>           FROM (SELECT ST_MakeLine(union_results.pnt) AS the_geom
>                      , union_results.gid AS gid
>                   FROM (SELECT a.gid
>                              , ST_StartPoint(ST_Intersection(a.the_geom,
> b.the_geom)) AS pnt
>                           FROM spl_mga_road_lines a
>                              , spl_mga_road_lines b
>                          WHERE ST_Intersects(a.the_geom,b.the_geom)
>                            AND
> GeometryType(ST_StartPoint(ST_Intersection(a.the_geom, b.the_geom))) =
> 'POINT'
>                                UNION
>                         SELECT a.gid
>                              , ST_Intersection(a.the_geom, b.the_geom) AS
> pnt
>                           FROM spl_mga_road_lines a
>                              , spl_mga_road_lines b
>                          WHERE ST_Intersects(a.the_geom,b.the_geom)
>                            AND GeometryType(ST_Intersection(a.the_geom,
> b.the_geom)) = 'POINT'
>                                UNION
>                         SELECT a.gid
>                              , ST_EndPoint(ST_Intersection(a.the_geom,
> b.the_geom)) AS pnt
>                           FROM spl_mga_road_lines a
>                              , spl_mga_road_lines b
>                          WHERE ST_Intersects(a.the_geom,b.the_geom)
>                            AND
> GeometryType(ST_EndPoint(ST_Intersection(a.the_geom, b.the_geom))) = 'POINT'
>                              ) union_results
>               GROUP BY union_results.gid) dump_results
> WHERE ST_Length(dump_results.the_geom) > 5.0)get_results
>
>
> Thank You
> Ben
>
>