2

I have a PostGIS database with all streets from OSM within a given bounding box. Now I want to split all roads from my table planet_osm_roads into segments of max 20 meters length and save those segments to a new PostGIS table.

The geometry of the roads is save in "way geometry(linestring,900913)". Can anyone give me a hint on how to accomplish this?

Improve this question
2
  • Out of curiosity, why 20 meters?
    – HeyOverThere
    Commented Jul 3, 2013 at 14:55
  • I am calculating statistics from car routes, where i have several sensor readings. I want to calculate stuff like speed, co2 emissions and so on for all the segments. There are a lot of measurements, and calculating those statistics for the whole line segment (street) does not show for example standing time at an intersection. To actually do this the most correct way I would need to split the streets at intersections, or where the speed limit changes....but that is too complicated right now - this serves my purpose for now.
    – Martin
    Commented Jul 3, 2013 at 17:29

1 Answer 1

Reset to default
4

use the ST_Segmentize() function (http://postgis.net/docs/manual-1.4/ST_Segmentize.html). Then split the linestring on each vertex:

SELECT ST_AsText(ST_MakeLine(start_point,end_point)) as geom FROM
(
    SELECT 
        ST_Pointn(geom, generate_series(1, ST_NumPoints(geom)-1)) as start_point, 
        ST_Pointn(geom, generate_series(2, ST_NumPoints(geom))) as end_point
    FROM (
        SELECT ST_Segmentize(ST_GeomFromText('LINESTRING(0 0,0 50)'),5) as geom
        ) as line
) as tmp;

linestring segments shorter than the treshold ('5' in this example) will stay the same, of course.

Improve this answer
4
  • thanks for the answer, I have not forgotten you, but I ran into some problems with different coordinate systems, as soon as I have resolved those I will test this!
    – Martin
    Commented Jul 2, 2013 at 7:04
  • Do not hesitate to ask if you need help with that!
    – chriserik
    Commented Jul 2, 2013 at 10:32
  • ok I think this works, but sorry for being a complete SQL noob. I put it like this: SELECT ST_AsText(ST_MakeLine(start_point,end_point)) as geom FROM ( SELECT ST_Pointn(geom, generate_series(1, ST_NumPoints(geom)-1)) as start_point, ST_Pointn(geom, generate_series(2, ST_NumPoints(geom))) as end_point FROM ( SELECT ST_Segmentize(way,5) as geom FROM planet_osm_line ) as line ) as tmp; SO how can I now have all these segments saved, they should countain the same columns and attributes as the line they came from, of course with a different id
    – Martin
    Commented Jul 3, 2013 at 10:43
  • 1
    Simply include the attributes you want to retain in the select statement like this (sorry for the readability, but code in comments is not really formatting well): SELECT ST_AsText(ST_MakeLine(start_point,end_point)) as geom, attribute FROM ( SELECT ST_Pointn(geom, generate_series(1, ST_NumPoints(geom)-1)) as start_point, ST_Pointn(geom, generate_series(2, ST_NumPoints(geom))) as end_point, attribute FROM ( SELECT ST_Segmentize(ST_GeomFromText('LINESTRING(0 0,0 50)'),5) as geom, 'sample_attribute' as attribute ) as line ) as tmp;
    – chriserik
    Commented Jul 3, 2013 at 13:43

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.