[postgis-users] OIDS, PostGIS and Quantum GIS
Simon Greener
simon at spatialdbadvisor.com
Sun May 10 16:36:46 PDT 2009
Folks,
Not being a PostgreSQL expert I can't comment about OIDs except to say that, from the discussion so far it appears that:
1. They are numeric (32 bit integers)
2. Will restart numbering once Max(32BitInteger) is reached (though very few database tables with transactional spatial data would hit this limit in the short or medium term).
3. OIDs may be dropped by the PostgreSQL team at some stage (a virtual concern).
That data in relational databases should have primary keys (and other contraints) is a given that all on this forum acknowledge. However, what you cannot assume is that the primary key of any one table will be a single, integer, column. A primary key may be:
A. Text, Timestamp, Float or Integer etc datatypes.
B. May include multiple attributes.
Generally, GIS like SINGLE column INTEGER primary keys or unique columns/indexes.
I have yet to find a GIS that supported anything else. (I am keen to hear of one that does support A and B above.)
So, where an existing primary key is not based on a single integer column, for GIS access one is left with limited options:
1. Add a new unique integer column (identity or trigger populated from a sequence) - This may not be possible is the data modeller / application does not want this done.
2. Or use the OID.
Interesting discussion.
regards
Simon
On Sun, 10 May 2009 06:12:29 +1000, <pcreso at pcreso.com> wrote:
>
> 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...
>> >>>
>> >>>
>> >>>
>>
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
--
SpatialDB Advice and Design, Solutions Architecture and Programming,
Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional
Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist.
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.
Website: www.spatialdbadvisor.com
Email: simon at spatialdbadvisor.com
Voice: +61 362 396397
Mobile: +61 418 396391
Skype: sggreener
Longitude: 147.20515 (147° 12' 18" E)
Latitude: -43.01530 (43° 00' 55" S)
NAC:W80CK 7SWP3
More information about the postgis-users
mailing list