Opened 18 years ago
Last modified 13 years ago
#62 new patch
Qgis only supports postgres keys of type int
Reported by: | g_j_m | Owned by: | jef |
---|---|---|---|
Priority: | major: does not work as expected | Milestone: | Version 1.7.0 |
Component: | Data Provider | Version: | Trunk |
Keywords: | Cc: | mloskot, springmeyer, yjacolin, aren@… | |
Must Fix for Release: | No | Platform: | All |
Platform Version: | Awaiting user input: | no |
Description
Transfer of item 1337919 from sourceforge.
Qgis should support postgres table keys of types other than int. There are workarounds for most situations but the better solution would be to directly support other key types.
Attachments (1)
Change History (21)
comment:1 by , 18 years ago
comment:2 by , 17 years ago
Awaiting user input: | unset |
---|---|
Must Fix for Release: | → No |
comment:3 by , 16 years ago
Type: | enhancement → defect |
---|
comment:4 by , 16 years ago
Owner: | changed from | to
---|
comment:5 by , 16 years ago
Milestone: | Version 1.0.0 → Version 2.0.0 |
---|
comment:6 by , 16 years ago
Type: | defect → enhancement |
---|
comment:7 by , 16 years ago
An email from the developers mailing list (Sun, 31 Aug 2008 02:42:42 +1200):
Currently QGIS requires a int4 column with unique IDs for accessing data in a PostGIS database. I routinely work with OpenStreetMap data which uses int8 for its IDs. Currently the OSM IDs still fit into an int4, but this will not work forever. At the moment its a bit of a hassle that I always have to keep this limitation in mind.
Is the limitation on int4 columns something hardcoded deep in the code or is it something that could be changed reasonably easy? I understand the need for a unique ID column, but it would be nice if the data type was more flexible. I think at least int2, int4, and int8 should be supported.
Jochen Topf
comment:8 by , 16 years ago
comment:9 by , 16 years ago
Cc: | added |
---|
comment:10 by , 16 years ago
Cc: | added |
---|
+1 to this being made more flexible. uDig/geotools provides a much more noticeably more flexible approach to reading in postgis tablea (even without a unique id field), so perhaps this could might be a good reference:
http://svn.geotools.org/trunk/modules/plugin/postgis/src/main/java/org/geotools/data/postgis/ autogeneration of fids: http://svn.geotools.org/trunk/modules/plugin/postgis/src/main/java/org/geotools/data/postgis/fidmapper/
comment:11 by , 15 years ago
Cc: | added |
---|
comment:12 by , 15 years ago
since r10474 the postgres providers resorts to using ctid as feature id, if no other usable primary key is found.
As a ctid currently consists of a 32bit block number and and 16bit offset, that option is limited to tables that have block numbers below 0x10000.
That problem would also disappear by applying the patch above to implement 64bit feature ids.
comment:13 by , 15 years ago
Type: | enhancement → patch |
---|
comment:14 by , 15 years ago
Milestone: | Version 2.0.0 → Version 1.3.0 |
---|
comment:16 by , 14 years ago
Milestone: | Version 1.5.0 → Version 1.6.0 |
---|
follow-up: 18 comment:17 by , 14 years ago
It would also be nice if PostGIS sequences could be used for layer keys.
I frequently wish to view the result of applying PostGIS aggregate functions using QGIS but this can't be done using relational views as the results of such queries lack anything that could be used as a primary key; I therefore end up littering my db with temporary tables created using 'SELECT...INTO...' so as to view the results of aggregate queries in QGIS. Allowing a self-incrementing sequence col within a view to be used as a layer key would be a (slightly ugly) way around this.
follow-up: 19 comment:18 by , 14 years ago
Replying to willfurnass:
It would also be nice if PostGIS sequences could be used for layer keys.
They already can. If the primary key is for instance SERIAL
, the default value is a nextval()
call, which QGIS evaluates on INSERT
.
I frequently wish to view the result of applying PostGIS aggregate functions using QGIS but this can't be done using relational views as the results of such queries lack anything that could be used as a primary key; I therefore end up littering my db with temporary tables created using 'SELECT...INTO...' so as to view the results of aggregate queries in QGIS. Allowing a self-incrementing sequence col within a view to be used as a layer key would be a (slightly ugly) way around this.
The feature ids are necessary to identify which feature is to be deleted or updated. The table/view is not only queried once, but eg. for each render operation with the current extent in the where clause. And in every query each feature is supposed to have the same id as in the previous queries. I'm not sure how that should work with sequences in views.
comment:19 by , 14 years ago
The feature ids are necessary to identify which feature is to be deleted or updated. The table/view is not only queried once, but eg. for each render operation with the current extent in the where clause. And in every query each feature is supposed to have the same id as in the previous queries. I'm not sure how that should work with sequences in views.
Interestingly both of the following views can be loaded by uDig.
CREATE VIEW myview AS ( SELECT zone_ref, ST_ConvexHull(ST_Collect(wkb_geometry)) FROM table_of_points GROUP BY zone_ref);
CREATE VIEW myview_with_id AS ( SELECT nextval('some_seq'), zone_ref, ST_ConvexHull(ST_Collect(wkb_geometry)) FROM table_of_points GROUP BY zone_ref);
Does anyone know how uDig internally handles the rendering and manipulation of tables/views lacking a UNIQUE constraint? Would it be possible to allow for the viewing of such relations in some sort of read-only mode? Apologies if I'm missing something obvious here.
comment:20 by , 13 years ago
Cc: | added |
---|
A Debian was filed about this issue:
http://bugs.debian.org/355944