3

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

5

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).

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

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.