Overestimated extent from ST_EstimatedExtent result for geography column

[strk]

Testing after #5734 I find these estimates over large. Longitude -10..10 is estimated as -60..60

From QGIS testcase:

strk@qgis_test=# select st_asewkt(g) from test_geog_ext;
        st_asewkt         
--------------------------
 SRID=4326;POINT(-10 -60)
 SRID=4326;POINT(-9 -54)
 SRID=4326;POINT(-8 -48)
 SRID=4326;POINT(-7 -42)
 SRID=4326;POINT(-6 -36)
 SRID=4326;POINT(-5 -30)
 SRID=4326;POINT(-4 -24)
 SRID=4326;POINT(-3 -18)
 SRID=4326;POINT(-2 -12)
 SRID=4326;POINT(-1 -6)
 SRID=4326;POINT(0 0)
 SRID=4326;POINT(1 6)
 SRID=4326;POINT(2 12)
 SRID=4326;POINT(3 18)
 SRID=4326;POINT(4 24)
 SRID=4326;POINT(5 30)
 SRID=4326;POINT(6 36)
 SRID=4326;POINT(7 42)
 SRID=4326;POINT(8 48)
 SRID=4326;POINT(9 54)
 SRID=4326;POINT(10 60)
(21 rows)


strk@qgis_test=# select st_extent(g::geometry) from test_geog_ext;
     st_extent      
--------------------
 BOX(-10 -60,10 60)
(1 row)

strk@qgis_test=# select ST_EstimatedExtent('public','test_geog_ext','g');
                               st_estimatedextent                               
--------------------------------------------------------------------------------
 BOX(-60.88928484347299 -60.88928484347299,60.88928484347299 60.88928484347299)
(1 row)

[strk]

I tried making the dataset span less latitudes, from -10 to 10 (longitude -40..40) and I'm getting:

extent height estimated 82.7105220022653, real 20.0 (with stats)

Could the code get confused between X and Y ?

[pramsey]

Copying in note from chat

this is expected… so the statistics and the index both probably have an idea of the extent that matches what’s in your head (something taller than it is wide). But our path to converting that geocentric box into something planar is this:

• find all the corners of the geocentric box
• find the average corner (which will be fairly central) so call it the center
• find the furthest corner from the center, make the distance to that corner by the radius
• you now have a “cap”, a center and a radius, that contains the original box, however said “cap” is as wide as it is tall
• find the planar bounds of that cap, which also ends up as wide as it is tall

(edited)

So the problem is, again, converting from a geocentric bounding volume to a planar rectangle that works in all cases. Your example extent does in fact contain the input, just with a lot of over coverage, but still: contract met!

[pramsey]

If anyone wants to pursue this, the work is not in the stats box or index box, those are coming out of the routine just fine, it's gbox_geocentric_get_gbox_cartesian converting those geocentric bounding volumes into a planar lon/lat box that does justice (covers) to the shapes in the original geocentric extent.

[pramsey]

Dupe of #5734