How to get MBR of lineString column including multiple rows?

Question

I need to get MBR from table with multiple rows from lineString type column.

Using envelope I could get MBR from single row because envelope is not aggregate function.

How to get one MBR of lineString column including multiple rows?

Well, an option may be: find points for `LineString`: first and second- so, for all rows. Then, select minimum X, Y and maximum X, Y and build MBR explicitly as Polygon from there. Will work, of course, if your `lineStrings` are just point-to-point lines (so, contain only two points - otherwise it's impossible to predict all points from a line). Alternative may be stored code which iterates through cursor. But that would be slow. That's the place, where spatial fails (that's why using it in general is debatable - either it's a good practice or bad) — Alma Do, Jul 14 '14 at 08:27
@AlmaDo , I have done it. But you are right. It's better to calculate result else somewhere. — sectus, Jul 15 '14 at 10:00

score 0 · Accepted Answer · edited May 23 '17 at 12:06

Complicated query. Last row would has result.

SELECT 
   @id := id, 
   @geometry := Envelope(if(isNull(@geometry), `line`, GeometryCollection( `line` , geomFromText(AsText(@geometry))))), 
   AsText(@geometry)
FROM `table_line`
JOIN (SELECT @id :=0, @geometry := NULL) `tmp`
WHERE id > @id
ORDER BY id ASC

Using this answer

geomFromText(AsText(@geometry)) -- this is necessary because GeometryCollection does not work with direct variables.

P.S. but it's very complicated, do not use it in production.

How to get MBR of lineString column including multiple rows?

1 Answers1