[postgis-users] How to implement a GUID

[Milo van der Linden]

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.
>
>   


-- 


	

Milo van der Linden
mlinden at zeelandnet.nl <mailto:mlinden at zeelandnet.nl>
milovanderlinden at gmail.com <mailto:milovanderlinden at gmail.com>
milo at 3dsite.nl <mailto:milo at 3dsite.nl>
http://www.3dsite.nl

	  	

De informatie in dit bericht reflecteerd mijn persoonlijke mening en
niet die van een bedrijf of instantie. Aan de informatie kunnen geen
rechten worden ontleend. Indien dit bericht onderdeel is van een forum,
mailing-list of community dan gelden automatisch de bijbehorende
voorwaarden.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070724/a114aa90/attachment.html>