[GIS] a suitable Global/Universal Unique Identifier for a PostGIS database

postgispostgresqlspatial-database

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.

Conceptual diagram

Any tips?

Best Answer

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

Related Question