[postgis-users] alpha GT2 Web Map Server with PostGIS support

[Thomas, Cord]

I am not that familiar with Postgres system tables, but ....

This gets you some useful information on the table's primary key - 

select pc.relname, pi.indisprimary, pi.indkey from  pg_index pi join
pg_class pc on pc.relfilenode = pi.indexrelid where indrelid in (select
relfilenode from pg_class where relname='<name of table you are looking
at>') and pi.indisprimary = 't';

where pc.relname - is the name of the primary key (useful for debugging)
  pi.indisprimary - indicates whether the index is a primary key 
  pi.indkey - is a vector of fields in the index

the vector returned tells you the index of the primary key. if it is one
value, this is trivial.  if more, then becomes non-trivial.

to answer your question - you can say 'select *' and then know which field
is the primary key.

of course, there maybe other built-in functions i am not familiar with for
determining a table's primary key fields....  

cord

-----Original Message-----
From: cholmes at openplans.org [mailto:cholmes at openplans.org]
Sent: Friday, December 13, 2002 10:33 AM
To: PostGIS Users Discussion; Paul Ramsey
Subject: Re: [postgis-users] alpha GT2 Web Map Server with PostGIS
support


That sounds good, I'll work on implementing it.  The thought of using
the primary key definitely occured to me, but I didn't know exactly how
to go about doing it, so I decided to get this release out more quickly
and do the primary key as the next step.  I thought of using the oid,
which works fine for a wms, where you are just getting the features. 
But for a transactional web feature service it doesn't work as well. 
The problem arises when trying to add new features, since you'd have to
do an insert specifying the oid, which you're not allowed to (at least
to the best of my knowledge).  If someone wanted to add a new feature
and specified the feature id as 1556, they should be able to then do a
getFeature request with feature id = 1556 and get that feature back. 
But just inserting the feature would create an oid that is not 1556. 
And yes, I'll work on using the oid as a backup, and just have the
datasource throw errors if someone tries an modifyFeatures or
addFeatures.  My thinking is wfs-centric, but yeah, using oid's makes
good sense for wms's when the primary key is not available.

As for implementation, since I'm not a sql expert, and since I could
either dig around for a few hours or ask you all, is there a way I can
make sure that the primary key is the first column when I do a select
statement?  Basically the way the code works now is that our internal
feature representation is constructed by the names and types of the
columns.  Right now we do a 'select * from tablename', and then iterate
through the metadata of the resultset, getting column names and types
for each column, to represent each attribute in our internal feature
model.  Is it possible to do some sort of 'select * from tablename,
order primary_key first'?  (don't worry, I use the names of the columns
for the actual queries instead of the 'select *', this is just to get
the metadata when nothing is known about the db and the select is done
with a limit of one)  Or do I need to use the jdbc DatabaseMetaData to
retrieve the primary keys?  I guess I could switch the way we figure out
the columns to just using DatabaseMetaData functions, but I inherited
our current way, and it works well, so I don't really want to change it
if there's a solid work around.


            Thanks,


                 Chris

Quoting Paul Ramsey <pramsey at refractions.net>:

> A counter point to this one is that oids are *not* indexed by
> default. 
> The "best" first choice for a uid would be the column which is
> flagged 
> as the "primary key" as that column is both guaranteed to be unique
> and 
> indexed. Failing that, the oid would be a reasonable backup, since it
> is 
> at least guaranteed to be unique.
> 
> P.
> 
> chodgson at refractions.net wrote:
> > You might be better to use the OID value for the feature
> identifier. After all, 
> > it is entirely possible to have a table with only a geometry
> column. The OID 
> > value is what the mapserver connector uses by default (but you can
> specify 
> > another column if you want). The OID is an "invisible" column that
> postgres 
> > adds to all tables, if you didn't know - it's the "object
> identifier" and is at 
> > least unique for all of the rows in a given table.
> > 
> > I would highly recommend making this a configurable option, as your
> identifying 
> > column should probably have an index, as it is silly to have to
> index another 
> > column when you already have a perfectly good, indexed, unique
> value.
> > 
> > Chris Hodgson
> 
> 
> 
> -- 
>        __
>       /
>       | Paul Ramsey
>       | Refractions Research
>       | Email: pramsey at refractions.net
>       | Phone: (250) 885-0632
>       \_
> 
> 
> _______________________________________________
> 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