[postgis-users] OIDS, PostGIS and Quantum GIS

[pcreso at pcreso.com]

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
>