Quick way to access the MBR of a SpatiaLite geometry column

[3B3C891A-156D-11DE-8AC6-A4FF55D89593]

Hi,

Is there an easy way of quickly getting the MBR of a (spatially
indexed) geometry field in SpatiaLite? We need to do this for very
large database tables and manually calculating for every row is too
time consuming for our purposes.

Ideally, an equivalent to the PostGIS ESTIMATED_EXTENT function would
be great - ie an estimate based on a database vacuum is perfectly
fine. Otherwise is there a way to interrogate the spatial index itself
to find the biggest parts of the R-Tree and union these handful of
geometries together?

Many thanks,
F.

[Alessandro Furieri]

I'm not really sure to understand what you are exactly meaning.
Are you mainly interested in retrieving MBRs for any single geometry,
or do you intend to get the full-EXTENT for the whole table ?

I'll assume we are speaking of the full-extent ...
you can then use:

SELECT Min(MbrMinX(geometry)), Min(MbrMinY(geometry)),
Max(MbrMaxX(geometry)), Max(MbrMaxY(geometry))
FROM my_table;

this will require a quite negligible time if MY_TABLE
contains some thousand rows; if MY_TABLE contains some
million rows it will require several seconds.

If your Geometry column is supported by an R*tree spatial index
you can directly query the index, as in:

SELECT Min(xmin), Min(ymin), Max(xmax), Max(ymax)
FROM idx_my_table_geometry;

timing is (more or less) quite the same as above.

bye, Sandro

[Fraser Kirkpatrick]

Hi Sandro - yes it was the full extent of the whole table we were after. For smaller tables the query above performs well - as you say negligible time or seconds to return. The biggest table we have is 6 million polygons and that does take a minute or so to calculate so I was really just wondering whether there is any scope to optimise this.

Obviously in this last case this is a large number of complex geometries so either some sort of pre-calculated estimate or interrogating the spatial index itself may have been quicker. It looks like you can interrogate the spatial indexes directly so I'll maybe have a quick look into this to see if I can optimise our large table based on querying the contents of the index instead.

However, overall we are very impressed by the performance of SpatiaLite!

Many thanks,
Fraser.

[a.fu...@lqt.it]

Hi Fraser,
some month ago I had to handle a similar problem;
in my case I had a 1.5 millions points table.

I fixed this quite satisfactorily adding an ancillary table ('extents'):
- I feed this table once at start-up time (connection)
- the user can request an explicit update if and when
  required ... obviously, waiting a little bit ...

Please note: the MbrMinX(), MbrMax() ... functions runs
in very short time because SpatiaLite includes an explicit MBR def
directly within the Geometry binary encoding;
so such functions have not to parse the whole geometry, but they
simply query this pre-built internal MBR; and the internal MBR
is silently updated for each INSERT or UPDATE involving a Geometry.
Anyway, reading 6 million rows takes its times ...

Querying the spatial index is a little faster, because
you simply have to fetch four DOUBLEs, and loading the
whole BLOB (may be, a very heavy one ...) isn't required
in this case.

bye,
Sandro