Bug report #12306

QGIS fails to load min-values for graduated style when datasource is postgres and table contains negative values

Added by Stefan Mecke over 4 years ago. Updated over 4 years ago.

Status:Closed
Priority:High
Assignee:-
Category:-
Affected QGIS version:2.8.0 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:20484

Description

QGIS fails to load min-values or compute the lowest classes for graduated style when datasource is postgres and the table contains negative values. Therefore some features are not shown.

Steps to reproduce:

  • create a simple postgres/postgis table
create table qgistest (id serial, val int, geo geometry(Point));

insert into qgistest (val, geo) values (-10, ST_GeomFromText('POINT(-2 1)'));
insert into qgistest (val, geo) values (-5, ST_GeomFromText('POINT(-1 1)'));
insert into qgistest (val, geo) values (0, ST_GeomFromText('POINT(0 1)'));
insert into qgistest (val, geo) values (5, ST_GeomFromText('POINT(1 1)'));
insert into qgistest (val, geo) values (10, ST_GeomFromText('POINT(2 1)'));
insert into qgistest (val, geo) values (15, ST_GeomFromText('POINT(3 1)'));
insert into qgistest (val, geo) values (35, ST_GeomFromText('POINT(4 1)'));
insert into qgistest (val, geo) values (75, ST_GeomFromText('POINT(5 1)'));
  • add this table to qgis as layer: You will see 8 points with default symbols
  • now open up layer properties; click style; choose 'graduated', choose column 'val' and click 'classify'. Leave 'number of classes at 5' and mode at 'same interval'.
  • the calculated classes are:
    • 0 - 15
    • 15 - 30
    • 20 - 45
    • 45 - 60
    • 60 - 75
  • click 'ok'
  • now only 6 features will be displayed; the two points with negative values are missing (because the lowest value in the source table is -10, so the bounds of the classes are wrong!)
  • this is also the case when choosing mode = quantil or mode = jenks with 3 classes.
  • This bug occurs with QGIS 2.8 (QGIS 2.8.0; QT 4.8.6; GDAL/OGR 1.10.1; Ubuntu 14.4 64bit) and 2.8.1 (QGIS 2.8.1; QT 4.8.1; GDAL/OGR 1.7.3; Linux Mint Maya) when data source is a postgres table.
  • I could reproduce this bug with Postgres 9.1 and Postgres 9.4
  • QGIS works as expected when the layer source is a shape file (e.g. after exporting the postgis-layer to shp file)

Associated revisions

Revision f7b790ff
Added by Jürgen Fischer over 4 years ago

postgres provider: use min()/max() before casting (followup bf56457; fixes #12306)

Revision 377c1dba
Added by Jürgen Fischer over 4 years ago

postgres provider: use min()/max() before casting (followup bf56457; fixes #12306)

(cherry picked from commit 7ce195ebec7330f101bb6922055a079958bfd58a)

History

#1 Updated by Mike Taves over 4 years ago

  • Affected QGIS version changed from 2.8.1 to 2.8.0
  • Operating System deleted (Linux Ubuntu)
  • Target version set to Version 2.8.2
This bug can be reproduced and extended to not just negative values, but values above 9999. For example, consider a continuation:
truncate qgistest;
insert into qgistest (val, geo) values (-10, ST_GeomFromText('POINT(-2 1)'));
insert into qgistest (val, geo) values (-5, ST_GeomFromText('POINT(-1 1)'));
insert into qgistest (val, geo) values (75, ST_GeomFromText('POINT(5 1)'));
insert into qgistest (val, geo) values (9999, ST_GeomFromText('POINT(5 1)'));
insert into qgistest (val, geo) values (10000, ST_GeomFromText('POINT(5 1)'));
insert into qgistest (val, geo) values (12000, ST_GeomFromText('POINT(5 1)'));

Now the 5 equal interval classes are:
  • -10.0000 - 1991.8000
  • 1991.8000 - 3993.6000
  • 3993.6000 - 5995.4000
  • 5995.4000 - 7997.2000
  • 7997.2000 - 9999.0000

So now negative values are included, but the arbitrary high limit is now 9999. Values above that are not shown.

Note that QGIS 2.6 did not have this bug, and I first saw it in QGIS 2.8.0 a few weeks ago.

The affected modes that exhibit behaviours of this bug are:
  • Equal Interval
  • Pretty Breaks

#2 Updated by Jürgen Fischer over 4 years ago

  • Status changed from Open to Closed

#3 Updated by Falko Engel over 4 years ago

I have a similiar problem using QGIS 2.8.1 on Ubuntu with PostgreSQL 9.4 and PostGIS 2.1.5
I think that all different modes in the graduated style are not working correctly.

The modes "equal interval", "natural breaks" and "pretty breaks" never show the true maximum values and don't sort values correctly when negative values are involved. Furthermore I don't think the value 9999 is a threshold for this behaviour. Actually the limit of the displayed maximum changes each time one switches from one mode to the other.

I can confirm that with the same data exported to shp the problem doesn't occur.

One additional wish: I would be nice if one had the option to pick an individual color for NULL-values.

Also available in: Atom PDF