postgis view load error

[kwythers@…]

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;

[g_j_m]

Committed some changes to svn (r5514). 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?

[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?

[g_j_m]

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.

[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

[g_j_m]

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 r5514 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 r5514 does now state a sensible reason why it can't load your view, I can then close this ticket.

[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...

[g_j_m]

Ticket has been resolved in SVN r5518 mainly through improved communication of the problem to the user.

[(none)]

Milestone Version 0.8 deleted