Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
    
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
add comment

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)

share|improve this answer
    
Thank you, its really help me)) –  Kliver Max Oct 16 '12 at 9:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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