[postgis-users] OIDS, PostGIS and Quantum GIS

nicklas.aven at jordogskog.no nicklas.aven at jordogskog.no
Sat May 9 03:57:23 PDT 2009 

Hallo Juan Pedro
 
I don't know enough about how OIDS are handled to discuss that, but I don't really understand why you can't use a column with unique values.
As suggested earlier, if you use
alter table yourTableName add column aColumnName serial UNIQUE ;

then you will get unique values created for each row and new default values in the future. 
Then you don't redefine your PK. Then what is the problem?
This new column will just be there to help QGis to keep things in order. I guess this is what is happening in the background in every application working with tables without primary keys, that they have som hidden unique index for the rows.
I'm I missing something?
 
Cheers
Nicklas

2009-05-09 Juan Pedro P�rez Alc�ntara wrote:

Hello all,
>
>thank you for all your responses, it is my first post to the list and I
>thank you all for your interest in my question.
>
>Don't get me wrong for what I'm going to say, for, as said, I appreciate
>all the ideas you say to me. Although I come from a GIS background and
>I'm a geographer myself, the center of all my processes and projects is
>and always will be the geographic relational model in PostGIS, so I
>fully understand the importance of PK and the technicalities of building
>and managing a relational model (in fact, this is a lecture I teach at
>the university here in Spain). This issue of working with PK in QGIS has
>been around for me for quite a long time, but, given that I've been
>always dealed with it with no problems (int4 PK) I haven't had the need
>to find a better solution. That's because I've been always able to find
>a int4 PK suitable for my tables, just being because I designed them or
>because I receive external data from, as you say,
>"shapefiles-driven-minds" :) of fellow geographers here at the
>university (no critics at all, I'm unable to perform the complex
>geophysical and geosocial analysis they perform either) and it were easy
>to adapt in the model. But now I'm receiving data from the government
>that are well designed, with a "model-mind" approach, so this is not
>always possible, due to the use of non-int4 PK or multi-field PK.
>
>I have experimented a little more with the OIDS approach and it seems
>that they don't interfere with the actual PK fields of the tables. It
>seems that although you created the table WITH OIDS, PK conflicts are
>still successfully detected, so it seems that Postgre don't think of the
>OID field as part of the PK fields and such.
>
>So, what do you think? Are my findings OK, or is there a, for me,
>unforeseen reason why I should not use OIDS?
>
>Again, thank you all.
>
>Juan Pedro Pérez Alcántara
>
>
>
>On Fri, 2009-05-08 at 14:09 -0700, pcreso at pcreso.com wrote:
>> Hi,
>> 
>> This is often an issue for people fromm a GIS background, where the issue of PK's is hidden or implicit, or ignored completely, as in shapefiles.
>> 
>> You only need to lose a record from the dbf or shp file, or change the order of one of them, to find out why PK's are a good idea. Such things are safer explicit than implicit. They are a key reason for the success of the relational model, and relational databases.
>> 
>> For me, the QGIS issue is not that it requires a PK, but that it can only use an int PK, wheras the DB supports PK's of any datatype, as well as composite keys using more than one column. (Also, there is at least one bug in QGIS when it comes to identifying a suitable column in a view, as in some unusual cases it fails to correctly identify a suitable column, but that is a separate issue, and only in particular circumstances)
>> 
>> Generally this is not really a big issue, as QGIS can also use a unique index, and the DB supports multiple unique indexes on a table. So to work with QGIS, you can still use your non-int PK, or not use one at all, just add a new unique index on a suitable integer column for QGIS.
>> 
>> A simple way to do this is:
>> 
>> "alter table add column qkey serial;"
>> 
>> this is an easy way of creating an integer column automatically populated as a sequence, so it is filled with unique values as it is created.
>> 
>> To be used by QGIS, this column must have a unique index created on it once it is there. 
>> 
>> "create unique index _qkey_idx on (qkey);"
>> 
>> You can, of course, use your own names for the table, column & index....
>> 
>> 
>> HTH,
>> 
>> Brent Wood
>> 
>> 
>> 
>> --- On Sat, 5/9/09, Ben Madinwrote:
>> 
>> > From: Ben Madin
>> > Subject: Re: [postgis-users] OIDS, PostGIS and Quantum GIS
>> > To: "PostGIS Users Discussion"
>> > Date: Saturday, May 9, 2009, 12:23 AM
>> > Juan,
>> > 
>> > Agreed that this can be annoying (especially in light of
>> > some other GIS packages which don't seem to care at all, or
>> > for instance shapefiles.
>> > 
>> > I have had the same frustration when constructing
>> > geometries in a query or for a view, or any type of
>> > aggregate query really. This is also a problem for querymaps
>> > in MapServer.
>> > 
>> > The alternative I settled with was creating a false key -
>> > for instance if the view was a makeline from points, making
>> > an id that was the gid of the first * 100000 + the gid of
>> > the second.
>> > 
>> > That may help. It may not - but there are better minds than
>> > mine hovering nearby, so I look forward to their
>> > suggestions.
>> > 
>> > cheers
>> > 
>> > Ben
>> > 
>> > 
>> > On 08/05/2009, at 7:36 PM, Andreas Neumann wrote:
>> > 
>> > > hm - what's wrong with having a primary key in every
>> > table? Good DB-Design
>> > > requires primary keys. Other tools will refuse to work
>> > with your data if
>> > > you don't use primary keys as well. As an example,
>> > pgadmin3 requires a
>> > > primary key if you want to edit the data in the grid
>> > view.
>> > > 
>> > > If you use the datatype "serial" or a sequence it is
>> > not complicated at
>> > > all to use primary keys.
>> > > 
>> > > Andreas
>> > > 
>> > > On Fri, May 8, 2009 1:18 pm, Juan Pedro Pérez
>> > Alcántara wrote:
>> > >> Hello,
>> > >> 
>> > >> perhaps this is a silly one, but I'm having a hard
>> > time dealing with
>> > >> primary keys restrictions in Quantum GIS. Not
>> > always is possible or
>> > >> desirable to put a, sometimes, artificial int4 PK
>> > in some tables only to
>> > >> be able to load them in QGIS. Those restrictions
>> > are very frustrating.
>> > >> 
>> > >> I have been messing around for a solution, and I
>> > have experimented with
>> > >> OIDS. This seems to be enough for QGIS, but I fear
>> > the behavior of OIDS.
>> > >> I don't like the idea of non-controlled PK in my
>> > tables. So my question
>> > >> is simple: does creating a table WITH OIDS means
>> > that the OIDS will be
>> > >> part of the PK of the table like you it or not, or
>> > you have to specify
>> > >> that the OIDS are part of the PK in the ADD
>> > CONSTRAINT statement?
>> > >> 
>> > >> My hope is that PostgreSQL uses internally OIDS
>> > without interfering with
>> > >> the true PK of the table, while QGIS is happy by
>> > having them around.
>> > >> OIDS will not play any role in my model other than
>> > that.
>> > >> 
>> > >> Greetings,
>> > >> 
>> > >> Juan Pedro Pérez Alcántara
>> > >> 
>> > >> 
>> > >> _______________________________________________
>> > >> postgis-users mailing list
>> > >> postgis-users at postgis.refractions.net
>> > >> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> > >> 
>> > > 
>> > > 
>> > > --Andreas Neumann
>> > > http://www.carto.net/neumann/
>> > > http://www.svgopen.org/
>> > > 
>> > > _______________________________________________
>> > > postgis-users mailing list
>> > > postgis-users at postgis.refractions.net
>> > > http://postgis.refractions.net/mailman/listinfo/postgis-users
>> > 
>> > --
>> > Ben Madin
>> > REMOTE INFORMATION
>> > 
>> > t : +61 8 9192 5455
>> > f : +61 8 9192 5535
>> > m : 0448 887 220
>> > Broome WA 6725
>> > 
>> > ben at remoteinformation.com.au
>> > 
>> > 
>> > 
>> > 
>> > 
>> > Out here, it pays to know...
>> > 
>> > 
>> > _______________________________________________
>> > postgis-users mailing list
>> > postgis-users at postgis.refractions.net
>> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>> > 
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> 
>
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090509/6072abeb/attachment.html>

More information about the postgis-users mailing list