1

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

enter image description here

  • 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
3

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
|improve this answer|||||
  • +1 for LEAST - neat! didn't see your answer while writing the comment – geozelot Jun 5 '18 at 15:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.