9

I'm just a beginner in GIS, so I'll be glad to see any comment here.

I have tables in PostgreSQL which I've connected to QGIS. There are columns in tables with datatype array[]. But it's not easy for users to insert data into column using such view as {a,d,c}. Is there an easy way to edit the data?

I have just some idea to put data in QGIS like 'a,b,c' and make a trigger on INSERT/UPDATE BEFORE which will put data into Postgres table in right look. But it also seems not the best way.

1

The only way that I'm aware of is kind of a 'per-use kludge'.

For example I have data that includes an array of property numbers (attribute name propnum_array, dtype varchar[]), and I sometimes want to identify any data where the array is empty.

Testing for propnum_array = {} or propnum_array is null or propnum[0]='' (or any other variations on that type of theme) makes QGIS gag, however I can do regexp_match( propnum_array,'\\d') or propnum_array ilike '{""}' or propnum_array = '{""}' and partition the data into stuff that has non-empty propnum_array and stuff that hasn't.

What this means is that if the arrays have structure (e.g., specific values at specific elements in the array), you can scan for those: if you want all values where the second element in a 3-element array is 'Y', then you'll want something like regexp_match( propnum_array,'[,]*\'Y\'\[,]*') (pretty sure you have to escape the commas and single-quotes internal to the match string, but that a use-case for someone else to worry about).

The fact that propnum_array ilike '{""}' works, seems to indicate that QGIS treats arrays as if they're text. Alternatively, it might cast them to text on-the-fly, if required - but then it would not make sense for array types to be undocumented (AFAIK). I haven't been able to find any docs on array handling, and there are questions on the osgeo-qgis mailing list as recently as Jan 2015.

I guess the major hint is that there are no array functions in the expressions editor, although I understand that the 'Data Driven Input Mask' plugin has had array handling since v1.0.0 (Feb 2015).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.