[Gdal-dev] Re: ogr2ogr with PostgreSQL 8.1

[Charlie Savage]

 From the postgresql manuals:

The oid type is currently implemented as an unsigned four-byte integer. 
Therefore, it is not large enough to provide database-wide uniqueness in 
large databases, or even in large individual tables. So, using a 
user-created table's OID column as a primary key is discouraged. OIDs 
are best used only for references to system tables.

This seems like something better left to the database designer...

Charlie

R. Paul Warriner wrote:
> Bart,
> 
> I found the same to be true. PostGIS lists had some discussion about 
> this last year, as 8.1 was due out, but the topic seemed to drop off.
> 
> It appears you can use anything for the key, but that sort of leaves it 
> up to the developer to make sure the identifier is not duplicated 
> somewhere in another table being joined. As the non guru I am, it 
> appears merging tables, that duplicate IDs, could pose a serious 
> problem, not to mention what it might be doing with the geometry_columns 
> table, or other geometry funtions, if duplicated.
> 
> I wonder if the OID drop was a performance or limits issue, and the 
> PostgreSQL developers felt it was better left to the database designers.
> 
> Regards,
> Paul
> 
> -----Original Message-----
> From: Bart van den Eijnden (OSGIS) [mailto:bartvde at xs4all.nl]
> Sent: Monday, January 16, 2006 10:55 AM
> To: 'Bart van den Eijnden (OSGIS)'
> Cc: gdal-dev at lists.maptools.org
> Subject: Re: [Gdal-dev] ogr2ogr with PostgreSQL 8.1
> 
> Sorry, there seems no apparent need since in the MAP file the unique
> column is overrideable, this is my use case which works fine:
> 
> CONNECTIONTYPE POSTGIS
> CONNECTION "user=postgres dbname=gisarts host=127.0.0.1 port=5432
> password=*****"
> DATA "wkb_geometry from percelen_region USING UNIQUE ogc_fid"
> 
> Best regards,
> Bart
> 
> Bart van den Eijnden (OSGIS) wrote:
> 
>  > Hi list,
>  >
>  > shouldn't the tables created by ogr2ogr explicitly specify WITH OIDS?
>  > AFAIK all the Mapserver SQL assumes there is an OID column. I am using
>  > the latest FWTools.
>  >
>  > CREATE TABLE percelen_region
>  > (
>  > ogc_fid int4 NOT NULL DEFAULT
>  > nextval('percelen_region_ogc_fid_seq'::regclass),
>  > wkb_geometry geometry,
>  > aanduid char(12),
>  > gemcod char(5),
>  > sectie char(2),
>  > nummer char(5),
>  > secnum char(7),
>  > hoek numeric(8,3),
>  > CONSTRAINT enforce_dims_wkb_geometry CHECK (ndims(wkb_geometry) = 2),
>  > CONSTRAINT enforce_geotype_wkb_geometry CHECK
>  > (geometrytype(wkb_geometry) = 'MULTIPOLYGON'::text OR wkb_geometry IS
>  > NULL),
>  > CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(wkb_geometry) = -1)
>  > )
>  > WITHOUT OIDS;
>  >
>  > Best regards,
>  > Bart
>  >
> 
> 
> -- 
> Bart van den Eijnden
> OSGIS, Open Source GIS
> http://www.osgis.nl
> 
> _______________________________________________
> Gdal-dev mailing list
> Gdal-dev at lists.maptools.org
> http://lists.maptools.org/mailman/listinfo/gdal-dev
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Gdal-dev mailing list
> Gdal-dev at lists.maptools.org
> http://lists.maptools.org/mailman/listinfo/gdal-dev