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

[Paragon Corporation]

Ben,

You are correct, ST_Dump really only dumps out MULTI and geometry
collections.  

If you want to break a line into individual line segments -- you probably
want to use ST_PointN in combo with generate_series.

For example

SELECT gid, ST_AsText(ST_MakeLine(ST_PointN(the_geom,
n),ST_PointN(the_geom,n+1)) ) As geomwkt, 
ST_Distance(ST_PointN(the_geom, n),ST_PointN(the_geom,n+1)) As dist
FROM (VALUES (1 , ST_GeomFromText('LINESTRING(1 2, 3 4, 5 8, 6 10)') ) , (2
, ST_GeomFromText('LINESTRING(1 2, -1 -2)') )) As foo(gid,the_geom)
	CROSS JOIN generate_series(1, 1000) As n
WHERE n < ST_Npoints(foo.the_geom);

Which gives you:

 gid |        geomwkt        |       dist
-----+-----------------------+------------------
   1 | LINESTRING(1 2,3 4)   | 2.82842712474619
   1 | LINESTRING(3 4,5 8)   | 4.47213595499958
   1 | LINESTRING(5 8,6 10)  | 2.23606797749979
   2 | LINESTRING(1 2,-1 -2) | 4.47213595499958

Foo of course you can replace with a real table of line strings.  That's
just for demo.


Leo

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
benton101
Sent: Monday, May 11, 2009 10:16 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Find individual lengths of a line between vertices


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

--
View this message in context:
http://www.nabble.com/Find-individual-lengths-of-a-line-between-vertices-tp2
3494997p23494997.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users