Interoperability : SERIAL or Explicit Sequence

[F T]

Hello,

I would like to design a Postgresql / Postgis database that would be the most interoperable as possible.
The spatial tables could be used by (Select, Insert, Update, Delete) :
- a Web GIS (Mapserver, geoserver, ...)
- commercial clients (Geoconcept, Mapinfo, FME, ESRI)
- Opensource clients (QGIS, GvSIG, ...).
Great dream Isn't it? ...

I know that some of these "soft" need to a numeric unique key (Mapserver for example don't deal with character keys...).
I know OID are depreciated.

I think is would be a good idea to add a unique numeric key to all my tables.
What is the best way to do that?

   - Add a SERIAL field (but it is PostgreSQL spécific?).
   - Add a numeric field explicitely managed by an associated sequence (it does the same but seems more opened?).

So SERIAL or EXPLICIT SEQUENCE?

Thanks for your responses

Fabrice


 




_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Greg Williamson-3]

Some parts of this message have been removed. Learn more about Nabble's security policy.

Fabrice --

I can't speak to Oracle directly, but Informix supports a SERIAL type (different mechanics under the hood but IIRC the port from Informix to postgres didn't have any issues with DDL declaring a SERIAL type. I'd go with that, personally, although BIGINT sequence might be needed if you anticipate enough rows to make it worth while.

When you dump the DDL for your database you'll see that something like "my_gid SERIAL PRIMARY KEY" gets broken out into the explicit sequence, but it all works out to the same thing.

I think MapServer handles SERIALs ok -- been a while since I worked with it though. ESRI doesn't work and play well with others, and I can't speak to the other applications.

HTH,

Greg W.

---

From: F T <oukile@...>
To: postgis-users@...
Sent: Fri, October 23, 2009 12:03:07 AM
Subject: [postgis-users] Interoperability : SERIAL or Explicit Sequence

Hello,

I would like to design a Postgresql / Postgis database that would be the most interoperable as possible.
The spatial tables could be used by (Select, Insert, Update, Delete) :
- a Web GIS (Mapserver, geoserver, ...)
- commercial clients (Geoconcept, Mapinfo, FME, ESRI)
- Opensource clients (QGIS, GvSIG, ...).
Great dream Isn't it? ...

I know that some of these "soft" need to a numeric unique key (Mapserver for example don't deal with character keys...).
I know OID are depreciated.

I think is would be a good idea to add a unique numeric key to all my tables.
What is the best way to do that?

   - Add a SERIAL field (but it is PostgreSQL spécific?).
   - Add a numeric field explicitely managed by an associated sequence (it does the same but seems more opened?).

So SERIAL or EXPLICIT SEQUENCE?

Thanks for your responses

Fabrice


 

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Ben Madin]

Fabrrice,

Check the PostgreSQL docs for this, but SERIAL is really just a  
convenience word when declaring a table, not a type. Thye column type  
will be integer.

If you subsequently pg_dump the table, it will dump the type as int  
with the sequence creation commands in the script.

Finally, I believe that you would want (or need) to specify SERIAL  
UNIQUE now to ensure primary key is unique.



--

Ben Madin
AusVet Animal Health Services
P.O. Box 5467
Broome   WA   6725
Australia

t : [phone]
f : [phone]
m : 0448 887 220
e : ben@...

AusVet's website:  http://www.ausvet.com.au

This transmission is for the intended addressee only and is  
confidential information. If you have received this transmission in  
error, please delete it and notify the sender. The contents of this  
email are the opinion of the writer only and are not endorsed by  
AusVet Animal Health Services unless expressly stated otherwise.  
Although AusVet uses virus scanning software we do not accept  
liability for viruses or similar in any attachments.

On 23/10/2009, at 19:03, F T <oukile@...> wrote:

> Hello,
>
> I would like to design a Postgresql / Postgis database that would be  
> the most interoperable as possible.
> The spatial tables could be used by (Select, Insert, Update, Delete) :
> - a Web GIS (Mapserver, geoserver, ...)
> - commercial clients (Geoconcept, Mapinfo, FME, ESRI)
> - Opensource clients (QGIS, GvSIG, ...).
> Great dream Isn't it? ...
>
> I know that some of these "soft" need to a numeric unique key  
> (Mapserver for example don't deal with character keys...).
> I know OID are depreciated.
>
> I think is would be a good idea to add a unique numeric key to all  
> my tables.
> What is the best way to do that?
>
>    - Add a SERIAL field (but it is PostgreSQL spécific?).
>    - Add a numeric field explicitely managed by an associated  
> sequence (it does the same but seems more opened?).
>
> So SERIAL or EXPLICIT SEQUENCE?
>
> Thanks for your responses
>
> Fabrice
>
>
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@...
> http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users