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
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
