[postgis-users] Find individual lengths of a line between vertices
Paragon Corporation
lr at pcorp.us
Mon May 11 23:09:35 PDT 2009
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
More information about the postgis-users
mailing list