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)      .
.          .
.          .
.          .
LSG
  • 537
  • 1
  • 5
  • 19

2 Answers2

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

thibautg
  • 2,097
  • 10
  • 16
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.

Community
  • 1
MappaGnosis
  • 33,857
  • 2
  • 66
  • 129