line to point conversion with start point and end point detection

by Dharshan   Last Updated July 17, 2017 10:22 AM

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?



Related Questions




Speeding up closest point query?

Updated July 20, 2017 16:22 PM