[Qgis-user] Postgres arrays in QGIS and form widget

[Bernhard Ströbl]

Hi Olivier,

IMHO arrays are a not self-explaining concept for normal (i.e. 
non-programmer) users. I could however imagine a table widget (talking 
Qt not QGIS) as input where the user can add/remove entries. This would 
be only doable with Python, I think.

Am 11.01.2015 15:17, schrieb Olivier Dalang:
> Hi !
>
> Postgres' arrays are a great feature. They allow store several values in
> one field :
>
> CREATE TABLE lots(
>      id serial,
>      owners text[]
> );
> INSERT INTO lots(owners) VALUES( ARRAY['john','mike','jenny'] );
>
> But is there a way to edit this cleanly in QGIS with the forms ?
> Natively, the form widget will be a plain text input, and the value will
> display like this
> {"john","mike","jenny"}
> This is more or less usable for text fields, but not really clean, since
> the user needs to escape some characters, and it's not very readable.
> But then it's impossible to use with other widgets (checkbox, range, etc.)
>
> I'd love to have a dynamic input where the user could add and remove
> fields, and where QGIS widget types could be used.
>
> Actually, I'd even need a form where I'd have the same number of items
> for several arrays, and where adding/removing an item would work on
> several field at once, to work on a table like this :
> CREATE TABLE lots(
>      id serial,
>      first_names text[],
>      last_names text[],
>      age int[]
> );

In this case I would change the data model into a n-2-m relationship 
because one person could own many lots (I understand you are only giving 
an example but IMHO the the "traditional" way of normalizing nmakes this 
much more understandable):

CREATE TABLE lots(
	id serial NOT NULL,
	CONSTRAINT "lots_pkey" PRIMARY KEY (id)
);

CREATE TABLE lot_owner(
	id serial NOT NULL,
	first_name text,
	last_name text,
	age INTEGER,
	CONSTRAINT "lot_owner_pkey" PRIMARY KEY (id)	
);

CREATE TABLE lot_has_owner(
	lot_id INTEGER NOT NULL,
	lot_owner_id INTEGER NOT NULL,
	CONSTRAINT "lot_has_owner_pkey" PRIMARY KEY (lot_id, 	lot_owner_id),
	CONSTRAINT "fk_lot" FOREIGN KEY (lot_id)
       	REFERENCES lots (id) MATCH SIMPLE
       	ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT "fk_lot_owner" FOREIGN KEY (lot_owner_id)
       	REFERENCES lot_owner (id) MATCH SIMPLE
       	ON UPDATE CASCADE ON DELETE RESTRICT
);

Bernhard

>
>
> Is there a way to do it ?
> Maybe with python ?
> Has anyone of you already done this and would agree to share ?
> Any pointer in the right direction would be very welcome !
>
> Thanks a lot in advance,
>
>
> Olivier
>
>
>


__________ Information from ESET Mail Security, version of virus signature database 10997 (20150111) __________

The message was checked by ESET Mail Security.
http://www.eset.com