Inconsistent layer extent between ST_Extent and ST_Estimated_Extent

[qgib]

Author Name: Andreas Neumann (@andreasneumann)
Original Redmine Issue: 21718
Affected QGIS version: 3.7(master)
Redmine category:data_provider/postgis
Assignee: Jürgen Fischer

---

We have a strange situation of a WMS service where there is only one layer in the project.

If the layer is loaded directly as the leaf layer, then not all data is displayed, if the layer is loaded as root (top level) layer, then all data is displayed.

For comparison reasons, in the attached project and screenshot the leaf layer is displayed in gray, and the top level layer in color (yellow).

Project is in EPSG 2056 and should also be loaded as such.

Data source is PostgreSQL (data attached).

---

• wms_strange_clipping_problem.png (Andreas Neumann)
• clipped_wms.qgs (Andreas Neumann)
• TestClip.qgs (Andreas Neumann)
• ameisennester_1700.backup (Andreas Neumann)

[qgib]

Author Name: Andreas Neumann (@andreasneumann)

---

Forgot to add the URL of the WMS:

https://services.geo.zg.ch/ows/TestClip

[qgib]

Author Name: Alessandro Pasotti (@elpaso)

---

• assigned_to_id was configured as Alessandro Pasotti

[qgib]

Author Name: Andreas Neumann (@andreasneumann)

---

This issue might not be a server issue.

We are experiencing strange things on the Desktop with this data as well (e.g. the extent that QGIS calculates (invalid) is different from the extent that Postgis is calculating (coorect))

Will post more infos when I find out more.

[qgib]

Author Name: Andreas Neumann (@andreasneumann)

---

More info:

SELECT 1 AS pk, st_estimated_extent('ameisen_1700','ameisenschutz','geom') AS geom

run in Postgis

gives the invalid result that also QGIS uses.

[qgib]

Author Name: Andreas Neumann (@andreasneumann)

---

Issue is that QGIS uses the ST_Estimated_Extent() from Postgis as layer extent, which may differ from real extent given from ST_Extent()

The question is, if QGIS should be using ST_Estimated_Extent(), because it may differe substantially from the real extent given back by ST_Extent()

Note that I did not enable the "use estimated table metadata" checkbox.

[qgib]

Author Name: Marco Bernasocchi (@mbernasocchi)

---

the culprit might be here https://github.com/qgis/QGIS/blob/master/src/providers/postgres/qgspostgresprovider.cpp#L3240

[qgib]

Author Name: Andreas Neumann (@andreasneumann)

---

Maybe this line should be:

if ( !mIsQuery && mUseEstimatedMetadata && mSqlWhereClause.isEmpty()  )

[qgib]

Author Name: Andreas Neumann (@andreasneumann)

---

• assigned_to_id was changed from Alessandro Pasotti to Jürgen Fischer
• operating_system was changed from Linux to all
• version was changed from 3.6.1 to 3.7(master)
• category_id was changed from QGIS Server to Data Provider/PostGIS
• subject was changed from QGIS Server WMS: inconsistent layer extent when viewing leaf layer vs root layer to Inconsistent layer extent between ST_Extent and ST_Estimated_Extent

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

Andreas Neumann wrote:

Maybe this line should be:

if ( !mIsQuery && mUseEstimatedMetadata && mSqlWhereClause.isEmpty()  )

Depends. @mUseEstimatedMetadata@ was meant to trade performance for accuracy - and not to avoid usage of inaccurated stats. So the original version was to use the stats even if there is a where clause (which otherwise would alter the returned extent), if @mUseEstimatedMetadata@ is on.

Wouldn't it be better to just analyze the table to update the stats? You want reliable stats for other queries too.

[qgib]

Author Name: Andreas Neumann (@andreasneumann)

---

Hi Jürgen,

Thanks for having a look at the issue. In my case (PostgreSQL 10, Postgis 2.4) the vacuum analyze did not help unfortunately.

However, Marco B. imported the data into his PostgreSQL 11 and did not reproduce the issue.

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

Andreas Neumann wrote:

Thanks for having a look at the issue. In my case (PostgreSQL 10, Postgis 2.4) the vacuum analyze did not help unfortunately.

However, Marco B. imported the data into his PostgreSQL 11 and did not reproduce the issue.

Interesting - 9.6/2.3 and 10/2.4 have the issue - 11/2.5 is fine.

test=# select version(),postgis_version(),st_estimatedextent('ameisen_1700','ameisenschutz','geom'),st_extent(geom) from ameisen_1700.ameisenschutz;
-[ RECORD 1 ]------+----------------------------------------------------------------------------------------
version            | PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (Debian 7.2.0-1) 7.2.0, 64-bit
postgis_version    | 2.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
st_estimatedextent | BOX(2681941.25 1219439.125,2683874.5 1222586.5)
st_extent          | BOX(2680800.79 1219175.85,2683864.79 1222570.85)

test=# select version(),postgis_version(),st_estimatedextent('ameisen_1700','ameisenschutz','geom'),st_extent(geom) from ameisen_1700.ameisenschutz;
-[ RECORD 1 ]------+-------------------------------------------------------------------------------------------------------
version            | PostgreSQL 10.5 (Debian 10.5-1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.2.0-3) 8.2.0, 64-bit
postgis_version    | 2.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
st_estimatedextent | BOX(2681941.25 1219439.125,2683874.5 1222586.5)
st_extent          | BOX(2680800.79 1219175.85,2683864.79 1222570.85)

test=# select version(),postgis_version(),st_estimatedextent('ameisen_1700','ameisenschutz','geom'),st_extent(geom) from ameisen_1700.ameisenschutz;
-[ RECORD 1 ]------+-------------------------------------------------------------------------------------------------------
version            | PostgreSQL 11.2 (Debian 11.2-2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-2) 8.3.0, 64-bit
postgis_version    | 2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
st_estimatedextent | BOX(2680800.75 1219175.75,2683865 1222570.875)
st_extent          | BOX(2680800.79 1219175.85,2683864.79 1222570.85)

[qgib]

Author Name: Andreas Neumann (@andreasneumann)

---

hm - strange.

What do you suggest?

I am happy to upgrade our server one day (not immediately) - but I guess that I am not the only one with such a combo (10/2.4) ;-(

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

Applied in changeset 95bd748.

---

• status_id was changed from Open to Closed
• done_ratio was changed from 0 to 100

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

Andreas Neumann wrote:

I am happy to upgrade our server one day (not immediately) - but I guess that I am not the only one with such a combo (10/2.4) ;-(

Now st_estimatedextent is only used when "use estimated metadata" is enabled, but in that case the where clause is still ignored. So it should still have the intended boost when enabled, but not be inaccurate anymore when disabled.