Bug report #10904

postgresql table shows only 500 features when adding from "add vector layer" and db has not postgis installed

Added by Joachim Deutmann over 2 years ago. Updated 5 months ago.

Status:Closed Start Date:07/16/2014
Priority:Normal Due date:
Assigned to:- % Done:

0%

Category:OGR Data Provider
Target version:-
Platform: Pull Request or Patch supplied:No
Platform version: Affected version:2.6.0
Status info: Causes crash or corruption:No
Resolution:up/downstream Tag:

Description

I tried to connect to a postgres database with over 130.000 datasets and only 500 where displayed in the canvas. This happens in Version 2.4.0. In Version 2.2.0 everything is allright.

OS WinXP Prof.
QGIS Version 2.4
Postgres 8.3

postgres_limitations.jpg - layerwindow with objectcount (45.1 kB) Joachim Deutmann, 07/16/2014 08:09 am

postgres_limitations-2.jpg - metadata of postgresdb (154.6 kB) Joachim Deutmann, 07/16/2014 08:09 am

postgres_limitations-3.jpg (80.4 kB) Joachim Deutmann, 07/16/2014 08:09 am

postgres_limitations-4.jpg - Connect to postgresDB via "Open a vector layer" (29.8 kB) Joachim Deutmann, 07/16/2014 08:09 am

view-in-1.8.0.jpg - loading postgresdb in qgis 1.8 (91.2 kB) Joachim Deutmann, 07/17/2014 12:19 am

view-in-2.2.0.jpg - loading postgresdb in qgis 2.2 (101.3 kB) Joachim Deutmann, 07/17/2014 12:19 am

view-in-2.4.0.jpg - loading postgresdb in qgis 2.4 (82.2 kB) Joachim Deutmann, 07/17/2014 12:19 am

create_statement.jpg - pgadmin screenshot (139.8 kB) Joachim Deutmann, 07/17/2014 03:36 am

create_statement_2.jpg - pgadmin screenshot (133.3 kB) Joachim Deutmann, 07/17/2014 03:36 am

attributtable.jpg - attribut table view (165.8 kB) Joachim Deutmann, 07/17/2014 03:36 am

reduced_dataset.jpg - reduced data (no strhsnr field) (155.4 kB) Joachim Deutmann, 07/17/2014 06:18 am

postgresdb_as_postgis_layer.jpg - trying to open my postgreSQL Database with "Add PostGIS Layer" (140.6 kB) Joachim Deutmann, 07/17/2014 06:18 am

warnings.png - Warnings (161.5 kB) Joachim Deutmann, 09/01/2014 03:08 am

testdaten.zip (3.3 MB) Joachim Deutmann, 11/10/2014 09:21 am

adrtest.backup - dump of database (postgreSQL 8.4) (2.4 MB) Joachim Deutmann, 11/10/2014 09:21 am

History

Updated by Jan Lippmann over 2 years ago

Have you tried via "Add PostGIS layer" to load the dataset?

Updated by Joachim Deutmann over 2 years ago

Jan Lippmann wrote:

Have you tried via "Add PostGIS layer" to load the dataset?

Yes, i try, but it is not a postgis dataset, so loading with "Add PostGIS layer" doesn't work... Because of using the database not only in qgis, it is not possible to create it as a PostGIS database (i use it also for a search function in an openlayers-client).

I have other databases in PostgreSQL with PostGIS Extension; they work (all datasets are displayed in the canvas).

How ever, in the QGIS Versions below 2.4.0, it works without problems or limitiations (see screenshots). I think, it is an forgotten limitation during testing the Vers 2.4.0 ?!?

Updated by Jan Lippmann over 2 years ago

I can't reproduce this problem. i've tested with 400000 multipolygons (load via "Add Vectorlayer"). may be the problem is within the data and the OGR provider and german "umlaute"?
Have you tried to load data only with the gid and the geometry column?
can you post the create statement of the table?

win 7 64 bit
qgis 32 bit
postgres 9.3 postgis 2.0

QGIS-Version 2.4.0-Chugiak QGIS-Codeversion 8fdd08a
Kompiliert gegen Qt 4.7.1 Laufendes Qt 4.7.1
Kompiliert mit GDAL/OGR 1.11.0 Läuft mit GDAL/OGR 1.11.0
Kompiliert mit GEOS 3.4.2-CAPI-1.8.2 Läuft mit GEOS 3.4.2-CAPI-1.8.2 r3921
PostgreSQL-Client-Version 8.3.10 SpatiaLite-Version 4.1.1
QWT-Version 5.2.1 PROJ.4-Version 480
QScintilla2-Version 2.6.2

Updated by Joachim Deutmann over 2 years ago

first of all, my postgres version is 8.4 not 8.3.

I think, it is not a problem with the german "umlaute", because they are shown correctly in the attribut table (see screenshot "attributtable"). In the two other screenshots (taken from pgadmin) you will see the creation statement of the table "adressen".

I would try to reduce the data (delete the column "strhsnr") and then open it with qgis 2.4. I post the result later.

win xp 32bit
qgis 32bit
postgres 8.4 postgis

QGIS-Version 2.4.0-Chugiak QGIS-Codeversion 8fdd08a
Kompiliert gegen Qt 4.7.1 Laufendes Qt 4.7.1
Kompiliert mit GDAL/OGR 1.11.0 Läuft mit GDAL/OGR 1.11.0
Kompiliert mit GEOS 3.4.2-CAPI-1.8.2 Läuft mit GEOS 3.4.2-CAPI-1.8.2 r3921
PostgreSQL-Client-Version 8.3.10 SpatiaLite-Version 4.1.1
QWT-Version 5.2.1 PROJ.4-Version 480
QScintilla2-Version 2.6.2

Updated by Giovanni Manghi over 2 years ago

  • Subject changed from access to postgres database limited to 500 datasets to postgresql table shows only 500 features
  • Status changed from Open to Feedback
  • Priority changed from Normal to Severe/Regression

Yes, i try, but it is not a postgis dataset, so loading with "Add PostGIS layer" doesn't work...

if is not PostGIS what is? just curious, but actually this should be the clue to allow someone understand what is going on

Updated by Joachim Deutmann over 2 years ago

Giovanni Manghi wrote:

Yes, i try, but it is not a postgis dataset, so loading with "Add PostGIS layer" doesn't work...

if is not PostGIS what is? just curious, but actually this should be the clue to allow someone understand what is going on

I overwrite the database monthly with this batch:

"ogr2ogr -overwrite -f "PostgreSQL" PG:"host=xxxxx dbname=adrtest port=5432 user=adressen password=xxxxx" ohne_strhsnr_test.shp -nln adressdaten"

Open the database "adrtest" with "Add PostGIS Layer" fails, because the database has no postgis support (see screenshot "trying to open...").

reducing the dataset (delete field 'strhsnr'(this field contains german "umlaute")) has no effect. Table still shows only 500 features

This is curious: exactly the same database can be open in QGIS 1.8 and in QGIS 2.2 without any limitations or other problems. (see screenshots)

Updated by Jürgen Fischer over 2 years ago

  • Category changed from Data Provider to OGR Data Provider

Updated by Jürgen Fischer over 2 years ago

Not reproducable here either - are there any messages in the log?

Updated by Joachim Deutmann over 2 years ago

Jürgen Fischer wrote:

Not reproducable here either - are there any messages in the log?

Hello,

in the protocol i have found only a few warnings, but no error message. All warnings have nothing to do with postgres or postgis. (see warnings.png). Writes QGIS a log file? If so: where this file is located?

best regards

Updated by Joachim Deutmann over 2 years ago

Joachim Deutmann wrote:

Jürgen Fischer wrote:

Not reproducable here either - are there any messages in the log?

Hello,

in the protocol i have found only a few warnings, but no error message. All warnings have nothing to do with postgres or postgis. (see warnings.png). Writes QGIS a log file? If so: where this file is located?

best regards

Updated by Giovanni Manghi over 2 years ago

  • Priority changed from Severe/Regression to Normal

I just tested with data of my own (with many millions of features) under Windows and Linux with qgis master, using postgresql 9.3/postgis 2.1 under Linux and postgresql 8.3/postgis 1.4 under Windows.

The command I used to import the data was something like

ogr2ogr -f "PostgreSQL" PG:"host=localhost user=*** dbname=curso password=***" crif2011.shp -nln crif

The described issue does not show in both cases.

If would be necessary to attach sample data to try understand if the eventual issue is there.

Updated by Giovanni Manghi over 2 years ago

  • Status changed from Feedback to Closed
  • Resolution set to worksforme

closing for lack of feedback. please reopen if necessary.

Updated by Joachim Deutmann over 2 years ago

Hallo everybody,

the problem is still unsolved. I try it with qgis 2.6 but it is the same as in qgis 2.4. Only 500 features are shown in the canvas. I send you some data to reproduce the error. I try it on another personal computer and the error occours in the same way as on my PC.

In the zip-file there are
1. a csv-file with dummy data
2. a shape-file with the csv-data inside
3. the batch-file to fill the postgreSQL - Database "adrtest" (NO POSTGIS is used during creation of the database "adrtest" with pgadmin, because not needed)
4. Two Hardcopies to see the result in the Canvas of QGIS 1.8 and in the canvas of QGIS 2.6.

At last i upload a dump of the database "adrtest" for you too, so you can see the result.

The data must contain East and North Values because i need the values explicit for a location-search-script in an openlayers webgis.

I hope you find the error. Thank you for your help.

Yours
Joachim

Updated by Giovanni Manghi over 2 years ago

  • Subject changed from postgresql table shows only 500 features to postgresql table shows only 500 features when adding from "add vector layer" and db has not postgis installed
  • Platform deleted (windows)
  • Platform version deleted (xp prof)
  • Resolution deleted (worksforme)
  • Affected version changed from 2.4.0 to 2.6.0

Joachim Deutmann wrote:

Hallo everybody,

the problem is still unsolved. I try it with qgis 2.6 but it is the same as in qgis 2.4. Only 500 features are shown in the canvas. I send you some data to reproduce the error. I try it on another personal computer and the error occours in the same way as on my PC.

In the zip-file there are 1. a csv-file with dummy data 2. a shape-file with the csv-data inside 3. the batch-file to fill the postgreSQL - Database "adrtest" (NO POSTGIS is used during creation of the database "adrtest" with pgadmin, because not needed) 4. Two Hardcopies to see the result in the Canvas of QGIS 1.8 and in the canvas of QGIS 2.6.

At last i upload a dump of the database "adrtest" for you too, so you can see the result.

The data must contain East and North Values because i need the values explicit for a location-search-script in an openlayers webgis.

I hope you find the error. Thank you for your help.

Yours Joachim

Now I see, you must import data into a DB without PostGIS (a detail that I overlooked before) and add the layer using the "add vector layer" dialog instead of "add postgis layer".

Seems to me that would be much more sense to have postgis in a db to store geographic data, but anyway, it is confirmed.

Updated by Even Rouault 5 months ago

  • Status changed from Reopened to Closed
  • Resolution set to up/downstream

Fixed by GDAL trunk r35631 (2.2dev) and branches/2.1 r35632 (for 2.1.2) "PG: fix errors caused by missing geometry_columns/spatial_ref_sys tables in non PostGIS databases, that prevent reading more than 500 features (fixes #10904)"

Also available in: Atom