[postgis-users] How to implement a GUID

[Mark Cave-Ayland]

On Tue, 2007-07-24 at 12:49 +0200, Milo van der Linden wrote:
> Hello list!
> 
> I have been thinking my head off trying to figure it out and
> documentation won't help me.
> 
> Yesterday I recieved the tip to use a GUID as a database wide unique
> key. Well, I think it is the only option I have.
> 
> I import a shape file into a temp table.
> >From this temp table is then insert the content into 2 other tables:
> final_attributes and final_geom
> 
> Final_attributes should contain all the attribute fields plus a column
> to connect it to final_geom
> Final_geom should contain a column to connect to Final_attributes plus
> the_geom.
> 
> Because Final_attributes is a table that also connects to other
> geom_tables; it needs a id that is unique over 3 or even more tables.
> 
> If I can generate GUIDs with php, Is that a good option?
> Is there a way to use the geometry column to generate some sort of
> unique ID? I would also like to check if the geometry is unique within
> it's final_geom table!
> 
> What datatype should I use to store the GUID?
> 
> I cannot figure it out... please lend me a hand

Hi Milo,

Can you not just use a sequence to have a unique key across multiple
tables as suggested by Stephen? For example:


CREATE SEQUENCE seq1;

CREATE TABLE t1 AS (
	id int8 default nextval('seq1'),
	the_geom geometry
);


CREATE TABLE t2 AS (
	id int8 default nextval('seq1'),
	the_geom geometry
);

INSERT INTO t1 SELECT the_geom FROM geom_temp_table;
INSERT INTO t2 SELECT the_geom FROM geom_temp_table;

If you then do:

	SELECT id FROM t1;
	SELECT id FROM t2;

you should find that id is unique across both tables since they share
the same sequence.


Kind regards,

Mark.

-- 
ILande - Open Source Consultancy
http://www.ilande.co.uk