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
nmtoken
  • 13,355
  • 5
  • 38
  • 87
dk_nls
  • 83
  • 1
  • 4
  • 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. – user1702401 Jan 16 '13 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 '13 at 08:29

1 Answers1

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
simpleuser001
  • 3,864
  • 18
  • 21
  • 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 '13 at 10:37
  • so do you have "utility" road in your data ? – simpleuser001 Jan 17 '13 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 '13 at 11:59