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 10 years ago. Updated about 8 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/OGR
Affected QGIS version:2.6.0 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:up/downstream
Crashes QGIS or corrupts data:No Copied to github as #:19268

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, 2014-07-16 08:09 AM

postgres_limitations-2.jpg - metadata of postgresdb (155 KB) Joachim Deutmann, 2014-07-16 08:09 AM

postgres_limitations-3.jpg (80.4 KB) Joachim Deutmann, 2014-07-16 08:09 AM

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

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

view-in-2.2.0.jpg - loading postgresdb in qgis 2.2 (101 KB) Joachim Deutmann, 2014-07-17 12:19 AM

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

create_statement.jpg - pgadmin screenshot (140 KB) Joachim Deutmann, 2014-07-17 03:36 AM

create_statement_2.jpg - pgadmin screenshot (133 KB) Joachim Deutmann, 2014-07-17 03:36 AM

attributtable.jpg - attribut table view (166 KB) Joachim Deutmann, 2014-07-17 03:36 AM

reduced_dataset.jpg - reduced data (no strhsnr field) (155 KB) Joachim Deutmann, 2014-07-17 06:18 AM

postgresdb_as_postgis_layer.jpg - trying to open my postgreSQL Database with "Add PostGIS Layer" (141 KB) Joachim Deutmann, 2014-07-17 06:18 AM

warnings.png - Warnings (162 KB) Joachim Deutmann, 2014-09-01 03:08 AM

testdaten.zip (3.31 MB) Joachim Deutmann, 2014-11-10 09:21 AM

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

History

#1 Updated by Jan Lippmann over 10 years ago

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

#2 Updated by Joachim Deutmann over 10 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 ?!?

#3 Updated by Jan Lippmann over 10 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

#4 Updated by Joachim Deutmann over 10 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

#5 Updated by Giovanni Manghi over 10 years ago

  • Status changed from Open to Feedback
  • Subject changed from access to postgres database limited to 500 datasets to postgresql table shows only 500 features
  • 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

#6 Updated by Joachim Deutmann over 10 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)

#7 Updated by Jürgen Fischer over 10 years ago

  • Category changed from Data Provider to Data Provider/OGR

#8 Updated by Jürgen Fischer over 10 years ago

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

#9 Updated by Joachim Deutmann over 10 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

#10 Updated by Joachim Deutmann over 10 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

#11 Updated by Giovanni Manghi about 10 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.

#12 Updated by Giovanni Manghi about 10 years ago

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

closing for lack of feedback. please reopen if necessary.

#13 Updated by Joachim Deutmann about 10 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

#14 Updated by Giovanni Manghi about 10 years ago

  • Resolution deleted (worksforme)
  • 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
  • Operating System deleted (windows)
  • OS version deleted (xp prof)
  • Affected QGIS 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.

#15 Updated by Even Rouault about 8 years 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 PDF