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

[qgib]

Author Name: Giovanni Manghi (@gioman) Original Redmine Issue: 19946 Affected QGIS version: 3.3(master) Redmine category:processing/ogr Assignee: Nyall Dawson

---

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 issue(s): #27760 (relates) Redmine related issue(s): 19938

---

[qgib]

Author Name: Nyall Dawson (@nyalldawson)

---

• status_id was changed from Open to In Progress
• assigned_to_id was configured as Nyall Dawson

[qgib]

Author Name: Nyall Dawson (@nyalldawson)

---

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......" '

---

• status_id was changed from In Progress to Feedback

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

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.

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

• relates was configured as #27760

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

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.

[qgib]

Author Name: Nyall Dawson (@nyalldawson)

---

Applied in changeset 7977450796903babff4791301e64ecf52f52b039.

---

• done_ratio was changed from 0 to 100
• status_id was changed from Feedback to Closed