5

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?

Improve this question

2 Answers 2

Reset to default
6

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_LineSubstring as ST_Line_Substring is now deprecated.

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.

Improve this answer
4
  • 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
    Commented Oct 16, 2012 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
    Commented Oct 16, 2012 at 5:07
  • Yes, i have a number of point not her coordinates and want to get segment to the next point.
    – Kliver Max
    Commented Oct 16, 2012 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
    Commented Oct 16, 2012 at 7:34
2

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)

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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