2

I am new to PostGIS. I have some linestring geometry values. Each linestring contains multiple points. I have dumped the points from that geometry by ST_DumpPoints. By ST_AsText, I have found that the first linestring contains 10 point sets (x,y,z) then second linestring contains 4 point sets. Now, I want to create a column named start and another column named end. The start will contain the start point of each point set of the linestring and end point will contain the next point of the point set of the linestring without the z value. E.g.

linestring1 dumped points: (1,2,2),(2,5,4),(3,4,5)......
linestring2 dumped points: (1,3,2),(3,3,4),(4,5,6)....

Now source and End columns will be

Source     End
(1,2)      (2,5)
(2,5)      (3,4)
(3,4)      .
.          .
.          .
.          .
.          .
(1,3)      (3,3) 
(3,3)      (4,5)
(4,5)      .
.          .
.          .
.          .
4

You can use the following query to dump the points, retrieve their order number (nr), drop the Z value with ST_Force2D and then left join them to the next one:

WITH 
a AS
(
  SELECT 1 AS id, ST_GeomFromText('LINESTRING( 1 2 2, 2 5 4, 3 4 5)') geom
  UNION
  SELECT 2 AS id, ST_GeomFromText('LINESTRING( 1 3 2, 3 3 4, 4 5 6)') geom
)
, c AS
(
  SELECT
    b.id,
    (b.dump).path[1] nr,
    ST_Force2D((b.dump).geom) geom
  FROM
  (
    SELECT
      id,
      ST_DumpPoints(geom) dump
    FROM
      a
  ) b
)
SELECT
  c.id id,
  c.nr nr,
  ST_AsText(c.geom) geom,
  c_next.nr next_nr,
  ST_AsText(c_next.geom) next_geom
FROM
  c
LEFT JOIN
  c AS c_next ON c.id = c_next.id AND c.nr + 1 = c_next.nr;

Result:

Result

2

If I have understood you correctly you want to use ST_StartPoint and ST_EndPoint. To drop the z value use ST_Force_2D.

To break the line into segments (which it sounds from your comment that this is what you want to do), see here.

  • The start point is giving the first point but end is giving the last point set, but I need each point – LSG Dec 9 '16 at 11:44
  • I think he wants to get individual segments. Please have a look at my answer. I hope it answers the question. – thibautg Dec 9 '16 at 12:05
  • Yeah - I think you're right. I see that now. – MappaGnosis Dec 9 '16 at 12:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.