2

I am working on converting linestring into points where I also need to detect start point and end point of line. I was able to generate points from linestring to series of points along with start point and end point.

This is the query.

select foo.line_id, st_astext(st_pointn(geo,generate_series(1,st_npoints(geo)))), st_astext(st_startpoint(geo)), st_astext(st_endpoint(geo)) from temp_line as foo;

Which results in like bellow(dummy data).

+--------+----------------------------+----------------------------+----------------------------+
|line_id | st_astext                  | st_astext                  | st_astext                  |
|--------+----------------------------+----------------------------+----------------------------|
|  6329  | POINT(55.280967 25.216052) | POINT(55.280967 25.216052) | POINT(55.282489 25.215534) |
|  6329  | POINT(55.281085 25.216118) | POINT(55.280967 25.216052) | POINT(55.282489 25.215534) |
|  6329  | POINT(55.281175 25.216151) | POINT(55.280967 25.216052) | POINT(55.282489 25.215534) |
|  6339  | POINT(55.281241 25.216142) | POINT(55.280967 25.216052) | POINT(55.282489 25.215534) |
|  6339  | POINT(55.281323 25.216082) | POINT(55.280967 25.216052) | POINT(55.282489 25.215534) |
+--------+----------------------------+----------------------------+----------------------------+

Instead of start and end point I want to select a feild with 1 if its a start or end point, else 0.

Like this

+--------+----------------------------+-----------+
|line_id | st_astext                  | s_e_point |
|--------+----------------------------+-----------+
|  6329  | POINT(55.280967 25.216052) | 1         |
|  6329  | POINT(55.281085 25.216118) | 0         |
|  6329  | POINT(55.281175 25.216151) | 1         |
|  6339  | POINT(55.281241 25.216142) | 1         |
|  6339  | POINT(55.281323 25.216082) | 1         |
+--------+----------------------------+-----------+

Will someone help me in doing this?

3
SELECT
    id,
    CASE WHEN path = 1 OR (SELECT ST_NPoints(geom) FROM stackexchange.q_247866 WHERE id = p.id) 
         THEN 1 
         ELSE 0 
    END start_end,
    path,
    geom
FROM (
    SELECT
        (dp).path[1] path,
        (dp).geom,
        id
    FROM (
        SELECT 
            (ST_DumpPoints(geom)) dp, -- use geometry_dump format
            id -- keep line id
        FROM stackexchange.q_247866
    ) a
) p;
  • Thanks for the help. I will explore ST_DumpPoints(). In your above query I am getting more than one row returned by a subquery used as an expression error. Any suggestion on that so that I can update your query? – Aparichith Jul 17 '17 at 12:04
  • I updated the query, was testing it with one row only :) – Michal Zimmermann Jul 17 '17 at 13:09
  • I wanted both start point and end point to be marked as 1 and rest as 0. And I made a small update in case statement like CASE WHEN path = 1 OR (SELECT ST_NPoints(geom) FROM stackexchange.q_247866 WHERE id = p.id) THEN 1 ELSE 0 END start_end. Please update your query to accept your answer. – Aparichith Jul 17 '17 at 17:02
  • Answer updated. – Michal Zimmermann Jul 18 '17 at 7:08

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.