bounds of a table

[andrea antonello]

Hi, I found in the archives that the way to get the bounds of a table is:

SELECT Min(MbrMinX(geom)) AS min_x, Min(MbrMinY(geom)) AS
min_y,Max(MbrMaxX(geom)) AS max_x, Max(MbrMaxY(geom)) AS max_y FROM
tableName

This is slow and I am keep to think there is a faster way based on
some hidden info. Is there?

Thanks,
Andrea

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

Hi Andrea,

in order to precisely measure the table's full extent we are
always required to perform at least once a full table scan,
and this could easily be a time consuming step most notably
when the target table contains several tenth million rows.

anyway a smarter mechanism effectively avoiding to repeatedly
execute such operation without any real need exists; please see:

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=metadata-4.0

and check the documentation about these SQL functions:

GetLayerExtent()
UpdateLayerStatistics()
InvalidateLayerStatistics()

bye Sandro

[Pepijn Van Eeckhoudt]

What I’ve done in the past is to extract the bounds contained in the root node of the rtree (if it exists of course). This is less precise than querying the data since the rtree only stores the extent using single precision floating point, but it is obviously much much faster since it’s an O(1) operation.

The rtree nodes are stored in one of the shadow tables. It gets created as

CREATE TABLE <rtree_table_name>_node(nodeno INTEGER PRIMARY KEY, data BLOB)

The root node is guaranteed to have nodeno 1. By design the extent stored in each node in an rtree is the union of the extents of all its children. As a consequence the root node is guaranteed to have an extent the contains all the indexed entries.

The layout of the blob is

int16 rtree_depth;

int16 nb_entries;

<nb_entries times>

  int64 child_nodeno
  float32 min_x;
  float32 max_x;
  float32 min_y;
  float32 max_y;

You’ll need to query the blob using SQL and then calculate the union of the extent for each entry record. There’s no way to query these values directly AFAIK. The comment section at the top of <sqlite>/ext/rtree/rtree.c describes this in more detail.

Best regards,

Pepijn Van Eeckhoudt

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To post to this group, send email to spatiali...@googlegroups.com.
Visit this group at http://groups.google.com/group/spatialite-users.
For more options, visit https://groups.google.com/d/optout.

[mj10777]

The 'hidden' info is in vector_layers_statistics, which is keep up to date:

SELECT extent_min_x AS min_x,extent_min_y AS min_y, extent_max_y AS max_x, extent_max_y AS may_y FROM vector_layers_statistics WHERE table_name='berlin_geometries' AND geometry_column='soldner_polygon'

-4.240358 1207.500000 38747.958906 38747.958906

 

Thanks,
Andrea

[andrea antonello]

Thanks a lot for your answers.

The vector_layers_statistics seems to be the right choice. One has
just to make sure to update them when adding or removing features.

Cheers,
Andrea

[mj10777]

On Tuesday, 9 June 2015 08:21:43 UTC+2, moovida wrote:

Thanks a lot for your answers.

The vector_layers_statistics seems to be the right choice. One has
just to make sure to update them when adding or removing features.

I believe, after the initial filling AND UpdateLayerStatistics() has been called (i.e. the extent_* values are NOT NULL)

- the INSERT,UPDATE and DELETE triggers keep the 'vector_layers_statistics' entries up to date.