# How to select segment of linestring using PostGIS?

i have PostgreSQL+PostGIS.

I have a linestring:

``````"LINESTRING(60.7014631515719 56.8441322356241,60.7023117507097 56.8445673405349,60.702948200063 56.8447993944193,60.703902874093 56.8448574076656,60.706236521722 56.8447993944193,60.7094187684889 56.8449444273664,60.7121236782406 56.8450894597515,60.715571112238 56.8452925041466,60.718382096882 56.8454085290207,60.7204505572805 56.8453505166286,60.7222538304482 56.8450314468649,60.7246405155233 56.8444513130533,60.7260194891224 56.8440742212539,60.7260194891224 56.8440742212539,60.7260194891224 56.8440742212539,60.7260194891224 56.844045214035)"
``````

Now i want to get a substring of this linestring. It is possible get a substring if i have a beggin point and end point of substring what i want to create?

But the more I wonder if I have a point of the line can I get it the next point and create a line of this segment?

-

There is more than one question here, and I'm a bit confused about what you want to achieve.

The core function you need is ST_Line_Substring

That takes a "fraction" of the line as the start and end points. Since you want to work in point coordinates, you need to convert those to "fractions" of this line.

For that, you can use ST_Line_Locate_Point

For the last part of your question, perhaps you can calculate the vertices by doing a subline based on the points you have, then taking a PointN with the "next to first" and "next to last" points, then taking a new subline.

-
can you tell how to use loop in query? For example i have a 5th point and want to take 5-6 segment. I gonna use loop to find 5th point? – Kliver Max Oct 16 '12 at 4:54
Can you try explaining this again? Do you have the exact fifth (or any) vertex point, and just want one one segment (to the next vertex)? – BradHards Oct 16 '12 at 5:07
Yes, i have a number of point not her coordinates and want to get segment to the next point. – Kliver Max Oct 16 '12 at 5:10
I'm having trouble following your question, but if you have the index of the coordinates (in a linestring) you can use PointN to get the point coordinates. Then you can use ST_Line_Locate_Point to get the fraction, then you can use ST_Line_Substring. However there is probably a faster way. If you have the point number (x), then you can use PointN to get the coordinates of that point. Then you can use PointN with x+1. Then you can MakeLine using the two points. – BradHards Oct 16 '12 at 7:34

So taking my last suggestion, here is how you could do it in code (in spatialite, but PostGIS will be much the same):

``````SELECT AsText(MakeLine(ST_PointN(line, idx), ST_PointN(line, idx+1))) FROM (SELECT ST_LinestringFromText("LINESTRING(60.7014631515719 56.8441322356241,60.7023117507097 56.8445673405349,60.702948200063 56.8447993944193,60.703902874093 56.8448574076656,60.706236521722 56.8447993944193,60.7094187684889 56.8449444273664,60.7121236782406 56.8450894597515,60.715571112238 56.8452925041466,60.718382096882 56.8454085290207,60.7204505572805 56.8453505166286,60.7222538304482 56.8450314468649,60.7246405155233 56.8444513130533,60.7260194891224 56.8440742212539,60.7260194891224 56.8440742212539,60.7260194891224 56.8440742212539,60.7260194891224 56.844045214035)") AS line, 5 AS idx);
``````

LINESTRING(60.706237 56.844799, 60.709419 56.844944)

-
Thank you, its really help me)) – Kliver Max Oct 16 '12 at 9:50