Splitting a line into segments

Question

I would like to split a line to segments using PostGIS. For this I found the following SQL String:

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;

This gives me a correct result with 10 rows from 1 geometry. If I will change the linestring in the follow String I will only get one row.

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(8.53011977325458 47.4525557235204,8.53683231588295 47.4527692267847)'
        ),5) as geom
        ) as line
) as tmp;

Why doesn't the query not giving multiple rows? Please can you help me in this case. For the line in the second example I should have a splitting of the line in segments.

Answer 1

It's a question of units. In your first SQL string the LINESTRING units are in meters. In your second SQL string, you're basically using the same syntax but gave an argument that I suppose are in decimal degrees while PostGIS is interpreting them as meters. Add the SRID at the end of the ST_GeomFromText:

ST_GeomFromText(
    'LINESTRING(8.53011977325458 47.4525557235204,8.53683231588295 47.4527692267847)', 4326)

4326 is the SRID for WGS84.