[Qgis-developer] DB Manager and other PostGIS issues: notes from a course

[Paolo Cavallini]

Hi all,
just finished my latest course on PostGIS, we spotted several issues in
QGIS 2.18:

* DB Manager
  * create table: field types are limited; in particular, timestamptz is
missing
  * create table: SRID has to be entered by hand; the projection widget
should be added
  * edit table -> Table properties -> Field properties: the field
`Length` appears wrong; for geom it shows `geomtype,srid`, for
varchar(255) and others, adding a length will cause an error:
ALTER TABLE "public"."buffer_union" ALTER "round" TYPE varchar(255) (24)
length has to be entered by hand changing varchar(255) into e.g. varchar(20)
  * we got frequent errors similar to [0]

* Browser
  * importing a shapefile of points results in a (generally undesirable)
MULTIPOINT, without a possibility of choice for the user
  * a table created with SRID=0
CREATE TABLE pt
 (id INTEGER, name text, geom GEOMETRY(Point,0,2));
INSERT INTO pt (id, name, geom)
 VALUES (1, 'Origin', ST_GeomFromText('POINT(0 0)', 0));
results in two entries: one with mouseover= geom as Point in 0 and one
geom as Point in -2147483648

* Processing
  * there are two Import into Processing (one QGIS and one OGR)
  * for the first (QGIS), batch processing fails with `Missing parameter
value: Table to import to (leave blank to use layer name (row 1)`
  * for the second (OGR), it also fails because it tries to write in the
spatial_ref_sys (CRS should default to source CRS) [1]
  * the gdal output is not interpreted correctly (html and encoding) [1]

Thanks in advance for any comment. I'll open the tickets where relevant.
All the best.

[0]
TypeError: pyqtSignal must be bound to a QObject, not 'PGVectorTable'
Traceback (most recent call last):
  File
"C:/PROGRA~1/QGIS2~1.18/apps/qgis/./python/plugins\db_manager\db_manager.py",
line 116, in tabChanged
    self.refreshTabs()
  File
"C:/PROGRA~1/QGIS2~1.18/apps/qgis/./python/plugins\db_manager\db_manager.py",
line 142, in refreshTabs
    self.preview.loadPreview(item)
  File
"C:/PROGRA~1/QGIS2~1.18/apps/qgis/./python/plugins\db_manager\layer_preview.py",
line 62, in loadPreview
    self._clear()
  File
"C:/PROGRA~1/QGIS2~1.18/apps/qgis/./python/plugins\db_manager\layer_preview.py",
line 83, in _clear
    self.item.aboutToChange.disconnect(self.setDirty)
TypeError: pyqtSignal must be bound to a QObject, not 'PGVectorTable'

[1]
Processing algorithm 1/3...
<b>Algorithm Import Vector into PostGIS database (available connections)
starting...</b>
GDAL command:
<code>ogr2ogr -progress --config PG_USE_COPY YES -f PostgreSQL PG:"
dbname='corso_novembre' host=localhost port=5432 user='utente'
password='utente' sslmode=allow active_schema=public " -lco DIM=2
/home/paolo/Scrivania/postgis/Dati/corsi_acqua.shp corsi_acqua
-overwrite -lco GEOMETRY_NAME=geom -lco FID=id -nln public.corsi_acqua
-spat 1602877.73437 4818080.89111 1732409.01563 4879071.07031 -nlt
PROMOTE_TO_MULTI<code>
GDAL command output:
<code><span style="color:darkgray">Warning 6: Progress turned off as
fast feature count is not available. </span><code>
<code><span style="color:darkgray">ERROR 1: ERRORE: permesso negato per
la relazione spatial_ref_sys </span><code>
<code><span style="color:darkgray"> </span><code>
<code><span style="color:darkgray">ERROR 1: ERRORE: la transazione
corrente è interrotta, i comandi saranno ignorati fino alla fine del
blocco della transazione </span><code>
<code><span style="color:darkgray"> </span><code>
<code><span style="color:darkgray">ERROR 1: CREATE TABLE
"public"."corsi_acqua" ( "id" SERIAL, PRIMARY KEY ("id"), "nome_fiume"
VARCHAR(20), "count" VARCHAR(11), "geom"
geometry(MULTILINESTRING,900914) ) </span><code>
<code><span style="color:darkgray">ERRORE: la transazione corrente è
interrotta, i comandi saranno ignorati fino alla fine del blocco della
transazione </span><code>
<code><span style="color:darkgray"> </span><code>
<code><span style="color:darkgray">ERROR 1: Unable to write feature 0
from layer corsi_acqua. </span><code>
<code><span style="color:darkgray">ERROR 1: Terminating translation
prematurely after failed </span><code>
<code><span style="color:darkgray">translation of layer corsi_acqua (use
-skipfailures to skip errors) </span><code>
Converting outputs
Algorithm Import Vector into PostGIS database (available connections)
correctly executed...
-- 
Paolo Cavallini - www.faunalia.eu
QGIS & PostGIS courses: http://www.faunalia.eu/training.html
https://www.google.com/trends/explore?date=all&geo=IT&q=qgis,arcgis