[Qgis-user] Conditional Value Mapping
Víctor Manuel Herreros Villanueva
vherreros at ucm.es
Thu Apr 23 05:00:46 PDT 2015
Dear André,
I asked for the same problem months ago without obtaining any answer. As
far as I know, there is no QGIS feature implemented that allows you to
achieve what you are looking for.
So I decided to dive into my underlying PostgreSQL databse structure. The
solution I chose was the following: Two reference tables with data
description and one table with data for the user affected by one constraint.
*Reference table for attribute 1 (ATTR1_TABLE)*
-- ----------------
ID Textf
-- ----------------
01 Agriculture (01)
02 Forest (02)
-- ----------------
*Reference table for attribute 2 (ATTR2_TABLE)*
----- --------------
ID Textf
----- --------------
01.01 Corn (01.01)
01.02 Wheat (01.02)
02.01 Pines (02.01)
02.02 Olives (02.02)
----- --------------
*Structure of the table containing the data to be shown to the final user
(DATA_TABLE)*
attrib1_cod: character varying(9)
attrib1_desc: character varying(100)
attrib2_cod: character varying(9)
attrib2_desc: character varying(100)
Then I stablished the appropriate *database constraint*:
ALTER TABLE DATA_TABLE
ADD CONSTRAINT data_table_cc_attr2 CHECK (attrib2_cod IS NULL OR
attrib2_cod::text = ''::text OR "substring"(attrib2_cod::text, '^..'::text)
= attrib1_cod::text);
With this constraint, you ensure that the data to be introduced in
ATTR2_TABLE ID field must contain in the string beginning one of the values
present in ATTRIB1_TABLE ID field. So, if your user has selected
attrib1_cod/attrib1_desc 01/Agriculture (01) for DATA_TABLE, then, when
fullfilling attrib2_cod field he only can choose the values 01.01 or 01.02,
because both of them start with the string '01'.
Well, he can really choose any value for attrib2_cod, but only the
appropriate ones won't throw insertion errors.
The problem with this workaround is that the user can choose any value for
attr2_cod field. But, if you decide to show him just the descriptive text
fields, he can easily see which ones are proper values (because in the tex
field all records end with the numeric code).
I've personally decided to use just the numeric values for my users. So,
under QGIS, and through a widget (value map), I show my users the fields
attrib1_cod and attrib2_cod, but with the values taken from the text fields:
*Value map widget for attrib1_cod*
----- ----------------
Value Description
----- ----------------
01 Agriculture (01)
02 Forest (02)
-- ----------------
When the user selects from the drop down list the description 'Agriculture
(01)', he is really selecting the value '01' for attrib1_cod. The same
applies to attrib2_cod:
*Value map widget for attrib2_cod*
----- --------------
Value Description
----- --------------
01.01 Corn (01.01)
01.02 Wheat (01.02)
02.01 Pines (02.01)
02.02 Olives (02.02)
----- --------------
And finally, in order not to ask the user to fullfill the field
attrib*_desc (cause he has previously fullfilled attrib*_cod but from
descriptive values), I have two more triggers and trigger functions whose
mission is to fill in the descriptive fields provided the code fields have
been modified:
*Trigger for attrib1_desc on table DATA_TABLE*
CREATE TRIGGER update_attrib1_desc
BEFORE INSERT OR UPDATE
ON DATA_TABLE
FOR EACH ROW
EXECUTE PROCEDURE update_attrib1_desc();
*Trigger function for called by the previous trigger*
CREATE OR REPLACE FUNCTION update_attrib1_desc()
RETURNS trigger AS
$BODY$
DECLARE
BEGIN
IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE' ) THEN
NEW.attrib1_desc := (SELECT Textf FROM ATTR1_TABLE WHERE ID =
NEW.attrib1_cod);
RETURN NEW;
ELSE
RETURN NULL;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
So each time an attrib1_cod field is entered or updated, the trigger seeks
for the appropriate attrib1_desc field and writes it into attrib1_desc
field.
And the same for attib2_desc:
*Trigger for attrib2_desc on table DATA_TABLE*
CREATE TRIGGER update_attrib2_desc
BEFORE INSERT OR UPDATE
ON DATA_TABLE
FOR EACH ROW
EXECUTE PROCEDURE update_attrib2_desc();
*Trigger function for called by the previous trigger*
CREATE OR REPLACE FUNCTION update_attrib2_desc()
RETURNS trigger AS
$BODY$
DECLARE
BEGIN
IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE' ) THEN
NEW.attrib2_desc := (SELECT Textf FROM ATTR2_TABLE WHERE ID =
NEW.attrib2_cod);
RETURN NEW;
ELSE
RETURN NULL;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
A little bit confusing, but I haven't found any other way to solve the
problem...
Best regards.
2015-04-23 12:19 GMT+02:00 ALT SHN <i.geografica at alt-shn.org>:
> Hi,
>
> I am looking for a way to implement conditional value mapping in QGIS.
> This means that the values available in attribute number 2 depend on the
> value provided in attribute 1.
>
> Example:
>
> If the user enters "agriculture" in attribute 1, for attribute 2 only
> "corn" or "wheat" will be available.
>
> Instead, if the user enters "forest" in attribute 1, for attribute 2 only
> "pines" or "olives" will be available.
>
> And so on...
>
> Apparently this is not doable just using the QGIS widget expression
> builder, but I am not sure. Here's a screencast to help visualize. The
> problem is that if for "class" I pick other than "Agri_Field", for the
> "color_aggri" attribute the same possible values appear.
>
> [image: enter image description here]
>
> Thanks in advance for any hints!
>
>
> Regards,
>
>
> Andre
>
>
>
>
> --
> ---------------------------------------------------------------
>
>
> *Sociedade de História Natural*
> Departamento de Informação Geográfica
> Polígono Industrial do Alto do Amial
> Pav.H02 e H06
>
> i.geografica at alt-shn.org <laboratorio at alt-shn.org>
> www.shn.pt
> www.alt-shn.blogspot.com
> Facebook <https://www.facebook.com/SociedadeDeHistoriaNatural?ref=hl>
>
> TLM: 964138188
>
>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-user
>
--
<http://www.ucm.es/> *Universidad Complutense de Madrid*
<http://www.ucm.es/> Dr. *Víctor Manuel Herreros Villanueva*
Jefe de Proyecto
*Gestión de EspaciosData Warehouse*
Jardín Botánico
Av. Complutense s/n
28040 Madrid
*Tfn. (+34) 91 394 7275 Fax. (+34) 91 394 4773* vherreros at ucm.es
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20150423/aeafb60a/attachment.html>
More information about the Qgis-user
mailing list