Opened 17 years ago
Closed 17 years ago
Last modified 14 years ago
#143 closed bug (fixed)
postgis view load error
|Reported by:||Owned by:||g_j_m|
|Priority:||major: does not work as expected||Milestone:|
|Must Fix for Release:||No||Platform:||OS X|
|Platform Version:||10.4.6||Awaiting user input:||no|
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.
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
mn_timber=# \d 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 |
"forties_pkey" PRIMARY KEY, btree (gid) "sidx_mn_pls_grid" gist (the_geom)
"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)
"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
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 |
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;
Change History (8)
comment:1 by , 17 years ago
|Status:||new → assigned|
comment:2 by , 17 years ago
the sql statement gives the following:
mn_timber=# select count(distinct gid) = count(gid) from public.all_timber_data;
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?
comment:3 by , 17 years ago
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.
comment:4 by , 17 years ago
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
comment:5 by , 17 years ago
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.
comment:6 by , 17 years ago
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...
comment:7 by , 17 years ago
|Status:||assigned → closed|
Ticket has been resolved in SVN r5518 mainly through improved communication of the problem to the user.
comment:8 by , 14 years ago
Milestone Version 0.8 deleted
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:
Kirk - can you try this SQL on your data and let us know what it returns?