[postgis-users] How to implement a GUID

[Klaus Förster]

Hi Milo,

why don't you use NEXTVAL() and CURRVAL() to gain more control over your
sequence while splitting up the temporary table:

BEGIN;

INSERT INTO final_geom (SELECT NEXTVAL(seq1), the_geom FROM
geom_temp_table);
INSERT INTO final_attrib (SELECT CURRVAL(seq1), the_attrib FROM
geom_temp_table);

-- more to follow ...

COMMIT;

is this what you're looking for?

regards
Klaus

Milo van der Linden wrote:
> Hello Mark,
> 
> I am using your suggestion at this moment but the problem is as follows:
> 
> I do an insert into the final_geom table which then uses a new id
> but;
> 
> I need this id to do an insert into the final_attrib table!
> 
> Basically what I want to do is:
> 
> INSERT geom into table1 AND INSERT attrib1, attrib2, attrib3 into table2
> FROM temp_table.
> The resulting table1 and table2 need to have exactly the same ID.
> 
> I was first thinking about building an id in my temp_table and then do
> the 2 insert; But..my temp table is constantly created automatically by
> ogr2ogr, so I (seem to) have no influence on it's structure.
> 
> Mark Cave-Ayland schreef:
>> 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.
>>
>>   
> 
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-- 
         O-.
         /_ )
          | (U
klaus*   /|
        / /
        ~ ~