# ST_LineSubString Query Not Giving Expected Results

I have a multilinestring that is approximately 9700 meters long. My goal is to merge the multilinestring into a single 9700m linestring, then split the merged linestring into 97 linestrings of 100m lengths. When using the query below (taken from the docs for ST_LineSubstring), I get the expected results, but I cannot view the results in QGIS. I suspect I am not understanding something conceptual about the coordinate system?

The SRID for `public.subdivide_lineTesting` is `4326` and I'm using PostGIS 2.2.1.

``````SELECT ST_LineSubstring(the_geom, 100.00*n/length,
CASE
WHEN 100.00*(n+1) < length
THEN 100.00*(n)/ length
ELSE 1
END) AS the_geom
FROM (
SELECT ST_LineMerge(geom) AS the_geom,
ST_Length(geom::geography) AS length
FROM public."subdivide_lineTesting"
) AS t
CROSS JOIN generate_series(0,10000) AS n
WHERE n*100.00/length < 1
``````

Also, a side observation: When I count the number of points in the geometry produced using the below query, every tuple is null except for the last one. That record shows an expected 2 points.

``````st_numpoints(ST_LineSubstring(the_geom, 100.00*n/length,
CASE
WHEN 100.00*(n+1) < length
THEN 100.00*(n)/ length
ELSE 1
END)) as points
`````` • it has to be `(n+1)` in `WHEN` and `THEN`. otherwise, the function tries to produce a substring from and to the same point. that should also explain your observation. btw., the `10000` is an arbitrary number representing an assumed count of segments to be produced; you can set it to `100` in your case. hope that helps. – geozelot Jun 5 '18 at 15:18
• Could you give us actual test data and not screen shots? – Evan Carroll Jun 5 '18 at 18:13

As it is, the ST_LineSubstring returns a point except for the last segment. Assuming we are not on the last segment, the query becomes

``````SELECT ST_LineSubstring(the_geom, 100.00*n/length, 100.00*(n)/ length) AS the_geom
``````

which corresponds to a point.

You will want to extract to the next segment, so

``````SELECT ST_LineSubstring(the_geom, 100.00*n/length,
CASE
WHEN 100.00*(n+1) < length
THEN 100.00*(n+1)/ length
ELSE 1
END) AS the_geom
``````

or in a simpler version

``````SELECT ST_LineSubstring(the_geom, 100.00*n/length,
LEAST(100.00*(n+1)/length, 1.0)) AS the_geom
``````
• +1 for `LEAST` - neat! didn't see your answer while writing the comment – geozelot Jun 5 '18 at 15:27