What is a suitable Global/Universal Unique Identifier for a PostGIS database?

Question

I have read that using OIDs as a primary key in a postgreSQL/PostGIS db is poor practice because there are instances where these can be reset. Sounds logical, but then what is a suitable alternative? I believe there is an option to use a "Universal Unique Identifer" UUID, but the large text and number value that spits out is horrible.

Just a bit more background to my situation. I have all of my spatial tables created with a field called "gid" which is the primary key for that table and unique only to that table. I have an issue now because I want to relate my spatial tables (all with a "gid" field starting at 1 and incrementing) to one large table with the related information. Obviously for my relationship to work all of my spatial features need a unique identifier which differentiates them from one another.

EDITED Added this image as per Peters' comment. Peter this is the idea I have in my head, it may not be the best way to go about it or it might not even be good db design. I am interested in what you think.

Any tips?

Answer 1

I would create separate intermediary tables buildings_attach, parcels_attach, etc. Then you don't need a global identifier.

Answer 2

Two Solutions:

1)Create a single sequence and make all the tables use that sequence, can be done from the beginning or you may create a ID column and update your tables now.

To Create the sequence:

CREATE SEQUENCE universal_sequence;

Then a table:

CREATE TABLE (
colname integer NOT NULL DEFAULT nextval('universal_sequence'));

To Update an existing table id field with new IDs (do it for all the tables that you want to follow the same sequence):

UPDATE table1
SET id=nextval('universal_sequence'));

2)The other solution: Create a temporary sequence and them run the query creating a new ID column.

More here: http://www.postgresql.org/docs/8.4/static/sql-createsequence.html

Answer 3

The best option is the UUID or GUID. They are built for this reason, globally unique no matter what table. Ugly? Yes but they are the best for this situation.

See https://stackoverflow.com/questions/294933/generate-unique-id-to-share-with-multiple-tables-sql-2008

I have seen methods where people use data from the table to make IDs eg col1 + somestring + col2, I would really really adivse against this (see here). Intelligent IDs are a really bad idea.

Answer 4

Hallo

Why don't you take the id from the big table and put in the spatial tables instead?

If one row in one of the spatial tables relates to multiple rows in the big table I see the problem, otherwise the big table id should be enough, or am I missing something.

/Nicklas