postgis view load error

[qgib]

Author Name: kwythers-umn-edu - (kwythers-umn-edu -)
Original Redmine Issue: 143

Redmine category:build/install
Assignee: Gavin Macaulay -

---

I am trying to load a postgis view in a a new build of 0.8 qgis. The error complains about now column being usable as a unique key. The error then goes on to list the columns and give discriptions. However, there is a column in the view that is unique, and the error message declares that it is suitable. The column is 'gid' and is reported in the error message a suitable.

Here are the discriptions of the the database as well as the "mn_pls_grid" table and the view "all_timber_data", which is returning the error. Let me know if you need any other info. Thanks.

mn_timber=# \d
List of relations
Schema | Name | Type | Owner
--------+------------------------+----------+----------
public | all_timber_data | view | kwythers
public | county | table | kwythers
public | forties | table | kwythers
public | forties_gid_seq | sequence | kwythers
public | geometry_columns | table | kwythers
public | grid_cell | view | kwythers
public | mn_pls_grid | table | kwythers
public | rdir | table | kwythers
public | session | table | kwythers
public | session_session_id_seq | sequence | kwythers
public | spatial_ref_sys | table | kwythers
public | timber_type | table | kwythers
public | timber_volume | table | kwythers
public | timber_volume_seq | sequence | kwythers
(14 rows)

mn_timber=# \d mn_pls_grid
Table "public.mn_pls_grid"
Column | Type | Modifiers
------------+----------+-------------------------------------------------------
gid | integer | not null default nextval('forties_gid_seq'::regclass)
area | numeric |
perimeter | numeric |
pls_fort_ | bigint |
pls_fort_i | bigint |
county_id | smallint |
township | smallint |
rdir_id | smallint |
range | smallint |
section | smallint |
forty_id | smallint |
glot | smallint |
parc | smallint |
glotmatch | bigint |
the_geom | geometry |
Indexes:
"forties_pkey" PRIMARY KEY, btree (gid)
"sidx_mn_pls_grid" gist (the_geom)
Check constraints:
"enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
"enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL)
"enforce_srid_the_geom" CHECK (srid(the_geom) = -1)
Foreign-key constraints:
"mn_pls_grid_county_id_fkey" FOREIGN KEY (county_id) REFERENCES county(county_id)
"mn_pls_grid_forty_id_fkey" FOREIGN KEY (forty_id) REFERENCES forties(forty_id)
"mn_pls_grid_rdir_id_fkey" FOREIGN KEY (rdir_id) REFERENCES rdir(rdir_id)

mn_timber=# \d all_timber_data
View "public.all_timber_data"
Column | Type | Modifiers
----------------+-------------------+-----------
gid | integer |
the_geom | geometry |
area | numeric |
perimeter | numeric |
township | smallint |
range | smallint |
rdir_shortname | character varying |
rdir_name | character varying |
section | smallint |
forty_name | character varying |
county_name | character varying |
year | character varying |
cords | integer |
mbm | integer |
poles | integer |
posts | integer |
tt_shortname | character varying |
tt_name | character varying |
View definition:
SELECT mn_pls_grid.gid, mn_pls_grid.the_geom, mn_pls_grid.area, mn_pls_grid.perimeter, mn_pls_grid.township, mn_pls_grid.range, rdir.rdir_shortname, rdir.rdir_name, mn_pls_grid.section, forties.forty_name, county.county_name, timber_volume."year", timber_volume.cords, timber_volume.mbm, timber_volume.poles, timber_volume.posts, timber_type.tt_shortname, timber_type.tt_name
FROM mn_pls_grid, rdir, county, forties, timber_volume, timber_type
WHERE mn_pls_grid.rdir_id = rdir.rdir_id AND mn_pls_grid.county_id = county.county_id AND mn_pls_grid.forty_id = forties.forty_id AND mn_pls_grid.gid = timber_volume.grid_id AND timber_volume.tt_id = timber_type.tt_id;

[qgib]

Author Name: Gavin Macaulay - (Gavin Macaulay -)

---

Committed some changes to svn (7eb8a7c (SVN r5515)). I'm not sure if this will fix the problem, but it should make the cause of the problem clearer.

Kirk - if you can try this version out and let us know what qgis reports, that'd help.

After qgis has found columns in the view that it thinks are suitable for use as a key, it checks the column to see if it actually contains unique data. It appears that in your case the column that is flagged as suitable ('gid' derives from 'public.mn_pls_grid.gid' and is suitable) turns out to have non-unique data in it (or at least that's the only way, from my reading of the code, that it can end up rejecting a 'suitable' column). I'm not sure how this could happen given the primary key constraint on it.

The uniqueness is testing using the SQL:

select count(distinct gid) = count(gid) from public.all_timber_data;

Kirk - can you try this SQL on your data and let us know what it returns?

---

• status_id was changed from Open to In Progress

[qgib]

Author Name: anonymous - (anonymous -)

---

the sql statement gives the following:

mn_timber=# select count(distinct gid) = count(gid) from public.all_timber_data;
?column?

f
(1 row)

If the gid columns are susposed to be unique, that is the problem. the gid columns contain multiple non-unique values. If this is the case, would creating a true oid column (with unique value for each row) solve the issue?

[qgib]

Author Name: Gavin Macaulay - (Gavin Macaulay -)

---

That 'f' result is why qgis is not using the gid column as a key - as qgis will of stated in it's error dialog box, it requires a unique key into the table.

In reply to your question, yes, if the view includes a column from a table that contains unique data of type int4, qgis should load and display the table.

[qgib]

Author Name: anonymous - (anonymous -)

---

Not to beat this to death but.... The problem is that the view creates an (for lack of better term) an inner join. The gid column is unique in the table it comes from, but when the view is created, there are several records displayed for each gid (hence gid is no longer unique).

Where is the qgis "error dialog box"? In the future I will look there rather than bothering you. PS sorry I missed you last night (errr... early this moring).

Thanks for the help

[qgib]

Author Name: Gavin Macaulay - (Gavin Macaulay -)

---

Kirk,

By 'error dialog box' I mean the dialog box that says that the view couldn't be loaded, and also lists the columns in the view and states why each column wasn't suitable for use as a key. The changes that I put into SVN 7eb8a7c (SVN r5515) involved adding some more explanation for your case, where a column appears suitable on first look, but actually doesn't contain unique data. This extra stuff is at the bottom of the text in the dialog box.

Kirk - If you can confirm that qgis 7eb8a7c (SVN r5515) does now state a sensible reason why it can't load your view, I can then close this ticket.

[qgib]

Author Name: anonymous - (anonymous -)

---

Gavin,

I just rebuilt the latest SVN. I tried to load the postgis layer and see the new message you put in there, "Note: _initially appeared suitable but does not contain unique data, so is not suitable." I think you can close...

[qgib]

Author Name: Gavin Macaulay - (Gavin Macaulay -)

---

Ticket has been resolved in SVN 0b12aca (SVN r5519) mainly through improved communication of the problem to the user.

---

• status_id was changed from In Progress to Closed
• resolution was changed from to fixed

[qgib]

Author Name: Anónimo (Anónimo)

---

Milestone Version 0.8 deleted