3

Is it possible to use SQL or PL/pgsql to get the largest rectangle in the R-tree index (or other types of indices) of a table, so that it can be used as Bounding box for PostGIS table?

If possible, this would be much faster than going through all shapes and aggregating the bounding box (as suggested in the answers of Bounding box for PostGIS table).

(By comparison, shapefiles have bounding rectangles pre-computed. It would be nice if PostGIS can have this too in some form.)

tinlyx
  • 11,057
  • 18
  • 71
  • 119
  • on some environments I used to have a 'meta' database containing e.g. a table with the max extend of each table as bbox references, created by the methods you linked, which was only slightly faster than aggregating the extend (somewhat defeating the purpose of an index...). other than that, the geometries of the index relations are not directly accessible AFAIK (but I probably have only read the same things as you did already...would appreciate an insight view on this) – geozelot May 12 '18 at 19:41
  • Would ST_EstimatedExtend do for you? – user30184 May 13 '18 at 06:13
  • 1
    @user30184 Thanks! ST_EstimatedExtent is what I'm looking for - an extent that can be obtained in roughly constant time. – tinlyx May 13 '18 at 06:21
  • I am glad that has answered your question, which is a good one, but which I misunderstood. I was searching for something to actually allow you to examine each r-tree box at any level, which is a lot more painful to do. – John Powell May 14 '18 at 07:40
  • @JohnPowellakaBarça Searching the r-tree boxes is my original goal. I still think it's a good solution if possible at all. – tinlyx May 14 '18 at 07:56

1 Answers1

0

It seems that this function will be available in PostGIS 2.5. From the "New Features" in the NEWS:

#2256, _postgis_index_extent() for extent from index (Paul Ramsey)

which states:

In 16149:

Proof of content for reading table extent from the top of the index. Can be called with _postgis_index_extent(tbl regclass, col text) and returns box2d. ...

tinlyx
  • 11,057
  • 18
  • 71
  • 119