Bug report #19946

ogr based tools do not work anymore with PostGIS inputs (possibly also other rdbms datasources)

Added by Giovanni Manghi about 6 years ago. Updated about 6 years ago.

Status:Closed
Priority:High
Assignee:Nyall Dawson
Category:Processing/OGR
Affected QGIS version:3.3(master) Regression?:Yes
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:27768

Description

Subject says it all. On QGIS master (at least) the GDAL/OGR command with this type of datasources is not built anymore the correct way, resulting in a failure. I tested "buffer", "dissove" and "export to PostGIS", so it seems that possibly all tools are affected. Example:

GDAL command:
ogr2ogr /tmp/processing_db57843afc44487c9e08a83e234924aa/8b8daebc43394493b27bf9136acdf271/OUTPUT.shp "dbname='teste' host=localhost port=5432 user='teste' password='teste' sslmode=disable key='gid' srid=4326 type=MultiPolygon checkPrimaryKeyUnicity='1' table=\"lixo1\".\"teste\" (geom) sql=" -dialect sqlite -sql "SELECT ST_Union(geom) AS geom, region FROM lixo1.tm_world_borders_0.3 GROUP BY region" -f "ESRI Shapefile"
GDAL command output:
FAILURE:

Unable to open datasource `dbname='teste' host=localhost port=5432 user='teste' password='teste' sslmode=disable key='gid' srid=4326 type=MultiPolygon checkPrimaryKeyUnicity='1' table="lixo1"."teste" (geom) sql=' with the following drivers.

-> `PCIDSK'

-> `netCDF'

-> `JP2OpenJPEG'

-> `PDF'

-> `ESRI Shapefile'

-> `MapInfo File'

-> `UK .NTF'

-> `OGR_SDTS'

-> `S57'

-> `DGN'

-> `OGR_VRT'

-> `REC'

-> `Memory'

-> `BNA'

-> `CSV'

-> `NAS'

-> `GML'

-> `GPX'

-> `LIBKML'

-> `KML'

-> `GeoJSON'

-> `Interlis 1'

-> `Interlis 2'

-> `OGR_GMT'

-> `GPKG'

-> `SQLite'

-> `OGR_DODS'

-> `ODBC'

-> `WAsP'

-> `PGeo'

-> `MSSQLSpatial'

-> `OGR_OGDI'

-> `PostgreSQL'

-> `MySQL'

-> `OpenFileGDB'

-> `XPlane'

-> `DXF'

-> `CAD'

-> `Geoconcept'

-> `GeoRSS'

-> `GPSTrackMaker'

-> `VFK'

-> `PGDUMP'

-> `OSM'

-> `GPSBabel'

-> `SUA'

-> `OpenAir'

-> `OGR_PDS'

-> `WFS'

-> `SOSI'

-> `HTF'

-> `AeronavFAA'

-> `Geomedia'

-> `EDIGEO'

-> `GFT'

-> `SVG'

-> `CouchDB'

-> `Cloudant'

-> `Idrisi'

-> `ARCGEN'

-> `SEGUKOOA'

-> `SEGY'

-> `XLS'

-> `ODS'

-> `XLSX'

-> `ElasticSearch'

-> `Walk'

-> `Carto'

-> `AmigoCloud'

-> `SXF'

-> `Selafin'

-> `JML'

-> `PLSCENES'

-> `CSW'

-> `VDV'

-> `GMLAS'

-> `TIGER'

-> `AVCBin'

-> `AVCE00'

-> `HTTP'

Related issues

Related to QGIS Application - Bug report #19938: GDAL/OGR vector geoprocessing algorithms not working with... Closed 2018-09-25

Associated revisions

Revision 79774507
Added by Nyall Dawson about 6 years ago

[processing][ogr] Fix conversion of non-disk based layer sources
to GDAL commands

Fixes #19946

History

#1 Updated by Nyall Dawson about 6 years ago

  • Status changed from Open to In Progress
  • Assignee set to Nyall Dawson

#2 Updated by Nyall Dawson about 6 years ago

  • Status changed from In Progress to Feedback

Can you confirm that the error is the missing "PG:" part before "dbname='teste' host=localhost port=543..."? E.g. ' PG:"dbname='teste' host=localhost port=5432......" '

#3 Updated by Giovanni Manghi about 6 years ago

Nyall Dawson wrote:

Can you confirm that the error is the missing "PG:" part before "dbname='teste' host=localhost port=543..."? E.g. ' PG:"dbname='teste' host=localhost port=5432......" '

there seems to be more stuff to be wrong in the created command, I'm having a look at it.

#4 Updated by Jürgen Fischer about 6 years ago

  • Related to Bug report #19938: GDAL/OGR vector geoprocessing algorithms not working with GPKG, SQLite, FileGDB, etc inputs added

#5 Updated by Giovanni Manghi about 6 years ago

Nyall Dawson wrote:

Can you confirm that the error is the missing "PG:" part before "dbname='teste' host=localhost port=543..."? E.g. ' PG:"dbname='teste' host=localhost port=5432......" '

so there are a number of parameters in the call created by QGIS that are not supposed to be there, not at least the way they were implemented in QGIS3. I can't find any reference of the following in ogr2ogr docs as also in the ogr/postgres page:

sql=
(geom)
sslmode=
key=
srid=
type=
checkPrimaryKeyUnicity=
table=

A call that works here would be (referring to the "dissolve" tool):

ogr2ogr OUTPUT.shp PG:"dbname='teste' host='localhost' port=5432 user='teste' password='teste'" "lixo1"."tm_world_borders" -dialect sqlite -sql "SELECT ST_Union(geom) AS geom, region FROM '"lixo1"."tm_world_borders"' GROUP BY region" -f "ESRI Shapefile"

note that for ogr based geoprocessing operations using SQL (with SQLITE dialect, as internal ogr SQL is more limited) the schema/table names in the FROM clause must be around single quotes, otherwise it won't work.

#6 Updated by Nyall Dawson about 6 years ago

  • % Done changed from 0 to 100
  • Status changed from Feedback to Closed

Also available in: Atom PDF