[gdal-dev] ogr2ogr, preserve_fid and Postgres sequences

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

[gdal-dev] ogr2ogr, preserve_fid and Postgres sequences

César Martínez
Hi,

When loading a layer on PostGIS using ogr2ogr, a sequence is created for the serial primary key.
If -preserve_fid paramenter is provided, the sequence start is not properly initialized (i. e. sequence starts on 1). This leads to problems if the layer is later edited. Is this an intended behaviour or should be considered a bug?

Note that I am using GDAL version 1.11.3.

Example:

# Using -preserve_fid
ogr2ogr -preserve_fid -update -f PostgreSQL "PG:host='localhost' dbname='test'" myshp.shp  myshp -nln "test1"

# SELECT last_value from test1_ogc_fid_seq ;
 last_value
------------
          1
(1 row)

# Not using -preserve_fid: sequence properly initialized to 102
ogr2ogr -update -f PostgreSQL "PG:host='localhost' dbname='test'" myshp.shp  myshp  -nln "test2"

# SELECT last_value from test2_ogc_fid_seq ;
 last_value
------------
        102
(1 row)

Thanks in advance,

César

_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: ogr2ogr, preserve_fid and Postgres sequences

jratike80
Hi César,

What are the problems you face when editing such layer? Do you have an
alternative suggestion about what to do for the sequence?

-Jukka Rahkonen-



--
Sent from: http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: ogr2ogr, preserve_fid and Postgres sequences

César Martínez
Hi Jukka, see answer inline:

2017-09-07 16:48 GMT+02:00 jratike80 <[hidden email]>:
Hi César,

What are the problems you face when editing such layer?


If you later try to add a new record to the layer without providing an id, it will raise an error, defeating the purpose of having a serial PK. For instance:

INSERT INTO test1 (wkb_geometry)
VALUES (ST_GeomFromText('MULTIPOINT(-5.34510248270791 36.1506091987899)', 4258)) ;

ERROR: duplicate key value violates unique constraint "test1_pkey"
SQL state: 23505
Detail: Key (ogc_fid)=(1) already exists.

They you must provide the ogc_fid by hand:

INSERT INTO test1 (ogc_fid, wkb_geometry)
VALUES (102, ST_GeomFromText('MULTIPOINT(-5.34510248270791 36.1506091987899)', 4258)) ;

 
Do you have an
alternative suggestion about what to do for the sequence?

The sequence should be initialized in the same way as it is done when -preserve_fid is not provided.
For the layer & sequence in the example, the following SQL statement would work:

SELECT set_val('test1_ogc_fid_seq', max(ogc_fid)) FROM test1 ;

César
 

-Jukka Rahkonen-



--
Sent from: http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev



--

_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: ogr2ogr, preserve_fid and Postgres sequences

Even Rouault-2

On jeudi 7 septembre 2017 17:05:36 CEST César Martínez wrote:

> Hi Jukka, see answer inline:

>

> 2017-09-07 16:48 GMT+02:00 jratike80 <[hidden email]>:

> > Hi César,

> >

> > What are the problems you face when editing such layer?

>

> If you later try to add a new record to the layer without providing an id,

> it will raise an error, defeating the purpose of having a serial PK. For

> instance:

>

> INSERT INTO test1 (wkb_geometry)

> VALUES (ST_GeomFromText('MULTIPOINT(-5.34510248270791 36.1506091987899)',

> 4258)) ;

>

> ERROR: duplicate key value violates unique constraint "test1_pkey"

> SQL state: 23505

> Detail: Key (ogc_fid)=(1) already exists.

>

> They you must provide the ogc_fid by hand:

>

> INSERT INTO test1 (ogc_fid, wkb_geometry)

> VALUES (102, ST_GeomFromText('MULTIPOINT(-5.34510248270791

> 36.1506091987899)', 4258)) ;

>

> > Do you have an

> > alternative suggestion about what to do for the sequence?

>

> The sequence should be initialized in the same way as it is done when

> -preserve_fid is not provided.

> For the layer & sequence in the example, the following SQL statement would

> work:

>

> SELECT set_val('test1_ogc_fid_seq', max(ogc_fid)) FROM test1 ;

 

Would you mind opening a ticket in Trac about that

https://trac.osgeo.org/gdal/newticket

 

Even

 

--

Spatialys - Geospatial professional services

http://www.spatialys.com


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: ogr2ogr, preserve_fid and Postgres sequences

César Martínez
Done, thanks for the support:

https://trac.osgeo.org/gdal/ticket/7032

César

2017-09-07 17:41 GMT+02:00 Even Rouault <[hidden email]>:

On jeudi 7 septembre 2017 17:05:36 CEST César Martínez wrote:

> Hi Jukka, see answer inline:

>

> 2017-09-07 16:48 GMT+02:00 jratike80 <[hidden email]>:

> > Hi César,

> >

> > What are the problems you face when editing such layer?

>

> If you later try to add a new record to the layer without providing an id,

> it will raise an error, defeating the purpose of having a serial PK. For

> instance:

>

> INSERT INTO test1 (wkb_geometry)

> VALUES (ST_GeomFromText('MULTIPOINT(-5.34510248270791 36.1506091987899)',

> 4258)) ;

>

> ERROR: duplicate key value violates unique constraint "test1_pkey"

> SQL state: 23505

> Detail: Key (ogc_fid)=(1) already exists.

>

> They you must provide the ogc_fid by hand:

>

> INSERT INTO test1 (ogc_fid, wkb_geometry)

> VALUES (102, ST_GeomFromText('MULTIPOINT(-5.34510248270791

> 36.1506091987899)', 4258)) ;

>

> > Do you have an

> > alternative suggestion about what to do for the sequence?

>

> The sequence should be initialized in the same way as it is done when

> -preserve_fid is not provided.

> For the layer & sequence in the example, the following SQL statement would

> work:

>

> SELECT set_val('test1_ogc_fid_seq', max(ogc_fid)) FROM test1 ;

 

Would you mind opening a ticket in Trac about that

https://trac.osgeo.org/gdal/newticket

 

Even

 

--

Spatialys - Geospatial professional services

http://www.spatialys.com


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev



--

_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev