[postgis-users] OIDS, PostGIS and Quantum GIS
pcreso at pcreso.com
pcreso at pcreso.com
Sat May 9 13:12:29 PDT 2009
Hi guys,
Andreas is correct in that Postgres OIDs do not form a reliable primary key for a table, and has explained why.
However, in terms of providing an ad hoc column that QGIS can use to uniquely identify a record, it is very unlikely that oids will not work.
In theory it is possible that a given table may have two records with the same oid value, but the odds against it are enormous, just not quite impossible.
I think it will be useful in this discussion to distinguish between a QGIS unique identifier & a database primary key. They do not have to be the same thing, and often are not, but when appropriate & convenient, a PK can also be a QGIS unique identifier.
Brent Wood
--- On Sat, 5/9/09, Andreas Neumann <a.neumann at carto.net> wrote:
> From: Andreas Neumann <a.neumann at carto.net>
> Subject: Re: [postgis-users] OIDS, PostGIS and Quantum GIS
> To: jp.alcantara at geo-st.com, "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Cc: pcreso at pcreso.com
> Date: Saturday, May 9, 2009, 11:04 PM
> Hi Juan,
>
> First let me say the following:
>
> Please include the QGIS developers list in the further
> discussion, since
> this seem to be more QGIS related than Postgis related?
>
> In fact, the QGIS developers may have already changed the
> pkey behavior
> in more recent releases - so please include them in the
> discussion.
>
> Regarding OIDs: those aren't really stable. They increase
> until a
> certain limit is reached and then they start again at zero
> or one. Here
> is what the PostgreSQL manual says about the topic:
>
> "The oid type is currently implemented as an unsigned
> four-byte integer.
> Therefore, it is not large enough to provide database-wide
> uniqueness in
> large databases, or even in large individual tables. So,
> using a
> user-created table's OID column as a primary key is
> discouraged. OIDs
> are best used only for references to system tables."
> (http://www.postgresql.org/docs/8.4/static/datatype-oid.html)
>
> So to be on the safe side, I wouldn't use oids as primary
> keys.
>
> In addition, I don't know if oids are still the same if you
> dump your
> data and import it on another machine.
>
> After all, primary keys are used as unique, stable
> identifiers, which I
> don't think that oids can reliably deliver.
>
> Thanks,
> Andreas
>
> 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 <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 <table>_qkey_idx on
> <table>(qkey);"
> >>
> >> You can, of course, use your own names for the
> table, column & index....
> >>
> >>
> >> HTH,
> >>
> >> Brent Wood
> >>
> >>
> >>
> >> --- On Sat, 5/9/09, Ben Madin <ben at remoteinformation.com.au>
> wrote:
> >>
> >>
> >>> From: Ben Madin <ben at remoteinformation.com.au>
> >>> Subject: Re: [postgis-users] OIDS, PostGIS and
> Quantum GIS
> >>> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> >>> 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...
> >>>
> >>>
> >>>
>
>
More information about the postgis-users
mailing list