Bug report #5813

cannot load postgis layers from sde database

Added by Wouter Boasson over 8 years ago. Updated over 8 years ago.

Status:Closed
Priority:High
Assignee:-
Category:Data Provider/PostGIS
Affected QGIS version:1.8.0 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:fixed
Crashes QGIS or corrupts data:No Copied to github as #:15296

Description

Serious regression: up to version 1.7.4 loading of postgis layers from an ESRI ArcSDE database worked perfectly well, starting with 1.8.0 every not POINT layer becomes virtually unaccessable.
Reason: ArcSDE saves the geometry as type GEOMETRY (except for POINT layers), and QGis starts trying to detect the geometry type. This will probably take forever, as I can't see any progress in about 20 minutes..
Ticking the 'look in geometry columns only' checkbox does not help. This renders QGis useless to load the data from our ArcSDE database, something we need to take full advantage of the postgis database. It also prevents us to start providing QGis as our base GIS, instead of ArcGIS, something we're considering. Regression + blocking the upgrade is why I filed this issue as a blocker.
See the screenshot showing the geometry_columns contents and the QGis postgis load dialog. Running on postgresql 8.3, postgis 1.3 (dictated by ESRI).

qgis-postgis-sde.png - geometry_columns, qgis postgis load dialog (266 KB) Wouter Boasson, 2012-06-18 01:47 AM

qgis19dev-postgis-sde.log Magnifier - debug log (24.3 KB) Wouter Boasson, 2012-06-18 03:18 AM

qgis19dev-postgis-sde.png - 1.9dev hanging (119 KB) Wouter Boasson, 2012-06-18 03:18 AM

qgis17-autodetect-postgis-sde.png - 1.7 autodetecting layer type (81 KB) Wouter Boasson, 2012-06-18 03:18 AM

qgis17-postgis-sde.png - 1.7 showing geometry type from geometry_columns (153 KB) Wouter Boasson, 2012-06-18 03:18 AM

Associated revisions

Revision d12c4a79
Added by Jürgen Fischer over 8 years ago

determine geometry types of GEOMETRY in geometry_columns (related to #5813)

History

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

  • Priority changed from Severe/Regression to High

Are you getting output in the message log? Is there a public database one could try?

Which platform are you using?

If Windows I'd suggest to install qgis-dev (nightly build of master) from OSGeo4w and to log the debug output (with DebugView).

On Linux you should build a debug version and get debug output from that.

#2 Updated by Wouter Boasson over 8 years ago

Thanks for the quick response!
- My platform: Linux Ubuntu 1.04 LTS
- Double checked the Windows 1.7.1 and 1.7.4 versions, they work perfectly fine (see screenshots)
- Installed and tested the Windows OSGeo4W nightly build (1.9dev), same issue as 1.8.0 on Ubuntu
- Captured the debug output on Windows, of which I think it will not be of much help, unfortunately; the log starts when clicking 'Connect' (I stripped the log a bit, it was just displaying a thousand more layers without errors)

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

Wouter Boasson wrote:

- Captured the debug output on Windows, of which I think it will not be of much help, unfortunately; the log starts when clicking 'Connect' (I stripped the log a bit, it was just displaying a thousand more layers without errors)

You're right. Doesn't help much. I'm seeing no "Retrieving geometry types: " at all. Is that with "look in geometry columns only" checked?

What about a public database?

#4 Updated by Wouter Boasson over 8 years ago

Indeed, with look in geometry columns only checked.
Unfortunately, I can't provide you with a public database for security and license reasons. My guess is, as things are working properly for POINT types, that you can easily replicate the issue by putting type 'GEOMETRY' in the geometry_columns table for a non-POINT type. The type itself is correctly stored in the data records, see the following output:

sde_gdbrivm=# SELECT objectid, pc4, GeometryType(shape) FROM nl.adm_pc4_2010_basis LIMIT 2;
 objectid | pc4  | geometrytype 
----------+------+--------------
        1 | 1011 | POLYGON
        2 | 1012 | POLYGON
(2 rows)

sde_gdbrivm=# SELECT * FROM public.geometry_columns WHERE f_table_name = 'adm_pc4_2010_basis';
 f_table_catalog | f_table_schema |    f_table_name    | f_geometry_column | coord_dimension | srid  |   type   
-----------------+----------------+--------------------+-------------------+-----------------+-------+----------
                 | nl             | adm_pc4_2010_basis | shape             |               2 | 28992 | GEOMETRY
(1 row)

sde_gdbrivm=# 

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

  • Status changed from Open to Feedback

Wouter Boasson wrote:

Indeed, with look in geometry columns only checked.

with 'look in geometry columns only' checked only the geometry columns with 'real' geometry types are considered. So what's the output without the checkmark?

#6 Updated by Wouter Boasson over 8 years ago

Without the 'only look in the geometry_columns table' option (UNchecked) the tables are correctly scanned in the 1.8 version. However, this takes more than 5 minutes in our case, as we have thousands of layers, many of them with millions of records. This not only takes too much time, but also imposes a pretty heavy database load.

I experimented a bit more, and will try to outline the differences more precisely.

1.7 and older behaviour:
  • with 'only look in the geometry_columns table' (OLIG) checked, the layer selection dialog immediately shows the layers, and does not try to detect the geometry type, any layer can be selected and loaded (regardless of the type was already detected)
  • with OLIG UNchecked it starts to detect the geometry type, and does not lock the layers that were not detected yet
1.8/1.9 dev behaviour
  • with OLIG checked, the layer selection dialog immediately shows the layers, and does not allow one to select a layer for which the geometry type is registered as GEOMETRY, it says 'Detecting...' but nothing happens (also checked on the database server side)
  • with OLIG UNchecked it starts to detect the geometry type, but locks the layers for which the geometry type is not yet detected; it looks as if the entire dialog is locked (wait cursor is shown), but you can indeed open a schema and select layers for which the geometry type is already detected, but the ones for which the type is not yet detected are greyed out (this is different from 1.7 and older)

Hope this helps to nail down the problem!

In practice the 1.7 and older behaviour was very acceptable to us. Both modes could be used, as in both modes the dialog opens quickly and displays the available layers, and they could be immediately selected (no locked/greyed out layers) and loaded. The OLIG option was very usefull to prevent 5 minutes of unneccessary heavy load on the database, and prevents clutter from working tables that aren't registered (a good thing in a multi-user enterprise database).

IMHO the most gentle behaviour would be the 1.7 way (do not grey out anything in both modes), with a slight modification: initially pick only the first record (LIMIT 1) and perform a better check at load (or select?) time. Alternatively, make it only detect the geometry type for unknown layers as soon as you select one and even skip the initial check (selectable with a check box?).

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

Wouter Boasson wrote:

Without the 'only look in the geometry_columns table' option (UNchecked) the tables are correctly scanned in the 1.8 version. However, this takes more than 5 minutes in our case, as we have thousands of layers, many of them with millions of records. This not only takes too much time, but also imposes a pretty heavy database load.

You can check 'use estimated metadata' to speed that up. d12c4a79 fixes the 'only look in the geometry_columns table' issue - geometry_columns columns that are GEOMETRY are also scanned now.

#8 Updated by Wouter Boasson over 8 years ago

Thank you, so if I install the latest nightly build (tomorrow), this should work again? The use estimated metadata is also very helpful.

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

Wouter Boasson wrote:

Thank you, so if I install the latest nightly build (tomorrow), this should work again? The use estimated metadata is also very helpful.

The patch was applied before 1am CEST - so the nightly builds already have it.

#10 Updated by Wouter Boasson over 8 years ago

Excellent! It is working again, the combination of 'estimated metadata' and 'geometry_columns only' is just what we need. Thanks! Will this patch be backported to the 1.8 branch?

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

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

Wouter Boasson wrote:

Excellent! It is working again, the combination of 'estimated metadata' and 'geometry_columns only' is just what we need. Thanks! Will this patch be backported to the 1.8 branch?

no, we don't plan to do any 1.8 point releases.

Also available in: Atom PDF