# How to count the number of 100m length chunks in a table with line geometry?

And by chunks I do not mean segments so I can't use st_segmentize for this

In other words I have a linetable which has many lines of varying length. Example:

``````id | st_length(geom) | intended count for result
1 | 30 | 1
2 |  72 | 1
3 | 150 | 2
4 | 10 | 1
5 | 230 | 3
6 | 350 | 4
``````

What I'm after is the sum of the above intended column, so 1+1+2+1+3+4 = 12

I have come up with a way of doing this, but because lines can go up to 8k in length, it's just not a good solution

``````select a.count100 + b.count200 from
( select count(*) count100 from linetable where st_length(geom) < 100) a,
( select count(*) count200 from linetable where st_length(geom) >= 100 and st_length(geom) < 200) b
``````

Is there a way to do this more efficiently

If I get that right this should trivially be

``````SELECT SUM(CEIL(ST_Length(geom) / 100.0))::INT
FROM   <lines>
;
``````

in the case that you want to count 100m (or less) segments per LineString ( -> 12), or

``````SELECT CEIL(SUM(ST_Length(geom)) / 100.0)::INT
FROM   <lines>
;
``````

if 100m (or less) segments of the total length needs to be counted (-> 9).

• Yep the 12 is what I was after, thanks! The 9 might be useful for other people too May 28, 2021 at 15:12