1

I need help splitting a linestring into smaller linestrings, using PostGIS.

I need to route to closest point on the linestring, so I need to have more points on the line

Sample Linestring = LINESTRING(11.959813031048 55.3269904906477,11.9613429171704 55.3257458375313,11.9656473425314 55.325719907258,11.9675402524794 55.3251753715195)

I have no luck with the following

select st_astext(st_segmentize(st_linefromtext('LINESTRING(11.959813031048 55.3269904906477,11.9613429171704 55.3257458375313,11.9656473425314 55.325719907258,11.9675402524794 55.3251753715195)'
),2) ) as geom 

But something is missing? I would like to have my line split into lines max 10 meters between points.

I hope somebody could get me back on track.


I then dump the line into points

select st_astext((st_dumpPoints(st_segmentize(st_linefromtext('LINESTRING(11.959813031048 55.3269904906477,11.9613429171704 55.3257458375313,11.9656473425314 55.325719907258,11.9675402524794 55.3251753715195)'
),10 ))).geom) as geom
2
  • I don't have correct solution, but problem is: you have WGS coordinates and you don't specify coordinate system. Therefore, those are just planar coordinates, with distances less than 0.01 units between points. Of course there aren't any points added, when you try to segmentize them with 10 units value. Jan 16, 2013 at 15:50
  • Ah thanks just the input I was missing! Now it segmentizes fine!! select st_astext((st_dumpPoints(ST_Transform( (st_segmentize(ST_Transform(ST_GeomFromText('LINESTRING(11.959813031048 55.3269904906477,11.9613429171704 55.3257458375313,11.9656473425314 55.325719907258,11.9675402524794 55.3251753715195)' ,4326),25832 ),10 )) ,4326)) ).geom) as geom
    – dk_nls
    Jan 17, 2013 at 8:29

1 Answer 1

2

This is answer to " I need to route to closest point on the linestring " First slect closest point on line

http://www.postgis.org/docs/ST_Line_Locate_Point.html
float ST_Line_Locate_Point(geometry a_linestring, geometry a_point);

Then

http://www.postgis.org/docs/ST_Line_Substring.html
geometry ST_Line_Substring(geometry a_linestring, float startfraction, float endfraction);

Result geometry is now from startpoint to closest point on line to used point. no reason to segmentize or anythign else. This need that data is "LINESTRING" type, MULTI* wont work

Also point and linestring data has to be in same srid, you can transform dat using ST_Transform(...)

-- select one point from table, get closests line and return linegeometry 
-- which is from start to closestpoint
SELECT ST_Line_Substring(a_linestring, 0 , ST_Line_Locate_Point(a_linestring, a_point))
FROM lines l , point p 
WHERE p.id =1 ORDER BY ST_Distance(a_linestring, p.a_point) LIMIT 1 
3
  • Hi thanks for your answer. The reason for splitting the line into many points, is I'm routing from a road to a utility line, but closest point on the utility line is not the closest point on the line i can drive/route to, so finding nearest line, split that line into many points and then try to route to the points (binary search) and find shortest routing distance.
    – dk_nls
    Jan 17, 2013 at 10:37
  • so do you have "utility" road in your data ? Jan 17, 2013 at 10:54
  • I have one table with all roads, and one table with all cables. (I'm using commercial routing lib to do the routing, since I'm running postgres 9.2 and haven't had any luck with getting pgrouting to run on my dev. setup yet). I'm able to get my routes now, but there might be smarter ways?
    – dk_nls
    Jan 17, 2013 at 11:59

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.