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 a PostGIS Database with all streets from osm within a given boundingbox. 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?

Cheers, Martin

share|improve this question
    
Out of curiosity, why 20 meters? –  HeyOverThere Jul 3 '13 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 Jul 3 '13 at 17:29
add comment

1 Answer

up vote 3 down vote accepted

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.

Regards, Christian

share|improve this answer
    
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 Jul 2 '13 at 7:04
    
Do not hesitate to ask if you need help with that! –  chriserik Jul 2 '13 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 Jul 3 '13 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 Jul 3 '13 at 13:43
    
wow thanks this works! –  Martin Jul 3 '13 at 17:24
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.